import pymysql
# 创建数据库连接
db = pymysql.connect(host="localhost", port=3306, user="root", password="123456", database="stu", charset="utf8")
# 创建游标对象
cur = db.cursor()
sql = "select * from class where gender='m';"
cur.execute(sql)
# 获取一个查询结果
one_row = cur.fetchone()
print(one_row) # 元组
# 获取多个查询结果
many_row = cur.fetchmany(2)
print(many_row) # 元组套元组
# 获取所有查询结果
all_row = cur.fetchall()
print(all_row) # 元组套元组
写操作
try:
sql = "insert into class (name,age,score) values ('%s','%s','%s')" % (name, age, score) # 注:字符串类型%s要加引号
cur.execute(sql)
db.commit()
except Exception as e:
db.rollback() # 退回到commit执行之前的数据库状态
print(e)
# 关闭游标对象
cur.close()
# 关闭数据苦连接
db.close()
DataFrame与mysql交互
from sqlalchemy import create_engine
from sqlalchemy.types import Integer, VARCHAR, Date, DECIMAL
conn = create_engine("mysql+pymysql://username:password@localhost:3306/dbname?charset=utf8")
df.to_sql("表名", con=conn, index=False, if_exists="replace", dtype={
"id":Integer(),
"GMV_date":Date(),
"platform":VARCHAR(length=20),
"GMV_result":DECIMAL(),
"GMV_target":DECIMAL(),
"completion":DECIMAL(precision=7, scale=2, asdecimal=True)
})