Chapter 6: MySQL with Python

In this Article you will learn how to use a widely used database management system called MySQL in Python.  You do not need any previous knowledge of MySQL to use this tutorial, but there is a lot more to MySQL than covered in this short introductory tutorial.

MySQL database:

Data is stored in a collection of tables with each table consisting of a set of rows and columns. This is similar to how data is stored in SQLite.   To interact with the data stored in tables we use a special-purpose programming language called SQL.

Generic Database Interface:

Most Python's database interface remains to Python's DB-API standard and most of the databases have ODBC support. Other than that Java database usually support JDBC and programmers can work with that from Jython

Benefits of Python DB programming:

  • Programming in Python is considerably simple and efficient with compared to other languages, so as the database programming
  • Python database is portable and the program is also portable so both can give advantage in case of portability
  • Python supports SQL cursors
  • It also supports Relational Database systems
  • The API of Python for database is compatible with other databases also
  • It is platform independent

Step 1: Install MySQL

First you must install a MySQL driver, use the specific installation method below.

On Windows:

Install MySQLdb using the installer.

On Linux:

Install MySQLdb using:

sudo apt-get install python-mysqldb yum install mysql-python

depending on your version.

On Mac:

Follow the installation instructions from stackoverflow

MySQL server has to be running before going to the next step.

Step 2: Setup the database

Make sure you have database access, from the command line type:

mysql -u USERNAME -p

img

MySQL will then ask your password.  Type these commands:

mysql\> CREATE DATABASE pythonspot; mysql\> USE pythonspot;

We go on the create the table:

[./media/image2.png](./media/image2.png) CREATE TABLE IF NOT EXISTS examples ( id int(11) NOT NULL AUTO_INCREMENT, description varchar(45), PRIMARY KEY (id) );

Then we can insert data into the table (these are SQL queries):

INSERT INTO examples(description) VALUES ("Hello World"); INSERT INTO examples(description) VALUES ("MySQL Example"); INSERT INTO examples(description) VALUES ("Flask Example");

You can now grab all records from the table using a SQL query:

mysql\> SELECT \* FROM examples; +----+---------------+ \| id \| description \| +----+---------------+ \| 1 \| Hello World \| \| 2 \| MySQL Example \| \| 3 \| Flask Example \| +----+---------------+ 3 rows **in** set (0.01 sec)

Step 3: Getting the data from Python

You can access the database directly from Python using the MySQLdb module.

#!/usr/bin/python* **import** MySQLdb  db = MySQLdb.connect(host="localhost", *\# your host*  user="root", *\# username* passwd="root", *\# password* db="pythonspot") *\# name of the database*  *\# Create a Cursor object to execute queries.* cur = db.cursor()  *\# Select data from table using SQL query.* cur.execute("SELECT \* FROM examples")  *\# print the first and second columns*  **for** row **in** cur.fetchall() : **print** row[0], " ", row[1]

Output:

1 Hello World 2 MySQL Example 3 Flask Example

AUTHOR

READ NEXT

Boostlog is an online community for developers
who want to share ideas and grow each other.

Delete an article

Deleted articles are gone forever. Are you sure?