欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

python连接mysql数据库示例(做增删改操作)

程序员文章站 2022-05-30 20:35:47
一、相关代码数据库配置类 mysqldbconn.py 复制代码 代码如下:#encoding=utf-8'''created on 2012-11-12mysql co...

一、相关代码
数据库配置类 mysqldbconn.py

复制代码 代码如下:

#encoding=utf-8
'''
created on 2012-11-12

mysql conn连接类
'''

import mysqldb

class dbconn:

    conn = none

    #建立和数据库系统的连接
    def connect(self):
        self.conn = mysqldb.connect(host="localhost",port=3306,user="house", passwd="house" ,db="house",charset="utf8")

    #获取操作游标
    def cursor(self):
        try:
            return self.conn.cursor()
        except (attributeerror, mysqldb.operationalerror):
            self.connect()
            return self.conn.cursor()

    def commit(self):
        return self.conn.commit()

    #关闭连接
    def close(self):
        return self.conn.close()

mysqldemo.py类

复制代码 代码如下:

#encoding=utf-8
'''
created on 2012-11-12

@author: steven

mysql操作demo
done:创建表,删除表,数据增、删、改,批量插入
'''
import mysqldbconn

dbconn = mysqldbconn.dbconn()

def process():
    #建立连接
    dbconn.connect()
    #删除表
    droptable()
    #创建表
    createtable()
    #批量插入数据
    insertdatas()
    #单条插入
    insertdata()
    #更新数据
    updatedata()
    #删除数据
    deletedata()
    #查询数据
    querydata()
    #释放连接
    dbconn.close()

def insertdatas():
    sql = "insert into lifeba_users(name, realname, age) values(%s, %s, %s)"
    tmp = (('steven1', '测试1',26), ('steven2', '测试2',25))
    executemany(sql, tmp)

def updatedata():
    sql = "update lifeba_users set realname = '%s' where name ='steven1'"%("测试1修改")
    execute(sql)

def deletedata():
    sql = "delete from lifeba_users where id=2"
    execute(sql)

def querydata():
    sql = "select * from lifeba_users"
    rows = query(sql)
    printresult(rows)

def insertdata():
    sql = "insert into lifeba_users(name, realname, age) values('%s', '%s', %s)"%("steven3","测试3","26")
    print sql
    execute(sql)

def executemany(sql, tmp):
    '''插入多条数据'''
    conn=dbconn.cursor()
    conn.executemany(sql, tmp)

def execute(sql):
    '''执行sql'''
    conn=dbconn.cursor()
    conn.execute(sql)

def query(sql):
    '''查询sql'''
    conn=dbconn.cursor()
    conn.execute(sql)
    rows = conn.fetchmany(10)
    return rows

def createtable():
    '''创建表'''
    conn=dbconn.cursor()
    conn.execute('''
    create table `lifeba_users` (
      `id` int(11) not null auto_increment,
      `name` varchar(50) default null,
      `realname` varchar(50) default null,
      `age` int(11) default null,
      primary key  (`id`)
    ) engine=myisam default charset=utf8;
    ''')
#    dbconn.commit()

def droptable():
    '''删除表'''
    conn=dbconn.cursor()
    conn.execute('''
    drop table if exists `lifeba_users`
    ''')
#    dbconn.commit()

def printresult(rows):
    for row in rows:
        for i in range(0,len(row)):#遍历数组
            print row[i], #加, 不换行打印
        print ''

if __name__ == '__main__':

    process()