Create SQLAlchemy engine like pyodbc.connect()?-CodePudding

i have set up a connection to sql server using pyodbc but now i try to connect it using sqlalchemy but i get this error

raise exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Could not parse rfc1738 URL from string 'Driver={sql server};Server=L39;Database=master;Trusted_Connection=true;'

Process finished with exit code 1

this what i do for pyodbc and work good

import pyodbc
conn = pyodbc.connect('Driver={sql server};'
                      'Server=L39;'
                      'Database=master;'
                      'Trusted_Connection=true;')

i need to do it by something like this

from sqlalchemy import create_engine
engine = create_engine()

CodePudding user response：

SQL Alchemy uses a URL to connect, not a connection string (which is what pyodbc expects).

Here's the documentation you need: https://docs.sqlalchemy.org/en/14/core/engines.html#microsoft-sql-server

Copying the pertinent snippets here for posterity:

The typical form of a database URL is:
dialect driver://username:password@host:port/database

The SQL Server dialect uses pyodbc as the default DBAPI. pymssql is also available:
# pyodbc
engine = create_engine('mssql pyodbc://scott:tiger@mydsn')

# pymssql
engine = create_engine('mssql pymssql://scott:tiger@hostname:port/dbname')

CodePudding user response：

If you have a working ODBC connection string

connection_string = (
    'Driver=ODBC Driver 17 for SQL Server;'
    'Server=L39;'
    'Database=master;'
    'Trusted_Connection=Yes;'
)

you can easily use that to create a SQLAlchemy Engine:

from sqlalchemy import create_engine
from sqlalchemy.engine import URL

connection_url = URL.create(
    "mssql pyodbc",
    query={"odbc_connect": connection_string}
)
engine = create_engine(connection_url)