SQLAlchemy/pandas to_sql for SQLServer-在主数据库中创建表 [英] SQLAlchemy/pandas to_sql for SQLServer -- CREATE TABLE in master db

问题描述

使用MSSQL(2012版)，我正在使用SQLAlchemy和pandas(在python 2.7上)将行插入到SQL Server表中.

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

在使用特定服务器字符串尝试pymssql和pyodbc之后，我尝试使用odbc名称:

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)

这部分效果很好，并且可以与其他方法(pymssql等)一起使用.但是，pandas的to_sql方法不起作用.

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")

使用此语句，我得到一个一致的错误，即pandas试图在sql服务器的主数据库中创建一个CREATE TABLE，但这并不是针对此问题.

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.

如何使pandas/SQLAlchemy/pyodbc指向正确的mssql数据库? to_sql方法似乎忽略了我在引擎连接字符串中输入的任何内容(尽管read_sql方法似乎可以正常使用它.

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.

推荐答案

要回答此问题:问题是您在表名本身中指定了架构.如果提供"MyDB.dbo.Loader_foo"作为表名，pandas将把完整的字符串解释为表名，而不仅仅是"Loader_foo".

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".

解决方案是仅提供"Loader_foo"作为表名.如果您需要指定特定的架构以将该表写入其中，则可以使用schema kwarg(请参见

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")

这篇关于SQLAlchemy/pandas to_sql for SQLServer-在主数据库中创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！