List sql tables in pandas.read_sql
10,751
Solution 1
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())
Solution 2
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
"""
Solution 3
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()
Comments
-
tnknepp almost 2 years
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.
-
Ryan Schaefer about 6 yearsyou should edit this to provide context on what you did as well as format the code.