Tips and Tricks About Computers, Web Development, Linux, the Internet and the Like
Archive for October, 2010
MySQL and Python – The Problems and [Some] Solutions
Oct 15th
Intro
I’m still relatively new to Python. I dabble once in a while. Usually when I catch myself writing something overly complicated in bash. I figured doing MySQL from a Python script would be easier and better implemented in Python. Well, I was in for a few surprises I’d like to share with others looking to integrate with MySQL in Python before you encounter the same problems I did.
Getting on with it
As a Python noob, it made sense to me to use the python-mysqldb module. It “works” but not well. Read through the [working] code below and see if you can find the problem and how I circumvented it.
#!/bin/python
# Tyler Mulligan (tyler@doknowevil.net)
# MySQL the WRONG way in Python
import MySQLdb
def connection_settings():
global DB_ADMIN, DB_ADMIN_PASSWORD, DB_NAME, DB_HOST, DB_USER, DB_PASSWORD
DB_ADMIN="root"
DB_ADMIN_PASSWORD="local"
DB_NAME="foobardb"
DB_HOST="localhost"
DB_USER="foobar"
DB_PASSWORD="local"
def run_admin_sql():
conn = MySQLdb.Connection(host=DB_HOST, user=DB_ADMIN, passwd=DB_ADMIN_PASSWORD)
cursor = conn.cursor()
#cursor.execute("DROP USER %s@%s", (DB_USER, DB_HOST))
#cursor.execute("DROP DATABASE %s" % DB_NAME)
cursor.execute("CREATE USER %s@%s IDENTIFIED BY %s", (DB_USER, DB_HOST, DB_PASSWORD))
cursor.execute("CREATE DATABASE %s" % DB_NAME)
cursor.execute("GRANT USAGE ON %s.* TO %s@%s IDENTIFIED BY '%s'" % (DB_NAME, DB_USER, DB_HOST, DB_PASSWORD))
cursor.execute("GRANT ALL PRIVILEGES ON *.* to %s@%s IDENTIFIED BY %s", (DB_USER, DB_HOST, DB_PASSWORD))
cursor.close()
conn.close()
conn = MySQLdb.Connection(db=DB_NAME, host=DB_HOST, user=DB_USER, passwd=DB_PASSWORD)
cursor = conn.cursor()
cat_table_sql = """CREATE TABLE cats
(
`id` int(3) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`age` int(2) NOT NULL,
`type` int(2) NOT NULL,
PRIMARY KEY (`id`)
)"""
cursor.execute(cat_table_sql)
cursor.execute("INSERT INTO cats (name, age, type) VALUES (%s, %s, %s)", ("fluffy", 5, 1))
cursor.execute("INSERT INTO cats (name, age, type) VALUES (%s, %s, %s)", ("meow meow", 6, 1))
cursor.execute("INSERT INTO cats (name, age, type) VALUES (%s, %s, %s)", ("purrfect", 3, 1))
cursor.execute("SELECT * FROM cats")
results = cursor.fetchall()
cursor.close()
conn.close()
return results
connection_settings()
print run_admin_sql()The problem is parameterization. The python-mysqldb module sucks at it. It’s glaringly obvious when you look at the follow snippet and how I worked around it.
cursor.execute("CREATE USER %s@%s IDENTIFIED BY %s", (DB_USER, DB_HOST, DB_PASSWORD)) # GOOD
cursor.execute("CREATE DATABASE %s" % DB_NAME) # BAD NEWS BEARS
cursor.execute("CREATE DATABASE %s", (DB_NAME)) # but the module won't support this
The difference, is that the first implementation uses the module’s parameterization to escape the variables that are passed to it, making it safe. The second is using Python’s string formatter which means I’d have to do all the escaping and sanitization prior, which is obviously more dangerous and annoying.
The MySQLdb docs bury this little note, “Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.”
You can implement it this way but you’ll be wasting your time. There are better modules out there to make up for this one’s shortcomings. OurSQL and SQLAlchemy were the two libraries recommended to me in #python on irc.freenode.org. I will make another post when I develop the same example above using one of these libraries.
Multiple X (Desktop) Sessions in Ubuntu
Oct 13th
This post has a lot to do with graphics but there are no graphics. It’s a walk-through explanation and and proof of concept of some very interesting features of Linux as a desktop operating system.
If you aren’t familiar with X, than this webpage might confuse you, the X Window System is what draws the GUI (graphical user interface) for Ubuntu. On top of this, you may have a Window Manager or Desktop Environment, such as Gnome (Ubuntu default) or KDE (Kubuntu).
When you boot up Ubuntu, it creates a set of “virtual terminals”. These VTs are accessible via a key combination of clt+alt+f1-12. VT7 (ctrl+alt+f7) is the default and it handles X’s “screen 0″. If you play around, with the key combination, you’ll notice you drop into consoles with a login prompt (f1-6) or a blank screen (f8-f12, don’t worry if you see USB errors).
These virtual terminals used to be handled by X which was slower and more prone to crash (citation needed) but since Ubuntu 8.04, this has been handled by “Kernel Mode”, where this management is handled by the kernel. You can switch to another virtual terminal and create another X session.
Typically in Linux, you could switch to another VT, login [as another user] and type $ startx — :1 (special argument ‘--‘ marks the end of client arguments and the beginning of server options, :1 defines screen 1). This will work in Ubuntu but the part where I found it failing was switching between this newly created X session and back to my original :0 on VT7.
The way I found to do this in Ubuntu seems a bit counter-intuitive. Before I explain, you should create a new user, if you don’t have another already. You can do this by going to System > Administration > Users and Groups.
To create a second X session in Ubuntu, go to your logout menu (default top right) and select “switch user”, and login as another user (you don’t want to create an error in the user environment). When you login as another user, Ubuntu creates a screen :1 on VT8. This means, you can change back to VT7 with ctrl+alt+f7, then back to VT8 with ctrl+alt+f8. I suspect this is the reason VT8-12 show up as blank screens instead of login terminals. Ubuntu seems to be leveraging the power of virtual terminals for “user switches”.
I haven’t noticed much in performance loss doing this and the other big question is practicality. Why would you ever do this? Perhaps you are testing software and want isolated test cases or you want a dedicated user for games with a more streamlined window manager and want to be able to flip back and forth.
Similar areas I came across in my research were Nested X sessions and Multiseat X.


