Friday, June 5, 2020 ---Andy
一、导入sqlite3模块+定义sql执行函数
import sqlite3
class MySqlite:
def __init__(self,database="Andy.db"):
self.database = database
def execute(self,sql=''):
"""执行sql语句，@return: select操作：rows-->[()...]"""
try:
# 1.连接数据库
conn = sqlite3.connect(self.database)
# 2.执行sql:以查询数据库里面包含的所有表为例
rows = [row for row in conn.execute(sql)]
# 插入-删除-修改：需要提交
conn.commit()
# 3.关闭数据库
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'):
"""查看表的结构"""
return self.execute(sql=f"PRAGMA table_info({table})")
def show(self,table='sqlite_master'):
"""查看表的数据"""
return self.execute(sql=f"select * from {table}")
二、创建表，查询存在的表，查看表结构
# 2-1.自动创建sqlite3_test_db.db数据库文件，并创建表
sqli = MySqlite('sqlite3_test_db.db')
sqli.execute("create table user(name text PRIMARY KEY NOT NULL, age int)")
# 2-2.查询当前数据库有哪些表
sqli.tables()
[('user',), ('sqlite_autoindex_user_1',)]
# 2-3.查询表结构
sqli.table_info(user)
[(0, 'name', 'text', 1, None, 1), (1, 'age', 'int', 0, None, 0)]
三、数据增删改查
# 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].过程中有任何问题，欢迎交流！
Q597966823
