如何使用python使用mssql+pyodbc连接字符串创建和删除数据库 [英] How to create and delete database using mssql+pyodbc connection string using python

问题描述

我有一个如下的数据库引擎:

I have a database engine as the below:

from sqlalchemy import create_engine
import pydoc

# connect db
engine = create_engine('mssql+pyodbc://xxxx\MARTRNO_EXPRESS/toolDB?driver=SQL+Server+Native+Client+11.0')
connection = engine.connect()

我尝试使用类似下面的代码来创建一个数据库，使用这个连接作为下面的代码:

I tried to use something like the below code as to create a database using this connection as the below code:

from database import connec
import pandas as pd

def delete_all_tables_from_db():
    delete_all_tables_query = "CREATE DATABASE MyNewDatabase"

    delete_all_tables_df = pd.read_sql(delete_all_tables_query, connec.engine)
    connec.engine.execute(delete_all_tables_df)

delete_all_tables_from_db()

但我发现这个错误:

Traceback (most recent call last):
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1245, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\default.py", line 588, in do_execute
    cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]CREATE DATABASE statement not allowed within multi-statement transaction. (226) (SQLExecDirectW)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:/Users/haroo501/PycharmProjects/ToolUpdated/database/delete_all_tables_from_db.py", line 10, in <module>
    delete_all_tables_from_db()
  File "C:/Users/haroo501/PycharmProjects/ToolUpdated/database/delete_all_tables_from_db.py", line 7, in delete_all_tables_from_db
    delete_all_tables_df = pd.read_sql(delete_all_tables_query, connec.engine)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\pandas\io\sql.py", line 432, in read_sql
    return pandas_sql.read_query(
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\pandas\io\sql.py", line 1218, in read_query
    result = self.execute(*args)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\pandas\io\sql.py", line 1087, in execute
    return self.connectable.execute(*args, **kwargs)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 2182, in execute
    return connection.execute(statement, *multiparams, **params)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 976, in execute
    return self._execute_text(object_, multiparams, params)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1143, in _execute_text
    ret = self._execute_context(
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1249, in _execute_context
    self._handle_dbapi_exception(
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1476, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line 1245, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\haroo501\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\default.py", line 588, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]CREATE DATABASE statement not allowed within multi-statement transaction. (226) (SQLExecDirectW)')
[SQL: CREATE DATABASE MyNewDatabase]
(Background on this error at: http://sqlalche.me/e/f405)

Process finished with exit code 1

我试图修改这个数据库并且工作正常，但我必须承担使用权限.

I tried to modify this database and works fine but I have to assume the permission for the use.

我正在使用 MicroSoft SQL Management Studio SQL EXPRESS:

I am using MicroSoft SQL Managment Studio SQL EXPRESS:

1. 服务器类型数据库引擎
2. 身份验证 Windows 身份验证 我没有数据库的用户名和密码

1. Server Type Database Engine
2. Authentication Windows Authentication I don't have use name and password for the database

我认为现在这部分的问题:

I think now the problem in this part:

'mssql+pyodbc://xxxx\SMARTRNO_EXPRESS/toolDB?driver=SQL+Server+Native+Client+11.0'

我用这个数据库连接字符串直接连接到toolDB

That I use this database connection string to connect directly to the toolDB

所以我需要像下面这样的连接字符串:

So I need something like a connection String as the below one:

# connect db
engine = create_engine('mssql+pyodbc://xxxx\SMARTRNO_EXPRESS?driver=SQL+Server+Native+Client+11.0')
connection = engine.connect()

至于能够在本服务器中创建数据库并能够删除或创建甚至修改数据库

as to able to create a database in this server and able to delete or create or even modify database

推荐答案

好吧！我通过在 database .py 文件中创建一个新连接作为以下代码并添加 autocommit = True 解决了这个问题:

Well! I solved this by creating a new connection in database .py file as the below code and by adding the autocommit = True:

conn = pyodbc.connect("driver={SQL Server};server=WINKPN-3B5JTT2\SMARTRNO_EXPRESS; database=master; trusted_connection=true",
                      autocommit=True)

并尝试通过以下代码访问此连接:

And Tried to access this connection by the below code:

from database import connec

def create_all_tables_from_db():
    create_all_tables_query = "CREATE DATABASE MyNewDatabase"
    connec.conn.execute(create_all_tables_query)

create_all_tables_from_db()

这篇关于如何使用python使用mssql+pyodbc连接字符串创建和删除数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！