我是python初学者。我想使用Python3检查SQL server上的test2数据库。如果它不存在,我将创建它。但是,我发现报告了一个错误,我不知道该怎么办?
python代码:
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()错误信息:
---------------------------------------------------------------------------
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")发布于 2021-01-24 22:28:20
我已经通过修改以下代码解决了我的问题:
sql = "if not exist (select * from sys.databases where name = 'test2')"至
sql = "if not exists (select * from sys.databases where name = 'test2') begin create database test2 end"发布于 2021-01-24 22:23:00
我没有可以测试的MS SQL Server安装,但是
if not exist (select * from sys.databases where name = 'test2')在我看来不像是有效的SQL。
也许你想做这样的事情:
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)另一种方法可能是尝试直接创建数据库,如果异常已经存在,则捕获该异常。如果在您获取计数和发送create语句之间有其他人创建数据库的机会,那么这是一种更好的方法。
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()发布于 2021-01-24 21:26:52
嗨,希望下面的代码能帮助你!
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!")https://stackoverflow.com/questions/65870972
复制相似问题