“数据源名称太长" SQLAlchemy中的mssql + pyodbc错误 [英] "Data source name too long" error with mssql+pyodbc in SQLAlchemy

问题描述

我正在尝试使用SQLAlchemy和pyobdc将数据帧上载到Azure SQL Server数据库上的数据库.我已经建立了连接，但是上传时出现错误提示

I am trying to upload a dataframe to a database on Azure SQL Server Database using SQLAlchemy and pyobdc. I have established connection but when uploading I get an error that says

(pyodbc.Error)('IM010'，'[IM010] [Microsoft] [ODBC驱动程序管理器]数据源名称太长(0)(SQLDriverConnect)')

我不确定这个错误的出处，因为我之前使用sqlalchemy都没有问题.我在下面附加了我的代码，有人可以帮助我诊断问题吗?

I'm not sure where this error is coming from since I've used sqlalchemy before without a problem. I've attached my code below, can anybody help me diagnose the problem?

username = 'bcadmin'
password = 'N@ncyR2D2'
endpoint = 'bio-powerbi-bigdata.database.windows.net'

engine = sqlalchemy.create_engine(f'mssql+pyodbc://{username}:{password}@{endpoint}')

df.to_sql("result_management_report",engine,if_exists='append',index=False)

我知道其他ETL方法，例如Data Factory和SSMS，但我更喜欢使用熊猫作为ETL流程.

I know of other ETL methods like Data Factory and SSMS but I'd prefer to use pandas as the ETL process.

请帮助我解决此错误.

推荐答案

这里有三个问题:

1. 如果用户名或密码可能包含@字符，则需要在连接URI中对其进行转义.
2. 对于mssql+pyodbc方言，必须在URI中包括数据库名称，以便SQLAlchemy能够识别主机名".连接(与"DSN"连接相对).
3. 对于mssql+pyodbc主机名连接，还必须使用driver属性提供ODBC驱动程序名称.

1. If a username or password might contain an @ character then it needs to be escaped in the connection URI.
2. For the mssql+pyodbc dialect, the database name must be included in the URI in order for SQLAlchemy to recognize a "hostname" connection (as opposed to a "DSN" connection).
3. Also for mssql+pyodbc hostname connections, the ODBC driver name must be supplied using the driver attribute.

构建适当的连接URI的最简单方法是使用sqlalchemy.engine.url.URL方法:

The easiest way to build a proper connection URI is to use the sqlalchemy.engine.url.URL method:

import sqlalchemy as sa

my_uid = "bcadmin"
my_pwd = "N@ncyR2D2"
my_host = "bio-powerbi-bigdata.database.windows.net"
my_db = "master"
my_odbc_driver = "ODBC Driver 17 for SQL Server"

connection_uri = sa.engine.url.URL(
    "mssql+pyodbc",
    username=my_uid,
    password=my_pwd,
    host=my_host,
    database=my_db,  # required; not an empty string
    query={"driver": my_odbc_driver},
)
print(connection_uri)
"""console output:
mssql+pyodbc://bcadmin:[e-mail]/master?driver=ODBC+Driver+17+for+SQL+Server
"""

engine = sa.create_engine(connection_uri, fast_executemany=True)

这篇关于“数据源名称太长" SQLAlchemy中的mssql + pyodbc错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！