connect to SQL Server using SQL alchemy without using DATABASE name

Question

I'm able to connect to SQL Server using SQL database name but my requirement is that I need to connect to sql server without connecting to database.

I've the following information available with me Port no., Instance name, db user/password, IP address.

My current command is this

engine_handle = create_engine('mssql+pyodbc://sa:pass@<IP address>/master', echo=False)

Now I'm able to connect because i've given db name - master but if i remove db name and give instance name or leave it altogether. i'll get the following error.

"engine = create_engine('mssql+pyodbc://sa:pass@IP address', echo=True)"

return self.dbapi.connect(*cargs, **cparams) DBAPIError: (Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') None None

It is ok If i can connect using instance name instead of DB name.

any help is appreciated.

Bryan · Accepted Answer · 2013-09-30 12:59:28Z

2

Use the URL that worked for you originally.

engine_handle = create_engine('mssql+pyodbc://sa:pass@<IP address>/master', echo=False)

Then you can issue CREATE DATABASE command, or whatever other operation you need to do.

When you leave out the database name, sqlalchemy is looking for a DSN named after the IP address you specify. Of course, the DSN doesn't exist and the exception is thrown. You must use a valid connection URL like those documented here.

Regarding the comment about not wanting to connect to a database - Each time you connect to SQL Server, your connection must have some database set for the connection. When created, your login had a default database assigned (if not specified, master is used).

edited Sep 30, 2013 at 12:59

answered Sep 27, 2013 at 13:03

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

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

4 Comments

Hemant Over a year ago

what if I wanted to create a new db after I connect to sql server. Also I'm not saying that i don't wanted to connected to any db but I would be ok to connect to DB instance instead of DB. As we are using the instance name to connect to DB in case of oracle, however, for MS I'm not able to do so.

Bryan Over a year ago

Use the CREATE DATABASE syntax to create a new database. The database specified in your connection won't matter.

Hemant Over a year ago

Sorry, I didn't get you. I'm very new to DB and both SQL Alchemy. Do you want me to put the Create_database command in the url or somewhere else?

Hemant Over a year ago

Ok, so if I'm understanding correctly, I've to connect to either default or some other database to connect to sql server, but I've to always specify some database. Thanks a lot for clearing it out.

Collectives™ on Stack Overflow

connect to SQL Server using SQL alchemy without using DATABASE name

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related