SQLite

python built-in sqlite3 module and you can import it directly.

import sqlite3

Create a Connection object

Use sqlite3.connect() function to connect a database, the suffix of the database file is <filename>.db. If it does not exist, it will be created automatically.

conn = sqlite3.connect('RottenTangerine.db')

or you can create a database in memory. this method would not create database file in your local storage.

conn = sqlite3.connect(':memory:')

Create a cursor object

We need to create a cursor object in the connection object to execute sql statements.

cur = conn.cursor()

cur.excute('<sql_text>')

:warning: Syntax error caused by using format string:

f"INSERT INTO alltest (test_name, author, answer) VALUES ({data_dict['test_name']}, {data_dict['author']}, NULL);"

You need to add additional quotes to make SQL statement syntaxial correct

In addition, this SQL statement cannot prevent SQL injections. I strongly recommend using parameterized queries. please refer this way.

Defending against SQL injection? · Issue #57 · TryGhost/node-sqlite3 (github.com)

• To select data from a table using a string as a parameter:

import sqlite3
conector = sqlite3.connect("database.db")
cursor = conector.cursor()
string = "Some interesting stuff"
cursor.execute("SELECT * FROM table WHERE item = ?", [string])

• To update data in a table using two parameters:

import sqlite3
conector = sqlite3.connect("database.db")
cursor = conector.cursor()
salary = 5000
id = 1
sql_parameterized_query = """Update employee set Salary = ? where id = ?"""
cursor.execute(sql_parameterized_query, (salary, id))

• To check if a user exists in a table using two parameters:

import sqlite3
conector = sqlite3.connect("database.db")
cursor = conector.cursor()
var1 = "password123"
var2 = "user123"
sql = "select exists (SELECT * from USERS where PASSWORD = ? AND USERNAME = ?)"
args = (var1,var2)
cursor.execute(sql, args)

Get query result

Get all query result

out = cur.fetchall()

Get the first query result

out = cur.fetchone()

Save Changes

You need to commit your changes to save the data in the database

conn.commit()

Quit

After using the database, you need to close the cursor and the connection of the database

cur.close()
conn.close()

SQLite and Pandas

DataFrame to SQL

Official documentation: pandas.DataFrame.to_sql — pandas 1.5.3 documentation (pydata.org)

import sqlite3
import pandas as pd

conn = sqlite3.connect('test.db')

df = pd.DataFrame(data=[[0, '10/11/12'], [1, '12/11/10']],
                  columns=['int_column', 'date_column'])
df.to_sql('test_data', conn)  # ::Note:: first argument is the table name

SQL to DataFrame

Official documentation: pandas.read_sql — pandas 1.5.3 documentation (pydata.org)

(You need to know the table name first)

import sqlite3
import pandas as pd

conn = sqlite3.connect('test.db')

df = pd.read_sql('SELECT * FROM test_data', conn)
print(df)

int_column date_column
0           0    10/11/12
1           1    12/D11/10

Get all table name

cur = conn.cursor()
cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
out = cur.fetchone()
print(out)

Date parsing

Apply date parsing to columns through the parse_dates argument

pd.read_sql('SELECT int_column, date_column FROM test_data',
            conn,
            parse_dates=["date_column"])

Enhance Date parsing

pd.read_sql('SELECT int_column, date_column FROM test_data',
            conn,
            parse_dates={"date_column": {"errors": "ignore","format": "%d/%m/%y"}})

Image Storing

There are two ways to store your images in the database:

• File name or File PATH
• Convert image to base64 string and store it

Conv to Base64 (Python)

import base64

# encode
base64_txt = base64.b64encode(open("<img_name>.jpg","rb").read())

# decode and store
open('<new_img_name>.jpg', 'w').write(base64.decodestring(base64_txt))

Python class storing

If your own defined python class is like this. you can use __dict__ to get the object's variable dictionary.

class Student:
    def __init__(self, name, student_id, gender):
        self.name = name
        self.student_id = student_id
        self.batch = gender

You can easily convert this dictionary to json format or store it in pandas DataFrame.

import json

s = Student('Eden Xiang', 'p1908370', 'M')

jsonstr = json.dumps(s.__dict__)