python使用MySQLdb访问mysql数据库的方法

yipeiwu_com6年前Python基础

本文实例讲述了python使用MySQLdb访问mysql数据库的方法。分享给大家供大家参考。具体如下:

#!/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","hunter","hunter","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()

希望本文所述对大家的Python程序设计有所帮助。

相关文章

python 解析XML python模块xml.dom解析xml实例代码

一 、python模块 xml.dom 解析XML的APIminidom.parse(filename)加载读取XML文件 doc.documentElement获取XML文档对象 no...

python中as用法实例分析

本文实例讲述了python中as用法。分享给大家供大家参考。具体分析如下: import some # some 为一个模组 如果想要改变被导入模组在当前模组中的名称,而不是sys...

Python编程实现蚁群算法详解

Python编程实现蚁群算法详解

简介 蚁群算法(ant colony optimization, ACO),又称蚂蚁算法,是一种用来在图中寻找优化路径的机率型算法。它由Marco Dorigo于1992年在他的博士论文...

Python 求数组局部最大值的实例

求数组局部最大值 给定一个无重复元素的数组A[0…N-1],求找到一个该数组的局部最大值。规定:在数组边界外的值无穷小。即:A[0]>A[-1],A[N-1] >A[N]。 显然,遍历一...

python 三元运算符使用解析

三元运算又称三目运算,是对简单的条件语句的简写 简单条件语句: if 条件成立: val = 1 else: val = 2 改成三元运算: val = 1 if 条件...