python db connection samples and program

python db connection samples and program

• 1.
  Connecting Python withSQL Database
• 2.
  OBJECTIVE To make student awareabout Connectivity between frontend -backend
• 3.
  PREREQUISITE ➢Detailed knowledge ofPython. ➢MySQL
• 4.
  Point to beFocussed (3 Important Steps) ➢ Download Python 3.5.3 and then install it ➢ Download MySQL API, exe file will be downloaded install it. ➢ Install MySQL-Python Connector ➢ Now connect MySQL Server using Python.
• 5.
  What is MySQLdb Whatis Connection What is a Cursor
• 6.
  • MySQLdb isan interface for connecting to a MySQL database server from Python. • It implements the Python Database API and is built on top of the MySQL C API. • Just type the following in your Python script and execute it − #!/usr/bin/python import MySQLdb What is MySQLdb
• 7.
  MySQLdb import MySQldb
• 8.
  • The nextstep to using MySQL in your Python scripts is to make a connection to the database that you wish to use. All Python DB- API modules implement a function 'module_name.connect‘ • This is the function that is used to connect to the database, in our case MySQL.. What is Connection
• 9.
  Connection MySQLdb Db=MySQLdb.connect (“localhost”, testuser”, ”test123”,”TESTDB”)
• 10.
  Connecting to aMySQL database : db = MySQLdb.connect(host=MY_HOST, user=MY_USER, passwd=MY_PASS, db=MY_DB) Your user name Your Password Name of the Database db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
• 11.
  Create Cursor cur=db.cursor()
• 12.
  •The next stepis to create a Cursor object. •It will let you execute all the queries you need •In order to put our new connnection to good use we need to create a cursor object. •The cursor object is an abstraction specified in the Python DB-API •It gives us the ability to have multiple seperate working environments through the same connection to the database. •We can create a cursor by executing the 'cursor' function of your database What is Cursor
• 13.
  Example of SimpleCode to Connect MySQL with Python <? xml version=“1.0” ?> <land> <forest> <Tree> --------------------------- --------------------------- --------------------------- </Tree> </forest> </land>
• 14.
  Creating database Table ➢Oncea Database Connection is established, we are ready to create tables using execute( ) method of the created cursor ➢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() # 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()
• 15.
  Insert Records intodatabse table ➢Its required to insert records in table for fetching records. ➢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 INSERT a record into the database. sql = """INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE, SEX, INCOME) VALUES('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()
• 16.
  Read Records (Select)into databse table ➢Read operation on any database means to fetch some useful information from the database. ➢We can use fetchone() method to fetch single record ➢fetchall() method to fetch 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.
• 17.
  ➢Example import MySQLdb db =MySQLdb.connect("localhost","testuser","test123","TESTDB" ) cursor = db.cursor() sql = "SELECT * FROM EMPLOYEE WHERE INCOME > '%d'" % (1000) try: cursor.execute(sql) # Fetch all the rows in a list of lists. results = cursor.fetchall() for row in results: fname = row[0] lname = row[1] age = row[2] sex = row[3] income = row[4] # Now print fetched result print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % (fname, lname, age, sex, income ) except: print "Error: unable to fecth data" # disconnect from server db.close()
• 18.
  Update information intodatabse table ➢UPDATE Operation on any database means to update one or more records, which are already available in the database. ➢The following procedure updates all the records having SEX as 'M'. Here, we increase AGE of all the males by one year. ➢Example import MySQLdb db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) cursor = db.cursor() sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M') try: cursor.execute(sql) db.commit() except: db.rollback()
• 19.
  Delete information intodatabse table ➢DELETE operation is required when you want to delete some records from your database. Following is the procedure to delete all the records from EMPLOYEE where AGE is more than 20 ➢Example import MySQLdb db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) cursor = db.cursor() sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20) try: cursor.execute(sql) db.commit() except: db.rollback()
• 20.
  ➢To disconnect Databaseconnection, use close() method. db.close() Disconnecting Database
• 21.
  Thank You