pandas读取mysql

发表于 | 分类于 | 阅读次数：

#读取

方式一:(元组嵌套格式)

1
2
3
4
5
db=pymysql.connect(host='localhost',port=3306,user='root',database='comp',password='123456',charset='utf8mb4')
cursor = db.cursor()
sql = 'select * from company_message '
cursor.execute(sql)
company_data = cursor.fetchall()

方式二:(dataframe格式)

1
2
3
4
conn=pymysql.connect(host='localhost',port=3306,user='root',database='comp',password='123456',charset='utf8mb4')
sql = 'select * from company_message '
datasss = pd.read_sql(sql,conn)
datasss

方式三:(dataframe格式)

1
2
3
4
5
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/comp')
sql = 'select * from company_message '
df = pd.read_sql_query(sql,engine)
print(df)

#存储

方式一:直接dataframe保存到mysql

1
2
3
4
5
from sqlalchemy import create_engine
import pandas as pd
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/comp?charset=UTF8')
pd.io.sql.to_sql(app_times,'app_times', engine, index=False, if_exists='append', chunksize=10000,)    
engine.dispose()

###方式二:正常存储

1
2
3
4
5
6
7
8
9
db = pymysql.connect(host='localhost', port=3306, user='root',database='comp',password='123456',
                             charset='utf8mb4')
cursor = db.cursor()
data = (data[0], data[1])
sql = 'insert into patent_apply_date (app_text,apply_msg) values (%s,%s)'
cursor.execute(sql, data)
db.commit()
cursor.close()
db.close()