How to create and delete database using mssql+pyodbc connection string using python

Question

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()

but I find this error:

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.

I am using MicroSoft SQL Managment Studio SQL EXPRESS:

Server Type Database Engine
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'

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

Pandas read_sql usually expects result set return from SELECT not action DDL statements like CREATE DATABASE which come to think of it may not be a statement you want to run in application script like Python. Databases are designed and planned resources in advance not dynamically run events on the fly. BTW - an SQLAlchemy engine can run any SQL statement (DDL or DML). — Parfait
– Parfait, Commented Feb 24, 2020 at 14:05
@Parfait thanks a lot for your comment it helps me alot.. what I understand you mean that I can use SELECT instead CREATE better? could you give an example for this, I appriate :) — Mahmoud Al-Haroon
– Mahmoud Al-Haroon, Commented Feb 24, 2020 at 14:12
@Parfait how can I use SELECT instead CREATE, ALTER and DROP? :) — Mahmoud Al-Haroon
– Mahmoud Al-Haroon, Commented Feb 24, 2020 at 14:18
@Parfait you mean something like this def create_db(): create_bd_query = "SELECT DATABASE toolDB" select_result = connec.conn.execute(create_bd_query) return select_result — Mahmoud Al-Haroon
– Mahmoud Al-Haroon, Commented Feb 24, 2020 at 14:20
Ultimately, it depends on what you are trying to do. Use pandas to import or export data tables results. For other actions, use the pyodbc cursor connection or SQLAlchemy engine connection. Once again, I would refrain from creating databases in Python. Do that high level command once on the server with Management Studio or sqlcmd. — Parfait
– Parfait, Commented Feb 24, 2020 at 15:35

Mahmoud Al-Haroon · Accepted Answer · 2020-02-24 13:45:19Z

1

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()

answered Feb 24, 2020 at 13:45

Mahmoud Al-Haroon

2,49910 gold badges47 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create and delete database using mssql+pyodbc connection string using python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related