pandas操作sql数据库

导包

import pymssql
from sqlalchemy import create_engine
import pandas as pd

连接数据库

#读数据
conn = pymssql.connect(server='.',user = 'sa',password ='test',database='pos')
sql = 'select * from stock_data '
df0 = pd.read_sql(sql,conn)

写入数据

df =pd.read_csv('option_price.csv')
#写入数据
engine = create_engine('mssql+pymssql://sa:test@127.0.0.1/50_ak')
#conn = engine.connect()
df.to_sql(name = '50_ak',con=engine,index=False,if_exists='replace')

获取数据

conn = pymssql.connect(host='.', user='sa', password='test', database='50_ak', charset='utf8')
engine = create_engine('mssql+pymssql://sa:test@127.0.0.1/50_ak')
sql_query = 'select * from [50_ak]'
df_read = pd.read_sql(sql_query, engine)