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()