7

Is it possible to connect to MSSQL server, using sqlalchemy and thencreate a database?

I use the following:

sqlalchemy.create_engine("mssql+pyodbc://sa:pwd@localhost/")

But I get an error:

Detail DBAPIError: (Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') None None

I would like to connect to the server, then create database and work with it.

JBL
  • 12,588
  • 4
  • 53
  • 84
Eugene
  • 588
  • 1
  • 8
  • 20

1 Answers1

11

Give it a try:

import urllib

connection_string = "DRIVER={SQL Server};SERVER=localhost;UID=sa;PWD=pwd"
connection_string = urllib.quote_plus(connection_string) 
connection_string = "mssql+pyodbc:///?odbc_connect=%s" % connection_string

engine = sqlalchemy.create_engine(connection_string)
connection = engine.connect()
connection.execute("create database test")
connection.close()

Part of the code was taken from this answer.

Hope that helps.

Community
  • 1
  • 1
alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
  • 1
    It not connects to server, I've already wrote it, sorry, maybe I've not correctly described my problem. I get the same error. – Eugene Jul 30 '13 at 07:49
  • 1
    How about using this as a connection string `mssql+pyodbc:///?odbc_connect=DRIVER%3D{SQL Server}%3BSERVER%3Dlocalhost%3BUID%3Dsa%3BPWD%3Dpwd`? – alecxe Jul 30 '13 at 08:04
  • The idea is to make `pyodbc` connection string without `Database` setting, take a look: http://docs.sqlalchemy.org/en/latest/dialects/mssql.html#additional-connection-examples – alecxe Jul 30 '13 at 08:09
  • Thanks, now it connects to db server, but not log in to it. I think what problems are in my password, because it contain special chars, such as '&'. How can I use this characters in this string? – Eugene Jul 30 '13 at 08:29
  • It is works fine, thank you very much. Also please fix next string for other people in your example: `engine = sqlalchemy.create_engine("mssql+pyodbc://sa:pwd@localhost")` should be `engine = sqlalchemy.create_engine(connection_string)` and `connection.execute("commit")` not needed in this case – Eugene Jul 30 '13 at 08:41
  • I think this only works if there is a database under the username you're connecting with – howMuchCheeseIsTooMuchCheese Aug 21 '17 at 22:08
  • quote_plus is now included in parse. Use `urllib.parse.quote_plus()` – Zac Jul 21 '22 at 02:02