SQLite3 可使用 sqlite3 模块与 Python 进行集成。sqlite3 模块是由 Gerhard Haring 编写的。它提供了一个与 PEP 249 描述的 DB-API 2.0 规范兼容的 SQL 接口。您不需要单独安装该模块,因为 Python 2.5.x 以上版本默认自带了该模块。
使用sqlite tutorial提供的 “chinook” sample database 数据库chinook.db,下载地址:https://www.sqlitetutorial.net/sqlite-sample-database/
同时提供了数据库的ER-Diagram实体关系图, 可以使用数据库提供的11张表进行一些练习。
import sqlite3 conn = sqlite3.connect('chinook.db') cur = conn.cursor() # treat the cursor object cur as an iterator cur.execute('SELECT * from albums') # call fetchone() method / or fetchall() method print(cur.fetchone()) # iterating over each rows for row in cur.execute('SELECT * from albums'): print(row) cur.execute('SELECT * from customers') print(cur.fetchone()) # add where clause ArtistId = ('272',) # using ? is more secure than using %s cur.execute('SELECT * from albums where ArtistId = ?',ArtistId) print(cur.fetchall()) # using %s ArtistId = ('272',) # using ? is more secure than using %s cur.execute('SELECT * from albums where ArtistId = %s' % ArtistId) print(cur.fetchall()) cur.execute('SELECT * from artists') print(cur.fetchall()) # insert value cur.execute('INSERT OR REPLACE INTO artists values (276, "Jay Zhou")') cur.execute('SELECT * from artists') print(cur.fetchall()) # insert a list of records -- here we use executemany to insert another 3 singers newArtists = [(278, 'Eason Chan'), (279, 'Yoga Lin'), (280, 'Jane Zhang'),] # print(type(newArtists)) cur.executemany('INSERT OR IGNORE INTO artists values (? , ?)' , newArtists) for row in cur.execute('SELECT * from artists'): print(row) # using commit() to save those transactions / commiting those transations conn.commit() conn.close()