pymysql之python连接MySQL【黑马python培训】

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数据库，并做简单的操作。
---------------------