List sql tables in pandas.read_sql

Question

I would like to open an SQL 2005 database (file has extension of .mdf), and I have been trying this as such:

import pandas as pd
import pyodbc

server = 'server_name'
db = 'database_name'

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + server + ';DATABASE=' + db + ';Trusted_Connection=yes')

sql = """

SELECT * FROM table_name

"""
df = pd.read_sql(sql, conn)

Is there a way to query the database and list all tables using Pandas or pyodbc? I have virtually NO experience in databases, so any help will be great.

score 3 · Answer 1 · edited May 23 '17 at 10:33

3

This answer might be helpful: How do I get list of all tables in a database using TSQL?

Trying changing your SQL string to:

sql = """
SELECT * FROM information_schema.tables
"""

edited May 23 '17 at 10:33

Community

1
1

answered Oct 08 '15 at 15:51

measureallthethings

990
8
23

score 3 · Answer 2 · edited Mar 09 '18 at 15:52

3

import pyodbc as db

import pandas as pd

conn = db.connect("DRIVER={SQL Server}; SERVER=YourServerName; PORT=1433; DATABASE=YourDB; UID=User; PWD=Password;")

cursor = conn.cursor()

cursor.execute('''select * from sys.databases''')

df=pd.DataFrame(cursor.fetchall())

edited Mar 09 '18 at 15:52

BSMP

4,242
8
33
43

answered Mar 09 '18 at 15:05

Citizen

31
3

1

you should edit this to provide context on what you did as well as format the code. – Ryan Schaefer Mar 09 '18 at 15:24

score 1 · Answer 3 · answered Feb 24 '21 at 06:28

With sqllite3 and pandas you can do it by

import sqlite3 
import pandas as pd 
  
# create a connection 
con = sqlite3.connect('database.db') 
data = pd.read_sql_query('SELECT name from sqlite_master where type= "table";', con) 
  
# show first 5 table names
data.head()

List sql tables in pandas.read_sql

3 Answers3

Linked