import sys
import sqlite3 as sqlite
db_base_name='dingdan.db'
con = sqlite.connect(db_base_name)# 连接到SQLite数据库 # 如果文件不存在,会自动在当前目录创建:
# 创建一个Cursor:
def row_diplay():"""按列显示"""
cursor = con.cursor()
sql_code ='select * from tb_dingdan'
with con:
con.row_factory = sqlite.Row
cur = con.cursor()
cur.execute(sql_code)
rows = cur.fetchall()for row in rows:print(f"{row['id']} {row['NO']} {row['GuiGe']}")
def con_verison():"""版本显示"""
con = None
try:
con = sqlite.connect(db_base_name)
cur = con.cursor()
cur.execute('SELECT SQLITE_VERSION()')
data = cur.fetchone()[0]print(f"SQLite version: {data}")
except sqlite.Error as e:print(f"Error {e.args[0]}")
sys.exit(1)
finally:if con:
con.close()
def con_insert():"""重插入数据"""
con = sqlite.connect(db_base_name)
with con:
cur = con.cursor()#cur.execute("CREATE TABLE tb_dingdan(id INT, GuiGe , NO )")
cur.execute("INSERT INTO tb_dingdan VALUES(11,'Audi',52642,1,1)")
cur.execute("INSERT INTO tb_dingdan VALUES(12,'Mercedes',57127,1,1)")
cur.execute("INSERT INTO tb_dingdan VALUES(13,'Skoda',9000,1,1)")
cur.execute("INSERT INTO tb_dingdan VALUES(4,'Volvo',29000,1,1)")
cur.execute("INSERT INTO tb_dingdan VALUES(5,'Bentley',350000,1,1)")
cur.execute("INSERT INTO tb_dingdan VALUES(6,'Citroen',21000,1,1)")
cur.execute("INSERT INTO tb_dingdan VALUES(7,'Hummer',41400,1,1)")
cur.execute("INSERT INTO tb_dingdan VALUES(8,'Volkswagen',21600,1,1)")
def table_rebuile():"""重建数据表,并适当插入数据"""
conn = sqlite.connect(db_base_name)
with conn:
cur = conn.cursor()
GuanJian =((1,'DN200',20210101001,'20210101','芜湖'),(2,'DN300',20210101002,'20210101','芜湖'),(3,'DN200*400',20210101003,'20210101','桃江'),(4,'DN200 L1000',20210102001,'20210102','芜湖'),(5,'DN600',20210102002,'20210102','芜湖'),(6,'DN800',20210103001,'20210103','芜湖'),(7,'DN1200',20210103002,'20210103','芜湖'),)
con = sqlite.connect(db_base_name)
with con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS tb_dingdan")
cur.execute("CREATE TABLE tb_dingdan(id BIGINT, GuiGe NCHAR(55), No BIGINT,RiQi DATE,BeiZhu VARCHAR(255) )")
cur.executemany("INSERT INTO tb_dingdan VALUES(?, ?, ?,?,?)", GuanJian)
def table_rebuile_executescript():"""重建数据表,并适当插入数据"""
conn = None
try:
conn = sqlite.connect(db_base_name)
cur = conn.cursor()
cur.executescript("""
DROP TABLE IF EXISTS tb_dingdan;
CREATE TABLE tb_dingdan(id BIGINT, GuiGe NCHAR(55), No BIGINT,RiQi DATE,BeiZhu VARCHAR(255));
INSERT INTO tb_dingdan VALUES(1,'DN200',20210101001,'20210101','芜湖');
INSERT INTO tb_dingdan VALUES(2,'DN300',20210101002,'20210101','芜湖');
INSERT INTO tb_dingdan VALUES(3,'DN200*400',20210101003,'20210101','桃江');
INSERT INTO tb_dingdan VALUES(4,'DN200 L1000',20210102001,'20210102','芜湖');
INSERT INTO tb_dingdan VALUES(5,'DN600',20210102002,'20210102','芜湖');
INSERT INTO tb_dingdan VALUES(6,'DN800',20210103001,'20210103','芜湖');
INSERT INTO tb_dingdan VALUES(7,'DN1200',20210103002,'20210103','芜湖');
INSERT INTO tb_dingdan VALUES(8,'DN1200',20210103002,'20210103','芜湖');""")
con.commit()
except sqlite.Error as e:if con:
con.rollback()print(f"Error {e.args[0]}")
sys.exit(1)
finally:if con:
con.close()
def table_query_lastrow():"""最后插入行的id"""#con= sqlite.connect(':memory:')
con = sqlite.connect(db_base_name)
sql_code ='select * from tb_dingdan'
with con:
cur = con.cursor()#cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT);")#cur.execute("INSERT INTO friends(name) VALUES ('Tom');")#cur.execute("INSERT INTO friends(name) VALUES ('Rebecca');")#cur.execute("INSERT INTO friends(name) VALUES ('Jim');")#cur.execute("INSERT INTO friends(name) VALUES ('Robert');")
last_row_id = cur.lastrowid
print(f"The last Id of the inserted row is {last_row_id}")
def table_query_fetchall():"""fetchall()方法获取查询结果集的所有(或所有剩余)行,并返回元组列表"""
sql_code ='select * from tb_dingdan'
con = sqlite.connect(db_base_name)
with con:
cur = con.cursor()
cur.execute(sql_code)
rows = cur.fetchall()for row in rows:print(f"{row[0]} {row[1]} {row[2]}")
def table_query_fetchone():"""fetchone() 一行的查询结果集,返回一个元组,或返回None时,没有更多的数据是可用的。"""
sql_code ='select * from tb_dingdan'
con = sqlite.connect(db_base_name)
with con:
cur = con.cursor()
cur.execute(sql_code)
row = cur.fetchone()if row == None:#break;returnprint(f"{row[0]} {row[1]} {row[2]}")
def table_query_dictionary():"""通过列名来引用数据"""
sql_code ='select * from tb_dingdan'
con = sqlite.connect(db_base_name)
with con:
con.row_factory = sqlite.Row
sql_code ='select * from tb_dingdan'
cur = con.cursor()
cur.execute(sql_code)
rows = cur.fetchall()for row in rows:print(f"{row['id']} {row['GuiGe']} {row['No']}")
def table_query_parameterizedy():
uId =1
uNo ='2021'"""通过列名来引用数据"""
con = sqlite.connect(db_base_name)
with con:
sql_code ="UPDATE tb_dingdan SET No=? WHERE id=?"
cur = con.cursor()
cur.execute(sql_code,(uNo, uId))
rows = cur.fetchall()for row in rows:print(f"{row['id']} {row['GuiGe']} {row['No']}")
def table_query_parameterizedy_zhanwei():
uId =4
uNo ='2021'"""通过列名来引用数据"""
con = sqlite.connect(db_base_name)
with con:
cur = con.cursor()
cur.execute("SELECT No, GuiGe FROM tb_dingdan WHERE Id=:Id",{"Id": uId})
row = cur.fetchone()print(f"{row[0]}, {row[1]}")
def table_Images_insert():"""SQLite数据库插入一个图像"""
def readImage():"""读取图像文件"""
fin = None
try:
fin =open("sid.jpg","rb")
img = fin.read()return img
except IOError as e:print(e)
sys.exit(1)
finally:if fin:
fin.close()
con = None
try:
con = sqlite.connect(db_base_name)
cur = con.cursor()#游标实例化
data =readImage()#调用函数
binary = sqlite.Binary(data) #转换为二进制
cur.execute("INSERT INTO images(data) VALUES (?)",(binary,))#调用游标执行
con.commit()#提交
except sqlite.Error as e:if con:
con.rollback()#未完成回滚
print(e)
sys.exit(1)
finally:if con:
con.close()#关闭连接
def table_Images_read():
def writeImage(data):#写图函数
fout = None
try:
fout =open('sid2.png','wb')
fout.write(data)#写到一个图像文件中
except IOError as e:print(e)
sys.exit(1)
finally:if fout:
fout.close()
con = None
try:
con = sqlite.connect('ydb.db')
cur = con.cursor()
cur.execute("SELECT data FROM images LIMIT 1")
data = cur.fetchone()[0]writeImage(data)
except sqlite.Error as e:print(e)
sys.exit(1)
finally:if con:
con.close()
def table_yuanshuju_jiegou_read():
#读取了标的结构数据
con = sqlite.connect(db_base_name)
with con:
cur = con.cursor()
cur.execute('PRAGMA table_info(tb_dingdan)')
data = cur.fetchall()for d in data:print(f"{d[0]} {d[1]} {d[2]}")
def table_yuanshuju_data_read():
#读取了标的结构数据
con = sqlite.connect(db_base_name)
with con:
cur = con.cursor()
cur.execute('SELECT * FROM tb_dingdan')
col_names =[cn[0]for cn in cur.description]#第0列
rows = cur.fetchall()
#表头
print(f"{col_names[0]:3} {col_names[1]:10} {col_names[2]:7}")
# 表data
for row in rows:print(f"{row[0]:<3} {row[1]:<10} {row[2]:7}")
def read_tablename_all():
#读取了标的结构数据
con = sqlite.connect(db_base_name)
with con:
cur = con.cursor()
cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
rows = cur.fetchall()for row in rows:print(row[0])
def tabel_data_daochu():
#以SQL格式转储数据,创建数据库表备份
#con= sqlite.connect(db_base_name)
guanjian_data =((1,'DN200',20210101001,'20210101','芜湖'),(2,'DN300',20210101002,'20210101','芜湖'),(3,'DN200*400',20210101003,'20210101','桃江'),(4,'DN200 L1000',20210102001,'20210102','芜湖'),(5,'DN600',20210102002,'20210102','芜湖'),(6,'DN800',20210103001,'20210103','芜湖'),(7,'DN1200',20210103002,'20210103','芜湖'),)
def writeData(data):
f =open('tb_dingdan.sql','w')
with f:
f.write(data)
con = sqlite.connect(':memory:')
with con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS tb_dingdan")
cur.execute("CREATE TABLE tb_dingdan(id BIGINT, GuiGe NCHAR(55), No BIGINT,RiQi DATE,BeiZhu VARCHAR(255) )")
cur.executemany("INSERT INTO tb_dingdan VALUES(?, ?, ?,?,?)", guanjian_data)
cur.execute("DELETE FROM tb_dingdan WHERE Riqi ='20210103'")
data ='\n'.join(con.iterdump())writeData(data)
def tabel_data_daoru():
def readData():
f =open('tb_dingdan.sql','r')
with f:
data = f.read()return data
con = sqlite.connect(':memory:')
with con:
cur = con.cursor()
sql =readData()
cur.executescript(sql)
cur.execute("SELECT * FROM tb_dingdan")
rows = cur.fetchall()for row in rows:print(row)
def tabel_data_no_commit():
con = None
try:
con = sqlite.connect(db_base_name)
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS dingdan")
cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT)")
cur.execute("INSERT INTO friends(name) VALUES ('Tom')")
cur.execute("INSERT INTO friends(name) VALUES ('Rebecca')")
cur.execute("INSERT INTO friends(name) VALUES ('Jim')")
cur.execute("INSERT INTO friends(name) VALUES ('Robert')")#con.commit()
except sqlite.Error as e:if con:
con.rollback()print(e)
sys.exit(1)
finally:if con:
con.close()
def tabel_data_auto_commit():
con = None
try:
con = sqlite.connect(db_base_name, isolation_level=None) #自动提交模式设置isolation_level为无。
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS friends")
cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT)")
cur.execute("INSERT INTO friends(name) VALUES ('Tom')")
cur.execute("INSERT INTO friends(name) VALUES ('Rebecca')")
cur.execute("INSERT INTO friends(name) VALUES ('Jim')")
cur.execute("INSERT INTO friends(name) VALUES ('Robert')")
except sqlite.Error as e:print(e)
sys.exit(1)
finally:if con:
con.close()if __name__=='__main__':#con_verison()#row_diplay()#con_insert()#table_rebuile()#table_rebuile_executescript()#table_query_lastrow()#table_query_fetchall()#table_query_fetchone()#table_query_parameterizedy()#table_query_parameterizedy_zhanwei()#table_yuanshuju_jiegou_read()#table_yuanshuju_data_read()#read_tablename_all()#tabel_data_daochu()tabel_data_daoru()