Read data from SQL into Python as a dataframe

Question

I am wanting to read data from SQL into Python as a dataframe. I have the first steps completed successfully, but am not sure how to read it into Python as a dataframe.

This is what I am doing:

 import pandas as pd
 Cap = pyodbc.connect(
 'Driver={SQL Server};'
 'Server=Test\SQLTest;'
 'Database=Cap;'
 'Trusted_Connection=yes;'
)
 
 cursor = Cap.cursor()
 sql = pd.read_sql_query("SELECT dbo.Catalogs_ID_History$.Location FROM 
 Table 1", Cap)

Any suggestion is appreciated.

Answer 1

There is a function in pandas that does exactly what you want, pd.read_sql()

You need to create a connection to the database first. Here's a brief example:

import sqlalchemy
import pandas
engine = sqlalchemy.create_engine('sqlite:///')
df = pd.read_sql('''select sqlite_version();''', engine)

The official documentation is here: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html