连接创建数据库
# connect_sqlite3.py
import sqlite3
DB_Name = 'test.db'
# 连接数据库,如果不存在则会在当前目录创建
conn = sqlite3.connect(DB_Name)
print('连接数据库%s成功' % (DB_Name))
程序执行结果:
连接数据库test.db成功
创建数据库表
# create_table_sqlite3.py
import sqlite3
DB_Name = 'test.db'
Table_Name = 'STUDENT'
# 连接数据库,如果不存在则会在当前目录创建
conn = sqlite3.connect(DB_Name)
try:
# 创建游标
cursor = conn.cursor()
# 创建STUDENT表的SQL语句,默认编码为UTF-8
SQL = '''
CREATE TABLE %s (
SNO CHAR(10),
SNAME VARCHAR(20) NOT NULL,
PRIMARY KEY(SNO)
)
''' % (Table_Name)
# 创建数据库表
cursor.execute(SQL)
# 提交到数据库
conn.commit()
print('创建数据库表%s成功' % (Table_Name))
except Exception as e:
print(e)
# 回滚
conn.rollback()
print('创建数据库表%s失败' % Table_Name)
finally:
# 关闭数据库
conn.close()
程序执行结果:
创建数据库表STUDENT成功
增删改查
插入数据
# insertData_sqlite3.py
import sqlite3
DB_Name = 'test.db'
# 连接数据库,如果不存在则会在当前目录创建
conn = sqlite3.connect(DB_Name)
try:
# 创建游标
cursor = conn.cursor()
# 向STUDENT表插入数据的SQL语句
SQL = '''
INSERT INTO STUDENT VALUES('2016081111','张三'),('2016081112','李四'),('2016081113','王五');
'''
# 插入数据
cursor.execute(SQL)
# 提交到数据库
conn.commit()
print('插入数据到表STUDENT成功')
except Exception as e:
print(e)
# 回滚
conn.rollback()
print('插入数据到表STUDENT失败')
finally:
# 关闭数据库
conn.close()
程序执行结果为:
插入数据到表STUDENT成功
查询数据
# selectData_sqlite3.py
import sqlite3
DB_Name = 'test.db'
# 连接数据库,如果不存在则会在当前目录创建
conn = sqlite3.connect(DB_Name)
try:
# 创建游标
cursor = conn.cursor()
# 查询数据的SQL语句
SQL = '''
SELECT * FROM STUDENT;
'''
# 查询数据
cursor.execute(SQL)
# 获取一条数据
one = cursor.fetchone()
print(one)
# 获取所有数据
for row in cursor.fetchall():
print(row)
except Exception as e:
print(e)
print('查询数据失败')
finally:
# 关闭数据库
conn.close()
程序执行结果为:
('2016081111', '张三')
('2016081112', '李四')
('2016081113', '王五')
修改数据
# updateData_sqlite3.py
import sqlite3
DB_Name = 'test.db'
# 连接数据库,如果不存在则会在当前目录创建
conn = sqlite3.connect(DB_Name)
try:
# 创建游标
cursor = conn.cursor()
# 查询数据的SQL语句
SELECT_SQL = '''
SELECT * FROM STUDENT;
'''
# 修改数据的SQL语句
UPDATE_SQL = '''
UPDATE STUDENT SET SNAME='%s' WHERE SNO='%s'
''' % ('李华', '2016081111')
# 修改前
print('修改前')
cursor.execute(SELECT_SQL)
for row in cursor.fetchall():
print(row)
# 修改数据
cursor.execute(UPDATE_SQL)
# 提交到数据库
conn.commit()
# 修改后
print('修改后')
cursor.execute(SELECT_SQL)
for row in cursor.fetchall():
print(row)
except Exception as e:
print(e)
print('修改数据失败')
finally:
# 关闭数据库
conn.close()
程序执行结果:
修改前
('2016081111', '张三')
('2016081112', '李四')
('2016081113', '王五')
修改后
('2016081111', '李华')
('2016081112', '李四')
('2016081113', '王五')
删除数据
# deleteData_sqlite3.py
import sqlite3
DB_Name = 'test.db'
# 连接数据库,如果不存在则会在当前目录创建
conn = sqlite3.connect(DB_Name)
try:
# 创建游标
cursor = conn.cursor()
# 查询数据的SQL语句
SELECT_SQL = '''
SELECT * FROM STUDENT;
'''
# 删除数据的SQL语句
DELETE_SQL = '''
DELETE FROM STUDENT WHERE SNO='%s'
''' % ('2016081111')
# 删除前
print('删除前')
cursor.execute(SELECT_SQL)
for row in cursor.fetchall():
print(row)
# 删除数据
cursor.execute(DELETE_SQL)
# 提交到数据库
conn.commit()
# 删除后
print('删除后')
cursor.execute(SELECT_SQL)
for row in cursor.fetchall():
print(row)
except Exception as e:
print(e)
print('删除数据失败')
finally:
# 关闭数据库
conn.close()
程序执行结果:
删除前
('2016081111', '李华')
('2016081112', '李四')
('2016081113', '王五')
删除后
('2016081112', '李四')
('2016081113', '王五')