mysql - converting pandas to sql and sending it to a sql-server
问题描述
I would like to convert a pandas data-frame df
to an sql database and sent it to a sql-server. This is what I have done so far...
import sqlalchemy
import pickle
import os
server = 'HostAddres'
db = 'test_db'
login = 'Username'
passwd = 'Password'
engine_str = 'mysql+pymysql://{}:{}@{}/{}'.format(login, passwd, server, db)
engine = sqlalchemy.create_engine(engine_str, echo=False, encoding='utf-8')
df.to_sql(con=engine, name = "df_test", if_exists='replace', index=False)
OperationalError: (pymysql.err.OperationalError) (1045, "Access denied for user 'USER'@'HostAdress' (using password: YES)") (Background on this error at: http://sqlalche.me/e/e3q8)
Anyone knows how to fix this or what I am doing wrong ?
解决方案
the create engine string should look like:
dialect+driver://username:password@host :port /database (don't include the spaces)
so theoretically your script should be:
server = 'HostAddres'
db = 'test_db'
login = 'Username'
passwd = 'Password'
port = '8888'
engine_str = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(login, passwd, server,port, db)
engine = sqlalchemy.create_engine(engine_str, echo=False, encoding='utf-8')
df.to_sql(con=engine, name = "df_test", if_exists='replace', index=False)
As for the "to_sql" part itself there seems to be nothing wrong with it.
It's probably what i said in the comments and you only need to make sure you have permission to create or modify that database.
推荐阅读
- c++ - 重载 << 运算符函数:未解决的外部符号错误
- git - 无法使用 sourcetree macbook 从 gitlab 克隆
- javascript - 如何确保在 Expo/React Native 中的任何其他内容之前调用代码?
- symfony - 命令“symfony 控制台信使:失败:显示”不起作用
- swift - 如何使用 Codable 解析不同对象列表的 Json 数组?
- javascript - 如果 location.state 为空,则反应路由器重定向
- django-channels - 如何设置 django-channels ChannelsLiveServerTestCase 服务器端口?
- c# - 外壳导航
- wget - 无法下载 ADYEN 报告
- c++ - 我在 Visual Studio Code 中的 C++ 代码显示错误 AccessDenied