一、python要对mysql数据库操作首先要安装Mysql模块支持。MySQLdb

windows安装mysqldb

百度教程说安装

pip install mysqldb

这在我的电脑上安装失败:

  Could not find a version that satisfies the requirement mysqldb (from versions
: )
No matching distribution found for mysqldb

所以应该:

pip install MySQL-python

linux机器 
yum install python-pip 安装pip
yum install MySQL-python mysqldb
yum install mysql-devel

报错:

error: Microsoft Visual C++ 9.0 is required (Unable to find vcvarsall.bat).
Get it from http://aka.ms/vcpython27

缺少vc的一个库,我们根据提示到这里下载一下,然后安装,再次执行命令 :pip install mysql-python

还是报错:

No such file or directory
    error: command '"C:\Users\年浩\AppData\Local\Programs\Common\Microsoft\Visua
l C++ for Python\9.0\VC\Bin\amd64\cl.exe"' failed with exit status 2

这是因为缺少驱动。

MySQL-python-1.2.3.win-amd64-py2.7.exe

给两个下载地址:http://download.csdn.net/detail/weibin0320/6663763

或者:http://www.jb51.net/softs/73369.html#download

安装后再次执行install 命令。ok,没有报错。我们测试一下。

import MySQLdb

没有报错,安装成功。。

二、建立一个mysql连接&操作模块db.py

# -*- coding: utf-8 -*-
import MySQLdb


class db(): # DB连接类
    def __init__(self, host, dbname, port, user, pswd):
        self.host = host
        self.dbname = dbname
        self.port = port
        self.user = user
        self.pswd = pswd

    def select(self, txt):  # 执行查询方法
        result = []
        try:
            conn = MySQLdb.connect(host=self.host, user=self.user, passwd=self.pswd, db=self.dbname, port=self.port,
                                   charset='utf8')
            cscr = conn.cursor()
            cscr.execute(txt)
            result = cscr.fetchall()  #获取数据库的所有显示信息
            cscr.close()
            conn.close()
        except MySQLdb.Error, e:
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            exit(1)
        return result

    def update(self, txt):  # 执行更新方法
        try:
            conn = MySQLdb.connect(host=self.host, user=self.user, passwd=self.pswd, db=self.dbname, port=self.port,
                                   charset='utf8')
            crsr = conn.cursor()
            print txt
            crsr.execute(txt)
            crsr.close()
            conn.commit()
            conn.close()
        except MySQLdb.Error, e:
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            exit(1)

    def delete(self, txt):  # 执行删除方法
        try:
            conn = MySQLdb.connect(host=self.host, user=self.user, passwd=self.pswd, db=self.dbname, port=self.port,
                                   charset='utf8')
            crsr = conn.cursor()
            print txt
            crsr.execute(txt)
            crsr.close()
            conn.commit()
            conn.close()
        except MySQLdb.Error, e:
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            exit(1)

    def insert(self, txt):  # 执行添加方法
        try:
            conn = MySQLdb.connect(host=self.host, user=self.user, passwd=self.pswd, db=self.dbname, port=self.port,
                                   charset='utf8')
            crsr = conn.cursor()
            print txt
            crsr.execute(txt)
            crsr.close()
            conn.commit()
            conn.close()
        except MySQLdb.Error, e:
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            exit(1)

    def create(self, txt):  # 执行创建方法
        try:
            conn = MySQLdb.connect(host=self.host, user=self.user, passwd=self.pswd, db=self.dbname, port=self.port,
                                   charset='utf8')
            crsr = conn.cursor()
            print txt
            crsr.execute(txt)
            crsr.close()
            conn.commit()
            conn.close()
        except MySQLdb.Error, e:
            print "Mysql Error %d: %s" % (e.args[0], e.args[1])
            exit(1)

三、测试

先在数据库中创建些数据

表card_info:

测试代码:

# -*- coding: utf-8 -*-
import db
uc = db.db('192.168.40.4', 'uc', 3306, 'admin', 'Playmore123!')
oa = db.db('192.168.40.4', 'oa', 3306, 'admin', 'Playmore123!')
print("infomation:\n")
for i in range(1000001,1000003,1):
    info = oa.select('select id,name,age,sex,job from card_info where id=%s ;'%i)
    id = i
    name = info[0][1].encode('utf-8')
    age = str(info[0][2])
    sex = info[0][3].encode('utf-8')
    job = info[0][4].encode('utf-8')
    print "ID:{}\n" \
          "NAME:{}\n" \
          "AGE:{}\n" \
          "SEX:{}\n" \
          "JOB:{}\n".format(id,name,age,sex,job)
