How to use pandas.io.sql.read_frame method with parameterized SQL or which method should use?

[Daniel]

Hello,

Suppose I have an SQL like this:

sql = """

SELECT

columnA

FROM myTable

WHERE

myValue = ?"""

Is there a way I can pass a parameter to that SQL using the read_frame method?  Perhaps, I should be using another method?

BTW, I am connecting to a Sqlite3 database.

Thanks,

Daniel

[Miki Tebeka]

read_frame has an second parameter which is the connection object.

    conn = sqlite3.connect('/path/to/database')

    df = read_frame(sql, conn)

[Miki Tebeka]

Strike that, answered the wrong question. Sorry for the FUD.

[Daniel]

Looks like I'll have to resort to the standard way.  I looked at all the other functions in the pandas.io.sql package, but I don't see any of them having a method signature that suggests that I can pass in a parameter value for the sql.

[Miki Tebeka]

Seems like you can't do that in sqlite3 currently (http://code.google.com/p/pysqlite/issues/detail?id=20).

PostgreSQL (psycopg2) cursor has mogrify function that does what you want ...

[Daniel]

Not sure if I quite understand you, but I am currently executing parameterized SQL with sqlite3.  The issue is I want to be able to utilize panda's io.sql functions with paramterized SQL.  Currently, panda's io.sql functions' arguments don't allow for you to pass in a parameter value.

For example, here is the method signature for panda.io.sql.read_frame method:

read_frame(sql, con, index_col=None, coerce_float=True)

    Returns a DataFrame corresponding to the result set of the query

    string.

    Optionally provide an index_col parameter to use one of the

    columns as the index. Otherwise will be 0 to len(results) - 1.

    Parameters

    ----------

    sql: string

        SQL query to be executed

    con: DB connection object, optional

    index_col: string, optional

        column name to use for the returned DataFrame object.

As you can see, if I have an sql like "select * from table where column = ?", there isn't a way for me to pass in the value for the "?" into the read_frame method.  So instead, I have to do this within sqlite3's cursor.execute method which accepts sql and one or more parameter values.

[Daniel]

OK looks I found a way but I am not sure it is safe from sql injection  Does anyone know this is safe?

1. import psycopg2
2. import pandas.io.sql as sqlio
3.  
4. username = 'yomama'
5. pwd = 'yopwd'
6.  
7. conn = psycopg2.connect("host='your_host' dbname='mydb' user=" + username + " password=" + pwd)
8.  
9. sql = "select * from medicine_journal where medication = '%(medicine)s'"
10.  
11. # Create a dataframe that consists of the data defined by our SQL
12. df = sqlio.read_frame(sql % {'medicine':'flonase'}, conn)
13.  
14. conn.close()  # We've created our dataframe, so we can discard our connection now...
15.  
16. print df.to_string()

On Thursday, November 15, 2012 12:11:58 PM UTC-5, Daniel wrote: