Aaron Aaron - 7 months ago 10
Python Question

Trouble writing data from python to postgreSQL with psycopg2

I am trying to insert data from a python shell into a postgresql database using

psycopg2
. I'm trying to migrate the following database to a local postgreSQL file: http://www3.inegi.org.mx/sistemas/mapa/denue/default.aspx. Simplified example of workflow looks like this:

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

DB_CONN_INEGI = 'host=localhost user=postgres password=password port=5432 dbname=inegi'

conn = psycopg2.connect(DB_CONN_INEGI)
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()


I then execute this command to insert some values into the table:

cursor.execute("INSERT INTO denoue VALUES %s;" % tmp)


This executes just fine, but that when I check the record count with:

cursor.execute('SELECT COUNT(*) FROM denoue;')


This returns nothing (
None
). I can run the call above over and over again, but the result is always the same--no data seems to be getting added to the table.

Am I missing anything obvious? Apologies for lack of a quick working example--I can try to mock one up quickly that runs on local db if that would be useful here.

tmp
looks like this:

"(40740, 'ZONA MILITAR', 'SECRETAR\\xc3\\x8dA DE LA DEFENSA NACIONAL', 931410, 'Impartici\\xc3\\xb3n de justicia y mantenimiento de la seguridad y el orden p\\xc3\\xbablico', '11 a 30 personas', 'CALLE', 'R\\xc3\\x8dO LERMA', 'CALLE', 'R\\xc3\\x8dO SENA', 'CALLE', 'R\\xc3\\x8dO \\xc3\\x89UFRATES', 'CALLE', 'R\\xc3\\x8dO LERMA', 100.0, 'null', 'null', 'null', 'null', 'null', 'FRACCIONAMIENTO', 'COLINAS DEL R\\xc3\\x8dO', 'null', 'null', 'null', 20010.0, 1, 'AGUASCALIENTES', 1, 'AGUASCALIENTES', 1, 'Aguascalientes', '2013', 25, 'null', 'null', 'null', 'Fijo', 21.89341096, -102.32335734, 'DICIEMBRE 2014')"

Answer
cursor.execute('SELECT COUNT(*) FROM denoue;')

This would actually execute a query and return None. To get the query results, you should use fetchone() method:

results_count = cursor.fetchone()[0]
Comments