Python Program - Database - Insert Operation

• Database - Insert Operation

  #!/usr/bin/python
  
  import MySQLdb
  
  # Open database connection
  db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
  
  # prepare a cursor object using cursor() method
  cursor = db.cursor()
  
  # Prepare SQL query to INSERT a record into the database.
  sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
  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()

Python Program - Database - DELETE Operation:

• Delete Example

  #!/usr/bin/python
  
  import MySQLdb
  
  # Open database connection
  
  db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
  
  # prepare a cursor object using cursor() method
  cursor = db.cursor()
  
  # Prepare SQL query to DELETE required records
  sql = "DELETE FROM EMPLOYEE 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()

Python Program - Creating Database Table

• Creating Table

  #!/usr/bin/python
  
  import MySQLdb
  
  # Open database connection
  db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
  
  # 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 EMPLOYEE")
  
  # Create table as per requirement
  sql = """CREATE TABLE EMPLOYEE (
       FIRST_NAME CHAR(20) NOT NULL,
       LAST_NAME CHAR(20),
       AGE INT,
       SEX CHAR(1),
       INCOME FLOAT )"""
  
  cursor.execute(sql)
  
  # disconnect from server
  db.close()

Python Program - Database Connection

Before connecting to a MySQL database make sure followings:

1. You have created a database TESTDB.
2. You have created a table EMPLOYEE in TESTDB.
3. This table is having fields FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
4. User ID "testuser" and password "test123" are set to access TESTDB
5. Python module MySQLdb is installed properly on your machine.
6. You have gone through MySQL tutorial to understand MySQL Basics.

• Example:

  Following is the example of connecting with MySQL database "TESTDB"
  
  #!/usr/bin/python
  
  import MySQLdb
  
  # Open database connection
  db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
  
  # 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()

Python Program - Operators Precedence Example

• #!/usr/bin/python
  
  a = 20
  b = 10
  c = 15
  d = 5
  e = 0
  
  e = (a + b) * c / d #( 30 * 15 ) / 5
  print "Value of (a + b) * c / d is ", e
  
  e = ((a + b) * c) / d # (30 * 15 ) / 5
  print "Value of ((a + b) * c) / d is ", e
  
  e = (a + b) * (c / d); # (30) * (15/5)
  print "Value of (a + b) * (c / d) is ", e
  
  e = a + (b * c) / d; # 20 + (150/5)
  print "Value of a + (b * c) / d is ", e

• Output

  Value of (a + b) * c / d is 90
  Value of ((a + b) * c) / d is 90
  Value of (a + b) * (c / d) is 90
  Value of a + (b * c) / d is 50

Python Program - Membership Operators

• #!/usr/bin/python
  
  a = 10
  b = 20
  list = [1, 2, 3, 4, 5 ];
  
  if ( a in list ):
     print "Line 1 - a is available in the given list"
  else:
     print "Line 1 - a is not available in the given list"
  
  if ( b not in list ):
     print "Line 2 - b is not available in the given list"
  else:
     print "Line 2 - b is available in the given list"
  
  a = 2
  if ( a in list ):
     print "Line 3 - a is available in the given list"
  else:
     print "Line 3 - a is not available in the given list"

• Output

  Line 1 - a is not available in the given list
  Line 2 - b is not available in the given list
  Line 3 - a is available in the given list

Python Program - Logical Operators

• #!/usr/bin/python
  
  a = 10
  b = 20
  c = 0
  
  if ( a and b ):
     print "Line 1 - a and b are true"
  else:
     print "Line 1 - Either a is not true or b is not true"
  
  if ( a or b ):
     print "Line 2 - Either a is true or b is true or both are true"
  else:
     print "Line 2 - Neither a is true nor b is true"
  
  a = 0
  if ( a and b ):
     print "Line 3 - a and b are true"
  else:
     print "Line 3 - Either a is not true or b is not true"
  
  if ( a or b ):
     print "Line 4 - Either a is true or b is true or both are true"
  else:
     print "Line 4 - Neither a is true nor b is true"
  
  if not( a and b ):
     print "Line 5 - a and b are true"
  else:
     print "Line 5 - Either a is not true or b is not true"

• Output

  Line 1 - a and b are true
  Line 2 - Either a is true or b is true or both are true
  Line 3 - Either a is not true or b is not true
  Line 4 - Either a is true or b is true or both are true
  Line 5 - a and b are true

Python Program - Identity Operators

