4

I am trying to copy rows form a PostgreSQL table to another one, using pg8000 driver for Python. This is the code:

import pg8000
conn = pg8000.connect(user="postgres", password="XXXXX",database="test")
cursor = conn.cursor()
cursor.execute("SELECT * FROM exampletable")
results = cursor.fetchall()

What I want now is to completely insert the results to a new table. It should be something like:

cursor.executemany("INSERT INTO secondtable VALUES (%s)", results)

but is not functioning this way and I don't know how to fix it. Not all fields are of string type, maybe that's should be fixed somehow?

nrainer
  • 2,542
  • 2
  • 23
  • 35
user1680859
  • 1,160
  • 2
  • 24
  • 40

3 Answers3

3

the format %s is for scalar values. Convert results into a string, like this:

cursor.execute("INSERT INTO secondtable VALUES %s" % str(results))
koriander
  • 3,110
  • 2
  • 15
  • 23
  • It shows me an error: File "C:\Python34\lib\site-packages\pg8000\core.py", line 1566, in execute ps = cache['ps'][key] KeyError: ((705,), 'INSERT INTO secondtable VALUES %s') – user1680859 Feb 08 '15 at 21:55
  • yes, sorry, replace the comma with % (see revised answer) – koriander Feb 08 '15 at 22:15
  • Now it gives: TypeError: executemany() missing 1 required positional argument: 'param_sets' – user1680859 Feb 08 '15 at 22:18
  • Not all fields are of string type, maybe that is the problem? – user1680859 Feb 08 '15 at 22:28
  • no, just use execute instead of executemany. The problem with executemany is that you need to specify the number of columns in advance. – koriander Feb 09 '15 at 20:15
  • Now it gives: pg8000.ProgrammingError: (b'ERROR', b'42601', b'syntax error at or near "["'). It seems I should somehow remove the [, tried to convert the list to a tuple, but again error: pg8000.ProgrammingError: (b'ERROR', b'25P02', b'current transaction is aborted, commands ignored until end of transaction block' – user1680859 Feb 09 '15 at 21:57
  • I solved the last problem with conn.rollback(). So your answer is correct. Just instead of results, should be used tuple(results) – user1680859 Feb 09 '15 at 22:52
0

Slightly off topic, but the search for a related question leads here. I wanted to know how to format the bound variables for the execute sql string. The format given in koriander's answer does not work with pg8000 1.10.5. Here's what ended up working for me:

    some_field_value = 'value'
    sql = 'SELECT a_field FROM your_table WHERE some_field = %s'
    cursor.execute(sql, [some_field_value])

Details from the docs: pg8000 Cursor.execute

The important bit:

args – If paramstyle is qmark, numeric, or format, this argument should be an array of parameters to bind into the statement

Two things to keep in mind:

  1. pg8000 never accepts quotes around the format string (%s)
  2. The value of pg8000.paramstyle matters. default value is 'format'
Brad Dre
  • 3,580
  • 2
  • 19
  • 22
-1

The way to do this is with the cursor's executemany method.

Let's say we want to copy the contents of our users table to another table named users_copy*. We need to fetch the rows, then construct an INSERT statement to execute.

import pg8000

conn = pg8000.connect(user='souser', password='password', database='test')
cursor = conn.cursor()

# Fetch the data.
cursor.execute('SELECT * FROM users')
results = cursor.fetchall()

# Construct the INSERT statement, dynamically creating the correct
# number of placeholders for the VALUES clause.
placeholders = ', '.join(['%s'] * len(results[0]))
stmt = f"""INSERT INTO users_copy VALUES ({placeholders})"""

# Insert to results into the target table.
cursor.executemany(stmt, results)
conn.commit()
conn.close()

* The question specifies that the target table is in another database, but we don't need to connect to another database to demonstrate the technique. If the target table were in the same database then SELECT INTO, CREATE TABLE AS or INSERTINTO ... SELECT * FROM ... could be used to avoid having to retrieve the data, as koriander commented.

snakecharmerb
  • 47,570
  • 11
  • 100
  • 153