「python」MYSQL

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)