Read data from SQL into Python as a dataframe

user_5942100 0
Viewed 47

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.

pyodbc sql python
1 Answers

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

user_61515490
Related