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.

Python for MySQL

First, 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.

Create a new configuration file named conf. ini. You can write various configuration information, but specify the nodes (the file format is still strict ):

[App_info]
DATABASE = test
USER = app
PASSWORD = 123456
HOST = 172.17.1.1
PORT = 3306

[Mail]
Host = smtp.bkjia.com
Mail_from = bkjia0111@bkjia.com
Password = 654321
Send_to = bkjia0111@bkjia.com; bkjia0111@bkjia.com

Create a new file db. py in the same directory. The cool code is as follows:

#-*-Coding: UTF-8 -*-

Import MySQLdb # these two packages must be installed first
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 cannot be executed successfully.
Cur. close ()
Conn. close ()
Return rows
Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

The preceding method encapsulates the database operation method. You only need to provide an SQL statement, and CRUD can be used. Here we will use YY data to test the specific usage of adding, deleting, modifying, and querying (easy, I am really idle), and then write the above Code:

Def operation ():
# Query
Select = mysql ('select * from test ')

# Insert
'''
There are two points to note when inserting 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. This is not easy to say. Haha, the digit placeholder uses % d, the string uses % 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 number = % d where name = "% s" '% (99, 'jzhou '))

# Delete
Delete = mysql ('delete from test where number = % d and 'type' = "% s" '% (100, 'jzhou '))

Return select # I returned this message for the purpose of sending the mail below. By the way, the mail sending function is added.

I just want to make this simple operation complicated and add a 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 = cf. 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
Except t Exception, e:
Print str (e)
Return False

The email sending configuration is also written in conf. ini. In the main function, call the email sending function to end the mail:

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, which is very similar to mysql. The download package psycopg2 is not quite the same, that is, the SQL statements executed in postgresql must be enclosed in double quotation marks. Let's take a look:

#-*-Coding: UTF-8 -*-
Import psycopg2
Import ConfigParser

Cf = 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 ")

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
Except t Exception, e:
Print e

Def psql_oper ():
SQL = "select \" name \ ", \" type \ "from \" test \ "where \" name \ "= 'jzhou '"
Rows = psql (SQL)
Print rows

I have summarized that this blog is simple but contains three important knowledge points, ^_^

1. python reads the INI file (import ConfigParser)

2. Use python to operate mysql

3. Send emails in python

4. All posted are tested in practice. Even if it is very simple, this is an attitude!

-------------------------------------- Split line --------------------------------------

Install Python3.4 on CentOS source code

Python core programming version 2. (Wesley J. Chun). [Chinese version of hd pdf]

Python development technology details. (Zhou Wei, Zong Jie). [hd PDF scan version + book guide video + code]

Obtain Linux information using a Python script

Build a desktop algorithm transaction research environment using Python in Ubuntu

A Brief History of Python Development

Python details: click here
Python: click here

This article permanently updates the link address: