Python3操作关系型数据库

关系型数据库"/>

Python3操作关系型数据库

SQLite数据库

import sqlite3
import osdb_path = 'data.sqlite'
# data.sqlite文件不存在则创建该文件
if not os.path.exists(db_path):# 创建SQLite数据库conn = sqlite3.connect(db_path)# 获取Cursor对象c = conn.cursor()# 创建表c.execute('''CREATE TABLE persons(id INT PRIMARY KEY NOT NULL,name TEXT NOT NULL,age INT NOT NULL,address CHAR(100),salary REAL);''')# 提交connmit()# 关闭数据库conn.close()print('创建数据库成功')conn = sqlite3.connect(db_path)
c = conn.cursor()
# 删除数据
c.execute('DELETE FROM persons')
# 插入数据
c.execute('''INSERT INTO persons (id, name,age,address,salary) VALUES (0,'Alex',32,'California',20000.00);''')
c.execute('''INSERT INTO persons (id, name,age,address,salary) VALUES (1,'Allen',25,'Texal',15000.00);''')
connmit()
print('插入数据成功')
# 查询数据
persons = c.execute("SELECT * FROM persons order by age")
print(type(persons))
result = []
for p in persons:value = dict(id=p[0], name=p[1], age=p[2], address=p[3], salary=p[4])result.append(value)
conn.close()
print(result)

MySQL数据库

from pymysql import *
import jsondef connectDB():# connect("服务器IP", "用户名", "密码", "数据库名")db = connect("192.168.4.108", "wljkzx", "wljkzx", "ssm", charset='utf8')return dbdef createTable(db):c = db.cursor()sql = '''CREATE TABLE persons(id INT PRIMARY KEY NOT NULL,name TEXT NOT NULL,age INT NOT NULL,address CHAR(100),salary REAL);'''try:# 执行SQL语句c.execute(sql)dbmit()return Trueexcept Exception as e:# 回滚db.rollback()return Falsedef insertRecords(db):c = db.cursor()try:c.execute('DELETE FROM persons')c.execute('''INSERT INTO persons (id, name,age,address,salary) VALUES (0,'Alex',32,'California',20000.00);''')c.execute('''INSERT INTO persons (id, name,age,address,salary) VALUES (1,'Allen',25,'Texal',15000.00);''')dbmit()return Trueexcept Exception as e:print(e)db.rollback()return Falsedef selectRecords(db):c = db.cursor()sql = 'SELECT * FROM persons order by age'c.execute(sql)# 调用fetchall方法获取全部记录results = c.fetchall()# 重新组织结果成其他形式fields = ['id', 'name', 'age', 'address', 'salary']records = []for row in results:records.append(dict(zip(fields, row)))return json.dumps(records)db = connectDB()
if createTable(db):print("成功创建数据库")
else:print("表已存在")if insertRecords(db):print("成功插入记录")
else:print("插入记录失败")
print(selectRecords(db))
db.close()

转载于: