Python & MySQLdb - Part 1: The Absolute Basics

[jlgosse]

http://mikegrouchy.com/python-and-mysqldb-part1-the-absolute-basics/

[RyanMcGreal]

As soon as you 'get' MySQLdb, I recommend moving onto an ORM like SQLAlchemy. Among its advantages:

* You can easily change database engines without rewriting all your queries

* SQLAlchemy has built-in protection from SQL injection attacks.

* You can define your database tables and relations once (in SQLAlchemy) and it will create the database for you if it doesn't already exist.

[mgrouchy → RyanMcGreal]

Very neat, I had heard of SQLAlchemy before but have never tried it, looks like going forward I will have to give it a try.

[blhack]

something that I tore my hair out over when building my website (gibsonandlily.com) was how to handle input santizing for mysql.

I wrote some functions for it in python (and I'm sure that they don't work very well. Yes, I'm sure some of you could destroy my website...it is a hobby, please don't).

I later found out (and I'm in the process of replacing code) that you can have mysqldb handle it for you like this:

    cursor.execute("insert into links(url,description) values(%s,%s)", (url,desciption))

where url and description (the second ones) are variable holding the values you want to insert.

this was different than the way that I was doing it before, which was:

    insert_query = "insert into links(url,description) values('%s','%s')" % (url,description)

    cursor.execute(insert_query)

I'm sure this sounds completely stupid to some of you who make your living writing web apps...but I thought it was really cool :).

[lincolnq → blhack]

Yes, it's very important to do it the first way!

mysqldb has a bunch of Python->MySQL conversions. If you look at the source you can read converters.py -- it can convert lists, strings, numbers, dates and a lot of other types too.

[kierank]

One of the things that caused me a headache for about an hour was realising that you had to do a conn.commit() when updating or INSERTing.

[mgrouchy → kierank]

I probably should have mentioned that in the article, truth is it really depends on your set up, most of the mysql databases I have used are not set up for transactions.

[tocomment → kierank]

You can set your connection (possibly your db) to autocommit. I forget how offhand, maybe do a search.

[mgrouchy → tocomment]

MySQL is set to autocommit by default (you can check with "select @@autocommit", there are probably some other ways to check as well), then it comes to which engine you use, many of them don't support transactions, but some like InnoDB do. So it really all depends.

in response to lincolnq below: you are correct, if the engine does support autocommit it is turned on automatically. http://www.python.org/dev/peps/pep-0249/

[lincolnq → mgrouchy]

MySQL may be autocommit by default, but I believe the Python DB-API spec (and therefore mysqldb) specifies to turn autocommit off when the connection is opened.

[jlgosse]

A great little introduction to using MySQL in Python. Definitely worth a look for those who are interested to see how well it actually works.