pandas pyodbc.connect() 有效，但 sqlalchemy.create_engine().connect() 无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44760221/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pyodbc.connect() works, but not sqlalchemy.create_engine().connect()
提问by KOB
I am attempting to write a Python script that can take Excel sheets and import them into my SQL Server Express (with Windows Authentication) database as tables. To do this, I am using pandasto read the Excel files into a pandas DataFrame, I then hope to use pandas.to_sql()to import the data into my database. To use this function, however, I need to use sqlalchemy.create_engine().
我正在尝试编写一个 Python 脚本，该脚本可以将 Excel 工作表作为表格导入到我的 SQL Server Express（使用 Windows 身份验证）数据库中。要做到这一点，我使用pandas读取Excel文件成一个pandas DataFrame，那么我希望用pandas.to_sql()将数据导入到我的数据库。但是，要使用此功能，我需要使用sqlalchemy.create_engine().
I am able to connect to my database using pyodbcalone, and run test queries. This conection is done with the followng code:
我可以pyodbc单独使用连接到我的数据库，并运行测试查询。这个连接是用下面的代码完成的：
def create_connection(server_name, database_name):
config = dict(server=server_name, database= database_name)
conn_str = ('SERVER={server};DATABASE={database};TRUSTED_CONNECTION=yes')
return pyodbc.connect(r'DRIVER={ODBC Driver 13 for SQL Server};' + conn_str.format(**config))
...
server = '<MY_SERVER_NAME>\SQLEXPRESS'
db = '<MY_DATABASE_NAME>
connection = create_connection(server, db)
cursor = connection.cursor()
cursor.execute('CREATE VIEW test_view AS SELECT * FROM existing_table')
cursor.commit()
However, this isn't much use as I can't use pandas.to_sql()- to do so I need an engine from sqlalchemy.create_engine(), but I am struggling to figure out how to use my same details in my create_connection()function above to successfully create an engine and connect to the database.
但是，这没什么用，因为我不能使用pandas.to_sql()- 为此我需要一个来自 的引擎sqlalchemy.create_engine()，但我正在努力弄清楚如何在create_connection()上面的函数中使用相同的细节来成功创建一个引擎并连接到数据库。
I have tried many, many combinations along the lines of:
我已经尝试了很多很多组合：
engine = create_engine("mssql+pyodbc://@C<MY_SERVER_NAME>\SQLEXPRESS/<MY_DATABASE_NAME>?driver={ODBC Driver 13 for SQL Server}?trusted_connection=yes")
conn = engine.connect().connection
or
或者
engine = create_engine("mssql+pyodbc://@C<MY_SERVER_NAME>\SQLEXPRESS/<MY_DATABASE_NAME>?trusted_connection=yes")
conn = engine.connect().connection
回答by Gord Thompson
A Pass through exact Pyodbc stringworks for me from Python 3.6 on Windows:
一通通过精确Pyodbc字符串对我的作品在Python 3.6在Windows上：
from sqlalchemy import create_engine
import urllib
conn_str = (
r'Driver=ODBC Driver 11 for SQL Server;'
r'Server=(local)\SQLEXPRESS;'
r'Database=myDb;'
r'Trusted_Connection=yes;'
)
quoted_conn_str = urllib.parse.quote_plus(conn_str)
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted_conn_str))
cnxn = engine.connect()
rows = cnxn.execute("SELECT name FROM sys.tables").fetchall()
print(rows)
