Python SQLite开发

安装

$ sudo apt-get update
$ sudo apt-get install sqlite3 libsqlite3-dev

$ sqlite3 --version
3.40.1 2022-12-28 14:03:47 df5c253c0b3dd24916e4ec7cf77d3db5294cc9fd45ae7b9c5e82ad8197f38a24

$ sqlite3

> create table tb1(id int, name char(10));

> insert into tb1(id,name) values(1,'data 1');
> insert into tb1(id,name) values(2,'data 2');
> insert into tb1(id,name) values(3,'data 3');

> select * from tb1;

> .exit

python访问sqlite3

python3的标准库就有sqlite3。

• 打开sqlite3数据库

import sqlite3

db = 'pydb.db'
conn = sqlite3.connect(db)
print('connected')
print(conn)
conn.close()

执行

$ python test3.py
connected
<sqlite3.Connection object at 0x7fe49a27a540>

• CRUD

import sqlite3
from datetime import datetime
from prettytable import PrettyTable

def open_database():
    db = 'pydb.db'
    print('Connecting to SQLite...')
    conn = sqlite3.connect(db)
    print('connected')
    return conn

def create_table(conn):
    cursor = conn.cursor()
    sql = ''' create table if not exists product(
    id integer primary key autoincrement,
    name char(30) not null,
    stock integer,
    price float,
    created datetime
    )'''
    cursor.execute(sql)
    conn.commit()
    print('created a table')
    
def create_data(conn):
    cursor = conn.cursor()
    print('inserting data...')
    insert_sql = ("INSERT INTO product "
    "(name, stock, price, created) "
    "VALUES(?, ?, ?, ?)")    
    for i in range(1,6):
        params = ("product " + str(i), 3+i*4, 0.4+i*8, datetime.now())
        cursor.execute(insert_sql, params)
        product_id = cursor.lastrowid
        print('inserted with id=', product_id)
    conn.commit()
    cursor.close()
    print('done')

def read_data(conn):
    print('reading data')
    cursor = conn.cursor()
    cursor.execute("select id, name, stock, price, created from product")
    t = PrettyTable(['ID','Name', 'Stock', 'Price','Created'])
    for (id, name, stock, price, created) in cursor:
        t.add_row([id, name, stock, format(price,'.2f'), created])
    print(t)
    cursor.close()
    print('done')
        
def update_data(conn,id,product_name,stock,price):
    print('updating data for product id=' + str(id))
    update_sql = ("UPDATE product SET name=?, stock=?,price=? WHERE id=?")
    cursor = conn.cursor()
    params = (product_name,stock,price,id,)
    cursor.execute(update_sql, params)
    print(cursor.rowcount, ' products updated')
    conn.commit()
    cursor.close()
    print('done')
    

def delete_data(conn,id):
    print('deleting data with id=' + str(id))
    cursor = conn.cursor()
    
    params = (id,)
    cursor.execute("delete from product where id=?", params)
    print(cursor.rowcount, ' product deleted')
    conn.commit()
    
    cursor.close()
    print('done')



# open data
conn = open_database()
# creating table demo
create_table(conn)
create_data(conn)
read_data(conn)

# updating data demoprint('updating data demo')
id = 3
product_name = 'updated name'
stock = 10
price = 0.9
update_data(conn,id, product_name, stock, price)
read_data(conn)

print('deleting data demo')
delete_data(conn,id)
read_data(conn)

# close data
conn.close()

执行

