import sqlite3

# Connect Database (Create an instance of database interface)
db = sqlite3.connect(":memory:")

# Create a cursor for Query
cur = db.cursor()
# Execute a query
cur.execute("SELECT sqlite_version()")
# Read the query results
# cur.fetchone can fetch the first row of results
version = cur.fetchone()[0]
print(f"SQLite version {version}")

# Close curor and database
cur.close()
db.close()

db = sqlite3.connect("test.db")
cur = db.cursor()

cur.execute('''
Create table if not exists tb (
    id Integer Primary Key,
    a Text, 
    b Text
)
''')
cur.execute("Insert into tb (a, b) values ('one', 'two')")
cur.execute("Insert into tb (a, b) values ('three', 'four')")
db.commit() # commit the change

cur.execute("Select * from tb")

# 1. Read rows one by one
row = cur.fetchone()
while row: # 每执行一次，便往下读一行，直到读完
    print(row)
    row = cur.fetchone()

# 2. Read exact number of rows
rows = cur.fetchmany(3)
print(rows)

# 3. Read all rows and iterrate
rows = cur.fetchall()
print(rows)

>>> 
[(1, 'one', 'two'), (2, 'three', 'four')]

query = "Select * from tb"
df = pd.read_sql(query, db)

cur.execute("Select * from tb")

query = "Select * from tb where a = ?"
cur.execute(query, ('one',)) # 这里小括号内有个逗号是因为 python 本身的机制: 单个元素的 tuple 如果不带逗号就不会被当作一个 tuple

cur.execute('''
Create table if not exists tb (
    id Integer Primary Key,
    a Text, 
    b Text
)
''')
values = [
    ['one', 'two'],
    ['three', 'four']
]
insert_statement = "Insert into tb (a, b) values (?, ?)"
cur.executemany(insert_statement, values)