• #!/usr/bin/python
  
  a = 20
  b = 20
  
  if ( a is b ):
     print "Line 1 - a and b have same identity"
  else:
     print "Line 1 - a and b do not have same identity"
  
  if ( id(a) == id(b) ):
     print "Line 2 - a and b have same identity"
  else:
     print "Line 2 - a and b do not have same identity"
  
  b = 30
  if ( a is b ):
     print "Line 3 - a and b have same identity"
  else:
     print "Line 3 - a and b do not have same identity"
  
  if ( a is not b ):
     print "Line 4 - a and b do not have same identity"
  else:
     print "Line 4 - a and b have same identity"

• Output

  Line 1 - a and b have same identity
  Line 2 - a and b have same identity
  Line 3 - a and b do not have same identity
  Line 4 - a and b do not have same identity

Python Program - Basic - Comparison Operators

• #!/usr/bin/python
  
  a = 21
  b = 10
  c = 0
  
  if ( a == b ):
     print "Line 1 - a is equal to b"
  else:
     print "Line 1 - a is not equal to b"
  
  if ( a != b ):
     print "Line 2 - a is not equal to b"
  else:
     print "Line 2 - a is equal to b"
  
  if ( a <> b ):
     print "Line 3 - a is not equal to b"
  else:
     print "Line 3 - a is equal to b"
  
  if ( a < b ):
     print "Line 4 - a is less than b"
  else:
     print "Line 4 - a is not less than b"
  
  if ( a > b ):
     print "Line 5 - a is greater than b"
  else:
     print "Line 5 - a is not greater than b"
  
  a = 5;
  b = 20;
  if ( a <= b ):
     print "Line 6 - a is either less than or equal to b"
  else:
     print "Line 6 - a is neither less than nor equal to b"
  
  if ( b >= a ):
     print "Line 7 - b is either greater than or equal to b"
  else:
     print "Line 7 - b is neither greater than nor equal to b"
  

• Output

  Line 1 - a is not equal to b
  Line 2 - a is not equal to b
  Line 3 - a is not equal to b
  Line 4 - a is not less than b
  Line 5 - a is greater than b
  Line 6 - a is either less than or equal to b
  Line 7 - b is either greater than or equal to b

Python Program - Bitwise Operators Example

       #!/usr/bin/python

a = 60 # 60 = 0011 1100
b = 13 # 13 = 0000 1101
c = 0
c = a & b; # 12 = 0000 1100
print "Line 1 - Value of c is ", c

c = a | b; # 61 = 0011 1101
print "Line 2 - Value of c is ", c

c = a ^ b; # 49 = 0011 0001
print "Line 3 - Value of c is ", c

c = ~a; # -61 = 1100 0011
print "Line 4 - Value of c is ", c

c = a << 2; # 240 = 1111 0000
print "Line 5 - Value of c is ", c

c = a >> 2; # 15 = 0000 1111
print "Line 6 - Value of c is ", c

Output

Line 1 - Value of c is 12
Line 2 - Value of c is 61
Line 3 - Value of c is 49
Line 4 - Value of c is -61
Line 5 - Value of c is 240
Line 6 - Value of c is 15

Python Program - Basic - Assignment Operators Example

#!/usr/bin/python

a = 21
b = 10
c = 0

c = a + b
print "Line 1 - Value of c is ", c

c += a
print "Line 2 - Value of c is ", c

c *= a
print "Line 3 - Value of c is ", c

c /= a
print "Line 4 - Value of c is ", c

c = 200
c %= a
print "Line 5 - Value of c is ", c

c **= a
print "Line 6 - Value of c is ", c

c //= a
print "Line 7 - Value of c is ", c

Output

Line 1 - Value of c is 31
Line 2 - Value of c is 52
Line 3 - Value of c is 1092
Line 4 - Value of c is 52
Line 5 - Value of c is 11
Line 6 - Value of c is 1331
Line 7 - Value of c is 443

Python Program - Arithmatic Operators Example

#!/usr/bin/python

a = 21
b = 10
c = 0

c = a + b
print "Line 1 - Value of c is ", c

c = a - b
print "Line 2 - Value of c is ", c

c = a * b
print "Line 3 - Value of c is ", c

c = a / b
print "Line 4 - Value of c is ", c

c = a % b
print "Line 5 - Value of c is ", c

a = 2
b = 3
c = a**b #Exponent - Performs exponential (power) calculation on operators
print "Line 6 - Value of c is ", c

a = 10
b = 5
c = a//b #Floor Division - The division of operands where the result is the quotient in which the digits after the decimal point are removed.
print "Line 7 - Value of c is ", c

Output

Line 1 - Value of c is 31
Line 2 - Value of c is 11
Line 3 - Value of c is 210
Line 4 - Value of c is 2
Line 5 - Value of c is 1
Line 6 - Value of c is 8
Line 7 - Value of c is 2