9

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.

tnknepp
  • 5,888
  • 6
  • 43
  • 57

4 Answers4

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
"""
Community
  • 1
  • 1
measureallthethings
  • 1,102
  • 10
  • 26
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())
BSMP
  • 4,596
  • 8
  • 33
  • 44
Citizen
  • 31
  • 3
1

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()
Hunaidkhan
  • 1,411
  • 2
  • 11
  • 21
0

The problem with 'SELECT * FROM information_schema.tables' is that it returns a lot of information, like: 'TABLE_CATALOG', 'TABLE_SCHEMA', 'TABLE_NAME', 'TABLE_TYPE', 'ENGINE', etc.

If you need just list of table names, you can use idea from this post:

from sqlalchemy import create_engine
import pandas as pd

engine_new = create_engine("mysql+pymysql://YOUR_USER_NAME:YOUR_PASSWORD@DB_HOST:3306/DB_NAME")

connected_engine = engine_new.connect()

db_columns = pd.read_sql('SELECT table_name FROM information_schema.tables WHERE table_schema = "YOUR_DB_NAME"', connected_engine)

connected_engine.close()

This will return a DataFrames object with 1 column 'TABLE_NAME', which you can convert to a list with:

list_of_database_columns = db_columns["TABLE_NAME"].to_list()
Fedir Baronenko
  • 23
  • 5