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()