08/10/2024 09:39 about:blank

SQL Cheat Sheet: Accessing Databases using Python

SQLite

Topic Syntax Description Example

Create a new database and open
a database connection to allow 1. 1
sqlite3 to work with it. Call 2. 2
[Link]() to
connect() [Link]() create a connection to the 1. import sqlite3
database [Link] in 2. con = [Link]("[Link]")
the current working directory,
implicitly creating it if it does Copied!
not exist.
To execute SQL statements and 1. 1
fetch results from SQL queries,
cursor() [Link]() use a database cursor. Call 1. cursor_obj = [Link]()
[Link]() to create the
Cursor. Copied!
The execute method in
Python's SQLite library allows
to perform SQL commands,
including retrieving data from a 1. 1
table using a query like "Select
execute() cursor_obj.execute() * from table_name." When you 1. cursor_obj.execute('''insert into INSTRUCTOR values (1, 'Rav', 'Ahuja', 'TORONTO', 'CA')''')
execute this command, the
result is obtained as a Copied!
collection of table data stored
in an object, typically in the
form of a list of lists.
1. 1
2. 2
3. 3
4. 4
The fetchall() method in 5. 5
Python retrieves all the rows
fetchall() cursor_obj.fetchall() from the result set of a query 1. statement = '''SELECT * FROM INSTRUCTOR'''
and presents them as a list of 2. cursor_obj.execute(statement)
tuples. 3. output_all = cursor_obj.fetchall()
4. for row_all in output_all:
5. print(row_all)

Copied!
1. 1
2. 2
3. 3
The fetchmany() method
4. 4
retrieves the subsequent group
of rows from the result set of a 5. 5
query rather than just a single
fetchmany() cursor_obj.fetchmany() row. To fetch a few rows from 1. statement = '''SELECT * FROM INSTRUCTOR'''
the table, use 2. cursor_obj.execute(statement)
fetchmany(numberofrows) and 3. output_many = cursor_obj.fetchmany(2)
mention how many rows you 4. for row_many in output_many:
want to fetch. 5. print(row_many)

Copied!
read_sql_query() is a
function provided by the
Pandas library in Python, and it 1. 1
is not specific to MySQL. It is
a generic function used for 1. df = pd.read_sql_query("select * from instructor;", conn)
read_sql_query() read_sql_query()
executing SQL queries on
various database systems,
including MySQL, and
Copied!
retrieving the results as a
Pandas DataFrame.
1. 1
It provides a tuple indicating
[Link] the shape of a DataFrame or 1. [Link]
shape
Series, represented as (number
of rows, number of columns).
Copied!
[Link]() is a method
used to close the connection to
a MySQL database. When
called, it terminates the
connection, releasing any 1. 1
associated resources and
close() [Link]() ensuring the connection is no 1. [Link]()
longer active. This is important
for managing database Copied!
connections efficiently and
preventing resource leaks in
your MySQL database
interactions.
1. 1
2. 2
The CREATE TABLE statement 3. 3
is used to define and create a 4. 4
new table within a database. It 5. 5
specifies the table's name, the 6. 6
CREATE TABLE table_name ( structure of its columns
CREATE column1 datatype constraints, (including data types and 1. CREATE TABLE INTERNATIONAL_STUDENT_TEST_SCORES ( <br>
TABLE column2 datatype constraints, constraints), and any additional 2. country VARCHAR(50), <br>
... ); properties such as indexes. This 3. first_name VARCHAR(50), <br>
statement essentially sets up the 4. last_name VARCHAR(50), <br>
blueprint for organizing and 5. test_score INT
storing data in a structured
6. );
format within the database.

Copied!
[Link]() is a
function in the Seaborn Python 1. 1
data visualization library used 2. 2
[Link](x="x- to create a bar plot, also known
axis_variable", y="y- as a bar chart. It is particularly 1. import seaborn
barplot()
used to display the relationship 2. [Link](x='Test_Score',y='Frequency', data=dataframe)
axis_variable", data=data)
between a categorical variable
and a numeric variable by
showing the average value for Copied!
each category.
read_csv() df = read_csv() is a function in 1. 1
pd.read_csv('file_path.csv') Python's Pandas library used 2. 2
for reading data from a
Comma-Separated Values 1. import pandas
(CSV) file and loading it into a 2. df = pandas.read_csv('[Link]
Pandas DataFrame. It's a
common method for working
Copied!

about:blank 1/2
08/10/2024 09:39 about:blank
with tabular data stored in CSV
format
1. 1
df.to_sql() is a method in
2. 2
Pandas, a Python data
manipulation library used to 3. 3

df.to_sql('table_name', write the contents of a

to_sql() DataFrame to a SQL database. 1. import pandas
index=False) 2. df = pandas.read_csv('[Link]
It allows to take data from a
DataFrame and store it 3. df.to_sql("chicago_socioeconomic_data", con, if_exists='replace', index=False,method="multi")
structurally within a SQL
database table. Copied!
read_sql() is a function
1. 1
provided by the Pandas library
in Python for executing SQL 2. 2

df = pd.read_sql(sql_query, queries and retrieving the

read_sql() results into a DataFrame from 1. selectQuery = "select * from INSTRUCTOR"
conn) 2. df = pandas.read_sql(selectQuery, conn)
an SQL database. It's a
convenient way to integrate
SQL database interactions into Copied!
your data analysis workflows.

Db2

Topic Syntax Description Example

1. 1
2. 2
ibm_db.connect() is a Python 3. 3
function provided by the ibm_db library, 4. 4
conn = ibm_db.connect('DATABASE=dbname; which is used for establishing a
connect() HOST=hostname;PORT=port;UID=username; connection to an IBM Db2 or IBM Db2 1. import ibm_db
PWD=password;', '', '') Warehouse database. It's commonly used 2. conn = ibm_db.connect('DATABASE=mydb;
in applications that need to interact with 3. HOST=[Link];PORT=50000;UID=myuser;
IBM Db2 databases from Python. 4. PWD=mypassword;', '', '')

Copied!
1. 1
2. 2
3. 3
ibm_db.server_info(conn) is a 4. 4
Python function provided by the ibm_db
server_info() ibm_db.server_info() 1. server = ibm_db.server_info(conn)
library, which is used to retrieve
information about the IBM Db2 server to 2. print ("DBMS_NAME: ", server.DBMS_NAME)
which you are connected. 3. print ("DBMS_VER: ", server.DBMS_VER)
4. print ("DB_NAME: ", server.DB_NAME)

Copied!
[Link]() is a method used to close
the connection to a db2 database. When
called, it terminates the connection, 1. 1
releasing any associated resources and
close() [Link]() ensuring the connection is no longer 1. [Link]()
active. This is important for managing
database connections efficiently and Copied!
preventing resource leaks in your db2
database interactions.
1. 1
ibm_db.exec_immediate() is a 2. 2
sql_statement = "SQL statement goes Python function provided by the ibm_db 3. 3
here" library, which is used to execute an SQL
statement immediately without the need 1. # Lets first drop the table INSTRUCTOR in case it exists from a previous attempt.
exec_immediate() stmt = ibm_db.exec_immediate(conn,
to prepare or bind it. It's commonly used 2. dropQuery = "drop table INSTRUCTOR"
sql_statement) for executing SQL statements that don't 3. dropStmt = ibm_db.exec_immediate(conn, dropQuery)
require input parameters or don't need to
be prepared in advance.
Copied!

Author(s)
Abhishek Gagneja

D.M Naidu

about:blank 2/2