Python连接SQLite数据库

Python连接SQLite数据库

环境

Python 3.7.4
sqlite3

建立连接

def __init__(self, path):
	# 构造函数
	# 数据库路径path，若存在数据库则连接，否则建立.db文件
	# 连接到指定数据库
    self.connect = sqlite3.connect(path)
    self.cursor = self.connect.cursor()
    # 初始化数据库，建表
    self.initializeDatabase()

初始化数据库建表

def initializeDatabase(self):
	# 若不存在contact表，则建表
    sql = '''
        create table if not exists contact (
            id integer primary key autoincrement,
            name text,
            phone text
        )
    '''

	# 执行SQL语句
    self.cursor.execute(sql)
    # 对数据库进行更改操作需要提交
    self.connect.commit()

插入数据

def insert(self, name, phone):

    sql = '''
        insert into contact (
            name,
            phone
        ) values (?, ?)
    '''

	# 执行SQL语句
    self.cursor.execute(sql, (name, phone))
    self.connect.commit()

查询

def query(self):
    sql = 'select * from contact'
	
	# 执行SQL语句，返回执行结果
    contacts = self.cursor.execute(sql)
    for contact in contacts:
        for item in contact:
            print(item)

实现代码

import sqlite3

class Database():
    def __init__(self, path):
        self.connect = sqlite3.connect(path)
        self.cursor = self.connect.cursor()
        self.initializeDatabase()

    def __del__(self):
        self.cursor.close()
        self.connect.close()

    def initializeDatabase(self):

        sql = '''
            create table if not exists contact (
                id integer primary key autoincrement,
                name text,
                phone text
            )
        '''

        self.cursor.execute(sql)
        self.connect.commit()


    def query(self):
        sql = 'select * from contact'

        contacts = self.cursor.execute(sql)
        for contact in contacts:
            for item in contact:
                print(item)

    def insert(self, name, phone):

        sql = '''
            insert into contact (
                name,
                phone
            ) values (?, ?)
        '''


        self.cursor.execute(sql, (name, phone))
        self.connect.commit()



if __name__ == "__main__":
    path = './contact.db'
    database = Database(path)
    database.insert("Tony", "12341238908")
    database.insert("Hulk", "12341898908")
    database.insert("Captain", "12323238908")
    database.query()

运行结果

1
Tony
12341238908
2
Hulk
12341898908
3
Captain
12323238908

最后

• 由于博主水平有限，不免有疏漏之处，欢迎读者随时批评指正，以免造成不必要的误解！