Get tables list from SQL Server Database as a clean python list of strings

Question

I want the tables in SQL Server DB as a nice comma separated list. I have some code as below.

import pyodbc 
cnxn = pyodbc.connect('Trusted_Connection=yes', 
                     driver = '{ODBC Driver 13 for SQL Server}',
                     server = 'XXXXXX\SQLSERVER', 
                     database = 'AdventureWorks2016CTP3')
sql = "SELECT Distinct TABLE_NAME FROM information_schema.TABLES"

cursor = cnxn.cursor()
cursor.execute(sql)
print(cursor.fetchall())

I want the list to be like

['Address', 'AddressType',.....]

instead of

[('Address', ), ('AddressType', ), .....]

How to get this done.

Keerikkattu Chellappan

Possible duplicate of [Retrieving Data from SQL Using pyodbc](https://stackoverflow.com/questions/11451101/retrieving-data-from-sql-using-pyodbc) — Rahul Neekhra, Nov 06 '18 at 15:50
@RahulNeekhra I fail to see how this question and the question you linked are same or similar. — Keerikkattu Chellappan, Nov 06 '18 at 16:07

score 1 · Accepted Answer · answered Nov 06 '18 at 15:48

Looks like the list is returned as a list of single element tuples. You could join them using join() and using the index for the first element for one long string or you could use list comprehension to return them as a single list.

val = [('Table1',),('Table2',),('Table3',)]
table_list = [x[0] for x in val]
table_string = ', '.join([x[0] for x in val])

score 0 · Answer 2 · answered Nov 06 '18 at 15:50

0

If join() isn't your thing, this is a good time for list comprehension:

table_list = [x[0] for x in val]

Which will return a list like you specified.

answered Nov 06 '18 at 15:50

pythomatic

647
4
13

Get tables list from SQL Server Database as a clean python list of strings

2 Answers2