MySQL with Python


MySQL with Python



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.
MySQL 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.
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
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[0], " ", row[1]
Output:
1   Hello World
2   MySQL Example
3   Flask Example
Share:

Popular Posts

Search This Blog

  • ()
Training Trains. Powered by Blogger.

Follow by Email

Flickr

Privacy policy