Can't Write to PostGIS PostGreSQL database via psycopg2

[David Michael Schruth,]

On Oct 22, 8:43 pm, Erik Jones <e... at myemma.com> wrote:
> On Oct 22, 2007, at 8:19 PM, David Michael Schruth, wrote:
>
>
>
> > Hi,
>
> > I am sort of in a jam here.  I am using the PsycoPG2 library to read
> > data out of a windows XP based PostGIS / PostGreSQL database but I am
> > apparently unable to write (update or insert) even though I am able to
> > read (select)
>
> > I am using PsycoPG2 2.0.6 (psycopg2-2.0.6.win32-py2.5-pg8.2.4-
> > release.exe )
> > with Python 2.5 (python-2.5.msi)
> > and PostGres 8.2.5.-1   (postgresql-8.2.5-1.zip)
> > and PostGIS 8.2 (postgis-pg82-setup-1.3.1-1.exe)
>
> > I can use PGadminIII to paste the same SQL queries and execute just
> > fine, but when I try to execute them via python code like
>
> > import psycopg2
> > conn = psycopg2.connect("dbname='postgis' user='postgres'
> > host='localhost' password='12345'")
> > c=conn.cursor()
> > c.execute("""INSERT INTO thetable (name) VALUES ('asdf');""")
> > c.execute("""UPDATE thetable SET name = 'somename' WHERE id = 321;""")
> > print(c.statusmessage)
> > #returns "INSERT 0 1" and "UPDATE 0" respectively
>
> > It gives me very specialized table specific error messages if the
> > query is wrong, but when it's correct, it does nothing and doesn't
> > update.   The only way I can update is pasting the query into the
> > PgAdminIII query window.
>
> > This is a problem on two separate machines (XP and Windows 2003
> > server) with all of the above components installed by two different
> > people.''
>
> The return value of the insert of 'INSERT 0 1' indicates that one row
> was inserted so the insert certainly worked.  If you're not seeing
> the results when you look at the database after the script has run
> it's probably because you need to call conn.commit() after your
> execute statements in order to commit your transaction as psycopg
> does not, by default, run in "autocommit" mode.  The update's return
> value of 'UPDATE 0' indicates that the where condition of your update
> query did not, in fact, match any existing rows in your table.
>
> Erik Jones
>
> Software Developer | Emma®
> e... at myemma.com
> 800.595.4401 or 615.292.5888
> 615.292.0777 (fax)
>
> Emma helps organizations everywhere communicate & market in style.
> Visit us online athttp://www.myemma.com

Thank you so much that fixed the problem!