Cannot create database with SQLAlchemy with SQL Server

Question

Please note that I have been attempting to create a new database using SQLAlchemy, but have gotten many errors where I am attempting to work with laravel and vagrant. Thankfully I have been able to create a new database using pyodbc, but ultimately I would like to do everything through SQLAlchemy.

This is the code that works for creating a new database at this point using pyodbc:

    for tagList in myTagList:
        conn = pyodbc.connect("driver={SQL Server Native Client 11.0};server=localhost,2433; database=master; UID=myUser;PWD=myUser2", autocommit=True) 
        conn.execute('CREATE DATABASE ' + tagList["job"].jobNumber)

How would I change this to work with SQLAlchemy? Presently I have looked at many links like How to create a new database using SQLAlchemy? and sqlalchemy,creating an sqlite database if it doesn't exist but and https://sqlalchemy-utils.readthedocs.io/en/latest/database_helpers.html it appears to work with postgresql but not SQL Server:

Add support for creating databases (see How to create a new database using SQLAlchemy?):

Projects>pip install sqlalchemy-utils
pip install sqlalchemy-utils
DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won`t be maintained after that date. A future version of pip will drop support for Python 2.7. More details about Python 2 support in pip, can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support
Collecting sqlalchemy-utils
  Downloading https://files.pythonhosted.org/packages/bf/7e/3211ad9b3983b216d1b1863fd7734f80bacd1a62a5de8ff6844fb5ed1498/SQLAlchemy-Utils-0.35.0.tar.gz (129kB)
Requirement already satisfied: six in c:\users\myUser\appdata\roaming\python\python27\site-packages (from sqlalchemy-utils) (1.13.0)
Requirement already satisfied: SQLAlchemy>=1.0 in c:\python27\lib\site-packages (from sqlalchemy-utils) (1.3.11)
Installing collected packages: sqlalchemy-utils
    Running setup.py install for sqlalchemy-utils: started
    Running setup.py install for sqlalchemy-utils: finished with status 'done'
Successfully installed sqlalchemy-utils-0.35.0

--------------------------------------------------------------------------------

#Save historical data into new MyDB000N database
for tagList in myTagList:
    fsDatabaseEngine = create_engine('mssql+pyodbc://myUser:myUser2@localhost:2433/' + tagList["job"].jobNumber + '?driver=SQL+Server+Native+Client+11.0?trusted_connection=yes')
    if not database_exists(fsDatabaseEngine.url):
        create_database(fsDatabaseEngine.url)
    print(database_exists(fsDatabaseEngine.url))

-----

On this line if not database_exists(fsDatabaseEngine.url): receiving:

    self.__connect(first_connect_check=True)
  File "C:\Python27\lib\site-packages\sqlalchemy\pool\base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line 482, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('28000', u'[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user \'myUser\'. (18456) (SQLDriverConnect); [28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database "MyDB00001" requested by the login. The login failed. (4060); [28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user \'myUser\'. (18456); [28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database "MyDB00001" requested by the login. The login failed. (4060)')
(Background on this error at: http://sqlalche.me/e/rvf5)

-----

C:\Python27\lib\site-packages\sqlalchemy\connectors\pyodbc.py:79: SAWarning: No driver name specified; this is expected by PyODBC when using DSN-less connections
  "No driver name specified; "

-----

#Save historical data into new MyDB000N database
for tagList in myTagList:
    # fsDatabaseEngine = create_engine('mssql://mssql@localhost:2433/' + tagList["job"].jobNumber + '?driver=SQL+Server+Native+Client+11.0?trusted_connection=yes')
    # if not database_exists(fsDatabaseEngine.url):
    create_database('mssql://mssql@localhost:2433/' + tagList["job"].jobNumber)
    #print(database_exists(fsDatabaseEngine.url))

  File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line 482, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', u'[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
(Background on this error at: http://sqlalche.me/e/rvf5)
PS Projects>

-----

#Save historical data into new MyDB000N database
for tagList in myTagList:
    # fsDatabaseEngine = create_engine('mssql://mssql@localhost:2433/' + tagList["job"].jobNumber + '?driver=SQL+Server+Native+Client+11.0?trusted_connection=yes')
    # if not database_exists(fsDatabaseEngine.url):
    create_database('mssql://myUser:myUser2@localhost:2433/' + tagList["job"].jobNumber+ '?driver=SQL+Server+Native+Client+11.0?trusted_connection=yes')
    #print(database_exists(fsDatabaseEngine.url))

  File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 1246, in _execute_context
    cursor, statement, parameters, context
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line 581, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', u'[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]CREATE DATABASE statement not allowed within multi-statement transaction. (226) (SQLExecDirectW)')
[SQL: CREATE DATABASE [MyDB00001]]
(Background on this error at: http://sqlalche.me/e/f405)
PS Projects>

-----

#Save historical data into new MyDB000N database
for tagList in myTagList:
    with create_engine(
        'mssql://mssql',
        isolation_level="AUTOCOMMIT"
        ).connect() as connection:
            connection.execute('CREATE DATABASE ' + tagList["job"].jobNumber)
    # if not database_exists(fsDatabaseEngine.url):
    #create_database('mssql://myUser:myUser2@localhost:2433/' + tagList["job"].jobNumber)
    #print(database_exists(fsDatabaseEngine.url))

  File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line 482, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', u'[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
(Background on this error at: http://sqlalche.me/e/rvf5)
PS Projects>

-----

#Save historical data into new MyDB000N database
for tagList in myTagList:
    session.execute('CREATE DATABASE ' + tagList["job"].jobNumber)

  File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line 581, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', u'[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]CREATE DATABASE statement not allowed within multi-statement transaction. (226) (SQLExecDirectW)')
[SQL: CREATE DATABASE MyDB00001]
(Background on this error at: http://sqlalche.me/e/f405)
PS Projects>

-----

#Save historical data into new MyDB000N database
for tagList in myTagList:
    conn = pyodbc.connect("driver={SQL Server};server=ubuntu-bionic; database=master; trusted_connection=true",
                      autocommit=True)

Exception has occurred: OperationalError
('08001', u'[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (53)')
  File "Projects\interesting_data.py", line 81, in <module>
    autocommit=True)

-----

#Save historical data into new MyDB000N database
for tagList in myTagList:
    conn = pyodbc.connect("driver={SQL Server Native Client 10.0};server=localhost,2433; database=master; trusted_connection=true", autocommit=True)

Exception has occurred: InterfaceError
('IM002', u'[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
  File "Projects\interesting_data.py", line 80, in <module>
    conn = pyodbc.connect("driver={SQL Server Native Client 10.0};server=localhost,2433; database=master; trusted_connection=true", autocommit=True)

-----    

#Save historical data into new MyDB000N database
for tagList in myTagList:
    conn = pyodbc.connect("driver={SQL Server Native Client 11.0};server=localhost,2433; database=master; trusted_connection=true", autocommit=True)

Exception has occurred: OperationalError
('08001', u"[08001] [Microsoft][SQL Server Native Client 11.0]Invalid value specified for connection string attribute 'trusted_connection' (0) (SQLDriverConnect)")
  File "Projects\interesting_data.py", line 80, in <module>
    conn = pyodbc.connect("driver={SQL Server Native Client 11.0};server=localhost,2433; database=master; trusted_connection=true", autocommit=True)

-----

#Save historical data into new MyDB000N database
for tagList in myTagList:
    conn = pyodbc.connect("driver={SQL Server Native Client 11.0};server=localhost,2433; database=master; trusted_connection=yes", autocommit=True)

Exception has occurred: Error
('HY000', u'[HY000] [Microsoft][SQL Server Native Client 11.0]SQL Server Network Interfaces: No credentials are available in the security package\r\n (-2146893042) (SQLDriverConnect); [HY000] [Microsoft][SQL Server Native Client 11.0]Cannot generate SSPI context (-2146893042); [HY000] [Microsoft][SQL Server Native Client 11.0]SQL Server Network Interfaces: No credentials are available in the security package\r\n (-2146893042); [HY000] [Microsoft][SQL Server Native Client 11.0]Cannot generate SSPI context (-2146893042)')
  File "Projects\interesting_data.py", line 80, in <module>
    conn = pyodbc.connect("driver={SQL Server Native Client 11.0};server=localhost,2433; database=master; trusted_connection=yes", autocommit=True)

import urllib

#Save historical data into new MyDB000N database
for tagList in rdTagList:
    # if not database_exists(fsDatabaseEngine.url):
    params = urllib.parse.quote_plus("driver={SQL Server Native Client 11.0};server=localhost,2433; database=master; UID=phpUser;PWD=phpUser2")
    tagListEngine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
    tagListEngine.connect()

Which gives:

Exception has occurred: AttributeError
'module' object has no attribute 'parse'
  File "C:\Projects\interesting_data.py", line 82, in <module>
    params = urllib.parse.quote_plus("driver={SQL Server Native Client 11.0};server=localhost,2433; database=master; UID=myUser;PWD=myUser2")

What do you suggest? TIA.

UPDATE:

Not sure how I missed it before but ultimately based on @snakecharmerb's suggestion it is now working without requiring urllib:

#Save historical data into new MyDB000N database
for tagList in rdTagList:
    fsDatabaseEngine = create_engine('mssql+pyodbc://myUser:myUser2@localhost:2433/' + tagList["job"].jobNumber + '?driver=SQL+Server+Native+Client+11.0?trusted_connection=yes')
    if not database_exists(fsDatabaseEngine.url):
        create_database(fsDatabaseEngine.url)
    print(database_exists(fsDatabaseEngine.url))

Thank you!

UPDATE 2:

To zero in on the solution, in my research I had found explanations using postgres://postgres ..., but ultimately in my case I needed to change mssql://myUser to mssql+pyodbc://myUser...

conn.execute('CREATE DATABASE ' + tagList["job"].jobNumber) that's statement is really open to injection. If you need to create a database, you should pass parameters and use a dynamic statement with QUOTENAME: DECLARE @Create nvarchar(275); SET @Create = N'CREATE DATABASE ' + QUOTENAME(@Db) + N';'; EXEC sp_executesql @Create; — Thom A
– Thom A ♦, Commented Nov 28, 2019 at 17:12
The tagList["job"].jobNumber is something I have gotten from elsewhere in the python script so I don't see how it is open to SQL injection since these are values I have obtained from the local database. Also, you are showing me how to solve the injection problem in SQL but not in python which is not helpful. — user8128167
– user8128167, Commented Nov 28, 2019 at 17:20
If you print(fsDatabaseEngine.url) is it the correct connection string? The error message indicates that either the log in credentials or the database name must be wrong. — snakecharmerb
– snakecharmerb, Commented Nov 28, 2019 at 17:38
Okay I'm not not sure how I missed that--it is suddenly working now!. If I print the print(fsDatabaseEngine.url) it gives me the correct connection string. If you post that @snakecharmerb I will mark it as the answer. Thank you! — user8128167
– user8128167, Commented Nov 28, 2019 at 17:47
It is helpful, @user8128167 , as syntax such as "CREATE DATABASE @Parameter;` isn't valid syntax, and I assume you know how to parametrise a Python SQL statement. The fact that you were insecurely injecting said the me you didn't know how to safely inject. — Thom A
– Thom A ♦, Commented Nov 28, 2019 at 18:35

alfonsoSR · Accepted Answer · 2023-04-13 22:08:40Z

1

Based on @shakecharmerb's comment, this is the solution:

# Save historical data into new MyDB000N database
for tagList in rdTagList:
    fsDatabaseEngine = create_engine(
        'mssql+pyodbc://myUser:myUser2@localhost:2433/' +
        tagList["job"].jobNumber +
        '?driver=SQL+Server+Native+Client+11.0?trusted_connection=yes'
    )
    if not database_exists(fsDatabaseEngine.url):
        create_database(fsDatabaseEngine.url)
    print(database_exists(fsDatabaseEngine.url))

edited Apr 13, 2023 at 22:08

alfonsoSR

3131 silver badge12 bronze badges

answered Nov 29, 2019 at 21:58

user8128167

7,9046 gold badges72 silver badges89 bronze badges

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

Collectives™ on Stack Overflow

Cannot create database with SQLAlchemy with SQL Server

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related