How does MySQL work with Python

Source codes - MySQL access with Python

Language overview / Python / MySQL

MySQL access with Python

This page was viewed 20435 times.

This article was written in a Wikiweb system, which means that you can edit the article at any time if you have found an error or want to add something.


Keywords: python mysql access pymsql mysqldb howto tutorial

MySQL access via Python is not entirely intuitive, especially with former PHP developers :)


import MySQLdb

First we need to load the MySQL module. If a sufficiently current version is not available for the distribution, it must be built from the sources. At least the MySQL and Python development packages are required.
If you get an error message, you can download the MySQLdb module here for free.


mysql_opts = {
'host': "localhost",
'user': "my_user",
'pass': "my_pw",
'db': "my_db"
mysql = MySQLdb.connect (mysql_opts ['host'], mysql_opts ['user'], mysql_opts ['pass'], mysql_opts ['db'])

After these two lines we now have an open MySQL connection, which we can address via the mysql object.


mysql.apilevel = "2.0"
mysql.threadsafety = 2
mysql.paramstyle = "format"

The first option sets the API level for the communication between Python and MySQL (so far the current version "2.0" has always worked for me without any problems).
The thread safety option only becomes important in thread programming, whereby level 2 should usually be appropriate here as well.
The format specifies how variables are marked in the SQL command. format stands, for example, for the C syntax% s (attention: everything is a string, as numbers are also automatically converted to '123'). Another possibility would be pyformat (corresponds to:% (varname) s).


cursor = mysql.cursor ()
cursor.execute ("SELECT * FROM` products` ")
products = cursor.fetchall ()

In the first line a so-called 'cursor' is created, with which one can now access the MySQL-DB. Queries can be parallelized using several cursors.
Line two passes the SQL command while line three assigns the result to a variable. The content of products now has the following format
(('value_of_column1_and_line1', 'value_of_column2_and_line1'),
('value_of_column1_and_line2', 'value_of_column2_and_line2'))


cursor.execute ("SELECT` id`, `name` FROM` customers` WHERE `product_id` =% s", products [0] [0])
customer = cursor.fetchone ()

A single line is queried with this two-line character. The result in customer could look like this:
('123', 'my name')

The big advantage lies in the automatic escaping and converting of the values. For example, MySQL datetime columns can be compared directly with datetime objects and None values ​​are translated to NULL.

For further information, we recommend studying the help integrated in Python, as well as the (sometimes quite confusing) manuals on the homepage. [Url]

Do you have any questions or do you want to discuss the article?


Have you found an error? Then click on Edit and fix the error. Don't worry, you can't destroy anything, the article can be restored.

Language overview / Python / MySQL / MySQL access with Python