3

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.

cmcapellan
  • 335
  • 3
  • 16
  • 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 Jul 28 '15 at 07: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 Jul 28 '15 at 13:23
  • 1
    BTW, the chunksize argument needs an int, not a string -> `chunksize=10000` – joris Jul 29 '15 at 10:39

1 Answers1

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")
joris
  • 133,120
  • 36
  • 247
  • 202