Forums

EDIT: Solved. Added db.commit() and db.close() to the code below

I'm looking for recommendations for the interactions between my python scripts and functions and the mysql data base.

I figured the MySQLdb library was a pretty good place to start. I can read tables just fine. Inserting seems to work fine in that particular MYSQL session, but I'm unable to make persistent changes.

Code example below, using the simplest possible example I could think of, a two column table of type varchar(20).

import MySQLdb

db = MySQLdb.connect(host="mysql.server", user="jansimple", passwd="******", db="jansimple$lekegrind")  
cur = db.cursor()

cur.execute("insert into test values( 'Another PYTHON', 'MySQLdb insert')")
cur.execute("SELECT * FROM test")
rows = cur.fetchall()
print rows

# Must commit changes to make them stick. 
db.commit()

# Good form to close the database
db.close()

The last print command shows my table as I expect it to be after the successful insert command. However, as that session terminates those changes evaporate...

Do I need to run a magic mysql command to make those changes permanent?

Should I use another python library? (I figured mySQLdb was probably the right place to start, going really simple and learn along the way...)

As described above, my code lacked db.commit() to make changes stick...

Thank you for your patience!

Yes, if you're used to a MySQL library which sets autocommit by default then it can be a bit confusing to switch to one which doesn't and have to remember to call an explicit commit() whenever you want to push changes to the database.

Once you get into the habit of it, however, it makes it much easier to keep your database consistent when you have complete control of when transactions are committed. In case you're unfamiliar with MySQL's transaction support, you can carry out multiple statements and they will occur in a protected environment and only be copied to the actual database once you call commit(). If you close the connection without doing this, MySQL will automatically rollback any changes you've made since the last call to commit(). So, you can split your update over multiple statements, but keep them as a single atomic change to the database.

The official MySQL documentation has some background on transactions and also more details on how they can be used. You might not need that sort of detail, but it might be interesting reading. Note that transactions are only supported for the InnoDB storage engine - the older MyISAM storage engine always commits each statement, and also doesn't support basic relational database features such as foreign key constraints. Storage engines are chosen on a per-table basis.

Thanks a lot for the background info, Cartroo. Learning that sort of stuff is exactly why I signed up for the PA account: I am a firm believer in learning abstract concepts while at the same time getting my fingers dirty with nitty-gritty implementation-specific details, and such contributions from the user community is most appreciated.

Here at PA I can play around with mysql (and hopefully postgresql, if/when that is added) and python, learning more of both as I play around.

@jansimple: If you wish to work with PostgreSQL prior to it's debut on PA there are installers available for:

• 2 flavors of BSD
• 5 flavors of Linux
• Mac OS X
• Solaris
• Windows

The default configuration for a PG install is quite minimal on any modern hardware. Of course the requirements can be changed both up and down, but the point is that getting a server running is actually a trivial matter. Obviously (hopefully) tuning it for a particular production workload can take much more. And of course integrating with an environment like PA is likely not trivial, but doing it for yourself locally is.

The same installer link above even has multiple LiveCD's available if you want to play w/o any system changes.

Thanks a lot, I know I can install postgresql locally without much trouble, just as I can (and have!) installed python locally. But where's the fun in having a POSTGIS/postgresql installation that I can't integrate with cool web services? I have a great laptop, but I can't publish ANYTHING from it. The restrictions of my corporate network are too many to list here...

Sure, I can (and probably will) play with a local postgresql installation. But playing around at PA is so much cooler :)

I'm actually working on postgres today. Most extensions shouldn't be a problem. PostGIS looks a little trickier but we could definitely offer earth distance immediately.

I love how this started as a MySQL thread, but we hijacked it for PG...☺

I would like the PA developers to add a 'like' button...

@rcs1000: for comments in the forums?

Thanks OP, this helped me a lot!