创建 SQLAlchemy Engine 时出现 Pyodbc 错误

Question

我正在尝试将一个名为 df 的 Pandas 数据帧写入 SQL Express 中的表中，如下面的代码所示，但我在第 engine = sqlalchemy.create_engine('mssql://LENOVO-PCSQlEXPRESS\SQLEXPRESS/Databasewithinfo?trusted_connection=yes') 行中DBAPIError: (pyodbc.Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')错误。我在这篇文章中看到了答案，并试图遵循它。我知道我的server_name = LENOVO-PCSQlEXPRESS和database_name = Databasewithinfo，因此正在努力理解我哪里出了问题。

import sqlalchemy
from sqlalchemy import create_engine
engine = sqlalchemy.create_engine('mssql://LENOVO-PCSQlEXPRESS\SQLEXPRESS/Databasewithinfo?trusted_connection=yes')
df.to_sql('JPY_data', engine, chunksize=1000)

谢谢

Answer 1

这不是直接的答案，而是测试连接变体直到它起作用的工具包。

你真的想扔尽可能多的连接字符串变体，直到有什么东西工作。 我已经放了两个了。

我确实注意到您引用的帖子在连接字符串中只有一次 SQLEXPRESS，与您不同。

import sqlalchemy
from sqlalchemy import create_engine
def test_it(t_connect_string):
    #these are your connection setting, they are constant
    di = dict(server="LENOVO-PC",database="Databasewithinfo")    
    connect_string = t_connect_string % di
    try:
        engine = sqlalchemy.create_engine(connect_string)
        print "%s Success!" %  (connect_string)
        return True
    except Exception, e:
        print "%s Exception=>%s" %  (connect_string, e)
        return False
#put as many possible templates as you need until it connects, then you're good
li_test = [
    """mssql://%(server)sSQlEXPRESS\SQLEXPRESS/%(database)s?trusted_connection=yes""",
    #the post you refer to seems to have this format instead...
    """mssql://%(server)s\SQLEXPRESS/%(database)s?trusted_connection=yes """,
]
#test them until something works.
for test in li_test:
    result = test_it(test)
    if result:
        break

它对我来说很糟糕，因为我没有安装 odbc，但希望你会得到更多相关的错误。

mssql://LENOVO-PCSQlEXPRESSSQLEXPRESS/Databasewithinfo?trusted_connection=yes Exception=>No module named pyodbc
mssql://LENOVO-PCSQLEXPRESS/Databasewithinfo?trusted_connection=yes  Exception=>No module named pyodbc