Accessing MySQL database in Python
Data is everywhere and software applications use that. Data is either in memory, files or databases. Python has bindings for many database systems including MySQL, Postregsql, Oracle, Microsoft SQL Server and Maria DB. Data is stored in a collection of tables with each table consisting of a set of rows and columns. To interact with the data stored in tables we use a special-purpose programming language called SQL.
Python database interface install
In order to access a database in Python, we must download a Python database interface for the database we want to access. For example, if you need to access an Oracle database as well as a MySQL database, you must download both the Oracle and the MySQL database modules. The DB API provides a minimal standard for working with databases using Python structures and syntax wherever possible. The API allows us to import the module, make a connection to the database, issue statements and stored procedures, and close the connection.
One of these interfaces is MySQLdb. MySQLdb is an interface for connecting to a MySQL database server from Python. It uses the Python Database API v2.0 and is built on top of the MySQL C API. In this tutorial, we will be using MySQL as the database since MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web. It is one part of the very popular LAMP platform which consists of Linux, Apache, MySQL, and PHP.
Using package manager
$ sudo apt-get install python-mysqldb
Using Python's PIP
$ sudo pip install MySQL-python
Accessing database
In order to use the MySQL interface, we must include it into our Python script by using the import keyword. In order to connect to a MySQL database, we need to have some information. We need the hostname where the MySQL database resides, the database name we want to connect to, and the login credentials. Once we have that information, we can create a MySQL connection, execute a SQL query, and close the connection.
import MySQLdb
database = MySQLdb.connect("DB_HOST","USER","PASSWORD","DB_NAME" )
cursor = database.cursor( )
cursor.execute( "SELECT VERSION()")
data = cursor.fetchone()
print" data
db.close()
Read operations
Read operation means to get some information from the database. Once our database connection is established, we are ready to make a query into this database. You can use either fetchone() (as seen in the above example) method to get single record or fetchall() method to get multiple values from a database table. Below are some of the read operations.
fetchone() - Gets the next row of a query result set. A result set is an object that is returned when a cursor object is used to query a table.
fetchall() - Gets all the rows in a result set. If some rows have already been extracted from the result set, then it retrieves the remaining rows from the result set.
row count - Gets the number of rows affected by an execute() method
Write operations
Write operations can include updating a database, deleting records from a database or adding records to a database. To do this, we use SQL commands such as insert, update and delete. In fact, we can issue any SQL command by including it in the execute command between double quotes. Once the execute command is issued, we must commit the changes by executing the commit() method.
import MySQLdb
database = MySQLdb.connect("DB_HOST","USER","PASSWORD","DB_NAME" )
cursor = database.cursor( )
cursor.execute( "SELECT VERSION()")
data = cursor.fetchone()
print" data
cursor.execute ("DELETE from SOME_TABLE SOME COLUMN WHERE SOME_CRITERIA" )
database.commit()
db.close()