PYTHON MYSQLDB

As MySQL is a leading open source database management system, we should learn how to access mysql using python programs.

python-mysqldb is the package which enables you to access mysql database.

Installation

sudo apt-get install python-mysqldb
Before you need to install mysql in your linux
follow below procedure to install mysql
sudo apt-get install mysql-server
After installation we need to setup mysql server to allow connections by remote and localhost clients(clients may be python mysqldb or linux mysql-client).
vi /etc/mysql/my.cnf
search for bind-address = 127.0.0.1 and comment it out like this #bind-address = 127.0.0.1
save your file
Now start creating DB
$ mysql -u root -p
Enter root password
mysql> SHOW DATABASES;
Databases will be displayed (by default databases will be exits, which are used by mysql)
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+
2 rows in set (0.00 sec)
mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.02 sec)
 
we have created "mydb" databases
mysql> USE testdb;
Database changed
 
Here is the formula to give permission to user, substitute parameter and execute it
 
GRANT ALL PRIVILEGES ON *.* TO username@address IDENTIFIED BY “password”;
 
"username" is the username of the remote machine from which you are connecting from. 
 "address" is IP address of the remote machine.
 "password" is the password of the remote user.
press ctrl-d to exit for mysqldb.
Hope you are thing, where is the table to access/Insert data ?
Dont worry, this is all we needed to work with python, we can create table for python mysqldb.
All we need to start with python is
1) mysql installed
2) configure mysql-server to allow remote connections
3) set user permissions to modify database.
4) Create a database.
 
A fare knowledge on MySQL queries  or learn in just 15 mins  http://www.w3schools.com/sql/sql_syntax.asp
Now we ll start to play with python and mysql
 
import MySQLdb 
db = MySQLdb.connect("localhost/ipaddress", "username","password","mydb" ) 
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE") sql = """CREATE TABLE STUDENT (         NAME  CHAR(20) NOT NULL,         AGE INT,           SEX CHAR(1),         MARKS FLOAT )"""
cursor.execute(sql)
db.close()
 
After executing this code a table will be created in your "mydb " database. login to your mysql and verify by executing
 
"SHOW TABLES;" command
 
After creating table Insert below line to add entries to you table
sql = """INSERT INTO STUDENTS(NAME,         AGE, SEX, MARKS)         VALUES ('Nages', '20', 'M', 79)""" db.commit()  to save changes to table sql = """SELECT * FROM STUDENTS \       WHERE MARKS > '%d'" % (50)"""
 
In simple words execute any MYSQL queries you want  :)
Use below commands to get results after executing sql queries like above