$ python test3.py
Connecting to SQLite...
connected
created a table
inserting data...
inserted with id= 11
inserted with id= 12
inserted with id= 13
inserted with id= 14
inserted with id= 15
done
reading data
+----+-----------+-------+-------+----------------------------+
| ID | Name | Stock | Price | Created |
+----+-----------+-------+-------+----------------------------+
| 1 | product 1 | 7 | 8.40 | 2023-06-17 13:26:08.773060 |
| 2 | product 2 | 11 | 16.40 | 2023-06-17 13:26:08.773207 |
| 4 | product 4 | 19 | 32.40 | 2023-06-17 13:26:08.773225 |
| 5 | product 5 | 23 | 40.40 | 2023-06-17 13:26:08.773231 |
| 6 | product 1 | 7 | 8.40 | 2023-06-17 13:27:26.849659 |
| 7 | product 2 | 11 | 16.40 | 2023-06-17 13:27:26.849777 |
| 8 | product 3 | 15 | 24.40 | 2023-06-17 13:27:26.849792 |
| 9 | product 4 | 19 | 32.40 | 2023-06-17 13:27:26.849800 |
| 10 | product 5 | 23 | 40.40 | 2023-06-17 13:27:26.849808 |
| 11 | product 1 | 7 | 8.40 | 2023-06-17 13:27:59.655821 |
| 12 | product 2 | 11 | 16.40 | 2023-06-17 13:27:59.655979 |
| 13 | product 3 | 15 | 24.40 | 2023-06-17 13:27:59.655989 |
| 14 | product 4 | 19 | 32.40 | 2023-06-17 13:27:59.655994 |
| 15 | product 5 | 23 | 40.40 | 2023-06-17 13:27:59.655999 |
+----+-----------+-------+-------+----------------------------+
done
updating data for product id=3
0 products updated
done
reading data
+----+-----------+-------+-------+----------------------------+
| ID | Name | Stock | Price | Created |
+----+-----------+-------+-------+----------------------------+
| 1 | product 1 | 7 | 8.40 | 2023-06-17 13:26:08.773060 |
| 2 | product 2 | 11 | 16.40 | 2023-06-17 13:26:08.773207 |
| 4 | product 4 | 19 | 32.40 | 2023-06-17 13:26:08.773225 |
| 5 | product 5 | 23 | 40.40 | 2023-06-17 13:26:08.773231 |
| 6 | product 1 | 7 | 8.40 | 2023-06-17 13:27:26.849659 |
| 7 | product 2 | 11 | 16.40 | 2023-06-17 13:27:26.849777 |
| 8 | product 3 | 15 | 24.40 | 2023-06-17 13:27:26.849792 |
| 9 | product 4 | 19 | 32.40 | 2023-06-17 13:27:26.849800 |
| 10 | product 5 | 23 | 40.40 | 2023-06-17 13:27:26.849808 |
| 11 | product 1 | 7 | 8.40 | 2023-06-17 13:27:59.655821 |
| 12 | product 2 | 11 | 16.40 | 2023-06-17 13:27:59.655979 |
| 13 | product 3 | 15 | 24.40 | 2023-06-17 13:27:59.655989 |
| 14 | product 4 | 19 | 32.40 | 2023-06-17 13:27:59.655994 |
| 15 | product 5 | 23 | 40.40 | 2023-06-17 13:27:59.655999 |
+----+-----------+-------+-------+----------------------------+
done
deleting data demo
deleting data with id=3
0 product deleted
done
reading data
+----+-----------+-------+-------+----------------------------+
| ID | Name | Stock | Price | Created |
+----+-----------+-------+-------+----------------------------+
| 1 | product 1 | 7 | 8.40 | 2023-06-17 13:26:08.773060 |
| 2 | product 2 | 11 | 16.40 | 2023-06-17 13:26:08.773207 |
| 4 | product 4 | 19 | 32.40 | 2023-06-17 13:26:08.773225 |
| 5 | product 5 | 23 | 40.40 | 2023-06-17 13:26:08.773231 |
| 6 | product 1 | 7 | 8.40 | 2023-06-17 13:27:26.849659 |
| 7 | product 2 | 11 | 16.40 | 2023-06-17 13:27:26.849777 |
| 8 | product 3 | 15 | 24.40 | 2023-06-17 13:27:26.849792 |
| 9 | product 4 | 19 | 32.40 | 2023-06-17 13:27:26.849800 |
| 10 | product 5 | 23 | 40.40 | 2023-06-17 13:27:26.849808 |
| 11 | product 1 | 7 | 8.40 | 2023-06-17 13:27:59.655821 |
| 12 | product 2 | 11 | 16.40 | 2023-06-17 13:27:59.655979 |
| 13 | product 3 | 15 | 24.40 | 2023-06-17 13:27:59.655989 |
| 14 | product 4 | 19 | 32.40 | 2023-06-17 13:27:59.655994 |
| 15 | product 5 | 23 | 40.40 | 2023-06-17 13:27:59.655999 |
+----+-----------+-------+-------+----------------------------+
done

• 图片和Blob

import sqlite3
from datetime import datetime
from prettytable import PrettyTable

def open_database():
    db = 'pydb.db'
    print('Connecting to SQLite...')
    conn = sqlite3.connect(db)
    print('connected')
    return conn

