In this tutorial 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. If you are interested in learning more about SQL, please follow this tutorial.
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.
Step 1: Install MySQL
First you must install a MySQL driver, use the specific installation method below.
Install MySQLdb using the installer.
Install MySQLdb using:
sudo apt-get install python-mysqldb yum install mysql-python
depending on your version.
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
MySQL will then ask your password. Type these commands:
mysql> CREATE DATABASE pythonspot; mysql> USE pythonspot;
We go on the create the table:
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, " ", row
1 Hello World 2 MySQL Example 3 Flask Example