首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >Python 3无法检查数据库是否存在并创建数据库

问Python 3无法检查数据库是否存在并创建数据库
EN

Stack Overflow用户
提问于 2021-01-24 21:15:21
回答 3查看 599关注 0票数 1

我是python初学者。我想使用Python3检查SQL server上的test2数据库。如果它不存在,我将创建它。但是,我发现报告了一个错误,我不知道该怎么办?

python代码:

代码语言:javascript
复制
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()

错误信息:

代码语言:javascript
复制
---------------------------------------------------------------------------
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")
EN

回答 3

Stack Overflow用户

发布于 2021-01-24 22:28:20

我已经通过修改以下代码解决了我的问题:

代码语言:javascript
复制
sql = "if not exist (select * from sys.databases where name = 'test2')"

至

代码语言:javascript
复制
sql = "if not exists (select * from sys.databases where name = 'test2') begin create database test2 end"
票数 1
EN

Stack Overflow用户

发布于 2021-01-24 22:23:00

我没有可以测试的MS SQL Server安装,但是

代码语言:javascript
复制
if not exist (select * from sys.databases where name = 'test2')

在我看来不像是有效的SQL。

也许你想做这样的事情:

代码语言:javascript
复制
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语句之间有其他人创建数据库的机会,那么这是一种更好的方法。

代码语言:javascript
复制
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()
票数 0
EN

Stack Overflow用户

发布于 2021-01-24 21:26:52

嗨,希望下面的代码能帮助你!

代码语言:javascript
复制
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!")
票数 -1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/65870972

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档