Friday, June 5, 2020 ---Andy
一、導(dǎo)入sqlite3模塊+定義sql執(zhí)行函數(shù)
import sqlite3
class MySqlite:
def __init__(self,database="Andy.db"):
self.database = database
def execute(self,sql=''):
"""執(zhí)行sql語(yǔ)句，@return: select操作：rows-->[()...]"""
try:
# 1.連接數(shù)據(jù)庫(kù)
conn = sqlite3.connect(self.database)
# 2.執(zhí)行sql:以查詢數(shù)據(jù)庫(kù)里面包含的所有表為例
rows = [row for row in conn.execute(sql)]
# 插入-刪除-修改：需要提交
conn.commit()
# 3.關(guān)閉數(shù)據(jù)庫(kù)
conn.close()
return rows
except Exception as e:
print(e)
def tables(self):
"""查看所有表"""
return self.execute(sql='select name from sqlite_master')
def table_info(self,table='sqlite_master'):
"""查看表的結(jié)構(gòu)"""
return self.execute(sql=f"PRAGMA table_info({table})")
def show(self,table='sqlite_master'):
"""查看表的數(shù)據(jù)"""
return self.execute(sql=f"select * from {table}")
二、創(chuàng)建表，查詢存在的表，查看表結(jié)構(gòu)
# 2-1.自動(dòng)創(chuàng)建sqlite3_test_db.db數(shù)據(jù)庫(kù)文件，并創(chuàng)建表
sqli = MySqlite('sqlite3_test_db.db')
sqli.execute("create table user(name text PRIMARY KEY NOT NULL, age int)")
# 2-2.查詢當(dāng)前數(shù)據(jù)庫(kù)有哪些表
sqli.tables()
[('user',), ('sqlite_autoindex_user_1',)]
# 2-3.查詢表結(jié)構(gòu)
sqli.table_info(user)
[(0, 'name', 'text', 1, None, 1), (1, 'age', 'int', 0, None, 0)]
三、數(shù)據(jù)增刪改查
# 3-1 增
sqli.execute("insert into user values('Andy',18)")
sqli.execute("insert into user values('Amanda',18)")
# 3-2 刪
sqli.execute("delete from user where name = 'Amanda'")
# 3-3 改
sqli.execute("update user set age=19 where name = 'Andy'")
# 3-4 查
sqli.execute("select * from user")
[('Andy', 19)]
【完】
[1].過(guò)程中有任何問(wèn)題，歡迎交流！
Q597966823
