SQL Cheat Sheet: Accessing Databases using Python
SQLite
Topic Syntax Description Example
Create a new database and import sqlite3
open a database con = sqlite3.connect("INSTRUCTOR.db")
connection to allow
sqlite3 to work with it.
Call sqlite3.connect()
connect() sqlite3.connect() to create a connection to
the database
INSTRUCTOR.db in the
current working directory,
implicitly creating it if it
does not exist.
cursor_obj = con.cursor()
To execute SQL
statements and fetch
results from SQL queries,
cursor() con.cursor()
use a database cursor. Call
con.cursor() to create the
Cursor.
The execute method in
Python's SQLite library
allows to perform SQL cursor_obj.execute('''insert into INSTRUCTOR values (1, 'Rav', 'Ahuja
commands, including
retrieving data from a
table using a query like
"Select * from
execute() cursor_obj.execute()
table_name." When you
execute this command, the
result is obtained as a
collection of table data
stored in an object,
typically in the form of a
list of lists.
statement = '''SELECT * FROM INSTRUCTOR'''
cursor_obj.execute(statement)
output_all = cursor_obj.fetchall()
for row_all in output_all:
print(row_all)
The fetchall() method
in Python retrieves all the
fetchall() cursor_obj.fetchall() rows from the result set of
a query and presents them
as a list of tuples.
statement = '''SELECT * FROM INSTRUCTOR'''
cursor_obj.execute(statement)
The fetchmany() method output_many = cursor_obj.fetchmany(2)
retrieves the subsequent for row_many in output_many:
print(row_many)
group of rows from the
result set of a query rather
than just a single row. To
fetchmany() cursor_obj.fetchmany()
fetch a few rows from the
table, use
fetchmany(numberofrows)
and mention how many
rows you want to fetch.
read_sql_query() is a df = pd.read_sql_query("select * from instructor;", conn)
function provided by the
Pandas library in Python,
and it is not specific to
MySQL. It is a generic
read_sql_query() read_sql_query() function used for
executing SQL queries on
various database systems,
including MySQL, and
retrieving the results as a
Pandas DataFrame.
df.shape
It provides a tuple
indicating the shape of a
DataFrame or Series,
shape dataframe.shape
represented as (number of
rows, number of
columns).
con.close() is a method
used to close the
connection to a MySQL con.close()
database. When called, it
terminates the connection,
releasing any associated
resources and ensuring the
close() con.close()
connection is no longer
active. This is important
for managing database
connections efficiently
and preventing resource
leaks in your MySQL
database interactions.
The CREATE TABLE CREATE TABLE INTERNATIONAL_STUDENT_TEST_SCORES ( <br>
statement is used to define country VARCHAR(50), <br>
and create a new table first_name VARCHAR(50), <br>
within a database. It last_name VARCHAR(50), <br>
specifies the table's name, test_score INT
);
the structure of its
CREATE TABLE table_name ( columns (including data
CREATE column1 datatype
types and constraints), and
TABLE constraints, column2
datatype constraints, ... ); any additional properties
such as indexes. This
statement essentially sets
up the blueprint for
organizing and storing
data in a structured format
within the database.
seaborn.barplot() is a
import seaborn
function in the Seaborn seaborn.barplot(x='Test_Score',y='Frequency', data=dataframe)
Python data visualization
library used to create a bar
plot, also known as a bar
seaborn.barplot(x="x-
chart. It is particularly
barplot() axis_variable", y="y-
axis_variable", data=data) used to display the
relationship between a
categorical variable and a
numeric variable by
showing the average value
for each category.
import pandas
read_csv() is a function df = pandas.read_csv('https://data.cityofchicago.org/resource/jcxq-k9
in Python's Pandas library
used for reading data from
a Comma-Separated
df = Values (CSV) file and
read_csv() pd.read_csv('file_path.csv') loading it into a Pandas
DataFrame. It's a common
method for working with
tabular data stored in CSV
format
import pandas
df.to_sql() is a method df = pandas.read_csv('https://data.cityofchicago.org/resource/jcxq-k9
in Pandas, a Python data df.to_sql("chicago_socioeconomic_data", con, if_exists='replace', ind
manipulation library used
to write the contents of a
df.to_sql('table_name', DataFrame to a SQL
to_sql() index=False) database. It allows to take
data from a DataFrame
and store it structurally
within a SQL database
table.
read_sql() df = pd.read_sql(sql_query, read_sql() is a function selectQuery = "select * from INSTRUCTOR"
conn) provided by the Pandas df = pandas.read_sql(selectQuery, conn)
library in Python for
executing SQL queries
and retrieving the results
into a DataFrame from an
SQL database. It's a
convenient way to
integrate SQL database
interactions into your data
analysis workflows.
Db2
Topic Syntax Description Example
import ibm_db
ibm_db.connect() is a conn = ibm_db.connect('DATABASE=mydb;
Python function provided HOST=example.com;PORT=50000;UID=myuser;
by the ibm_db library, PWD=mypassword;', '', '')
which is used for
conn = establishing a connection
ibm_db.connect('DATABASE=dbname;
connect() HOST=hostname;PORT=port;UID=username;
to an IBM Db2 or IBM
PWD=password;', '', '') Db2 Warehouse database.
It's commonly used in
applications that need to
interact with IBM Db2
databases from Python.
server = ibm_db.server_info(conn)
print ("DBMS_NAME: ", server.DBMS_NAME)
print ("DBMS_VER: ", server.DBMS_VER)
ibm_db.server_info(conn) print ("DB_NAME: ", server.DB_NAME)
is a Python function
provided by the ibm_db
server_info() ibm_db.server_info() library, which is used to
retrieve information about
the IBM Db2 server to
which you are connected.
con.close() is a method
used to close the
connection to a db2 con.close()
database. When called, it
terminates the connection,
releasing any associated
resources and ensuring the
close() con.close()
connection is no longer
active. This is important
for managing database
connections efficiently
and preventing resource
leaks in your db2 database
interactions.
ibm_db.exec_immediate() # Lets first drop the table INSTRUCTOR in case it exists fr
is a Python function dropQuery = "drop table INSTRUCTOR"
provided by the ibm_db dropStmt = ibm_db.exec_immediate(conn, dropQuery)
library, which is used to
sql_statement = "SQL statement goes execute an SQL statement
here" immediately without the
exec_immediate() stmt = ibm_db.exec_immediate(conn,
need to prepare or bind it.
sql_statement)
It's commonly used for
executing SQL statements
that don't require input
parameters or don't need
to be prepared in advance.
Author(s)
Abhishek Gagneja
D.M Naidu
