1

I want to put my SQL code results into a table/data frame in Python. I have typed the code below and wanted to know what additional code I need to use in order to do this:

import pandas as pd    
f = pd.read_csv("/Users/sandeep/Downloads/Python/baseball.csv", header=0)

q = """
      select player, year,
              case when team='CHN' then 1 else 0 end as team_flag
      from  f
      where year=2006;      
    """
Billal Begueradj
  • 20,717
  • 43
  • 112
  • 130
SB_
  • 33
  • 7
  • Documentation: http://pandas.pydata.org/pandas-docs/stable/io.html#sql-queries – OneCricketeer Mar 19 '16 at 16:17
  • Also is `from f` your intention to get values from a CSV file? Why are you using SQL to query a CSV? – OneCricketeer Mar 19 '16 at 16:20
  • Yakym's solution is best, but if you are stuck with a CSV file, you can convert it to an in-memory sqlite3 database, and use pandas.read_sql on _that_ driver. See http://stackoverflow.com/a/14432914/1930462 – Ru Hasha Mar 19 '16 at 16:31

2 Answers2

1

Your pandas code suggests that you are reading from a .csv file, not an SQL database. In such case, you do not need to do anything else and f will in fact contain the DataFrame.

The syntax for obtaining a DataFrame from an SQL table can be found here:

At minimum, you will need two arguments: a query and a connection (which may be a string). Your query may be something like SELECT player, year, case FROM table_name WHERE team='CHN' AND year=2006. The connection string can be sqlite:////full_path.sqlite or sql_flavor://user:password@host:path.

It is arguably easier to add your indicator within the DataFrame rather than suing SQL; say df['team'] = df['team'] == 'CHN' should do it.

hilberts_drinking_problem
  • 11,322
  • 3
  • 22
  • 51
0

Use pandas.read_sql()

df = psql.read_sql(q,db)

To create the db connection, I suggest you use the following:

import psycopg2

db= psycopg2.connect("dbname='template1' user='dbuser' host='localhost' password='dbpass'")
johan855
  • 1,578
  • 4
  • 26
  • 51
  • Thanks for the above guys. I'm using the csv so that I can have some data to practice with. I could in the instance above code the query as it is simple and not use sql and get back the same results. But for more complex queries where I have to merge multiple tables and do subqueries.. sql would make it easier. for me. How do I get my results from the sql query into the same folder as my f table. I tried the import psycopg2 but it failed even when I tried pip install psycopg2 in the terminal. Being new to python if someone could give me the code it would be a great help to get me using sql. – SB_ Mar 19 '16 at 22:10
  • Also I didn't understand the concept of a connection. In R for example I could import the sql package and then just code in sql and any data created from my sql query would be in the same place as any other data set. – SB_ Mar 19 '16 at 22:14
  • post here how you tried to establish the db connection – johan855 Mar 20 '16 at 17:36
  • Hi mate. I've managed to get it working now. Thanks for getting back to me. – SB_ Mar 25 '16 at 06:07