def create_table(conn):
    cursor = conn.cursor()
    sql = ''' create table if not exists imagefiles(
    id integer primary key autoincrement,
    filename char(30) not null,
    imagetype char(30) not null,
    imgfile blob,
    created datetime
    )'''
    cursor.execute(sql)
    conn.commit()
    print('created a table')
    
def insert_image_data(conn,full_file_path,file_name,file_type):
    
    print('inserting image data')
    cursor = conn.cursor()
    
    with open(full_file_path, 'rb') as f:
        imagedata = f.read()
        params = (file_name,file_type,imagedata,datetime.now())
        query = ("insert into imagefiles (filename,imagetype,imgfile,created) values(?,?,?,?)")
        
    cursor.execute(query, params)
    img_id = cursor.lastrowid
    print('inserted with id=',img_id)
    conn.commit()
    cursor.close()
    
def read_image_data(conn, id,save_as_file):
    print('reading data id=',id)
    cursor = conn.cursor()
    try:
        params = (id,)
        query = ("select filename,imagetype,imgfile,created from imagefiles where id=?")
        cursor.execute(query,params)
        t = PrettyTable(['ID','File Name', 'Image Type','Created'])
        for (filename, imagetype, imgfile, created) in cursor:
            t.add_row([id, filename, imagetype, created])
            with open(save_as_file, 'wb') as f:
                f.write(imgfile)
            print('Save image data as ',save_as_file)
        print(t)
    except Exception as e:
        print(e)
    finally:
        cursor.close()
        pass

# open database
conn = open_database()
create_table(conn)
# inserting image data demo
print('inserting image data demo')
full_file_path = './image1.png'
file_name = 'image1.png'
file_type = 'image/png'
insert_image_data(conn,full_file_path,file_name,file_type)


print('reading image data demo')
save_as_file = './image1-read.png'
id = 1
read_image_data(conn,id,save_as_file)
print('done')

执行

$ python test3.py 
Connecting to SQLite...
connected
created a table
inserting image data demo
inserting image data
inserted with id= 1
reading image data demo
reading data id= 1
Save image data as  ./image1-read.png
+----+------------+------------+----------------------------+
| ID | File Name  | Image Type |          Created           |
+----+------------+------------+----------------------------+
| 1  | image1.png | image/png  | 2023-06-17 14:11:14.713869 |
+----+------------+------------+----------------------------+
done

• 事务

import sqlite3
from datetime import datetime
from prettytable import PrettyTable

def open_database():
    db = 'pydb.db'
    print('Connecting to SQLite...')
    conn = sqlite3.connect(db)
    print('connected')
    return conn

def read_data(conn):
    print('reading data')
    cursor = conn.cursor()
    cursor.execute("select id, name, stock, price, created from product")
    t = PrettyTable(['ID','Name', 'Stock', 'Price', 'Created'])
    for (id, name, stock, price, created) in cursor:
        t.add_row([id, name, stock, format(price,'.2f'), created])
    print(t)
    cursor.close()
    print('Read done')
    
# creating data demo
print('transaction demo')
conn = open_database()
print('Original data.....')
read_data(conn)

# set manual transaction
conn.isolation_level = None
try:
    cursor = conn.cursor()
    cursor.execute("BEGIN")
    for index in range(1,5):
        product_name = 'product ' + str(index)
        price = 1.2 * index
        stock = 10 + 2*index
        insert_sql = ("INSERT INTO product (name, stock, price, created) VALUES(?, ?, ?, ?)")
        # demo error
        # if index == 3:
        # insert_sql = insert_sql.replace('INSERT','INSERT1') # wrong statement
        params = (product_name, stock, price, datetime.now())
        conn.execute(insert_sql, params)
        product_id = cursor.lastrowid
        print('inserted with id=', product_id)
    conn.commit()
    cursor.close()
except Exception as e:
    cursor.execute("ROLLBACK")
    conn.rollback()
    print('error in inserting data')
    print(e)
    print('Update data.....')
    read_data(conn)
    conn.close()
    print('done')

print('Update data.....')
read_data(conn)
conn.close()
print('done')

执行

Pandas

import sqlite3
from datetime import datetime
from prettytable import PrettyTable
import pandas as pd

def open_database():
    db = 'pydb.db'
    print('Connecting to SQLite...')
    conn = sqlite3.connect(db)
    print('connected')
    return conn

conn = open_database()
df = pd.read_sql_query("select * from product", conn)
print(df)
conn.close()