安装
$ 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()
