Python进阶教程m12–sqlite3数据库

原文链接: http://www.juzicode.com/archives/3923

SQLite是一种轻量级、无配置、文件型的数据库，SQLite文件可以在不同的系统跨平台使用，不管是32位或者64位系统，也不管是大端模式或者小端模式CPU架构。任何人可以自由的拷贝、修改、出版、使用、编译、出售或者发布其源码和二进制文件，不管是商业性质或者非商业性质的目的。正因为如此宽松的协议，也为了保证项目的完全自由和免受版权妨碍，SQLite不接受任何第三方贡献代码。Python3内部集成了sqlite3模块，为SQLite提供Python接口，使用import sqlite3即可完成模块的导入。

1、连接数据库、创建游标

和大多数数据库的使用一样，首先需要连接数据库，使用sqlite3.connect(数据库文件db_name)的方法连接数据库文件，并生成连接实例conn，如果数据库文件db_name不存在，则自动创建数据库文件db_name。

使用sqlite3.connect()生成实例conn的cursor()方法创建游标，游标可以用来执行sql语句。

import  sqlite3
conn = sqlite3.connect(db_name)
cursor = conn.cursor()

2、关闭游标、关闭数据库连接

当使用完数据库后最好使用close()方法关闭游标和数据库连接：

cursor.close()
conn.close()

3、建表

先通过“CREATE TABLE 表名 (表头 类型, 表头 类型, ……)”构造sql语句，然后使用cursor.execute(sql)创建新表。

import  sqlite3
db_name = 'test.db'
table_name = 'table_juzicode'

conn = sqlite3.connect(db_name)
cursor = conn.cursor()
sql = '''CREATE TABLE '''+table_name +''' (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,      
    price REAL,
    weight REAL
    )'''
cursor.execute(sql)    
conn.commit()

cursor.close()
conn.close()

上述代码如果已经执行过一次并且已经建表成功，再次执行时因为数据库文件中已经存在该表，再次建同名表就会提示sqlite3.OperationalError: table table_juzicode already exists错误，所以在实际使用中需要先检查该表名是否已经存在：

print('\n-----欢迎来到www.juzicode.com')
print('-----公众号: 桔子code/juzicode \n')   

import  sqlite3
db_name = 'test.db'
table_name = 'table_juzicode'

#连接数据库，创建游标
conn = sqlite3.connect(db_name)
cursor = conn.cursor()
print(db_name +' 数据库连接成功')

#检查表名是否存在
print('检查表名......')
sql = '''SELECT tbl_name FROM sqlite_master WHERE type = 'table' '''
cursor.execute(sql)
values = cursor.fetchall()
tables = []
for v in values:
    tables.append(v[0])
print('数据库包含的表:',tables)

#表不存在则建表
if table_name not in tables:
    sql = '''CREATE TABLE '''+table_name +''' (
        _id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,      
        price REAL,
        weight REAL
        )'''
    cursor.execute(sql)    
    conn.commit()
    print(table_name + ' 创建成功')
else:
    print(table_name + ' 已经存在')

#关闭游标，关闭连接
cursor.close()
conn.close()

运行结果==========
test.db 数据库连接成功
检查表名......
数据库包含的表: ['table_juzicode', 'sqlite_sequence']
table_juzicode 已经存在

4、增删改查

接下来就是常用的增删改查，首先构造sql语句，然后用cursor.excute(sql)执行语句，再用conn.commit()提交事务。

4.1、增加

#增加
sql='''INSERT INTO ''' + table_name + ''' (_id,name,price,weight) \
      VALUES (1, 'orange', 3.2,  100.5 )'''    
cursor.execute(sql)
sql='''INSERT INTO ''' + table_name + ''' (name,price,weight) \
      VALUES ('apple', 5.12,  80.2 )'''    
cursor.execute(sql) 
sql='''INSERT INTO ''' + table_name + ''' (name,price,weight) \
      VALUES ('pear', 2.12,  210 )'''    
cursor.execute(sql)         
conn.commit()

执行后可以看到写入数据库的内容：

因为定义了_id是自增变量（INTEGER PRIMARY KEY AUTOINCREMENT），在sql语句中也可以不必指定_id的值。这个时候需要注意如果指定了_id，再次insert同样_id的记录时，会导致抛异常：IntegrityError: UNIQUE constraint failed: table_juzicode._id。

4.2、查询

sql = '''  SELECT _id, name, price, weight  from  ''' + table_name
values = cursor.execute(sql)
for v in values:
    print('----------------')
    print('_id   =',v[0])
    print('name  =',v[1])
    print('price =',v[2])
    print('weight=',v[3])

完整的代码实现：

import  sqlite3

#检查表是否存在，存在返回True
def check_table_exsist(cursor,table_name):
    sql = '''SELECT tbl_name FROM sqlite_master WHERE type = 'table' '''
    cursor.execute(sql)
    values = cursor.fetchall()
    tables = []
    for v in values:
        tables.append(v[0])
    print('数据库包含的表:',tables) 
    if table_name in tables:
        return True
    return False
 
 
if __name__ == '__main__':        
    db_name = 'test.db'
    table_name = 'table_juzicode'

    #连接数据库，创建游标
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()
    print(db_name +' 数据库连接成功')

    #检查表
    if check_table_exsist(cursor,table_name) is not True:
        print('表不存在:',table_name)
        sys.exit(-1)
        
    #查询并显示
    sql = '''  SELECT _id, name, price, weight  from  ''' + table_name
    values = cursor.execute(sql)
    for v in values:
        print('----------------')
        print('_id   =',v[0])
        print('name  =',v[1])
        print('price =',v[2])
        print('weight=',v[3])

    #关闭游标，关闭连接
    cursor.close()
    conn.close()
    print(db_name +' 数据库关闭')

