Python's crud for MySQL

I am idle of nothing to do, 2014 too pompous, blog also did not write several, hey ~ ~ ~ With this article to record the upcoming 2014

Python's various operations on various databases are full of streets, but I still like my style, involving other operations, but the focus is on the operation of the database. Ah ~ ~

Python operation MySQL

First, I'm used to writing configuration information to a configuration file so that it can be modified without the source code and then write a generic function for the call

Create a new configuration file named Conf.ini, you can write various configuration information, but all indicate the node (file format requirements are more strict):

[App_info]database=TestUSER=apppassword=123456HOST=172.17.1.1PORT =3306[mail]host=smtp.163. Commail_from[email protected]. Compassword=654321send_to [email protected]; [Email protected]

New files in the same directory db.py, lean code as follows, do not explain:

#-*-coding:utf-8-*-ImportMySQLdb#These two bags must be installed first .ImportCONFIGPARSERCF=Configparser.configparser () cf.read ("Conf.ini") DATABASE=cf.get ("App_info","DATABASE") USER=cf.get ("App_info","USER") PASSWORD=cf.get ("App_info","PASSWORD") HOST=cf.get ("App_info","HOST") PORT=cf.get ("App_info","PORT")defmysql (SQL):Try: Conn=mysqldb.connect (host=host,user=user,passwd=password,db=database,port=PORT) cur=conn.cursor () cur.execute (SQL) rows=Cur.fetchall () conn.commit ( )#this is necessary for additions and deletions, otherwise the transaction does not commit the execution is unsuccessfulcur.close () conn.close ( )returnrowsexceptmysqldb.error,e:Print "Mysql Error%d:%s"% (E.args[0], e.args[1])

The above is a way to encapsulate the operations database, just provide an SQL statement, CRUD can be manipulated. The following YY some data to test the specific usage of the change (easy, I really idle), and then the above code to write:

defoperation ():#Enquiryselect = MySQL ('SELECT * FROM Test')    #Insert    " "Insert this place there are 2 points to note: 1. Insert a few columns as specified below, insert all can not specify a column, but must be inserted after the value to be in order 2. Note that the following type column has a backslash on either side, because the type has a table in my database called this, or it can be called a keyword, not Adding anti-miter insertion will fail 3. Well, that's okay, hehe, the number placeholder is%d, the string is%s, and the string placeholder must be enclosed in double quotes" "Insert= MySQL ('INSERT INTO Test (name,number, ' type ') VALUES ("%s",%d, "%s")'%('Jzhou', 100,'VIP'))    #UpdateMySQL'Update test set number=%d where Name= '%s ''% (99,'Jzhou'))    #DeleteDelete = MySQL ('Delete from test where number =%d and ' type ' = '%s ''% (100,'Jzhou'))    returnSelect#I returned to this to send mail for the following, by the way to increase the ability to send mail

I just want to make this simple operation complex point, add a message to send the function, but also the above code:

mailto_list=[]send_info=cf.get ("Mail","send_to") Send_array=send_info.split (";") forIinchRange (len (send_array)): Mailto_list.append (Send_array[i]) Mail_host=cf.get ("Mail","Host") Mail_from=cf.get ("Mail","Mail_from") Mail_password=cf.get ("Mail","Password")defSend_mail (to_list,sub,content): Me=mail_from msg=mimetext (content,_subtype='HTML', _charset='Utf-8') msg['Subject']=Sub msg[' from']=Me msg[' to']=";". Join (to_list)Try: S=Smtplib.        SMTP () s.connect (mail_host) s.login (Mail_from,mail_password) S.sendmail (me,to_list,msg.as_string ()) S.close ()returnTrueexceptexception,e:PrintStr (e)returnFalse

Send mail Configuration I'm also writing in Conf.ini, and I'm going to call it in the main function to end this thing by sending a message:

if __name__=='__main__': Sub= u'do not ask why I write this blog, idle, is wayward! 'content=operation ()ifSend_mail (mailto_list,sub,content):Print 'Send Success'    Else:        Print 'Send failed'

In fact, I would also like to say that Python operation PostgreSQL, very similar to MySQL, download package psycopg2, not the same is the SQL statement executed in PostgreSQL have to add double quotes, to feel:

#-*-coding:utf-8-*-ImportPSYCOPG2ImportCONFIGPARSERCF=Configparser.configparser () cf.read ("Conf.ini") DATABASE=cf.get ("Cmdb_info","DATABASE") USER=cf.get ("Cmdb_info","USER") PASSWORD=cf.get ("Cmdb_info","PASSWORD") HOST=cf.get ("Cmdb_info","HOST") PORT=cf.get ("Cmdb_info","PORT")defpsql (SQL):Try: Conn=Psycopg2.connect(Database=database, User=user, Password=password, Host=host, port=PORT) cur=conn.cursor () cur.execute (SQL) rows=Cur.fetchall () conn.commit () Cur.close () Conn.close ( )returnrowsexceptexception,e:Printedefpsql_oper (): SQL="select \ "name\", \ "type\" from \ "test\" where \ "name\" = ' Jzhou '"rows=psql (SQL)PrintRows

I conclude that this blog is simple, but contains three important points of knowledge, ^_^

1. Python read INI file (to import Configparser)

2. Python operation MySQL

3. Send mail

Python's crud for MySQL