MySQL + python

Krzesimir · 2011-02-17 19:18:12

Hello.
I'm having problems with inserting data into tables using python.
For testing purposes I created table test with single column and inserted single value into it. Then I tried to insert something into it using this python code:

#! /usr/bin/python2

import MySQLdb

conn = MySQLdb.connect(***)
cur = conn.cursor()

print cur.execute("select * from test")
cur.execute("insert into test values(4)")
print cur.execute("select * from test")

cur.close()
conn.close()

conn = MySQLdb.connect(***)
cur = conn.cursor()
print cur.execute("select * from test")

cur.close()
conn.close()

IMHO the output should be: 1 2 2, but the output is: 1 2 1. So after I close connection inserted data seems to be removed.
I've got all required privileges.
Any ideas where the problem is?

EnvoyRising · 2011-02-17 19:32:02

IIRC, the API is transaction-based by default, so don't you need to commit changes? (eg conn.commit()). I'm just shooting from memory, so the syntax may be off

Last edited by EnvoyRising (2011-02-17 19:33:15)

firecat53 · 2011-02-17 20:02:01

Yes, you need the conn.commit(). Here's a generic sql_command I use to modularize that code:

def sql_statement(conn,statement):
    "abstracted sql command to include error checking"
    try:
        cursor = conn.cursor()
        cursor.execute(statement)
    except dbapi.Error, e:
        conn.rollback()
        raise Exception("Database Error %d: %s" % (e.args[0], e.args[1]))
    else:
        conn.commit()
        return cursor

Scott

Krzesimir · 2011-02-17 20:44:03

Thanks a lot. It's working of course.

firecat53 · 2011-02-17 21:23:04

Cool Don't forget to edit your first post and put [SOLVED] in the title.

Scott

Arch Linux

#1 2011-02-17 19:18:12

MySQL + python

#2 2011-02-17 19:32:02

Re: MySQL + python

#3 2011-02-17 20:02:01

Re: MySQL + python

#4 2011-02-17 20:44:03

Re: MySQL + python

#5 2011-02-17 21:23:04

Re: MySQL + python

Board footer