Connecting to MySQL with Python and PHP

As you know, there are many modules and APIs in Python. For databases, there is the DB-API, the database application programming interface. To use this API with MySQL, you need to have the MySQLdb driver. It’s built into versions later than 2.3. You can also download it from its SourceForge project page and install it with the following commands:

# tar -xvf foldername.tar.gz
# cd path-to-extracted-folder
#python setup.py install

Accessing MySQL through DB-API using MySQLdb comprises the following steps:

1. Import the MySQLdb module.
2. Open the connection to the server.
3. Run your queries.
4. Close the connection.

We will undertake these steps in a script mysqlpython.py:

import MySQLdb                                    #1
connection=MySQLdb.connect(host="servername", user="username", passwd="password", db="databasename")  #2
cur=connection.cursor()                           #3
cur.execute("create table lfy(name varchar(40), author varchar(40))")  #4
cur.execute("insert into lfy values('Foss Bytes','LFY Team')")         #5
cur.execute("insert into lfy values('Connecting MySql','Ankur Aggarwal')")
cur.execute("select * from lfy")                  #6
multiplerow=cur.fetchall()                        #7
print “Displaying All the Rows:  “, multiplerow
print multiplerow[0]
cur.execute("select * from lfy")
row=cur.fetchone()                                #8
print  “Displaying the first row: “, row
print "No of rows: ", cur.rowcount                #9
cur.close()                                       #10
connection.close()                                #11

Figure 1 below shows the output of the Python script.

A few notes with respect to the above code:

1. We imported the MySQLdb module to use the database API.
2. We connected to the MySQL server by calling the MySQLdb connect() function. The parameters required were the server name, MySQL username, MySQL password, and the database you want to use.
3. To retrieve query results, we created cur, an object of the cursor() class, which will be responsible for execution and fetching.
4. cur.execute() executes the query to create a table named lfy.
5. We inserted values into the lfy table.
6. We retrieved all values in the lfy table through a SELECT query.
7. cur.fetchall() will fetch all results of the query, and return them as a nested list.
8. cur.fetchone() will fetch one row at a time. You might wonder why we executed the SELECT query again — because fetchall() has already fetched all results of the previous query, and calling fetchone() would return None.
9. The rowcount property tells us the number of rows returned by the query.
10. We closed the cursor object, freeing the resources it holds.
11. We closed the connection. Always remember to close the connections; otherwise, it may be a major security risk.

Connecting MySQL with PHP

PHP is very popular for server-side scripting, and MySQL databases are widely used for storage of the data used in dynamic pages. Connecting to MySQL from PHP is quite easy, with in-built functions; it follows the same four rules we discussed in the Python section. We will do the same basic tasks as the Python script; so before trying out the code below, drop the previous table. Here’s our PHP script, mysqlphp.php:

<?php
$connection=mysql_connect("servername","username","password") or die("connectivity failed"); #1
mysql_select_db("database name",$connection);       #2
$create="create table lfy(name varchar(50), author varchar(50))";
mysql_query($create,$connection);                   #3
$insert1= "insert into lfy values('Foss Bytes','LFY Team')";
$insert2= "insert into lfy values('Connection Mysql','Ankur Aggarwal')";
mysql_query($insert1,$connection);
mysql_query($insert2,$connection);
$fetch= mysql_query("select * from lfy");
while($row=mysql_fetch_array($fetch))               #4
{
  print_r($row);                                    #5
}
mysql_close($connection);                           #6
?>

A detailed explanation of the above script is given below:

1. If the connection to MySQL fails, we use the die() function as an error-handling technique, to stop processing the page. The connection object is passed to other MySQL-related functions for use.
2. We selected the database we want to use.
3. We used mysql_query() to execute queries.
4. mysql_fetch_array() will fetch the results of the query in the form of an array. We have used a while loop to display the results.
5. To display the data, we used print_r(), which will display the whole row at one go. To access individual fields, use a string index of the field-name, like row["name"], etc. See the output screenshot (Figure 2) for a clearer understanding.
6. We closed the connection to the MySQL server.

Connecting to a database is easy! So start playing with your database through scripting :-)