On 5/12/15 4:51 PM, Sam Zhang wrote:
Hello,
I'm trying to use SQLAlchemy with a custom PL/R
<http://www.joeconway.com/plr/doc/index.html> function. My PL/R
function returns a "RECORD" type, so it requires a column definition
list. I'm trying to replicate this in SQLAlchemy:
|
SELECT *FROM diffindiff('dc','{"bronx"}','2014-01-01')AS t(est
FLOAT,stderr FLOAT,tstat FLOAT,pval FLOAT);
|
So far this is what I have:
|
+ q =session.query(func.diffindiff(target,comparisons,date))
+ results =q.all()
|
|
ProgrammingError:(psycopg2.ProgrammingError)materialize mode
required,but it isnotallowed inthiscontext
CONTEXT:InPL/R functiondiffindiff
[SQL:'SELECT diffindiff(%(diffindiff_2)s, %(diffindiff_3)s,
%(diffindiff_4)s) AS
diffindiff_1'][parameters:{'diffindiff_2':'dc','diffindiff_3':['bronx'],'diffindiff_4':'2014-01-01'}]
|
I've tried variations on this with query.alias and query.label, to no
avail. Any thoughts?
it looks like you are passing a Python list to the parameters which is
not what you've indicated as the SQL you're looking for. you're looking
to send '{"bronx"}', not ['bronx']. Check that the value of
"comparisons" is a string.
Beyond that, there might be issues here where you really need to be
using callproc().
Run this script:
import psycopg2
conn = psycopg2.connect(user=<username>, password=<password>,
host=<host>, dbname=<dname>)
cursor = conn.cursor()
cursor.execute(
"SELECT diffindiff(%(diffindiff_2)s, %(diffindiff_3)s,
%(diffindiff_4)s) AS diffindiff_1",
{'diffindiff_2': 'dc', 'diffindiff_3': '{"bronx"}', 'diffindiff_4':
'2014-01-01'}
)
print cursor.fetchall()
that script needs to work first before you can use this query with
SQLAlchemy.
Thanks,
Sam
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
