SQLAlchemy/pandas to_sql for SQLServer -- CREATE TABLE in master db

Question

Using MSSQL (version 2012), I am using SQLAlchemy and pandas (on Python 2.7) to insert rows into a SQL Server table.

After trying pymssql and pyodbc with a specific server string, I am trying an odbc name:

import sqlalchemy, pyodbc, pandas as pd

engine = sqlalchemy.create_engine("mssql+pyodbc://mssqlodbc")
sqlstring = "EXEC getfoo"
dbdataframe = pd.read_sql(sqlstring, engine)

This part works great and worked with the other methods (pymssql, etc). However, the pandas to_sql method doesn't work.

finaloutput.to_sql("MyDB.dbo.Loader_foo",engine,if_exists="append",chunksize="10000")

With this statement, I get a consistent error that pandas is trying to do a CREATE TABLE in the sql server Master db, which it is not permisioned for.

How do I get pandas/SQLAlchemy/pyodbc to point to the correct mssql database? The to_sql method seems to ignore whatever I put in engine connect string (although the read_sql method seems to pick it up just fine.

What version of pandas are you using? And can you try with specifying the table name as Loader_foo? (the dotted name will be used as a whole as table name, if you want to specify a schema there is a schema keyword) — joris
– joris, Commented Jul 28, 2015 at 7:23
Hi @joris, I am using pandas 0.16.2. Your tip to remove the dotted name (switch from MyDB.dbo.Loader_foo to just Loader_foo worked, thank you. Also, I am able to switch between a DSN and the direct server name and it works either way. Thanks! — cmcapellan
– cmcapellan, Commented Jul 28, 2015 at 13:23
BTW, the chunksize argument needs an int, not a string -> chunksize=10000 — joris
– joris, Commented Jul 29, 2015 at 10:39

joris · Accepted Answer · 2015-07-29 10:38:52Z

4

To have this question as answered: the problem is that you specify the schema in the table name itself. If you provide "MyDB.dbo.Loader_foo" as the table name, pandas will interprete this full string as the table name, instead of just "Loader_foo".

Solution is to only provide "Loader_foo" as table name. If you need to specify a specific schema to write this table into, you can use the schema kwarg (see docs):

finaloutput.to_sql("Loader_foo", engine, if_exists="append")
finaloutput.to_sql("Loader_foo", engine, if_exists="append", schema="something_else_as_dbo")

answered Jul 29, 2015 at 10:38

joris

140k37 gold badges258 silver badges207 bronze badges

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

1 Comment

cmcapellan Over a year ago

Thanks @joris...also caught the chunksize as int too.

Collectives™ on Stack Overflow

SQLAlchemy/pandas to_sql for SQLServer -- CREATE TABLE in master db

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related