Installing Mysql Module in Python 2 on Redhat 6

Python  is one the most powerful  language which is highly  tested with mysql database server 

Here i am going to discuss how to install and connect mysql database server using python

Step 1 :  Install  mysql server related all the software in your redhat 6

[root@exam ~]# rpm -qa | grep -i mysq
mysql-5.1.66-2.el6_3.x86_64
mysql-devel-5.1.66-2.el6_3.x86_64
mysql-bench-5.1.66-2.el6_3.x86_64
mysql-server-5.1.66-2.el6_3.x86_64
mysql-libs-5.1.66-2.el6_3.x86_64
perl-DBD-MySQL-4.013-3.el6.x86_64
mysql-connector-odbc-5.1.5r1144-7.el6.x86_64
mysql-test-5.1.66-2.el6_3.x86_64

If  these software  are not installed then you can install them using yum
[root@exam ~]#  yum  install  mysql*

 
OR
 
[root@exam ~]# yum  install  mysql-server  mysql  mysql-connector-odbc  mysql-libs
 
Step 2 :   Installing  python and mysql module
[root@exam ~]#  yum install MySQL-python
 
Note :  if you don’t  have  rpm  package  then go to given link
############################
https://sourceforge.net/projects/mysql-python/
################################
 
step 3 :   Start  the service  of mysql
 
[root@exam ~]#   service  mysqld  restart 
 
step 4 :  now check with python
 