j=1000009
j_update=oa.update("update card_info set job='搬砖99' where id=%s ;"%j)  #更新
j_info=oa.select('select id,name,age,sex,job from card_info where id=%s ;'%j) #查询
j_delete=oa.delete("delete from card_info where id = %s;"%j) #删除
j_insert=oa.insert("insert into card_info values(10,'test',99,'女','it99');") #插入
j_create=oa.create("create table person(id int not null auto_increment,name varchar(8),birthday datetime,constraint pk__person primary key(id));") #创建表

执行现象及结果:


另一个更好的代码实例:
# -*- coding: utf-8 -*-
import db


class zone():
    def __init__(self,zone):
        if zone == 'android':
            self.zone_id= '26'
        elif zone == 'test':
            self.zone_id= '25'
        else:
            exit(1)

    def getAllserver(self):
        ids = [str(i[0]) for i in db.db('192.168.40.4', 'uc', 3306, 'admin', 'Playmore123!').select('select id from t_s_server_list where zone_id = %s' % self.zone_id)]
        return ids

    def getNotHefu(self):
        ids = [str(i[0]) for i in db.db('192.168.40.4', 'uc', 3306, 'admin', 'Playmore123!').select('select id from t_s_server_list where zone_id = %s and mark & 16 != 16' % self.zone_id)]
        return ids


class servers():
    def __init__(self, server_id):
        oa = db.db('192.168.40.4', 'oa', 3306, 'admin', 'Playmore123!')
        uc = db.db('192.168.40.4', 'uc', 3306, 'admin', 'Playmore123!')
        uc_return = uc.select('select '
                              'l.id,'  # 0
                              'l.name,'  # 1
                              'l.address,'  # 2
                              'c.web_host,'  # 3
                              'c.db_host,'  # 4
                              'c.data_host,'  # 5
                              'l.mark, '  # 6
                              'l.cross_server '  # 7
                              'from t_s_server_list l '  # 8
                              'left join t_s_server_config c on'
                              ' l.id=c.id where l.id=%s ;' % server_id)
        print(uc_return)
        oa_return = oa.select('select server_path,merge_server_ids,xmx from server_publish_config where server_id = %s ;' % server_id)
        self.server_id = server_id
        self.ServerName = uc_return[0][1].encode('utf-8')
        self.innerIP, self.innerPort = uc_return[0][3].decode('utf-8').split('//')[1].split(':', 1)
        self.outterIP, self.outterPort = uc_return[0][2].decode('utf-8').split(':')
        self.DBIP, self.DBname = uc_return[0][4].decode('utf-8').split('//')[1].split(':3306/')
        self.staticIP, self.staticDBName = uc_return[0][5].decode('utf-8').split('//')[1].split(':3306/')
        self.mark = uc_return[0][6]
        self.CrossServerAddress = uc_return[0][7]
        if int(server_id) > 1000:
            self.CrossServerPort = str(10000 + int(server_id))
        else:
            self.CrossServerPort = str(20000 + int(server_id))
        self.serverPath = oa_return[0][0].decode('utf-8')
        if oa_return[0][1] != None:
            self.mergeId = oa_return[0][1].decode('utf-8')
        else:
            self.mergeId = ''
        self.xmx = oa_return[0][2]


    def getInnerIP(self):
        return self.innerIP


    def getInnerPort(self):
        return self.innerPort


    def getOutterIP(self):
        return self.outterIP

    def getOutterPort(self):
        return self.outterPort

    def getName(self):
        return self.ServerName

    def getDbName(self):
        return self.DBname

    def getDbHost(self):
        return self.DBIP

    def getStaticDbIp(self):
        return self.staticIP

    def getServerPath(self):
        return self.serverPath

    def getMergeId(self):
        return self.mergeId

    def getCrossServerPort(self):
        return self.CrossServerPort

    def getCrossServerAddress(self):
        return self.CrossServerAddress

    def getJavaXmx(self):
        return self.xmx

    def isHefu(self):
        if int(self.mark) & 16 == 16:
            return True
        else:
            return False

