SQL query into a dataframe

Question

Learning how to use dataframes. What am I doing wrong?

My attempt:

import pyodbc
import pandas as pd
from pandas import read_csv
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL
import time
import csv
import sys

Class Query(object):
  def do_something(self):
     con={'drivername':'mssql+pymssql','host': 'host-name', 'port': number, 'database': 'databasename','username':'username','password':'asdf'}

     query = """ select column1 from schema.table1 """

     df=pd.read_sql_query('parse',con,index_col=None,coerce_float=True,params=None,parse_dates=None,chunksize=None)

x=Query()
x.do_something()

the goal is to eventually store this df into a CSV file. Then, I'll need to run the query

query = """ select column2 from schema.table2 """ 

and append that column into the CSV file. Help please.

Answer 1

I used this code:

from sqlalchemy import create_engine
import psycopg2
mydb = create_engine('postgresql://[email protected]:5432/database')
df = pd.read_sql_query('''SELECT sections, ...''', con=mydb)
print(df) 

Also you can use pd.read_sql_table and pass it a list of columns. Looks like it's more convenient to loading tables.

Answer 2

Solved my own problem. Still don't know how to save to CSV or append to said CSV.

con={'drivername':'mssql+pymssql','host': 'host-name', 'port': number,     'database': 'databasename','username':'username','password':'asdf'}
db_uri = URL(**database_dict)
engine = create_engine(db_uri)
query = """ select column1 from schema.table1 """

 df=pd.read_sql_query(parse,con,index_col=None,coerce_float=True,params=None,parse_dates=None,chunksize=None)