PYTHON MY SQL DATA BASE ACCESS

 

PYTHON MY SQL DATA BASE ACCESS

 

                      Gad fly

                      msql

                      my sql

                      postgre sql

                      microsoft sql server 2000

                      informix

                      interbase

                      oracle

                      sybase

WHAT IS MY SQL?

        My sql is an interface for connecting to a my sql data base server from python. it implements the python database API v2.0 and is built on top of the mysql C API

 

HOW DO I INSTALL MY SQLDB?

#!/usr/bin/python

import MySQLdb

traceback (most recent call last):

 file"test.py",line 3,in <module>
   import Mysqldb

importerror:no module named My SQLdb

 

for Ubuntu,use the folllowing command -

$sudo apt get install python pip pythin dev libmysqlclient dev

for fedora , use the following command

 $sudo dnf install python python devel mysql redhat rpm config gcc 

for python command prompt use the following command

pip install MySQL-python

 

DATABASE CONNECTION

#!/usr/bin/python

 

import Mysqldb

 

#open database connection

db=My SQLdb.connect("127.0.0.1""sample""linkme30""SAMPLEDATA")

 

#prepare a cursor object using cursor() method

cursor=db.cursor()

 

#execute SQL query using execute method.

cursor.execute("SELECT VERSION()")

 

#fetch a single row using fetchone() method.

data = cursor.fetchone()

print("database version : %s " %data)

#disconnect from server

db.close()

Output

database version : 5.0.45

 

CREATING DATABASE TABLE

import MySQLdb

#open database connection

db=MySQLdb.db.connect("127.0.0.1","SAMPLE","linkme30","sample data")

#prepare a cursor object using cursor() method

cursor = db.cursor()

#drop table if it already exist using execute() method.

cursor.execute("drop table if exists jkemployee")

#create table as per requirement

sql="""create table jkemployee(

      empno int NOT NULL,

      empname  char(20),

      job char(20),

      age int,

      sex char(1),

      salary float )"""

 

cursor.execute(sql)

#disconnect from server

db.close()

INSERT operation

import MySQLdb

db = MSQLdb.connect("127.0.0.1","SAMPLE',"linkMe30","

SAMPLEDATA")

cursor=db.cursor()

sql="""INSERT INTO                  JKEMPLOYEE(empno,empname,job,age,sex,salary)          VALUES(100, 'mohan', 'manager', 25,'M' 18000)"""

 

try:

   #Execute the SQL command

   cursor.execuet(sql)

    #commit your changes in the database

    db.commit()

 

except:

     #rollback in case there is any error

     db.rollback()

# disconnect from server

db.close()

 

#!/usr/bin/python

import MySQLdb

#open database connection

db= MySQLdb.connec("127.0.0.1","SAMPLE","linkme30","SAMPLEDATA”)

#prepare a cursor object using cursor() method

cursor = db.cursor()

#prepare SQL query to INSERT a record into the database.

sql = "INSERT INTO jkemployee(empno,empname,job,age,sex,salary)

          VALUES('%d','%s','%s','%d','%c','%f')"%\

         (102, 'mohan','clerk',26,'M',12000)

try:

    cursor.execute(sql)  #Execute the SQL command

fb.commit()                  #commit your changes in the database

 

except:

   db.rollback()     #rollback in case there is any error

#disconnect from server

db.close

Example

following code segment is another form of ececution where you can pass parameters directly?

...........................................

user id = "SAMPLE"

password = "LinkMe30"

 

con.execute('insert into login values("%s","%s")'%\

                     (user_id, password))

 

READ Operation

import MySQLdb

#open database connection

db=MySQldb.connect("127.0.01","SAMPLE","LinkMe30","SAMPLEDATA")

#prepare a cursor object using cursor() method

cursor=db.cursor()

sql="SELECT*FROM JKEMPLOyee
          WHERE SALARY>'%d'"%10000)

try:

   #execute the SQL command

    cursor.execute(sql)

     #fetch all the rows in a list of lists.

   results=cursor.fetchall()

   for row in results:

          empno=row[0]

           empname= row[1]

          job= row[2]

         age= row[3]

         sex= row[4]

         salary= row[5]

       #now print fetched result

                           print "employee no=%d,employee name=%s, designation=%s,age=%d,sex=%c,salary=%f"% \

                       (empno,empname,job,sex,salary)

except:

       print ("error: unable to fetch data")

#disconnect from server

fb.close()

Output

employee no=100, employee  name=ramesh, designation=manager, age=26, sex=M,income=18000

update operation

import MySQLdb

#open database connection

db= MySQLdb.connect("127.0.0.1","SAMPLE","linkMe30","SAMPLEDATA")

#prepare cursor object using cursor() method

cursor = db.cursor()

#prepare a cursor object using cursor() method

 

cursor=db.cursor()

#prepare SQL query to UPDATE required records

sql ="UPDATE JKEMPLOYEE SET AGE=AGE+1

                                  WHERE SEX='%C'"%('M')

try:

#execute the SQL command

   cursor.execute(sql)

#commit your changes in the database

   db.commit()

except:

#rollback in case there is any error

db.rollback()

#disconnect from server

db.close()

 

Delete Operation

example

#!/usr/bin/python

 

import MySQLdb

 

#open database connection

db=MySQLdb.connect("127.0.0.1","SAMPLE" ,"linkMe30","SAMPLEDATA")

#prepare a cursor object using cursor() method

cursor=db.cursor()

#prepare a cursor object using cursor() method

cursor=db.cursor()

#prepare SQL query to DELETE required records

sql="DELETE FROMJKEMPLOYEE WHERE AGE>'%d'"%(20)

try:

#execute the SQL command

cursor.execute(sql)

#commit your changes in the database

db.commit()

except:

#rollback in case there is any error

db.rollback()

#disconnect from server

db.close()