Search code examples
pythonsqlpostgresqlcursorsqlobject

create temporary table from cursor

Is there any way, in PostgreSQL accessed from Python using SQLObject, to create a temporary table from the results of a cursor?

Previously, I had a query, and I created the temporary table directly from the query. I then had many other queries interacting w/ that temporary table.

Now I have much more data, so I want to only process 1000 rows at a time or so. However, I can't do CREATE TEMP TABLE ... AS ... from a cursor, not as far as I can see. Is the only thing to do something like:

rows = cur.fetchmany(1000);
cur2 = conn.cursor()
cur2.execute("""CREATE TEMP TABLE foobar (id INTEGER)""")
for row in rows:
    cur2.execute("""INSERT INTO foobar (%d)""" % row)

or is there a better way? This seems awfully inefficient.

Solution

  • I ended up doing this:

            sql.execute(connection, """
INSERT INTO blah VALUES %s;""" % (
    ", ".join("(%d)" % hid for hid in hids)))

    instead of 1000 separate inserts. Still don't know a better way, but this works well enough.