Executing a Postgresql query in Python that creates a table
Question: I'm creating a Flask webapp that displays the results of various Postgresql
queries. As part of the initialization, I want to run a query that creates a
table in Postgresql containing all of the data that I will need for subsequent
queries. My problem is that, although the webapp appears to initialize
correctly (in that it doesn't throw up any exceptions), I have no idea where
the new table is being stored (I believe I have namespaced it correctly) and I
can't access it from any other queries.

Code below:

    
    
    import psycopg2 as p2
    import pandas
    
    conn = p2.connect("dbname='x' user='y' host='z' password='password' port='1234'")
    cur = conn.cursor()
    
    def exec_postgres_file(cursor, postgres_file):
        statement = open(postgres_file).read()
        try:
            cursor.execute(statement)
        except (p2.OperationalError, p2.ProgrammingError) as e:
            print "\n[WARN] Error during execute statement \n\tArgs: '%s'" % (str(e.args))
    
    exec_postgres_file(cur,'/Filepath/initialization_query.sql')
    cur.execute("""SELECT * FROM schema.new_table""")
    rows = cur.fetchall()
    new_table_df = pandas.DataFrame(rows)
    print new_table_df
    
    exec_postgres_file(cur,'/Filepath/query1.sql')
    rows2 = cur.fetchall()
    query1_df = pandas.DataFrame(rows2)
    print query1_df
    

Where new_table is the table created during initialization_query. query1 is
trying to access new_table but an exception is thrown up after the second
exec_postgres_file statement:

> relation "new_table" does not exist LINE 10

Assume initialization_query is:

    
    
    select *
    into schema.new_table
    from schema.old_table
    where id in    ('A','B','C','D','E','F')
    ;
    

and query1 is:

    
    
    select date, sum(revenue) as revenue  
    from schema.new_table
    group by date
    ;
    

These both work when I run the query in a database management tool like
Navicat.


Answer: This is what Flask-Migrate has been made for: <http://flask-
migrate.readthedocs.org/en/latest/>

