Trouble writing to mysql database through pandas.to_sql using sqlalchemy, but not through sqlalchemy without pandas

Question

I am trying to write a dataframe to a MySQL table. Here is my code:

import pandas as pd
import sqlalchemy 
connectString="""mysql+pymysql://userName:passWord@hostIP/schema"""
engine = sqlalchemy.create_engine(connnectString)
connection=engine.connect()
myDataFrame.loc[:,['col1','col2']].to_sql('myTable',con=engine,index=True,if_exists='fail')

The code fails with what seems like a permissions error.

OperationalError: (pymysql.err.OperationalError) (1142, "CREATE command denied to user 'userName'@'hostIP' for table 'myTable'") [SQL: '\nCREATE TABLE `myTable` (\n\ID BIGINT, \n\t`col1` BIGINT, \n\t`col2` BIGINT\n)\n\n']

To test the permissions issue, I am able to successfully create and write a table when I run the following commands:

import pandas as pd
import sqlalchemy 
connectString="""mysql+pymysql://userName:passWord@hostIP/schema"""
engine = sqlalchemy.create_engine(connnectString)
connection=engine.connect()
command="""create table myTable as 
    SELECT * FROM anotherTable"""
engine.execute(command)
connection.close()

Also, the "show grants" command on the database I am trying to write to shows me that I have write permissions on it. The database is installed on a Red Hat Enterprise Linux Server, and I am running the commands from a jupyter-notebook opened on the same OS, but on a different machine.

Any ideas what's going on? Do you need any other info? Thanks.

I had this problem yesterday, I was only able to fix it using a windows authentication string format instead of username:password, but that probably won't be of help to you. — bphi
– bphi, Commented Aug 30, 2017 at 18:26
did you run the second script from the same machine as the first one? — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Aug 30, 2017 at 18:33

mootwo7 · Accepted Answer · 2017-10-11 20:08:56Z

The issue was that I did not have permission to write in some schemas, but had permission in others.

Looking at my above code:

connectString="""mysql+pymysql://userName:passWord@hostIP/schema"""

Here, I do not have write permission on "schema", but have read permission. When I would create a table using:

command="""create table myTable as 
SELECT * FROM anotherTable"""

'myTable' was actually: 'anotherSchemaWhereIhaveWritePermission'.'tableName'

And mysql would correctly create a table "tableName" at this other schema. So here, the schema specified in the initial connection was not important.

When I would run the supposedly equivalent command in pandas:

myDataFrame.loc[:,['col1','col2']].to_sql('myTable',con=engine,index=True,if_exists='fail')

Here, sql would try to create a table named "'anotherSchemaWhereIhaveWritePermission'.'tableName'" in the original schema I connected to, where I do not have write permission. Hence the error.

Collectives™ on Stack Overflow

Trouble writing to mysql database through pandas.to_sql using sqlalchemy, but not through sqlalchemy without pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related