Ad

List Names Of All Available MS SQL Databases On Server Using Python

Trying to list the names of the databases on a remote MS SQL server using Python (Just like the Object Explorer in MS SQL Server Management Studio).

Current solution: The required query is SELECT name FROM sys.databases;. So current solution is using SQLAlchemy and Pandas, which works fine as below.

import pandas    
from sqlalchemy import create_engine
#database='master'
engine = create_engine('mssql+pymssql://user:[email protected]:port/master')
query = "select name FROM sys.databases;"
data = pandas.read_sql(query, engine)

output:

                  name
0               master
1               tempdb
2                model
3                 msdb

Question: How to list the names of the databases on the server using SQLAlchemy's inspect(engine) similar to listing table names under a database? Or any simpler way without importing Pandas?

from sqlalchemy import inspect

#trial 1: with no database name
engine = create_engine('mssql+pymssql://user:[email protected]:port')
#this engine not have DB name
inspector = inspect(engine)
inspector.get_table_names() #returns []
inspector.get_schema_names() #returns [u'dbo', u'guest',...,u'INFORMATION_SCHEMA']

#trial 2: with database name 'master', same result
engine = create_engine('mssql+pymssql://user:[email protected]:port/master')
inspector = inspect(engine)
inspector.get_table_names() #returns []
inspector.get_schema_names() #returns [u'dbo', u'guest',...,u'INFORMATION_SCHEMA']
Ad

Answer

If all you really want to do is avoid importing pandas then the following works fine for me:

from sqlalchemy import create_engine
engine = create_engine('mssql+pymssql://sa:[email protected]:52865/myDb')
conn = engine.connect()
rows = conn.execute("select name FROM sys.databases;")
for row in rows:
    print(row["name"])

producing

master
tempdb
model
msdb
myDb
Ad
source: stackoverflow.com
Ad