confirm "database" string argument works with pymssql
SQL Azure does not support the USE
statement -- the database must be set at connection create time.
I verified that this is handled correctly by pymssql when invoked directly.
However, it looks like SQLAlchemy does not support this behavior.
Comments (9)
-
-
repo owner can you confirm "USE" only refers to the workaround you've been using? I dont see "USE" emitted from SQLAlchemy.
it would appear this is a feature add to support a newly added option in pymssql? you can of course create usernames in SQL Server that refer to the desired database directly.
-
repo owner - marked as enhancement
- changed title to support new "database" string argument supported by pymssql
- changed component to mssql
-
reporter Mike: I don't quite understand your question, but here are some partial answers
-
SQL Alchemy uses the
USE
statement (albeit in its lowercase form): https://bitbucket.org/zzzeek/sqlalchemy/src/32165f50209036a98959553e1c5e81537a091a15/lib/sqlalchemy/dialects/mssql/base.py?at=master#cl-1308 -
SQL Azure does not support
USE
: http://msdn.microsoft.com/en-us/library/ee336288.aspx
In Microsoft Microsoft Azure SQL Database, the database parameter can only refer to the current database. The USE statement does not switch between databases. To change databases, you must directly connect to the database.
-
-
repo owner the aforementioned "use" is necessary in exactly one very rare case: when you specify
schema="somedb.someowner"
, that is, a schemaname that itself has a dot in it and therefore intends to select from tables across databases, and you use reflection features, which includes the "has table" feature (e.g. metadata.create_all() without specifyingcheckfirst=False
). This form is not for general use and if azure doesn't support "USE" or equivalent, it can't be used. Please confirm you are attempting to use reflection with a dotted schema name and that is the source of the "use" statement, or otherwise please provide a full test case that illustrates where "USE" is coming from.in the vast majority of cases, a SQL connection should be dealing with just one database.
The "database" parameter should be sent, my previous statement was inaccurate:
from sqlalchemy.dialects.mssql import pymssql from sqlalchemy.engine import url u = url.make_url("mysql+pymssql://scott:tiger@hostname/dbname") print(pymssql.MSDialect_pymssql().create_connect_args(u))
output:
[[], {'host': 'hostname', 'password': 'tiger', 'user': 'scott', 'database': 'dbname'}]
-
repo owner - changed title to confirm "database" string argument works with pymssql
- marked as bug
-
reporter Confirm that this works if we use pymssql from git. The pymssql in pip is broken.
Thanks. Sorry for the confusion, this either changed in the last day or I somehow screwed up the other virtualenv.
I am no longer seeing failed "use database blah" in the freetds logs.
-
reporter - changed status to resolved
Confirmed that the database string is properly passed through to pymssql and that it works with pymyssql 2.1.1 (git) which is not yet released to pypi.
-
repo owner cool thanks!
- Log in to comment
A little more info from the pymssql docs:
http://www.pymssql.org/en/latest/ref/pymssql.html#pymssql.connect
including note about Azure.