Issues

Issue #3152 resolved

confirm "database" string argument works with pymssql

Daniel Halperin
created an issue

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)

  1. Mike Bayer 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.

  2. Daniel Halperin reporter

    Mike: I don't quite understand your question, but here are some partial answers

    1. 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

    2. 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.

  3. Mike Bayer 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 specifying checkfirst=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'}]
    
  4. Daniel Halperin 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.

  5. Log in to comment