converting pandas to sql and sending it to a sql-server

Question

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 ?

Try posting the dataframe so we can help you better, also you may be missing a ":" between the "@host" and "/database". it should look like @host:3306/database — Gorlomi
– Gorlomi, Commented Mar 1, 2020 at 21:03
Another possibility is that you simply don't have permission to create or modified in the database you're working on — Gorlomi
– Gorlomi, Commented Mar 1, 2020 at 21:08
@Gorlomi Thanks for your comment ! Sorry, but where am I missing an ":" ? I don#t see which line you are referring to. Hmm, good point. I will have to check with the administrator of the server. — henry
– henry, Commented Mar 1, 2020 at 21:24

Gorlomi · Accepted Answer · 2020-03-01 21:30:00Z

1

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.

answered Mar 1, 2020 at 21:30

Gorlomi

5152 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

henry Over a year ago

Can you add a reference to the documentation of sqlalchemy where it says that a port need to be added to the engine ?

Collectives™ on Stack Overflow

converting pandas to sql and sending it to a sql-server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related