MSSQL.create_engine

MSSQL.create_engine(database_name=None, auth=None, password=None)[source]

Create a SQLAlchemy connectable engine for connecting to a SQL Server database.

This method generates and returns a SQLAlchemy engine configured to connect to a SQL Server database using the provided or default database name, authentication method and password. The returned engine can be used to execute SQL queries and interact with the database.

Parameters:

  • database_name (str | None) – Name of the database to connect to; defaults to the currently-connected database if database_name=None.

  • auth (str | None) – Authentication method used to establish the connection; defaults to the current authentication method if auth=None.

  • password (str | int | None) – User’s password; if password=None (default), manual input of the correct password is required to establish the connection.

Returns:

A SQLAlchemy connectable engine.

Return type:

sqlalchemy.engine.Engine

  1. Use pyodbc (or pypyodbc):

    connect_string = 'driver={...};server=...;database=...;uid=username;pwd=...'
conn = pyodbc.connect(connect_string)  # conn = pypyodbc.connect(connect_string)

  2. Use SQLAlchemy:

    conn_string = 'mssql+pyodbc:///?odbc_connect=%s' % quote_plus(connect_string)
engine = sqlalchemy.create_engine(conn_string)
conn = engine.connect()

Examples:

>>> from pyhelpers.dbms import MSSQL
>>> mssql = MSSQL(verbose=True)
Connecting <server_name>@localhost:1433/master ... Successfully.
>>> db_engine = mssql.create_engine()
>>> db_engine.name
'mssql'
>>> db_engine.dispose()