Python for MySQL CRUD

Python for MySQL CRUD
Python is full of operations on various databases. However, I still like this style and it involves other operations, but it focuses on database operations. Ah ~~ When using Python to operate Mysql, I am used to writing configuration information to the configuration file, so that the source code is not needed for modification, and then write a common function for calling to create a new configuration file, which is named conf. ini, you can write a variety of configuration information, but all indicate the node (file format requirements are still relatively strict ): [app_info] DATABASE = testUSER = appPASSWORD = 123456 HOST = 172.17.1.1PORT = 3306 [mail] host = smtp.163.commail _ from = zhoujie0111@126.compassword = 654321send_to = zhoujie0111@139.com; zhoujie0111@163.com new file db under the same directory. py, the cool code is as follows, not explained: copy the Code #-*-coding: UTF-8-*-import MySQLdb # first, you must install these two packages import ConfigParser cf = 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") def mysql (SQL): try: conn = MySQLdb. connect (host = HOST, user = USER, passwd = PASSWORD, db = DATABASE, port = PORT) cur = conn. cursor () cur.exe cute (SQL) rows = cur. fetchall () conn. commit () # This is required for addition, deletion, and modification. Otherwise, the transaction is not committed and execution fails. close () conn. close () return rows t MySQLdb. error, e: print "Mysql Error % d: % s" % (e. args [0], e. args [1]) the copy Code encapsulates the database operation method. You only need to provide an SQL statement, and CRUD can be used. Here we will use some YY data to test the specific usage of adding, deleting, modifying, and querying (easy, I am really idle), and then write the code above: def operation (): # query select = mysql ('select * from test') # insert ''' there are two points to insert into this place: 1. insert specified columns as follows. you can insert all columns without specifying columns, but the values to be inserted later must be in order 2. note that the following type column has a reverse oblique point on both sides. This is because type has a table in my database that is also called this, or it can be called a keyword, insertion without a reverse oblique point will fail. 3. well, the placeholder numbers are % d, the string is % s, and the string placeholder must be enclosed in double quotation marks ''' insert = mysql ('insert into test (name, number, 'type') values ("% s", % d, "% s") '% ('jzhou', 100, 'vip ')) # update mysql ('Update test set n Umber = % d where name = "% s" '% (99, 'jzhou ')) # delete = mysql ('delete from test where number = % d and 'type' = "% s" '% (100, 'jzhou ')) return select # I returned this message for sending emails below. By the way, I added a mail sending function. I just wanted to make this simple operation complicated, added the mail sending function, followed by the above Code: mailto_list = [] send_info = cf. get ("mail", "send_to") send_array = send_info.split (";") for I in range (len (send_array): mailto_list.append (send_array [I]) mail_host = cf. get ("mail", "host") mail_from = c F. get ("mail", "mail_from") mail_password = cf. get ("mail", "password") def send_mail (to_list, sub, content): me = mail_from msg = MIMEText (content, _ subtype = 'html ', _ charset = 'utf-8') msg ['subobject'] = 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 () return True expect t Exception, e: print str (e) The configuration for sending emails is also written in conf. in ini, call the main function to send an email to end this thing: if _ name _ = '_ main __': sub = U' don't ask me why I wrote this blog. idle, just willful! 'Content = operation () if send_mail (mailto_list, sub, content): print 'send success 'else: print 'send failed' in fact, I also want to talk about python operations on postgresql, similar to mysql, the downloaded package psycopg2 is not exactly the same. The SQL statements executed in postgresql must be enclosed in double quotation marks. Feel this: #-*-coding: UTF-8-*-import psycopg2import ConfigParser cf = ConfigParser. configParser () cf. read ("conf. ini ") DATABASE = cf. get ("cmdb_info", "DATABASE") USER = cf. get ("cmdb_info", "USER") PASSWORD = cf. get ("cmdb_inf O "," PASSWORD ") HOST = cf. get ("cmdb_info", "HOST") PORT = cf. get ("cmdb_info", "PORT") def psql (SQL): try: conn = psycopg2.connect (database = DATABASE, user = USER, password = PASSWORD, host = HOST, port = PORT) cur = conn. cursor () cur.exe cute (SQL) rows = cur. fetchall () conn. commit () cur. close () conn. close () return rows failed t Exception, e: print e def psql_timeout (): SQL = "select \" name \", \ "type \" from \ "test \" where \ "n Ame \ "= 'jzhou'" rows = psql (SQL) print rows, ^ _ ^ 1. python reads the INI file (to import ConfigParser) 2. python operates mysql 3. python sends emails 4. Published emails are verified by practice. Even if it is very simple, this is an attitude!