Using pipes to redirect a function-generated SQL query to the psql command

I have a script that generates an SQL query as text e.g.

...
return "SELECT COUNT(*) FROM important_table" 

which I would then like to run on a PostgreSQL database. I can do this in two lines like this:

python SQLgeneratingScript.py parameters > temp.sql 
psql -f temp.sql -d my_database 

But seems like I shou开发者_C百科ld be able to one-line it with pipes, but I don't know how.

python SQLgeneratingScript.py parameters|psql -d my_database

i don't know why you are not using a python postgresql connector like psycopg2 ,but well if you want to do what you are trying to do using Unix command redirection you will have to do it like this in your code

...
print "SELECT COUNT(*) FROM important_table"  # or use sys.stdout.write()

this will write in your file temp.sql if you have ran your command like this:

python SQLgeneratingScript.py parameters > temp.sql

but well i will suggest writing in you file in python like this

def generate_sql():
   ...
   return "SELECT COUNT(*) FROM important_table"

with open('temp.sql', 'w') as f
   sql = generate_sql()
   f.write(sql)

or more use psycopg2 to execute directly your sql

import psycopg2

def generate_sql():
    ...
    return "SELECT COUNT(*) FROM important_table"

conn = psycopg2.connect("dbname= ...")

cur = conn.cursor()
sql = generate_sql()
cur.execute(sql)

conn.close()