MySQLdb对mysql数据库的中文操作测试补充(包括字符集gbk,utf8,latin1)

  前两天，用MySQLdb对mysql进行过中文测试，但是当时只是测试的字符集为gbk，和utf8的表.没有完成字符集latin1的测试。不过latin1可能才是我们遇到问题最多的。
最近正好使用到了，所以进行了一些测试。这样以后可以使用类似代码直接使用了。没有

#!/usr/bin/env python
#coding:utf-8

"""
测试python对mysql数据库的支持

1.增删改查中文操作

CREATE TABLE `zhanbulatin1` (
  `id` int(11) NOT NULL,
    `content` char(255) default NULL,

      PRIMARY KEY  (`id`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `zhanbuutf8` (
  `id` int(11) NOT NULL,
    `content` char(255) default NULL,

      PRIMARY KEY  (`id`)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8
"""

import MySQLdb
class processdb:
    def connection(self,user="root",password="123456",host="127.0.0.1",db="test",character="utf-8"):
        self.user=user
        self.password=password
        self.host=host
        self.db=db
        self.character=db
        conn=MySQLdb.connect(host=self.host,user=self.user,passwd=self.password,db=self.db,charset=self.character)
        c=self.conn.cursor()
        return c,conn

    def connectionlatin1(self):
        self.conn=MySQLdb.connect(host=self.host,user=self.user,passwd=self.password,db=self.db)
        self.c=self.conn.cursor()
        

    def connection(self):
        self.conn=MySQLdb.connect(host=self.host,user=self.user,passwd=self.password,db=self.db,charset=self.character)
        self.c=self.conn.cursor()
        
    def add(self,sql):
        print "#"*15,"add","#"*15
        self.c.execute(sql)
    
    
    def delrecord(self,sql):
        print "#"*15,"del","#"*15
        self.c.execute(sql)


    def update(self,sql):
        print "#"*15,"update","#"*15
        self.c.execute(sql)
    def querylatin1(self,sql):
        print "*"*40
        self.c.execute(sql)
        rs=self.c.fetchall()
        for i in rs:
            self.printList(i)
        return rs
    
    def printList(self,list):
        for i in list:
            try:
                if " " in i:
                    pass
                else:
                    print unicode(i,"gbk")
            except:
                print i

    def query(self,sql):
        print "*"*40
        self.c.execute(sql)
        rs=self.c.fetchall()

        for i in rs:
            print i[1]
        
        return rs

db1=processdb()
db1.user="root"
db1.password="1q2w3e4r"
db1.host="127.0.0.1"
db1.db="mysite2"
db1.character="utf8"
db1.connection()

table1="zhanbuutf8"

def createsql(tablename):
    content="中文插入"
    updatecontent="更新中文"
    id=1
    addsql="insert into %s(id,content) values(%d,'%s')"%(tablename,id,content)
    updatesql="update %s set content='%s' where id=%d"%(tablename,updatecontent,id)
    delsql="delete from %s where id=%d"%(tablename,id)
    querysql="select id,content from %s order by id desc"%(tablename)
    return addsql,updatesql,delsql,querysql

def createsqllatin1(tablename):
    content=unicode("中文插入","latin1")
    updatecontent=unicode("更新中文","latin1")

    id=1
    addsql="insert into %s(id,content) values(%d,'%s')"%(tablename,id,content)
    updatesql="update %s set content='%s' where id=%d"%(tablename,updatecontent,id)
    delsql="delete from %s where id=%d"%(tablename,id)
    querysql="select id,content from %s order by id desc"%(tablename)
    return addsql,updatesql,delsql,querysql
"""
1.测试表字符集为utf8,或者gbk时的中文插入删除，更新情况
"""
addsql,updatesql,delsql,querysql=createsql(table1)
db1.delrecord(delsql)
db1.add(addsql)
db1.query(querysql)
db1.update(updatesql)
db1.query(querysql)
db1.delrecord(delsql)
db1.query(querysql)

db1.conn.close()
"""
1.测试表字符集为latin1时的中文插入删除情况
"""
db1.connectionlatin1()
table2="zhanbulatin1"
addsql,updatesql,delsql,querysql=createsqllatin1(table2)

db1.delrecord(delsql)
db1.add(addsql)
db1.querylatin1(querysql)
db1.update(updatesql)
db1.querylatin1(querysql)
db1.delrecord(delsql)