Python中连接MySQL的库主要有三个,Python-MySQL,PyMySQL和SQLAlchemy,其中Python-MySQL已经停止更新,且只支持Python2,目前使用最广泛的是PyMySQL,使用方法如下:
安装
pip install pymysql
连接
代码中只需要 import pymysql 即可导入使用,pymysql的一般使用步骤为:
配置数据库连接信息
连接数据库,获取连接对象
使用连接对象获取一个游标(cursor)对象
使用cursor对象提供的方法执行SQL语句
关闭cursor对象
关闭连接对象
操作
以下为增删改查的实例:
查询函数, 传入键值进行查询并返回结果列表
def find(key, word):
con = pymysql.connect(host='localhost', user='root', password='1111', port=3306, db='logistics')
cursor = con.cursor()
sql = "select * from main_info where {}='{}'".format(key, word)
list = []
try:
cursor.execute(sql)
row = cursor.fetchone()
while row:
print(row)
list.append(row)
row = cursor.fetchone()
except Exception as e:
print(e)
finally:
cursor.close()
con.close()
return list
插入函数, 插入新信息
def insert(id, name):
con = pymysql.connect(host='localhost', user='root', password='1111', port=3306, db='logistics')
cursor = con.cursor()
data = {
'id': id,
'name': name,
}
table = 'main_info'
keys = ', '.join(data.keys())
values = (', '.join('"' + item + '"' for item in data.values()))
sql = "insert into {0}({1}) values({2})".format(table, keys, values)
try:
if cursor.execute(sql):
con.commit()
result = True
except Exception as e:
con.rollback()
result = False
finally:
cursor.close()
con.close()
return result
删除函数, 以删除制定id为例
def delete(id):
con = pymysql.connect(host='localhost', user='root', password='1111', port=3306, db='logistics')
cursor = con.cursor()
sql = "delete from main——info where id = {}".format(id)
try:
if cursor.execute(sql):
con.commit()
result = True
except Exception as e:
con.rollback()
result = False
finally:
cursor.close()
con.close()
return result
修改函数, 修改指定id的name部分
def modify(id, name):
con = pymysql.connect(host='localhost', user='root', password='1111', port=3306, db='logistics')
cursor = con.cursor()
sql = "update from main——info set name = {} where id = {}".format(name, id)
try:
if cursor.execute(sql):
con.commit()
result = True
except Exception as e:
con.rollback()
result = False
finally:
cursor.close()
con.close()
return result