PyMySQL包含了一个纯Python的MySQL客户端的库,它的目的是用来替换MySQLdb,并且工作在CPython,PyPy和IronPython。
PyMySQL官方地址:https://pypi.python.org/pypi/PyMySQL#documentation
下面是一个简单的例子和说明:
dbInfo={
'host': 'host_IP',
'port': 3306,
'user': 'user_name',
'password': 'password',
'db': 'db_name',
'charset': 'utf8',}
def queryMysql(sql): #将数据库的信息当成字段传给connect(),这样方便数据和函数分离,也可以直接在connect()里面写出参数
conn = pymysql.connect(**dbInfo)
#建立连接后创建游标
cur = conn.cursor()
#执行sql语句,select语句还需要fetch才能拿到数据,否则返回的是数据个数
cur.execute(sql)
res=cur.fetchall()
cur.close()
#sql语句不是自动提交的,需要手动提交才能保持修改
conn.commit()
#执行sql语句后关闭连接
conn.close()
return res sql='select * from table_name'
result=queryMysql(sql)
print(result)
下面是官方文档的例子,说明已经很详细了,我再简单补充一下:
import pymysql.cursors # Connect to the database
connection = pymysql.connect(host='localhost',
user='user',
password='passwd',
db='db',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)#cursorclass选择返回数据的格式,字典或者列表。
#try语句防止出现assert中断
try:
#使用with语句可以免去关闭connection的操作
with connection.cursor() as cursor:
# Create a new record
sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
cursor.execute(sql, ('[email protected]', 'very-secret')) # connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit() with connection.cursor() as cursor:
# Read a single record
sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
cursor.execute(sql, ('[email protected]',))
result = cursor.fetchone()
print(result)
finally:
connection.close()