Python 3 failed to check whether the database exists and create it

Question

I'm a python beginner. I want to use Python 3 to check the test2 database on the SQL server. If it doesn't exist, I will create it. However, I find that an error is reported and I don't know what to do?

python code:

import pymssql 
 
 
def conn():
    ret = pymssql.connect(host='DESKTOP-4CDQOMR', user = 'sa', password = '123456') 
    if ret:
        print("connect successfully!")
    else:
        print("connect failed!")

    return ret
        

 
 
if __name__ == '__main__':
    conn = conn()  
    if conn:
        cursor = conn.cursor()
        sql = "if not exist (select * from sys.databases where name = 'test2')"
        conn.autocommit(True)
        cursor.execute(sql)
        conn.autocommit(False) 
        conn.close()

error information:

---------------------------------------------------------------------------
MSSQLDatabaseException                    Traceback (most recent call last)
src\pymssql.pyx in pymssql.Cursor.execute()

src\_mssql.pyx in _mssql.MSSQLConnection.execute_query()

src\_mssql.pyx in _mssql.MSSQLConnection.execute_query()

src\_mssql.pyx in _mssql.MSSQLConnection.format_and_run_query()

src\_mssql.pyx in _mssql.check_cancel_and_raise()

src\_mssql.pyx in _mssql.maybe_raise_MSSQLDatabaseException()

MSSQLDatabaseException: (156, b"Incorrect syntax near the keyword 'select'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

During handling of the above exception, another exception occurred:

OperationalError                          Traceback (most recent call last)
F:\jupyter\connect.py in <module>
     20         sql = "if not exist (select * from sys.databases where name = 'test2')"
     21         conn.autocommit(True)
---> 22         cursor.execute(sql)
     23         conn.autocommit(False)
     24         conn.close()

src\pymssql.pyx in pymssql.Cursor.execute()

OperationalError: (156, b"Incorrect syntax near the keyword 'select'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

https://stackoverflow.com/questions/167576/check-if-table-exists-in-sql-server probably can help — Leo Arad, Jan 24 '21 at 13:18
@Leo Arad Sorry, I still can't find the answer I want in it, because it mainly discusses table instead of database — ZhangRuiAndGuo, Jan 24 '21 at 13:43

score 1 · Answer 1 · answered Jan 24 '21 at 14:28

1

I have solved my question by modifying the follow code :

sql = "if not exist (select * from sys.databases where name = 'test2')"

to

sql = "if not exists (select * from sys.databases where name = 'test2') begin create database test2 end"

answered Jan 24 '21 at 14:28

ZhangRuiAndGuo

65
5

score 0 · Answer 2 · answered Jan 24 '21 at 14:23

I don't have an installation of MS SQL Server to test against, but

if not exist (select * from sys.databases where name = 'test2')

does not look like valid SQL to me.

Perhaps you want to do something like this:

cur = conn.cursor()
# Check if the database already exists.
cur.execute("""SELECT COUNT(*) FROM sys.databases WHERE name = 'test2'""")
res = cur.fetchone()[0]
if res == 0:
    conn.autocommit(True)
    cur.execute("""CREATE DATABASE test2""")
    conn.autocommit(False)

Another approach might be to try to create the database directly, and catch the exception if it already exists. This is a better approach if there is a chance that someone else might create the database in between you getting the count and sending the create statement.

import sys

cur = conn.cursor()
conn.autocommit(True)
try:
    cur.execute("""CREATE DATABASE test2""")
except Exception as ex:
    # Rollback is probably unnecessary
    conn.rollback()
    print('Failed to create the database because', ex, file=sys.stderr)
finally:
    conn.autocommit(False)
    conn.close()

score -1 · Answer 3 · answered Jan 24 '21 at 13:26

Hi hope this code below helps u!

import mysql.connector

DB_NAME  = "test2"

client = mysql.connector.connect(host = "127.0.0.1",
                                 user = "admin",
                                 passwd = "password")
mycursor = client.cursor()
mycursor.execute("SHOW DATABASES")

if DB_NAME in mycursor:    print("Database exists :)")
else :
    print(f"Database {DB_NAME} doesn't exist, creating now....")
    mycursor.execute("CREATE DATABASE firstdatabase")
    print(f"Database {DB_NAME} created!")

The question is about MS -SQL Server, not MySQL. – snakecharmerb Jan 24 '21 at 13:28 — snakecharmerb, Jan 24 '21 at 13:28

Python 3 failed to check whether the database exists and create it

3 Answers3