sqlite數(shù)據(jù)庫在Python中的使用簡介
1. sqlite3介紹
import sqlite3
SQLite數(shù)據(jù)庫是一款非常小巧的嵌入式開源數(shù)據(jù)庫軟件楷拳，沒有獨立的維護進程毙芜，所有的維護都來自于程序本身玷室。在python中揩悄，使用sqlite3創(chuàng)建數(shù)據(jù)庫的連接丈积，當數(shù)據(jù)庫文件不存在時，會新建數(shù)據(jù)庫文件惠拭；如果數(shù)據(jù)庫文件已經(jīng)存在了扩劝，則打開數(shù)據(jù)庫文件。
連接對象可以是硬盤上面的數(shù)據(jù)庫文件职辅，也可以是建立在內(nèi)存中的棒呛，在內(nèi)存中的數(shù)據(jù)庫執(zhí)行完任何操作后，不需要提交事務的commit域携。
#使用:memory:標識打開的是內(nèi)存數(shù)據(jù)庫
con = sqlite3.connect(":memory:")
#在本地當前目錄下創(chuàng)建數(shù)據(jù)庫文件\打開數(shù)據(jù)庫文件-test.db
conn = sqlite3.connect('test.db')
打開數(shù)據(jù)庫時返回的對象conn就是一個數(shù)據(jù)庫連接對象簇秒，它可以有以下操作：
commit() --事務提交
rollback() --事務回滾
close() --關閉一個數(shù)據(jù)庫鏈接
cursor() --創(chuàng)建一個游標
創(chuàng)建一個游標對象：cu
cu = conn.cursor()
關閉一個游標對象
cu.close()
在sqlite3中，所有sql語句的執(zhí)行都要在游標對象的參與下完成
對于游標對象cu秀鞭，具有以下具體操作：
execute() --執(zhí)行一條sql語句
executemany() --執(zhí)行多條sql語句
close() --游標關閉
fetchone() --從結(jié)果中取出一條記錄
fetchmany() --從結(jié)果中取出多條記錄
fetchall() --從結(jié)果中取出所有記錄
scroll() --游標滾動
2. 創(chuàng)建table
創(chuàng)建表（id,name,gender,age,address,phone)趋观，通過cu.execute(create_table_sql)執(zhí)行sql語句創(chuàng)建表，如果失敗會拋出異常锋边≈逄常“table student already exists”，代表已經(jīng)存在student表宠默，不能再次創(chuàng)建麸恍。
create_table_sql = '''CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
`gender` varchar(4) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(200) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
)'''
try:
conn = sqlite3.connect('test.db')
cu = conn.cursor()
cu.execute(create_table_sql)
print 'table create successful'
except sqlite3.Error, why:
print 'create table failed:' + why.args[0]
2. 刪除table
刪除表，刪除失敗會拋出異常搀矫。
try:
#如果存在表先刪除
drop_table_sql = 'DROP TABLE IF EXISTS student'
conn = sqlite3.connect('test.db')
cu = conn.cursor()
cu.execute(drop_table_sql)
print 'delete table successful'
except sqlite3.Error, why:
print 'delete table failed:' + why.args[0]
2. 插入數(shù)據(jù)
try:
save_sql = 'INSERT INTO student values (?, ?, ?, ?, ?, ?)'
data = (1, 'zhang', '男', 20, '廣東省廣州市', '13423****62')
conn = sqlite3.connect('test.db')
cu = conn.cursor()
cu.execute(save_sql, data)
print 'save data successful'
except sqlite3.Error, why:
print 'save data failed:' + why.args[0]
cu.execute(save_sql, data)抹沪，data參數(shù)為元組，如果是其他類型會出現(xiàn)錯誤提示瓤球，上面代碼執(zhí)行完畢之后會提示：
save data failed:You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings.
是由于中文字符串導致的融欧，請先確定你的IDE或者系統(tǒng)默認編碼是utf-8,并且在中文字符串前+u來解決。
data = (1, 'Hongten', u'男', 20, u'廣東省廣州市', '13423****62')
3. 更新數(shù)據(jù)
更新數(shù)據(jù)卦羡，把ID等于1的Name更新為James噪馏。
update_sql = '''UPDATE student SET name = ? WHERE ID = ? '''
data = ('James', 1)
try:
conn = sqlite3.connect('test.db')
cu = conn.cursor()
cu.execute(update_sql, data)
conn.commit()
print 'table update successful'
except sqlite3.Error, why:
print 'table update failed:' + why.args[0]
4. 查詢數(shù)據(jù)
#查詢?nèi)繑?shù)據(jù)
fetchall_sql = '''SELECT * FROM student'''
try:
conn = sqlite3.connect('test.db')
cu = conn.cursor()
cu.execute(fetchall_sql)
content = cu.fetchall()
print content
# if len(content) > 0:
# for item in content:
# for element in item:
# print element,
# print ''
# else:
# for element in content:
# print element,
# print ''
except sqlite3.Error as why:
print "fetchall data failed:", why.args[0]
return
輸出：
[(1, u'Zhang', u'\u7537', 15, u'\u5e7f\u4e1c\u7701\u5e7f\u5dde\u5e02', u'13*******62'), (2, u'Li', u'\u7537', 22, u'\u7f8e\u56fd\u65e7\u91d1\u5c71', u'15*******63'), (3, u'Zhao', u'\u5973', 18, u'\u5e7f\u4e1c\u7701\u5e7f\u5dde\u5e02', u'18*******87'), (4, u'Xi', u'\u5973', 21, u'\u5e7f\u4e1c\u7701\u5e7f\u5dde\u5e02', u'14323****32')]
中文部分仍然顯示為亂碼，如果要顯示出中文字體绿饵，那需要按注釋部分欠肾，依次打印出每個字符串
#查詢一條數(shù)據(jù)
fetchall_sql = '''SELECT * FROM student WHERE ID = ? '''
data = 1
try:
conn = sqlite3.connect('test.db')
cu = conn.cursor()
d = (data,)
cu.execute(fetchall_sql, d)
content = cu.fetchall()
if len(content) > 0:
for item in content:
for element in item:
print element,
print ''
else:
for element in content:
print element,
print ''
print 'fetch the data successful'
except sqlite3.Error, why:
print 'fetch data failed:' + why.args[0]
5. 刪除數(shù)據(jù)
把WHERE后面的去掉，'DELETE FROM student'拟赊，刪除全部數(shù)據(jù)
#刪除一條數(shù)據(jù)
'''DELETE FROM student WHERE ID = ? AND NAME = ?'''
data = (1, 'James')
try:
conn = sqlite3.connect('test.db')
cu = conn.cursor()
cu.execute(update_sql, data)
conn.commit()
print 'delete data successful'
except sqlite3.Error, why:
print 'delete data failed:' + why.args[0]
以下是封裝成類后的全部代碼刺桃，新人代碼，如果出現(xiàn)問題請多多交流
# coding:utf-8
"""
@author: smartgang
@contact: zhangxingang92@qq.com
@file: SqliteHelper.py
@time: 2017/12/7 18:19
"""
import sqlite3
'''
SQLite數(shù)據(jù)庫是一款非常小巧的嵌入式開源數(shù)據(jù)庫軟件吸祟，沒有獨立的維護進程瑟慈，所有的維護都來自于程序本身桃移。在python中，使用sqlite3創(chuàng)建數(shù)據(jù)庫的連接葛碧，當數(shù)據(jù)庫文件不存在時借杰，會新建數(shù)據(jù)庫文件；如果數(shù)據(jù)庫文件已經(jīng)存在了进泼，則打開數(shù)據(jù)庫文件蔗衡。
連接對象可以是硬盤上面的數(shù)據(jù)庫文件，也可以是建立在內(nèi)存中的乳绕，在內(nèi)存中的數(shù)據(jù)庫執(zhí)行完任何操作后粘都，不需要提交事務的commit。
使用:memory:標識打開的是內(nèi)存數(shù)據(jù)庫
con = sqlite3.connect(":memory:")
在本地當前目錄下創(chuàng)建數(shù)據(jù)庫文件\打開數(shù)據(jù)庫文件-test.db
conn = sqlite3.connect('test.db')
打開數(shù)據(jù)庫時返回的對象conn就是一個數(shù)據(jù)庫連接對象刷袍，它可以有以下操作：
commit() --事務提交
rollback() --事務回滾
close() --關閉一個數(shù)據(jù)庫鏈接
cursor() --創(chuàng)建一個游標
創(chuàng)建一個游標對象：cu
cu = conn.cursor()
關閉一個游標對象
cu.close()
在sqlite3中翩隧，所有sql語句的執(zhí)行都要在游標對象的參與下完成
對于游標對象cu，具有以下具體操作：
execute() --執(zhí)行一條sql語句
executemany() --執(zhí)行多條sql語句
close() --游標關閉
fetchone() --從結(jié)果中取出一條記錄
fetchmany() --從結(jié)果中取出多條記錄
fetchall() --從結(jié)果中取出所有記錄
scroll() --游標滾動
'''
class SqliteHelper:
def __init__(self, dataFile):
try:
self.conn = sqlite3.connect(dataFile)
except sqlite3.Error as e:
print "連接sqlite數(shù)據(jù)庫失敗:", e.args[0]
def getcursor(self):
return self.conn.cursor()
def drop(self, table):
'''
if the table exist,please be carefull
'''
if table is not None and table != '':
cu = self.getcursor()
sql = 'DROP TABLE IF EXISTS ' + table
try:
cu.execute(sql)
except sqlite3.Error as why:
print "delete table failed:", why.args[0]
return
self.conn.commit()
print "delete table successful!"
cu.close()
else:
print "table does not exist呻纹！"
def create(self, sql):
'''
create database table
:param sql:
:return:
'''
if sql is not None and sql != '':
cu = self.getcursor()
try:
cu.execute(sql)
except sqlite3.Error as why:
print "create table failed:", why.args[0]
return
self.conn.commit()
print "create table successful!"
cu.close()
else:
print "sql is empty or None"
def insert(self, sql, data):
'''
insert data to the table
:param sql:
:param data:
:return:
'''
if sql is not None and sql != '':
if data is not None:
cu = self.getcursor()
try:
for d in data:
cu.execute(sql, d)
self.conn.commit()
except sqlite3.Error as why:
print "insert data failed:", why.args[0]
cu.close()
else:
print "sql is empty or None"
def fetchall(self, sql):
'''
query all data
:param sql:
:return:
'''
if sql is not None and sql != '':
cu = self.getcursor()
try:
cu.execute(sql)
content = cu.fetchall()
if len(content) > 0:
for item in content:
for element in item:
print element,
print ''
else:
for element in content:
print element,
print ''
except sqlite3.Error as why:
print "fetchall data failed:", why.args[0]
cu.close()
else:
print "sql is empty or None"
def fetchone(self, sql, data):
'''
query one data
:param sql:
:param data:
:return:
'''
if sql is not None and sql != '':
if data is not None:
cu = self.getcursor()
try:
d = (data,)
cu.execute(sql, d)
content = cu.fetchall()
if len(content) > 0:
for item in content:
for element in item:
print element,
print ''
else:
for element in content:
print element,
print ''
except sqlite3.Error as why:
print "fetch the data failed:", why.args[0]
return
cu.close()
else:
print "sql is empty or None"
def update(self, sql, data):
'''
update the data
:param sql:
:param data:
:return:
'''
if sql is not None and sql != '':
if data is not None:
cu = self.getcursor()
try:
for d in data:
cu.execute(sql, d)
self.conn.commit()
except sqlite3.Error as why:
print "update data failed:", why.args[0]
cu.close()
else:
print "sql is empty or None"
def delete(self, sql, data=None):
'''
delete the data
:param sql:
:param data:
:return:
'''
if sql is not None and sql != '':
cu = self.getcursor()
if data is not None:
try:
for d in data:
cu.execute(sql, d)
self.conn.commit()
except sqlite3.Error as why:
print "delete data failed:", why.args[0]
else:
try:
cu.execute(sql)
self.conn.commit()
except sqlite3.Error as why:
print "delete data failed:", why.args[0]
cu.close()
else:
print "sql is empty or None"
def __del__(self):
self.conn.close()
# test
TABLE_NAME = 'student'
def drop_table_test():
'''刪除數(shù)據(jù)庫表測試'''
print('刪除數(shù)據(jù)庫表測試...')
sqlhelper = SqliteHelper('test.db')
sqlhelper.drop(TABLE_NAME)
def create_table_test():
'''創(chuàng)建數(shù)據(jù)庫表測試'''
print('創(chuàng)建數(shù)據(jù)庫表測試...')
# 創(chuàng)建表（id,name,gender,age,address,phone)
create_table_sql = '''CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
`gender` varchar(4) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(200) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
)'''
sqlhelper = SqliteHelper('test.db')
sqlhelper.create(create_table_sql)
sqlhelper = SqliteHelper('test.db')
def save_test():
'''保存數(shù)據(jù)測試...'''
print('保存數(shù)據(jù)測試...')
save_sql = 'INSERT INTO student values (?, ?, ?, ?, ?, ?)'
data = [(1, 'Zhang', u'男', 15, u'北京', '12345678910'),
(2, 'Li', u'男', 50, u'吉林省長春市', '1567891****'),
(3, 'Zhao', u'女', 40, u'黑龍江', '18*********'),
(4, 'Xi', u'女', 30, u'上海', '1**********'),
(5, 'Liao', u'男', 15, u'湖南', '12345678910'),
(6, 'Ling', u'男', 50, u'大理', '1567891****'),
(7, 'JJJ', u'女', 40, u'***', '18*********'),
(8, 'LLL', u'女', 30, u'Hongkong', '1**********')]
sqlhelper.insert(save_sql, data)
def fetchall_test():
'''查詢所有數(shù)據(jù)...'''
print('查詢所有數(shù)據(jù)...')
fetchall_sql = '''SELECT * FROM student'''
sqlhelper.fetchall(fetchall_sql)
def fetchone_test():
'''查詢所有數(shù)據(jù)...'''
print('查詢一條數(shù)據(jù)...')
fetchall_sql = '''SELECT * FROM student WHERE ID = ? '''
data = 1
sqlhelper.fetchone(fetchall_sql, data)
update_sql = '''UPDATE student SET name = ? WHERE ID = ? '''
data = [(1, 'James'),
(2, 'Kobe')]
sqlhelper.update(update_sql, data)
def update_test():
'''更新數(shù)據(jù)'''
print('更新一條數(shù)據(jù)...')
update_sql = '''UPDATE student SET name = ? WHERE ID = ? '''
data = [('James', 1),
('Kobe', 2)]
sqlhelper.update(update_sql, data)
def delete_test():
'''刪除數(shù)據(jù)'''
print('刪除一條數(shù)據(jù)...')
update_sql = '''DELETE FROM student WHERE ID = ? AND NAME = ?'''
data = [(1, 'James')]
sqlhelper.delete(update_sql, data)
drop_table_test()
create_table_test()
save_test()
update_test()
delete_test()
參考文章：
