Python_sqlite3与sqlite数据库交互
基础功能
import sqlite3 # sqlite一个文件就是一个库 # 连接test.db数据库,没有就创建 conn = sqlite3.connect('test.db') # 创建一个cursor cur = conn.cursor() # 创建表 table_name = "company" cur.execute('''CREATE TABLE %s (ID integer PRIMARY KEY AUTOINCREMENT NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);''' % table_name) # 查询库中的表 ret = cur.execute("select name from sqlite_master where type='table' order by name;").fetchall() print("所有表名:", ret) # 查询表结构 ret = cur.execute('PRAGMA table_info(%s)' % table_name).fetchall() print("表结构", ret) # 插入数据 cur.execute("INSERT INTO %s (NAME,AGE,ADDRESS,SALARY) VALUES \ ('Paul', 32, 'California', 20000.00 ),\ ('Allen', 25, 'Texas', 15000.00 ),\ ('Teddy', 23, 'Norway', 20000.00 )" % table_name) # 查询数据 ret = cur.execute("SELECT * from %s" % table_name).fetchall() print("查询表格数据", ret) # 更新数据 cur.execute("UPDATE %s set SALARY = 25000.00 where ID=1" % table_name) ret = cur.execute("SELECT * from %s where ID=1" % table_name).fetchall() print("查询更新数据", ret) # 删除数据 cur.execute("DELETE FROM %s where ID=2" % table_name) ret = cur.execute("SELECT * from %s" % table_name).fetchall() print("查询删除后的数据", ret) # 删除表格 cur.execute("DROP TABLE %s;" % table_name).fetchall() # 提交数据 conn.commit() # 关闭连接 cur.close() conn.close()
简单封装
import sqlite3 class Sqlite(object): def __init__(self, db): # 连接数据库 self.database = sqlite3.connect(db) print("连接数据库") # 获取游标对象 self.cursor = self.database.cursor() def __del__(self): """对象销毁进行资源回收""" # 关闭游标 self.cursor.close() # 关闭数据库连接 self.database.close() print("__del__被执行") def execute(self, sql): """ 执行SQL :param sql: sql语句 :return:返回游标对象 """ return self.cursor.execute(sql).fetchall() def commit(self): """提交数据,提交失败则回滚""" try: self.database.commit() return 0 except Exception as e: self.database.rollback() return -1 if __name__ == '__main__': # sqlite一个文件就是一个库 # 连接test.db数据库,没有就创建 db = Sqlite("test.db") table_name = "company" # 创建表 db.execute('''CREATE TABLE %s (ID integer PRIMARY KEY AUTOINCREMENT NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);''' % table_name) # 查询库中的表 ret = db.execute("select name from sqlite_master where type='table' order by name;") print("所有表名:", ret) # 查询表结构 ret = db.execute('PRAGMA table_info(%s)' % table_name) print("表结构", ret) # 插入数据 db.execute("INSERT INTO %s (NAME,AGE,ADDRESS,SALARY) VALUES \ ('Paul', 32, 'California', 20000.00 ),\ ('Allen', 25, 'Texas', 15000.00 ),\ ('Teddy', 23, 'Norway', 20000.00 )" % table_name) # 查询数据 ret = db.execute("SELECT * from %s" % table_name) print("查询表格数据", ret) # 删除表格 db.execute("DROP TABLE %s;" % table_name) # 提交数据 db.commit()