python mysql处理_python处理MySQL

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)

})