Python2.7.10、pycharm、VM虚拟机、CentOS6.3、mysql
MySQLdb模式是Python中专门连接MySQL数据库的模块,另外Python开发环境的搭建真心感觉比其他的麻烦好多。。。这次光安装这个MySQLdb这个模块就花了好长时间,遇到好多问题,不过幸好还是成功使用上了,稍后我会加上一篇我是怎么安装的教程,会列出我遇到的问题供大家参考。
这里先介绍一下数据库,我们使用的是安装在CentOS上的MySQL数据库。数据库的安装这里不做介绍。
在MySQL中我们把所有表放在名为linux的数据库中,数据库包含两张表:存放linux信息数据的linux表中,存放硬盘数据的fdisk表中
这两个表的SQL:
1 CREATE TABLE linux //linux表 2 ( 3 os_version VARCHAR(1000) NOT NULL, 4 os_kernal VARCHAR(1000) not NULL, 5 os_data VARCHAR(1000) PRIMARY KEY, 6 ) 7 8 CREATE TABLE fdisk //fdisk表 9 ( 10 f_date TIMESTAMP(14) NOT NULL, 11 filesystem VARCHAR(1000) NOT NULL, 12 rom VARCHAR(1000) not NULL, 13 used VARCHAR(1000) not NULL, 14 not_used VARCHAR(1000) not NULL, 15 used_per VARCHAR(1000) NOT NULL, 16 mount VARCHAR(1000) not NULL 17 )
所有对于表的操作都在一个py文件中,包括插入和查询操作
mysql_db.py
1 #coding=UTF-8 2 import MySQLdb 3 import _mysql_exceptions 4 createlinuxsql = """ 5 CREATE TABLE linux 6 ( 7 os_version VARCHAR(1000) NOT NULL, 8 os_kernal VARCHAR(1000) not NULL, 9 os_data VARCHAR(1000) PRIMARY KEY, 10 11 ) 12 """ 13 createfdisksql = """ 14 CREATE TABLE fdisk 15 ( 16 f_date TIMESTAMP(14) NOT NULL, 17 filesystem VARCHAR(1000) NOT NULL, 18 rom VARCHAR(1000) not NULL, 19 used VARCHAR(1000) not NULL, 20 not_used VARCHAR(1000) not NULL, 21 used_per VARCHAR(1000) NOT NULL, 22 mount VARCHAR(1000) not NULL 23 ) 24 """ 25 class mysql: 26 def __init__(self): 27 try: 28 self.conn = MySQLdb.connect(host='192.168.179.129',user='root',passwd='ro',db='linux') 29 self.cur = self.conn.cursor() 30 except _mysql_exceptions.OperationalError,e: 31 print '数据库无法连接' 32 def __del__(self): 33 self.cur.close() 34 self.conn.commit() 35 self.conn.close() 36 def select(self): 37 try: 38 self.cur.execute("select * from linux") 39 for each in self.cur.fetchall(): 40 print each 41 except _mysql_exceptions.ProgrammingError,e: 42 self.cur.execute(createlinuxsql) 43 def os_insert(self,version,kernal,data): 44 try: 45 self.cur.execute("insert into linux VALUES ('%s','%s','%s')"% (version,kernal,data)) 46 except _mysql_exceptions.ProgrammingError,e: 47 self.cur.execute(createlinuxsql) 48 self.cur.execute("insert into linux VALUES ('%s','%s','%s')"% (version,kernal,data)) 49 def fdisk_select(self): 50 try: 51 self.cur.execute("select * from fdisk") 52 print self.cur.fetchall() 53 except _mysql_exceptions.ProgrammingError,e: 54 self.cur.execute(createfdisksql) 55 def fdisk_insert(self,filesystemz,romz,usedz,nousedz,usedperz,mountz): 56 try: 57 self.cur.execute("insert into fdisk(filesystem,rom,used,not_used,used_per,mount) / 58 VALUES ('%s','%s','%s','%s','%s','%s')"% (filesystemz,/ 59 romz,usedz,nousedz,usedperz,mountz)) 60 except _mysql_exceptions.ProgrammingError,e: 61 self.cur.execute(createfdisksql) 62 self.cur.execute("insert into fdisk(filesystem,rom,used,not_used,used_per,mount) / 63 VALUES ('%s','%s','%s','%s','%s','%s')"% (filesystemz,/ 64 romz,usedz,nousedz,usedperz,mountz))
不用去手动建立表,只要数据库存在会自动建立表的
同时对以前的一些代码进行了更新,更新只填写变动
os_info.py:
1 # coding=UTF-8 2 import linux_status 3 import os_info_in 4 from mysql_db import mysql 5 class os_info: 6 def __init__(self): 7 # self.linux_stat=linux_status.linux_status() 8 self.os_infos_in= os_info_in.os_info_in() 9 def os_info(self): 10 self.linux_stat=linux_status.linux_status() 11 self.linux_stat.os_version = self.os_infos_in.os_version() 12 self.linux_stat.os_kernal = self.os_infos_in.os_kernel() 13 self.linux_stat.os_date = self.os_infos_in.os_date() 14 return self.linux_stat 15 def os_in_mysql(self): 16 os_db=mysql() 17 os_db.os_insert(self.os_infos_in.os_version(),self.os_infos_in.os_kernel(),self.os_infos_in.os_date())
linux_fdisk.py:
def fdisk_mysql(self): self.fdisk_mysql=mysql() i=0 j=1 while j==1: try: self.fdisk_mysql.fdisk_insert(self.disk_info[i],self.disk_info[i+1],self.disk_info[i+2],/ self.disk_info[i+3],self.disk_info[i+4],self.disk_info[i+5]) i=i+6 except Exception,e: j=0
四、结果展示:
mysql不支持显示中文,把linux改为英文就可以了(export LANG=en_US)
最后~