SQLAlchemy to connect to MSSQL using instance name

Question

Ok so here is my use case. I've to make connection to different types of DB(MSSQL, oracl, MYSQL, etc.). I've .sql files for each of these database. As it seems sqlalchemy can't run .sql file so we need to open and execute the statements one by one from the .sql files over the connection.

So guys, I'm having this information and I wanted to connect using SQL Alchemy.

<db type="MSSQL" version="2005" patch_level="SP2" port="1433" id="MSSQLSERVER"/>

here MSSQLServer is the instance. No DB information is provide. so do I need DB name to connect to DB?

this is my command

engine = create_engine('mssql+pyodbc://sa:[email protected]/MSSQLSERVER', echo=True)

this is my complete code

from sqlalchemy.engine import create_engine engine = create_engine('mssql+pyodbc://sa:[email protected]', echo=False) connection = engine.connect() connection.execute( """ select @@version """ ) connection.close()

Kobi K · Accepted Answer · 2013-09-25 09:38:24Z

1

you don't need a db name, you can use this function i wrote: (it works for me on mySQL)

def ConnectToDB(self, server, uid, password): """ this method if for connecting to a db @param server: server name @param uid: username @param password: password """ connection_string = 'mysql://{}:{}@{}'.format(uid, password, server) try: self.engine = create_engine(connection_string) self.connection = self.engine.connect() except exc.SQLAlchemyError, e: self.engine = None return False, e return True, None

in your SQL statements you will say the DB and table some thing like this:

INSERT INTO `dbName`.`dbTable`.........

answered Sep 25, 2013 at 9:38

Kobi K

7,9376 gold badges47 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Hemant Over a year ago

I've edited my question to give a bit of background about what I'm trying to accomplish over here.

Hemant Over a year ago

I tried without the instance name. it seems not to work for MSSQL. I'm getting the following error - sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') None None

Kobi K Over a year ago

Do you run your sql statment this way: self.connection.execute(SQLStatment) P.S the error you wrote is it on the connection or when you are trying to send a statement?

Kobi K Over a year ago

i've searched a bit i think you can find your answer here: stackoverflow.com/a/15627017/1982962

Hemant Over a year ago

I've looked at the url that you've sent but I'm still not able to connect to the DB. I did some more research and it seems sqlserver odbc driver is not a problem when connecting from windows to windows

Mohanad Gadallah · Accepted Answer · 2025-11-15 21:31:47Z

I solved it by fixing the connection string in my .env file. Make sure you also open the two required ports in the network layer.

This is how I wrote the variable in .env file:

MSSQ_DATABASE=mssql+aioodbc://mohanad:myPassword%40123@your_mssq_server_ip\MSSQL2019SS/master?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes

my code :

# self._config.url is the value of MSSQ_DATABASE self._engine = create_async_engine( self._config.url, pool_size=self._config.pool_size, max_overflow=self._config.max_overflow, pool_timeout=self._config.pool_timeout, pool_recycle=self._config.pool_recycle, echo=self._config.echo, ) self._session_factory = async_sessionmaker( self._engine, expire_on_commit=False, class_=AsyncSession, )

Collectives™ on Stack Overflow

SQLAlchemy to connect to MSSQL using instance name

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related