pymysql - Python 使用 MySQL

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2 中则使用 mysqldb。PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。GitHub - PyMySQL/PyMySQL: Pure Python MySQL Client

import pymysql



# 连接数据库
connection = pymysql.connect(host='0.0.0.0',
                             port=3306,
                             user='nb_beta_rw',
                             password='123456',
                             db='test',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)



# 创建游标来执行SQL语句
cursor = connection.cursor()




# 创建表
effect_row = cursor.execute('''
                            CREATE TABLE  `nbdata_goldendog`.`relative_question` (
                                 `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
                                 `content` varchar(64) COMMENT '所有可选的相关问题',  
                                 `class` varchar(64)  COMMENT  '问题所属分类',
                                 `valid` varchar(64)  COMMENT  '表征是否可用',
                                 `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
                                 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', 
                                 PRIMARY KEY (`id`)
                            ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='relative_question table';
                            ''')




# 插入数据
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s',  %s,  '%s',  %s)" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
cursor.execute(sql)
connection.commit()




# 批量插入数据
sql = "INSERT INTO `nbdata_goldendog`.`relative_question` (content, class, valid) VALUES  (%s, %s, %s)"
cursor.executemany(sql,val)
connection.commit()


# pandas读取数据
import pandas as pd
data = pd.read_sql(sql = "select * from hot_question", con=connection)

查询数据（查）

fetchone()：一次获取一条记录

import  pymysql

db = pymysql.connect(host='localhost',user='root',db='test',
                     password='123456',port=3306,charset='utf8')

cursor = db.cursor()

cursor.execute('select count(*) from person')
result = cursor.fetchone()

db.close()

fetchall()：一次获取所有记录

import pymysql

db = pymysql.connect(host='localhost',user='root',db='test',
                     password='123456',port=3306,charset='utf8')

cursor = db.cursor()

cursor.execute('select name,age from person')
result = cursor.fetchall()

db.close()

使用pandas中的read_sql()方法，将提取到的数据直接转化为DataFrame，进行操作

import pymysql 
import pandas as pd

db = pymysql.connect(host='localhost',user='root',db='test',
                     password='123456',port=3306,charset='utf8')
cursor = db.cursor()

df1 = pd.read_sql("select * from student where ssex='男'",db)
display(df1)
df2 = pd.read_sql("select * from student where ssex='女'",db)
display(df2)

插入数据（增）

一次性插入一条数据

import pymysql

db=pymysql.connect(host='localhost',user='root', password='123456',
                   port=3306, db='test', charset='utf8')

cursor = db.cursor()

name = "aaa"
age = 8000
sql = 'insert into person(name,age) values ("aaa",8000)'

try:
    cursor.execute(sql)
    db.commit()
    print("插入成功")
except:
    print("插入失败")
    db.rollback()
db.close()

一次性插入多条数据

import  pymysql

db=pymysql.connect(host='localhost',user='root', password='123456',
                   port=3306, db='test', charset='utf8')

cursor = db.cursor()

sql = 'insert into person(name,age) values(%s,%s)'
datas = [('aaa',9000),('bbb',8000),('ccc',6000)]
try:
    cursor.executemany(sql,datas)
    db.commit()
    print("插入成功")
except:
    print("插入失败")
    db.rollback()
db.close()

更新数据（改）

一次性插入一条数据

import  pymysql

db=pymysql.connect(host='localhost',user='root', password='123456',
                   port=3306, db='test', charset='utf8')

cursor = db.cursor()

sql = 'update person set age=%s where name=%s'
try:
    cursor.execute(sql,[90000,"ddd"])
    db.commit()
    print("更新成功")
except:
    print("更新失败")
    db.rollback()
db.close()

删除数据（删）

import  pymysql

db=pymysql.connect(host='localhost',user='root', password='123456',
                   port=3306, db='test', charset='utf8')

cursor = db.cursor()

sql = 'delete from person where age=8000'
try:
    cursor.execute(sql)
    db.commit()
    print("删除成功")
except:
    print("删除失败")
    db.rollback()
db.close()

pymysql模块是默认开启mysql的事务功能的，因此，进行 "增"、 "删"、"改"的时候，一定要使用db.commit()提交事务，否则就看不见所插入的数据。