python-shell-MySQL

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

#!/usr/bin/python
#coding=utf-8
import 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-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()

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

复制代码 代码示例:
#!/usr/bin/env python

#encoding=utf-8d 

'''
@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()

“`