a r a r - 1 month ago 11
Python Question

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:password@server: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:password@server: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:password@server:port/master')
inspector = inspect(engine)
inspector.get_table_names() #returns []
inspector.get_schema_names() #returns [u'dbo', u'guest',...,u'INFORMATION_SCHEMA']

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:saPassword@localhost: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
Comments