Python MySQL performs database table changes and queries

This article mainly introduces the relevant information about Python MySQL for database table changes and queries. Friends in need can refer to the following

Python connects to MySQL for database table changes and queries:

python mysql insert delete query:

#!/usr/bin/python 
 
import MySQLdb 
def doInsert(cursor,db): 
  #insert 
  # Prepare SQL query to INSERT a record into the database. 
  sql = "UPDATE EMPLOYEE SET AGE = AGE+1 WHERE SEX = '%c'" %('M') 
  try: 
    cursor.execute(sql) 
    db.commit() 
  except: 
    db.rollback() 
 
def do_query(cursor,db): 
  sql = "SELECT * FROM EMPLOYEE \ 
     WHERE INCOME > '%d'" % (1000) 
  try: 
    # Execute the SQL command 
    cursor.execute(sql) 
    # Fetch all the rows in a list of lists. 
    results = cursor.fetchall() 
    print 'resuts',cursor.rowcount 
    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" 
 
def do_delete(cursor,db): 
  sql = 'DELETE FROM EMPLOYEE WHERE AGE > {}'.format(20) 
  try: 
    cursor.execute(sql) 
    db.commit() 
  except: 
    db.rollback() 
 
def do_insert(cursor,db,firstname,lastname,age,sex,income): 
  sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \ 
    LAST_NAME, AGE, SEX, INCOME) \ 
    VALUES ('%s', '%s', '%d', '%c', '%d' )" % \ 
    (firstname,lastname,age,sex,income) 
  try: 
    cursor.execute(sql) 
    db.commit() 
  except: 
    db.rollback() 
  
# Open database connection 
# change this to your mysql account 
#connect(server,username,password,db_name) 
db = MySQLdb.connect("localhost","root","root","pydb" ) 
# prepare a cursor object using cursor() method 
cursor = db.cursor() 
do_query(cursor,db) 
doInsert(cursor,db) 
do_query(cursor,db) 
do_delete(cursor,db) 
do_query(cursor,db) 
do_insert(cursor,db,'hunter','xue',22,'M',2000) 
do_insert(cursor,db,'mary','yang',22,'f',5555) 
do_insert(cursor,db,'zhang','xue',32,'M',5000) 
do_insert(cursor,db,'hunter','xue',22,'M',333) 
do_query(cursor,db) 
# disconnect from server 
db.close()

You can then encapsulate it as needed based on this.

The above is the detailed content of Python MySQL performs database table changes and queries. For more information, please follow other related articles on the PHP Chinese website!