pythonmysqlcrud_Python对MySQL的CRUD

Python对各种数据库的各种操作满大街都是&＃xff0c;不过&＃xff0c;我还是喜欢我这种风格的&＃xff0c;涉及到其它操作&＃xff0c;不过重点还是对数据库的操作。

Python操作MySQL

首先&＃xff0c;我习惯将配置信息写到配置文件&＃xff0c;这样修改时可以不用源代码&＃xff0c;然后再写通用的函数供调用

新建一个配置文件&＃xff0c;就命名为conf.ini&＃xff0c;可以写各种配置信息&＃xff0c;不过都指明节点(文件格式要求还是较严格的)&＃xff1a;

[app_info]

DATABASE&＃61;test

USER&＃61;app

PASSWORD&＃61;123456

HOST&＃61;172.17.1.1

PORT&＃61;3306

[mail]

host&＃61;smtp.linuxidc.com

mail_from&＃61;linuxidc0111&＃64;linuxidc.com

password&＃61;654321

send_to&＃61;linuxidc0111&＃64;linuxidc.com;linuxidc0111&＃64;linuxidc.com

同目录下新建文件db.py&＃xff0c;精悍的代码如下&＃xff0c;不解释&＃xff1a;

# -*-coding:utf-8 -*-

import MySQLdb  #首先必须装这两个包

import ConfigParser

cf&＃61;ConfigParser.ConfigParser()

cf.read("conf.ini")

DATABASE&＃61;cf.get("app_info","DATABASE")

USER&＃61;cf.get("app_info","USER")

PASSWORD&＃61;cf.get("app_info","PASSWORD")

HOST&＃61;cf.get("app_info","HOST")

PORT&＃61;cf.get("app_info","PORT")

def mysql(sql):

try:

conn&＃61;MySQLdb.connect(host&＃61;HOST,user&＃61;USER,passwd&＃61;PASSWORD,db&＃61;DATABASE,port&＃61;PORT)

cur &＃61; conn.cursor()

cur.execute(sql)

rows &＃61; cur.fetchall()

conn.commit()  #这个对于增删改是必须的&＃xff0c;否则事务没提交执行不成功

cur.close()

conn.close()

return rows

except MySQLdb.Error,e:

print "Mysql Error %d: %s" % (e.args[0], e.args[1])

上面是封装了操作数据库的方法&＃xff0c;只需提供一个sql语句&＃xff0c;CRUD均可操作。下面来YY一些数据来测试下增删改查的具体用法(easy的&＃xff0c;我真是闲)&＃xff0c;接着上面的代码写&＃xff1a;

def operation():

#查询

select &＃61; mysql(&＃39;select * from test&＃39;)

#插入

&＃39;&＃39;&＃39;

插入这个地方有2点需要注意&＃xff1a;

1.插入某几列如下指定&＃xff0c;插入全部可以不指定列&＃xff0c;但必须后面插入的值要按顺序

2.注意下面的type列两边有反斜点&＃xff0c;这是因为type在我这个数据库里有个表也叫这个&＃xff0c;或者可以把它叫关键字&＃xff0c;不加反斜点插入会失败

3.这没好说的&＃xff0c;呵呵&＃xff0c;数字占位符用%d,字符串用%s&＃xff0c;且字符串占位符必须用双引号括起来

&＃39;&＃39;&＃39;

insert &＃61; mysql(&＃39;insert into test (name,number,&＃96;type&＃96;) values("%s",%d,"%s")&＃39;%(&＃39;jzhou&＃39;,100,&＃39;VIP&＃39;))

#更新

mysql(&＃39;update test set number&＃61;%d where name&＃61;"%s"&＃39;%(99,&＃39;jzhou&＃39;))

#删除

delete &＃61; mysql(&＃39;delete from test where number &＃61; %d and &＃96;type&＃96;&＃61;"%s"&＃39;%(100,&＃39;jzhou&＃39;))

return select #我返回这个是为了下面发送邮件用的&＃xff0c;顺便增加个发送邮件的功能

我只是想把这个简单的操作搞的复杂点&＃xff0c;增加个发送邮件的功能&＃xff0c;也是接着上面的代码&＃xff1a;

mailto_list&＃61;[]

send_info&＃61;cf.get("mail","send_to")

send_array&＃61;send_info.split(";")

for i in range(len(send_array)):

mailto_list.append(send_array[i])

mail_host&＃61;cf.get("mail","host")

mail_from&＃61;cf.get("mail","mail_from")

mail_password&＃61;cf.get("mail","password")

def send_mail(to_list,sub,content):

me&＃61;mail_from

msg&＃61;MIMEText(content,_subtype&＃61;&＃39;html&＃39;,_charset&＃61;&＃39;utf-8&＃39;)

msg[&＃39;Subject&＃39;]&＃61;sub

msg[&＃39;From&＃39;]&＃61;me

msg[&＃39;To&＃39;]&＃61;";".join(to_list)

try:

s&＃61;smtplib.SMTP()

s.connect(mail_host)

s.login(mail_from,mail_password)

s.sendmail(me,to_list,msg.as_string())

s.close()

return True

except Exception,e:

print str(e)

return False

发送邮件的配置我也是写在conf.ini里的&＃xff0c;在主函数里调用一下发送邮件来结束这个东西&＃xff1a;

if __name__ &＃61;&＃61; &＃39;__main__&＃39;:

sub &＃61; u&＃39;不要问我为什么写这篇博客&＃xff0c;闲&＃xff0c;就是任性&＃xff01;&＃39;

content &＃61; operation()

if send_mail(mailto_list,sub,content):

print &＃39;send success&＃39;

else:

print &＃39;send failed&＃39;

其实我还想说一下python操作postgresql&＃xff0c;跟mysql非常类似&＃xff0c;下载包psycopg2&＃xff0c;不太相同的就是postgresql中执行的sql语句都要加双引号&＃xff0c;来感受一下&＃xff1a;

# -*-coding:utf-8 -*-

import psycopg2

import ConfigParser

cf&＃61;ConfigParser.ConfigParser()

cf.read("conf.ini")

DATABASE&＃61;cf.get("cmdb_info","DATABASE")

USER&＃61;cf.get("cmdb_info","USER")

PASSWORD&＃61;cf.get("cmdb_info","PASSWORD")

HOST&＃61;cf.get("cmdb_info","HOST")

PORT&＃61;cf.get("cmdb_info","PORT")

def psql(sql):

try&＃xff1a;

conn &＃61; psycopg2.connect(database&＃61;DATABASE, user&＃61;USER, password&＃61;PASSWORD, host&＃61;HOST, port&＃61;PORT)

cur &＃61; conn.cursor()

cur.execute(sql)

rows &＃61; cur.fetchall()

conn.commit()

cur.close()

conn.close()

return rows

except Exception,e:

print e

def psql_oper():

sql&＃61;"select \"name\",\"type\" from \"test\" where \"name\" &＃61; &＃39;jzhou&＃39;"

rows&＃61;psql(sql)

print rows

我总结了下&＃xff0c;此博客虽简单&＃xff0c;但包含三个重要的知识点&＃xff0c;^_^

1、python读取ini文件(要import ConfigParser)

2、python操作mysql

3、python发送邮件

4、发表出来的都是经过实践检验的&＃xff0c;即使很简单&＃xff0c;这是一种态度&＃xff01;

--------------------------------------分割线 --------------------------------------

Python 的详细介绍&＃xff1a;请点这里

Python 的下载地址&＃xff1a;请点这里