[root@exam ~]# python
Python 2.6.6 (r266:84292, Oct 12 2012, 14:23:48)
[GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux2
Type “help”, “copyright”, “credits” or “license” for more information.
>>> import  MySQLdb
>>> dir(MySQLdb)
[‘BINARY’, ‘Binary’, ‘Connect’, ‘Connection’, ‘DATE’, ‘DATETIME’, ‘DBAPISet’, ‘DataError’, ‘DatabaseError’, ‘Date’, ‘DateFromTicks’, ‘Error’, ‘FIELD_TYPE’, ‘IntegrityError’, ‘InterfaceError’, ‘InternalError’, ‘MySQLError’, ‘NULL’, ‘NUMBER’, ‘NotSupportedError’, ‘OperationalError’, ‘ProgrammingError’, ‘ROWID’, ‘STRING’, ‘TIME’, ‘TIMESTAMP’, ‘Time’, ‘TimeFromTicks’, ‘Timestamp’, ‘TimestampFromTicks’, ‘Warning’, ‘__all__’, ‘__author__’, ‘__builtins__’, ‘__doc__’, ‘__file__’, ‘__name__’, ‘__package__’, ‘__path__’, ‘__revision__’, ‘__version__’, ‘_mysql’, ‘apilevel’, ‘connect’, ‘connection’, ‘constants’, ‘debug’, ‘escape’, ‘escape_dict’, ‘escape_sequence’, ‘escape_string’, ‘get_client_info’, ‘paramstyle’, ‘release’, ‘result’, ‘server_end’, ‘server_init’, ‘string_literal’, ‘test_DBAPISet_set_equality’, ‘test_DBAPISet_set_equality_membership’, ‘test_DBAPISet_set_inequality’, ‘test_DBAPISet_set_inequality_membership’, ‘thread_safe’, ‘threadsafety’, ‘times’, ‘version_info’]

Note: 

What is MySQLdb?

MySQLdb is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2.0 and is built on top of the MySQL C API.

Some example  for  mysql connection  :-

[root@exam ~]# python
Python 2.6.6 (r266:84292, Oct 12 2012, 14:23:48) 
[GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux2
Type “help”, “copyright”, “credits” or “license” for more information.
>>> 
>>> import  MySQLdb
>>> x=MySQLdb.connect(‘localhost’)
>>> dir(x)

[‘DataError’, ‘DatabaseError’, ‘Error’, ‘IntegrityError’, ‘InterfaceError’, ‘InternalError’, ‘NotSupportedError’, ‘OperationalError’, ‘ProgrammingError’, ‘Warning’, ‘__class__’, ‘__delattr__’, ‘__dict__’, ‘__doc__’, ‘__enter__’, ‘__exit__’, ‘__format__’, ‘__getattribute__’, ‘__hash__’, ‘__init__’, ‘__module__’, ‘__new__’, ‘__reduce__’, ‘__reduce_ex__’, ‘__repr__’, ‘__setattr__’, ‘__sizeof__’, ‘__str__’, ‘__subclasshook__’, ‘__weakref__’, ‘_server_version’, ‘_transactional’, ‘affected_rows’, ‘autocommit’, ‘begin’, ‘change_user’, ‘character_set_name’, ‘client_flag’, ‘close’, ‘commit’, ‘converter’, ‘cursor’, ‘cursorclass’, ‘default_cursor’, ‘dump_debug_info’, ‘encoders’, ‘errno’, ‘error’, ‘errorhandler’, ‘escape’, ‘escape_string’, ‘field_count’, ‘get_character_set_info’, ‘get_host_info’, ‘get_proto_info’, ‘get_server_info’, ‘info’, ‘insert_id’, ‘kill’, ‘literal’, ‘messages’, ‘next_result’, ‘open’, ‘ping’, ‘port’, ‘query’, ‘rollback’, ‘select_db’, ‘server_capabilities’, ‘set_character_set’, ‘set_server_option’, ‘set_sql_mode’, ‘show_warnings’, ‘shutdown’, ‘sqlstate’, ‘stat’, ‘store_result’, ‘string_decoder’, ‘string_literal’, ‘thread_id’, ‘unicode_literal’, ‘use_result’, ‘warning_count’]

>>> print  x.get_server_info()
5.1.66
>>> print  x.stat()
Uptime: 1412  Threads: 1  Questions: 10  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg:
Note :  if you mysql  have  user name and password  are  set
[root@exam ~]# python
Python 2.6.6 (r266:84292, Oct 12 2012, 14:23:48)
[GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux2
Type “help”, “copyright”, “credits” or “license” for more information.
>>> import MySQLdb
>>> x=MySQLdb.connect(‘localhost’,’root’,’redhat’)
>>> x
<_mysql.connection open to ‘localhost’ at 7f9580>
>>>
Important :     Some  basic operation  of  database using python[root@exam ~]# python

Python 2.6.6 (r266:84292, Oct 12 2012, 14:23:48)
[GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux2
Type “help”, “copyright”, “credits” or “license” for more information.
>>> import  MySQLdb
>>> x=MySQLdb.connect(‘localhost’,’root’,’redhat’)

>>> y=x.cursor()
>>>
>>> y.execute(‘use mysql;’)
0L
>>> y.execute(‘show tables;’)

Use Database :
>>> y.execute(‘use lw;’)
Creating table in database :
>>> sql = ”’CREATE TABLE EMPLOYEE (
… FIRST_NAME CHAR(20) NOT NULL,
… LAST_NAME CHAR(20),
… AGE INT,
… SEX CHAR(1),
… INCOME FLOAT )”’
>>> y.execute(sql)
0L
Commit your changes in database:
>> x.commit()
Read Operation :

READ Operation on any database means to fetch some useful information from the database.Once our database connection is established, you are ready to make a query into this database. You can use either fetchone() method to fetch single record or fetchall() method to fetech multiple values from a database table.

fetchone(): It fetches 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(): It fetches 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.

rowcount: This is a read-only attribute and returns the number of rows that were affected by an execute() method.

[root@desktop67 ~]# python
Python 2.6.6 (r266:84292, Oct 12 2012, 14:23:48)
[GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux2
Type “help”, “copyright”, “credits” or “license” for more information.
>>>
>>>
>>> import MySQLdb
>>> x=MySQLdb.connect(‘localhost’)
>>> y=x.cursor()
>>> y.execute(‘show databases;’)
4L
>>> y.fetchall()
((‘information_schema’,), (‘lw’,), (‘mysql’,), (‘test’,))
>>>

One  more  example : 

[root@desktop67 ~]# python
Python 2.6.6 (r266:84292, Oct 12 2012, 14:23:48)
[GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux2
Type “help”, “copyright”, “credits” or “license” for more information.
>>>
>>>
>>> import MySQLdb
>>> x=MySQLdb.connect(‘localhost’)
>>> y=x.cursor()
>>> y.execute(‘show databases;’)
4L
>>> y.fetchall()
((‘information_schema’,), (‘lw’,), (‘mysql’,), (‘test’,))
>>>
>>>y.execute(‘select * from user;’)
>>> y.fetchall()
((‘localhost’, ‘root’, ”, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ”, ”, ”, ”, 0L, 0L, 0L, 0L), (‘desktop67.example.com’, ‘root’, ”, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ”, ”, ”, ”, 0L, 0L, 0L, 0L), (‘127.0.0.1’, ‘root’, ”, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ”, ”, ”, ”, 0L, 0L, 0L, 0L), (‘localhost’, ”, ”, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ”, ”, ”, ”, 0L, 0L, 0L, 0L), (‘desktop67.example.com’, ”, ”, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ”, ”, ”, ”, 0L, 0L, 0L, 0L))