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.

i am getting the error AttributeError: 'dict' object has no attribute 'cursor', but i do not see anything about defining the cursor when using pandas in the literature pandas.pydata.org/pandas-docs/version/0.19.2/generated/… — Devin Liner
– Devin Liner, Commented Jan 19, 2017 at 3:22

Community · Accepted Answer · 2017-05-23 11:45:46Z

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.

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Jan 19, 2017 at 8:46

Eugene Lisitsky

13k6 gold badges42 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Devin Liner · Accepted Answer · 2017-01-19 03:32:53Z

0

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)

answered Jan 19, 2017 at 3:32

Devin Liner

4491 gold badge5 silver badges12 bronze badges

1 Comment

Eugene Lisitsky Over a year ago

Save to CSV: pd.to_csv('path/to/file.csv')

Collectives™ on Stack Overflow

SQL query into a dataframe

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related