Usually for training and testing, we create pandas DataFrame from csv file but when we are working while large dataset or working with database stored in database, we need a way to fetch data into pandas DataFrame directly from database. In this article we will have a look at two methods for doing it.
Using sqlalchemy
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('postgresql://user:password@localhost:5432/dbName')
df = pd.read_sql_query("SELECT * FROM public.stk_1min1 where symbol = 'TCS'",con=engine)
df.head()
You can write query before using pd.read_sql_query function
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('postgresql://user:password@localhost:5432/dbName')
stk = 'TCS'
query = "SELECT * FROM public.stk_1min1 where symbol = "+"'"+stk+"'"
df = pd.read_sql_query(query,con=engine)
df.head()
Using psycopg2
import pandas as pd
import psycopg2
conn = psycopg2.connect(database='dbname', user="user", password="password", host="127.0.0.1", port="5432")
df = pd.read_sql_query("SELECT * FROM public.stk_1min1 where symbol = 'TCS'",con=conn,index_col=['index'])
df.head()
Similar to sqlalchemy
example, you can write query before using read_sql_query
function.