pymysql之python连接MySQL

本文主要是介绍pymysql之python连接MySQL，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

python连接MySQL之pysql
Python在2.X版本下连接MySQL使用MySQLdb,但是它不支持Python3.X版本。PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。

安装pymysql
pip3 install pymysql

连接MySQL数据库
db = pymysql.connect(host='localhost', user='root', password='密码', port=3306)
cursor = db.cursor()

PS：password='密码’中的密码是你的MySQL数据库的真实密码
测试连接是否成功：

# sql语句
sql = 'select version()'
# 运行
try:
cursor.execute(sql)
data = cursor.fetchone()
print('VERSION：', data)
except Exception as e:
db.collback()
print(e)
finally:
db.close()

测试结果：

新建数据库
# 连接
db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306)
cursor = db.cursor()
try:
sql = 'create DATABASE ziMing DEFAULT CHARACTER SET utf8'
cursor.execute(sql)
except Exception as e:
print(e)

在数据库中新建数据表
此时，在链接数据库时要绑定数据库，db=‘ziMing’
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
sql = 'create table if not exists students(id VARCHAR (255) NOT NULL, name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY (id))'
try:
cursor.execute(sql)
except Exception as e:
print(e)

增加数据1.0
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
id = '100'
name = 'ziMing'
age = '18'
sql = 'insert into students(id, name, age) VALUES(%s, %s, %s)'
try:
cursor.execute(sql, (id, name, age))
print('success')
except Exception as e:
print(e)
db.rollback()

增加数据2.0
利用字典实现动态增加数据
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
data = {
'id': '101',
'name': 'Michael',
'age': 16
}
table = 'students'
keys = ', '.join(data.keys())
print(keys)
values = ', '.join(['%s'] * len(data))
print(values)
sql = 'insert into {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
try:
cursor.execute(sql, tuple(data.values()))
db.commit()
print('Success')
except Exception as e:
print(e)
db.rollback()
finally:
db.close()
查看数据1.0
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
sql = 'select * from students WHERE age > 1'
try:
cursor.execute(sql)
print('count:', cursor.rowcount)
data = cursor.fetchone()
print('data:', data)
datas = cursor.fetchall()
print('datas:', datas)
for row in datas:
      print(row)
print('success')
except Exception as e:
print(e)
db.rollback()

查看数据2.0
用while循环查询每一行的数据，fetchall()的内存开销
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
# cursor = db.cursor()
# sql = 'select * from students WHERE age > 1'
# try:
#    cursor.execute(sql)
#    print('count:', cursor.rowcount)
#    data = cursor.fetchone()
#    while data:
#       print(data)
#       data = cursor.fetchone()
# except Exception as e:
#    print(e)
#    db.rollback()

修改数据
用where定位，再用update修改
db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()
sql = 'update students set age = %s WHERE name = %s'
try:
cursor.execute(sql, (36, 'Janel'))
db.commit()
print('Success')
except Exception as e:
print(e)
db.rollback()
finally:
db.close()

插入数据
data = {
'id': '101',
'name': 'Michael',
'age': 16
}
table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data))

db = db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()

# ON DUPLICATE KEY UPDATE:如果主键已经存在，就执行更新操作
sql = 'insert into {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE '.format(table=table, keys=keys, values=values)
update = ', '.join([" {key} = %s".format(key=key) for key in data])
sql += update
try:
cursor.execute(sql, tuple(data.values()) * 2)
db.commit()
print('Success')
except Exception as e:
print(e)
db.rollback()
finally:
db.close()

删除数据
同样，也是先用where定位数据，再用delete删除数据
db = pymysql.connect(host='localhost', user='root', password='@ROOT_root_123', port=3306, db='ziMing')
cursor = db.cursor()

table = 'students'
condition = 'age > 18'

sql = 'delete from {table} WHERE {condition}'.format(table=table, condition=condition)
try:
cursor.execute(sql)
db.commit()
print('Success')
except Exception as e:
print(e)
db.rollback()
finally:
db.close()

总结
上面是Python使用pymysql连接MySQL数据库并进行简单操作的示例。显然，如此频繁的编写sql语句无法做到高效所以，我将在另一篇博客中介绍如何使用Python + sqlalchemy连接MySQL数据库，并做简单的操作。
---------------------

这篇关于pymysql之python连接MySQL的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！