诛仙手游奇遇称号8:[Python]用Python开发MySQL脚本

来源:百度文库 编辑:九乡新闻网 时间:2024/03/28 22:17:48
  复杂的数据库操作   下面通过一个脚本animal.py来演示如何解决复杂的数据库操作,这里给出完整的animal.py代码。

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

  #animal.py:创建动物表并从中检索信息

  import sys

  import MySQLdb

  #连接到MySQL服务器

  try:

  conn = MySQLdb.connect (host = "localhost",

  user = "testuser",

  passwd = "testpass",

  db = "test")

  except MySQLdb.Error, e:

  print "Error %d: %s" % (e.args[0], e.args[1])

  sys.exit (1)

  #创建动物表并填充内容

  try:

  cursor = conn.cursor ()

  cursor.execute ("DROP TABLE IF EXISTS animal")

  cursor.execute ("""

  CREATE TABLE animal

  (

  name CHAR(40),

  category CHAR(40)

  )

  """)

  cursor.execute ("""

  INSERT INTO animal (name, category)

  VALUES

  ('snake', 'reptile'),

  ('frog', 'amphibian'),

  ('tuna', 'fish'),

  ('racoon', 'mammal')

  """)

  print "Number of rows inserted: %d" % cursor.rowcount

  #使用fetchone()进行循环提取

  cursor.execute ("SELECT name, category FROM animal")

  while (1):

  row = cursor.fetchone ()

  if row == None:

  break

  print "%s, %s" % (row[0], row[1])

  print "Number of rows returned: %d" % cursor.rowcount

  #使用fetchall()进行循环提取

  cursor.execute ("SELECT name, category FROM animal")

  rows = cursor.fetchall ()

  for row in rows:

  print "%s, %s" % (row[0], row[1])

  print "Number of rows returned: %d" % cursor.rowcount

  #发出修改名称的语句,共两种方式,第一种是在语句字符串

  #中使用数据值的字面值,第二种是使用位置标识符

  cursor.execute ("""

  UPDATE animal SET name = 'turtle'

  WHERE name = 'snake'

  """)

  print "Number of rows updated: %d" % cursor.rowcount

  cursor.execute ("""

  UPDATE animal SET name = %s

  WHERE name = %s

  """, ("snake", "turtle"))

  print "Number of rows updated: %d" % cursor.rowcount

  #创建一个字典游标,这样就可以使用位置而非名称来访问数据列中的值了。

  cursor.close ()

  cursor = conn.cursor (MySQLdb.cursors.DictCursor)

  cursor.execute ("SELECT name, category FROM animal")

  result_set = cursor.fetchall ()

  for row in result_set:

  print "%s, %s" % (row["name"], row["category"])

  print "Number of rows returned: %d" % cursor.rowcount

  cursor.close ()

  except MySQLdb.Error, e:

  print "Error %d: %s" % (e.args[0], e.args[1])

  sys.exit (1)

  conn.commit ()

  conn.close ()

  该脚本使用一个表来存储动物的名称和类别: