How to connect to MS-SQL server without database name using pyodbc python?-CodePudding

I need to connect to the ms-sql database and then create a new database there using python script. I have the user credentials for the login. So how to create the connection to the ms-sql server using python.

CodePudding user response：

If you do not have database name then use the connection string as mentioned in the code below. Create a database after connection and use the database finally.

import pyodbc

# if you have user id and password then try with this connection string
connection_string = f"DRIVER={SQL Server};SERVER={server_name};UID={user_id};PWD={password}"

# if using in the local system then use the following connection string
connection_string = f"DRIVER={SQL Server};SERVER={server_name}; Trusted_Connection=True;"

connection= pyodbc.connect(connection_string)
cursor = connection.cursor()

sql_create_database = f"CREATE DATABASE {database_name}"
cursor.execute(sql_create_database)

set_database = f"USE {database_name}"
cursor.execute(set_database)

CodePudding user response：

You can use this connection string

pyodbc.connect('DSN=MSSQLServerDatabase;UID=myuid;PWD=mypwd', autocommit=True)

It will connect using a default database, then you are free to do everything that the user (myuid) has permission to do. The autocommit portion is to you to be able to create the database with something like this:

cursor = conn.cursor()

sqlcommand = """
                   CREATE DATABASE mydbname
             """

cursor.execute(sqlcommand)

cursor.commit()

conn.commit()

refer to this other question by @Changemyname and answered by @Bryan on why the autocommit should be turned on