Connect to SQL server using pyodbc and sqlalchemy, cannot use "use database_name;"

Question

My code is as follows：

import urllib
import sqlalchemy
from sqlalchemy.orm import sessionmaker, scoped_session


def list_dbs():
    sql = """
            use master;
            SELECT name
            FROM   sys.databases;
        """
    try:
        odbc_connect = "DRIVER={SQL Server};Server=localhost;Database=master;port=1433"
        engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % urllib.quote_plus(odbc_connect),
                                          echo=True, connect_args={'autocommit': True})
        SessionFactory = sessionmaker(bind=engine)
        session = scoped_session(SessionFactory)
        result = session.execute(sql)
        for v in result:
            print(v)
    except Exception as e:
        print(e)


list_dbs()

Then an error "This result object does not return rows. It has been closed automatically."

But then I remove "use master;" and it works:

Because I have many databases, I have to use "use databasename." Any ideas?

If you're defining the database to connect to (master) in odbc_connect, why do you need USE master; in your SQL anyway? The USE is redundant, as you've already connected to the correct database. — Thom A
– Thom A ♦, Commented Dec 30, 2019 at 10:48
Are you able to run your SQL script directly on the server. I tried it and it worked correctly. Might this is a bug in the driver? — Peter Smith
– Peter Smith, Commented Dec 30, 2019 at 10:48
Also, any SQL Server only has one master database. Are you asking how to list the tables in each database rather than the databases on the server? — Peter Smith
– Peter Smith, Commented Dec 30, 2019 at 11:05
I have multiple databases on my instance, and I need to switch databases in the same SQL.And I've verified this SQL on the SSMS, and it works. — mutoulion
– mutoulion, Commented Dec 30, 2019 at 11:22

Gord Thompson · Accepted Answer · 2021-04-05 14:13:06Z

1

You are facing the issue discussed on GitHub here.

As it stands now, you need to execute the USE ... separately and then execute your queries. Example:

with engine.begin() as conn:
    db_name = conn.execute(text("SELECT DB_NAME()")).fetchone()[0]
    print(f'Current database: {db_name}')  # Current database: myDb
    tables = conn.execute(text("SELECT TOP 3 name FROM sys.tables ORDER BY name")).fetchall()
    print(tables)  # [('MillionRows',), ('myTable',), ('person',)]
    conn.execute(text("USE master"))
    db_name = conn.execute(text("SELECT DB_NAME()")).fetchone()[0]
    print(f'Current database: {db_name}')  # Current database: master
    tables = conn.execute(text("SELECT TOP 3 name FROM sys.tables ORDER BY name")).fetchall()
    print(tables)  # [('MSreplication_options',), ('spt_fallback_db',), ('spt_fallback_dev',)]

edited Apr 5, 2021 at 14:13

answered Dec 30, 2019 at 12:12

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

Collectives™ on Stack Overflow

Connect to SQL server using pyodbc and sqlalchemy, cannot use "use database_name;"

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related