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']
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')
with engine.connect() as conn:
rows = conn.exec_driver_sql("select name FROM sys.databases;").scalars().all()
print(rows)
# ['master', 'tempdb', 'model', 'msdb', 'test']