List names of all available MS SQL databases on server using python

Question

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']

They should exist in inspector.get_table_names(); have you tried print(inspector.get_table_names())? — FlipperPA
– FlipperPA, Commented Dec 29, 2015 at 21:22
Thanks! Already tried. This does return table names under a DB name, but not the DB names in the server. — Asif Rehan
– Asif Rehan, Commented Dec 29, 2015 at 21:26
D'oh! I clearly have my brain shut off during this holiday week, and misread the question entirely. I'm guessing in SQL Server get_schema_names() wouldn't work, since you'd just get a list of dbos? It is worth a shot; I don't have my SQL Server rig handy, or I'd do some testing (off of work this week). — FlipperPA
– FlipperPA, Commented Dec 29, 2015 at 21:27
get_schema_names() returns identical results for two different servers. So guessing that it shows the default building blocks of the servers, not the in-house DB names that I want. Edited the question with more info — Asif Rehan
– Asif Rehan, Commented Dec 29, 2015 at 21:44
I think you meant get_schema_names instead of get_table_names in your code block? — FlipperPA
– FlipperPA, Commented Dec 29, 2015 at 21:53

Gord Thompson · Accepted Answer · 2024-10-12 15:00:48Z

5

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']

edited Oct 12, 2024 at 15:00

answered Dec 31, 2015 at 0:40

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

1 Comment

Asif Rehan Over a year ago

Thanks! engine.connect().execute(...) works, even when port and DB name are not rovided in create_engine()!

Milovan Tomašević · Accepted Answer · 2020-12-28 11:27:01Z

2

It is also possible to obtain tables from a specific scheme with execute the single query with the driver below: DB-API interface to Microsoft SQL Server for Python.

pip install pymssql

import pymssql

# Connect to the database
conn = 
pymssql.connect(server='127.0.0.1',user='root',password='root',database='my_database')

# Create a Cursor object
cur = conn.cursor()

# Execute the query: To get the name of the tables from my_database
cur.execute("select table_name from information_schema.tables") # where table_schema = 'tableowner'
    for row in cur.fetchall():

# Read and print tables
for row in cur.fetchall():
    print(row[0])

output:

my_table_name_1
my_table_name_2
my_table_name_3
...
my_table_name_x

edited Dec 28, 2020 at 11:27

answered Dec 28, 2020 at 10:02

Milovan Tomašević

8,9712 gold badges58 silver badges49 bronze badges

Comments

gbar091315 · Accepted Answer · 2017-04-20 00:07:46Z

I believe the following snippet will list the names of the available databases on whatever server you choose to connect to. This will return a JSON object that will be displayed in your browser. This question is a bit old, but I hope this helps anyone curious who stops by.

from flask import Flask, request
from flask_restful import Resource, Api
from sqlalchemy import create_engine, inspect
from flask_jsonpify import jsonify

engine = create_engine('mssql+pymssql://user:password@server:port/master')

class AllTables(Resource):
    def get(self):
        conn = engine.connect()
        inspector = inspect(conn)
        tableList = [item for item in inspector.get_table_names()]
        result = {'data': tableList}
        return jsonify(result)

api.add_resource(AllTables, '/alltables')

app.run(port='8080')

Kehe CAI · Accepted Answer · 2016-10-14 04:18:42Z

0

here is another solution which fetch row by row:

import pymssql
connect = pymssql.connect(server, user, password, database)
cursor = connect.cursor(as_dict=True)
row = cursor.fetchone()
while row:
    for r in row.items():
        print r[0], r[1]
    row = cursor.fetchone()

answered Oct 14, 2016 at 4:18

Kehe CAI

1,22913 silver badges18 bronze badges

Collectives™ on Stack Overflow

List names of all available MS SQL databases on server using python

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related