python 连接Mysql
import pymysql
import pandas as pd
# 打开数据库连接
conn = pymysql.connect(
host='host',
port=3306,
charset ='utf8',
user='username',
passwd='pwd',
db='db_name')
# 使用 cursor() 方法创建一个游标对象 cursor
cur = conn.cursor()
# 使用 execute() 方法执行 SQL 查询
cursor.execute("SELECT VERSION()")
cursor.execute(sql)
# 获取所有记录列表
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
这样还是比较麻烦,有更好的方法直接写入DataFrame中
import pymysql
import pandas as pd
conn = pymysql.connect(
host='host',
port=3306,
charset ='utf8',
user='username',
passwd='pwd',
db='db_name')
# 直接返回得到DataFrame
df = pd.read_sql(sql,conn)