python中连接mysql数据库的方法

版本
pandas 1.1.0
pymysql 0.10.0
python 3.6.10
sqlalchemy 1.3.18

pymysql链接数据库

import pymysql

conn = pymysql.connect(
                host='localhost',
                user='root',
                password='123456',
                database='mydata',
                port=3306,
                charset='utf8'
)
cur = conn.cursor()
sql = 'select * from dataanalyst'
cur.execute(sql)
data = cur.fetchall()
print(data)
conn.close()

pymysql+pandas链接数据库

• 网上大部分博客是用sqlalchemy搭配pandas，然后我搜索了一下，听某讲师在视频中说pandas不支持pymysql实例化的connect，但是我在本机上测试是没问题的，可能是pandas的老版本不支持吧。我觉得这种pymysql+pandas用起来挺方便的。

import pymysql
import pandas as pd

conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='123456',
    database='mydata',
    port=3306,
    charset='utf8'
)
sql='select * from dataanalyst'
# 网上大部分是把sqlalchemy的connetion传进去，但是我测试使用pymysql的connect也可以。
data = pd.read_sql(sql,conn)
print(data)
conn.close()

sqlalchemy连接数据库

from sqlalchemy import create_engine
import pymysql

conn_info = 'mysql+pymysql://root:123456@localhost:3306/mydata?charset=utf8'
sql='select * from dataanalyst'
# create_engine没有cursor()属性，所以需要加.raw_connection()
conn = create_engine(conn_info).raw_connection()
cur = conn.cursor()
cur.execute(sql)
data = cur.fetchall()
print(data)
conn.close()

sqlalchemy,mysql,pandas连接数据库

import pandas as pd
import pymysql
from sqlalchemy import create_engine

conn_info = 'mysql+pymysql://root:123456@localhost:3306/mydata?charset=utf8'
sql='select * from dataanalyst'
conn = create_engine(conn_info).raw_connection()
data = pd.read_sql(sql,conn)
print(data)
conn.close()