Trouble writing data from python to postgreSQL with psycopg2

Question

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')"

Quick question: have you tried calling conn.commit() after the insert? — alecxe
– alecxe, Commented Apr 28, 2016 at 1:07
good idea. just tried it and that didn't solve the problem either, though. — aaron
– aaron, Commented Apr 28, 2016 at 1:29

alecxe · Accepted Answer · 2016-04-28 01:31:04Z

2

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]

answered Apr 28, 2016 at 1:31

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

aaron Over a year ago

ah, that's it, thanks! As long as I have you here, can I ask how I would pull in all the records from the database into the python shell?

alecxe Over a year ago

@Aaron sure, for example: cursor.execute('SELECT * FROM denoue;') and print(cursor.fetchall()).

aaron Over a year ago

ok, cool, this makes sense. i was looking for cursor.execute to actually return values. looks like the fetchone/fetchall commands are what get you there. thanks!

Collectives™ on Stack Overflow

Trouble writing data from python to postgreSQL with psycopg2

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related