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.
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).
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;
Here is the formula to give permission to user, substitute parameter and execute it
"username" is the of the remote machine from which you are connecting from.
" is IP address of the remote machine.
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
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 )"""
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