Import/execute sql file

[David]

Until now i did all my db queries with the DAL from web2py.
But first the first i now got a raw sql file that needs to be execute
at a certain point.
In the terminal i use the '\i' command to import and execute a sql
script.

If i try to use this with db.executesql("\i /path/to/file.sql") i get
an error: => CESTERROR: syntax error at or near "\" at character 1
.
Escaping the character "\\i" doesn't help, still got the same error.
Using psql from the system() call doesn't help (i don't have i
currently installed =] ).

Does someone ever came across this problem before?

[Mathew Grabau]

Calls are not executed with the psql command, they are done with psycopg2. 

You could try a subprocess.POpen call:

import subprocess

...

sp = subprocess.POpen("psql {insert remainder of command line here}", shell=True)

For more on the options of the subprocess module: http://docs.python.org/library/subprocess.html

[Brian M]

Try opening your file, reading its contents and then having db.executesql() execute what was read. Should work assuming the file's contents can be run by psycopg2 (i.e some special psql stuff might not work)

#untested code
f = open('/path/to/file.sql', 'r')
db.executesql(f.read())

[Bruno Rocha]

 
That works, but prefer to run inside a context:

with open("/path/to/file,sql", "r") as sql:
    code = sql.read()
    # here you can do some parsing, remove spaces, replace things
    db.executesql(code)