运行结果==========
 test.db 数据库连接成功
 数据库包含的表: ['table_juzicode', 'sqlite_sequence']
 _id   = 1
 name  = orange
 price = 3.2
 weight= 100.5
 _id   = 2
 name  = apple
 price = 5.12
 weight= 80.2
 _id   = 3
 name  = pear
 price = 2.12
 weight= 210.0
 test.db 数据库关闭

4.3、删除

print('删除记录......')
sql='''DELETE from ''' + table_name + '''  where _id=1'''    
cursor.execute(sql)
sql='''DELETE from ''' + table_name + '''  where _id=2'''    
ret = cursor.execute(sql)  
ret = conn.commit()

完整代码实现：

import  sqlite3

#检查表是否存在，存在返回True
def check_table_exsist(cursor,table_name):
    sql = '''SELECT tbl_name FROM sqlite_master WHERE type = 'table' '''
    cursor.execute(sql)
    values = cursor.fetchall()
    tables = []
    for v in values:
        tables.append(v[0])
    print('数据库包含的表:',tables) 
    if table_name in tables:
        return True
    return False
    
#查询、显示
def query(cursor,table_name):
    sql = '''  SELECT _id, name, price, weight  from  ''' + table_name
    values = cursor.execute(sql)
    for v in values:
        print('----------------')
        print('_id   =',v[0])
        print('name  =',v[1])
        print('price =',v[2])
        print('weight=',v[3])
        
if __name__ == '__main__':    
    db_name = 'test.db'
    table_name = 'table_juzicode'

    #连接数据库，创建游标
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()
    print(db_name +' 数据库连接成功')

    #检查表
    if check_table_exsist(cursor,table_name) is not True:
        print('表不存在:',table_name)
        sys.exit(-1)
     
    query(cursor,table_name)

    #删除
    print('删除记录后......')
    sql='''DELETE from ''' + table_name + '''  where _id=1'''    
    cursor.execute(sql)
    sql='''DELETE from ''' + table_name + '''  where _id=2'''    
    ret = cursor.execute(sql)  
    ret = conn.commit()

    query(cursor,table_name)

    #关闭游标，关闭连接
    cursor.close()
    conn.close()

运行结果==========
 test.db 数据库连接成功
 数据库包含的表: ['table_juzicode', 'sqlite_sequence']
 _id   = 1
 name  = orange
 price = 3.2
 weight= 100.5
 _id   = 2
 name  = apple
 price = 5.12
 weight= 80.2
 _id   = 3
 name  = pear
 price = 2.12
 weight= 210.0
 删除记录后……
 _id   = 3
 name  = pear
 price = 2.12
 weight= 210.0

4.4、修改

#更新
sql = ''' UPDATE ''' + table_name + ''' set price = 1.55  where _id=1'''    
cursor.execute(sql)
sql = ''' UPDATE ''' + table_name + ''' set price = 2.55  where _id=5'''    
cursor.execute(sql)
ret = conn.commit()

完整代码实现：

import  sqlite3

#检查表是否存在，存在返回True
def check_table_exsist(cursor,table_name):
    sql = '''SELECT tbl_name FROM sqlite_master WHERE type = 'table' '''
    cursor.execute(sql)
    values = cursor.fetchall()
    tables = []
    for v in values:
        tables.append(v[0])
    print('数据库包含的表:',tables) 
    if table_name in tables:
        return True
    return False
    
#查询、显示
def query(cursor,table_name):
    sql = '''  SELECT _id, name, price, weight  from  ''' + table_name
    values = cursor.execute(sql)
    for v in values:
        print('----------------')
        print('_id   =',v[0])
        print('name  =',v[1])
        print('price =',v[2])
        print('weight=',v[3])
        
if __name__ == '__main__':    
    db_name = 'test.db'
    table_name = 'table_juzicode'

    #连接数据库，创建游标
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()
    print(db_name +' 数据库连接成功')

    #检查表
    if check_table_exsist(cursor,table_name) is not True:
        print('表不存在:',table_name)
        sys.exit(-1)
     
    query(cursor,table_name)

    #更新
    sql = ''' UPDATE ''' + table_name + ''' set price = 11.55  where _id=1'''    
    cursor.execute(sql)
    sql = ''' UPDATE ''' + table_name + ''' set price = 21.55  where _id=2'''    
    cursor.execute(sql)
    ret = conn.commit()

    print('更新后......')
    query(cursor,table_name)

    #关闭游标，关闭连接
    cursor.close()
    conn.close()

运行结果==========
test.db 数据库连接成功
数据库包含的表: ['table_juzicode', 'sqlite_sequence']
----------------
_id   = 1
name  = orange
price = 3.2
weight= 100.5
----------------
_id   = 2
name  = apple
price = 5.12
weight= 80.2
----------------
_id   = 3
name  = pear
price = 2.12
weight= 210.0
更新后......
----------------
_id   = 1
name  = orange
price = 11.55  #####发生了变化 
weight= 100.5
----------------
_id   = 2
name  = apple
price = 22.55  #####发生了变化
weight= 80.2
----------------
_id   = 3
name  = pear
price = 2.12
weight= 210.0