SQL Server pyodbc connection failing in sqlalchemy but working through direct pyodbc connection

wvyml7n5 于 2023-06-21 发布在其他

关注(0)|答案(2)|浏览(121)

Why does this workc(I get a result set back):

sql_server = 'myserver.database.windows.net'
sql_database = 'pv'
sql_username = 'sqladmin'
sql_password = 'password1'   
sql_driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER='+sql_driver+';SERVER=tcp:'+sql_server+';DATABASE='+sql_database+';UID='+sql_username+';PWD='+ sql_password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT TOP 3 SAPPHIRE_CASE_ID FROM PV_ALL_SUBMISSIONS_SL")
        row = cursor.fetchone()
        while row:
            print (str(row[0]))
            row = cursor.fetchone()

But this fails:

import pyodbc
sql_engine = sqlalchemy.create_engine(f'mssql+pyodbc://{sql_username}:{sql_password}@{sql_server}/{sql_database}?driver=ODBC+Driver+17+for+SQL+Server')
df.to_sql('PV_ALL_CLOSED_CASES_SL', con=sql_engine, if_exists='append')

Error is:

OperationalError: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53)') (Background on this error at: https://sqlalche.me/e/14/e3q8)

While I know one is doing a read and the other a write, my issue seems to be just establishing a connection one way vs another, when using the same connection details. It isn't an Azure firewall issue as I am able to connect and run a select statment via the first method, but when using create_engine() of sqlalchemy, it fails to make the connection - but I am pretty sure the connection string is correct.

It is the same variables for server, user name and password being used in both connections.

I think the issue is that the real password as an "@" symbol in it, and so this interferes with the latter connection string.

sql-server

来源：https://stackoverflow.com/questions/72604194/pyodbc-connection-failing-in-sqlalchemy-but-working-through-direct-pyodbc-connec

2条答案

按热度按时间

yws3nbqq1#

Thanks to @Larnu, this worked:

from sqlalchemy.engine import URL
connection_string = f"DRIVER={sql_driver};SERVER={sql_server};DATABASE={sql_database};UID={sql_username};PWD={sql_password}"
connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})
sql_engine = sqlalchemy.create_engine(connection_url)

I dont have to url encode when I use a cx_Oracle connection, but hey it works now.

赞(0）回复(0）举报 2023-06-21

svmlkihl2#

I had the same problem where pyodbc worked fine directly but not with SQLAlchemy.

For the following combination of dependencies

Python 3.10.9
SQLAlchemy 2.0.15
pyodbc 4.0.39
ODBC Driver 17 for SQL Server

the only thing that worked was to use the URL.create() from SQLAlchemy and pass in all the parameters from ODBC directly without creating a connection string first.

engine_str = URL.create(
    drivername="mssql+pyodbc",
    username=<username>,
    password=<password>,
    host=<server>,
    port=1433,
    database=<database>,
    query={
        "driver": "ODBC Driver 17 for SQL Server",
        "TrustServerCertificate": "no",
        "Connection Timeout": "30",
        "Encrypt": "yes",
    },
)

engine = create_engine(engine_str)

Based on the ODBC connection string that Azure gave me:

Driver={ODBC Driver 18 for SQL Server};Server=<server>,1433;Database=<database>;Uid=<username>;Pwd=<password>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

I found the solution here.

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server pyodbc connection failing in sqlalchemy but working through direct pyodbc connection

2条答案

相关问题

热门标签

最新问答