if __name__ == '__main__':
    myserver = servers(4002)
    print 'server_id: ',myserver.server_id
    print 'ServerName: ',myserver.ServerName
    print 'innerIP,innerPort: ',myserver.innerIP, ':,', myserver.innerPort
    print 'outterIP,outterPort: ',myserver.outterIP, ':,', myserver.outterPort
    print 'DBIP,:DBname',myserver.DBIP, ':,', myserver.DBname
    print 'staticIP,: staticDBName', myserver.staticIP, ':,', myserver.staticDBName
    print 'CrossServerAddress: ',myserver.CrossServerAddress
    print 'CrossServerPort: ',myserver.CrossServerPort
    print 'serverPath: ',myserver.serverPath
    print 'mergeId: ',myserver.mergeId
    print 'xmx: ',myserver.xmx
    print 'isHefu ?',myserver.isHefu()

仿照着写的测试代码:
# -*- coding: utf-8 -*-
import db


class server():
    def __init__(self,id):
        uc = db.db('192.168.40.4', 'uc', 3306, 'admin', 'Playmore123!')
        oa = db.db('192.168.40.4', 'oa', 3306, 'admin', 'Playmore123!')
        oa_info = oa.select('select id,name,age,sex,job from card_info where id=%s ;'%id)
        if len(oa_info) == 0:
            print("数据库信息查询失败,请检查你的语法!")
            exit(1)
        else:
            print(oa_info)
            self.id = id
            self.name = oa_info[0][1].encode('utf-8')
            self.age = str(oa_info[0][2])
            self.sex = oa_info[0][3].encode('utf-8')
            self.job = oa_info[0][4].encode('utf-8')

    def get_id(self):
        return self.id

    def get_name(self):
        return self.name

    def get_age(self):
        return self.age

    def get_sex(self):
        return self.sex

    def get_job(self):
        return self.job


if __name__ == '__main__':
    myserver=server(1000001)
    print("ID:{}".format(myserver.id))
    print("NAME:{}".format(myserver.name))
    print("AGE:{}".format(myserver.age))
    print("SEX:{}".format(myserver.sex))
    print("JOB:{}".format(myserver.job))  #还可以写成这样print("JOB:{}".format(myserver.get_job())) 那么你就会有疑问了,为啥直接可以输出,我们上面定义了这么多get函数,这样是因为方便其他脚本调用它。


四、将数据库数据转换成json格式,方便ansible调用

例子:下面是数据库的一些测试数据

下面是将这些数据读取出来,然后输出成json格式

# -*- coding: utf-8 -*-
import db           #导入之前上面写的db连接模块
import json       #导入json模块
class server():
    def __init__(self):
        movie = db.db('192.168.40.4', 'movie', 3306, 'admin', 'Playmore123!')
        movie_id = movie.select('select id from coming_movies where id >5;')
        # print(movie_id)
        l = [int(i[0]) for i in movie_id]
        info={}
        # print(l)
        for i in l:
            id = i
            movie_info = movie.select('select mname,duration,director,actor,times from coming_movies WHERE id={};'.format(i))
            if len(movie_info) == 0:
                print("数据库信息查询失败,请检查你的语法!")
                exit(1)
            else:
                info[id]={}
                info[id].setdefault('id', i)
                info[id]['name']=movie_info[0][0]
                info[id]['duration']=movie_info[0][1]
                info[id]['director']=movie_info[0][2]
                info[id]['actor']=movie_info[0][3]
                info[id]['times']=movie_info[0][4]
        print json.dumps(info,indent=4).decode("unicode-escape")  #输出json,indent是表示按照缩进输出,4代表缩进4个字符,后面的decode函数是换成中文编码

if __name__ == '__main__':
    myserver=server()

效果:

需要注意的是ansible-playbook 调用时需要加上关键字。

如:echo.yaml

– hosts: all
user: game
tasks:
– name: echo txt
shell: echo {{ path }} && cd {{ path }} && /bin/touch 1

测试连通性:$ ansible -i get.sh all -m ping

你会发现不通,需要在json中加入关键字,-i参数是指定hosts,get.sh中必须要有host

修改下cat get_info.py

加入ansible_ssh_user,ansible_ssh_host这两个字段就行了!

 

 

 

python的zipfie简单应用

一、场景案例 需求:当开发上传某一个zip包时,自动解压到当前目录。已经解压过得zip包不再解压! 此处用到的模块有Python3自有模块os,sys,time,json,re,zipf...

阅读全文

vip电影的解析

一、出现的原因 由于各大互联网视频app均推出了“轻奢主义”的营销模式,导致了很多优质视频需要我们办这个月卡、年卡才能观看,更离谱的是腾讯最近推出了会员...

阅读全文

logging日志模块

一、日志输出合理的必要性 日式收集使我们日常工作中都会遇到的问题,而一个好的日志输出,则会给收集工作带来大大的效率提升。同时能够给程序员自己排错带来...

阅读全文

欢迎留言