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, Feb 24 '20 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, Feb 24 '20 at 14:12
@Parfait how can I use `SELECT` instead `CREATE`, `ALTER` and `DROP`? :) — Mahmoud Al-Haroon, Feb 24 '20 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, Feb 24 '20 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, Feb 24 '20 at 15:35
@Parfait Okay Look I under stand some how , so now if I'll use the `pyodbc` cursor connection how I can connect to database name, and If I use `SQLAlchemy` how to connect to the server?, thanks alot for supporting me I appretiate that :) — Mahmoud Al-Haroon, Feb 24 '20 at 16:02
The connection string for both DB-APIs specifies the database to connect to. — Parfait, Feb 24 '20 at 16:39
@Parfait so you mean that I have to specify the Both connection string one for the database name `create_engine` and the `pyodbc` is for the server right? — Mahmoud Al-Haroon, Feb 24 '20 at 17:13

score 1 · Answer 1 · answered Feb 24 '20 at 13:45

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

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

1 Answers1