python-shell-MySQL

这是一个用python语句实现的shell脚本编程调用MySQL数据库的简单实例，注意文章当中的 密码即可 需要更改。

#!/usr/bin/python#coding=utf-8import MySQLdb#和数据库建立连接,connect()方法用于创建数据库的连接，里面可以指定参数：用户名，密码，主机等信息。conn= MySQLdb.connect(        host='localhost',        port = 3306,        user='root',        passwd='密码即可',        db ='test'        )cur = conn.cursor()#建立游标#创建数据表（表头：student3,字段id ,name ,class,age）cur.execute("create table student3(id int ,name varchar(20),class varchar(30),age varchar(10))")#一次插入多条记录sqli="insert into student3 values(%s,%s,%s,%s)"cur.executemany(sqli,[    ('3','Tom','1 year 1 class','6'),    ('3','Jack','2 year 1 class','7'),    ('3','Yaheng','2 year 2 class','7'),    ])#获得表中有多少条数据aa=cur.execute("select * from student3")print aa#打印表中的多少数据，fetchmany()方法可以获得多条数据，info = cur.fetchmany(aa)for ii in info:    print ii#关闭游标cur.close()#conn.commit()方法在提交事物，在向数据库插入一条数据时必须要有这个方法，否则数据不会被真正的插入conn.commit()#关闭数据库连接conn.close()

显示结果：

3(3L, 'Tom', '1 year 1 class', '6')(3L, 'Jack', '2 year 1 class', '7')(3L, 'Yaheng', '2 year 2 class', '7')

以下是比较经典的简单的常用函数：

#查询数量def Count(cur):   count=cur.execute('select * from Student')   print 'there has %s rows record' % count#插入def Insert(cur):   sql = "insert into Student(ID,Name,Age,Sex)values(%s,%s,%s,%s)"   param = (2,'xiaoming',24,'boy')   cur.execute(sql,param)#查询 def  Select(cur):     n = cur.execute("select * from Student")       print "------"   for row in cur.fetchall():          for r in row:             print r      print "------"   #更新def Update(cur):   sql = "update Student set Name = %s where ID = 2"     param = ("xiaoxue")       count = cur.execute(sql,param)#删除def Delete(cur):       sql = "delete from Student where Name = %s"     param =("xiaoxue")       n = cur.execute(sql,param)   try:   conn=MySQLdb.connect(host='localhost',user='root',passwd='123456',db='python',port=3306)   cur=conn.cursor()   #数量   Count(cur)   #查询   Select(cur)   #插入   Insert(cur)   print "插入之后"   #查询   Select(cur)   #更新   Update(cur)   print "更新之后"   #查询   Select(cur)   #删除   Delete(cur)   print "删除之后"   #查询   Select(cur)#!/usr/bin/env python#encoding=utf-8'''Created on 2012-11-12Mysql Conn连接类'''import MySQLdbclass 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()

引用网上的demo参考网址http://www.jbxue.com/article/2二、MysqlDemo.py类

复制代码 代码示例:#!/usr/bin/env python#encoding=utf-8d '''@author: StevenMysql操作DemoDone:创建表，删除表，数据增、删、改，批量插入'''import MysqlDBConndbconn = 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 rowsdef 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()

“`