Python frequently reads MySQL related issues

1, need to select large amounts of data frequently, long time, memory consumption, how to solve the MySQL performance problem?

If there is no requirement for the number of results returned, you can control the number returned:

Cursor.fetchmany (size=1000)

This returns only 1000 data, and if the returned result is less than size, all data is returned;

If you only need one, it's simpler: Fetchone ()

2, every time the data inserted is too large, MySQL server has gone away how to solve?

stored as BLOB type;

Modified in my.conf: Max_allowed_packet = 500m

3, to the Python list type into MySQL, and then the next time you need to read in the list format, how to do?

Because the list type contains a comma with half-width, or if the inserted data contains special symbols, it is not possible to insert MySQL properly.

There are many ways in Google, and I'm taking base64. The data that will be inserted base64 encode can be stored in MySQL normally.

BASE64STR = Base64.b64encode (str (MYSQLSTR))

Mysqlstr = Base64.b64decode (B64STR)

Note: When you read, you need to Base64decode, when you get the STR, you can not use the list sequence to take the value. What to do?

Eval (String)

As above, Eval can be a good solution to this problem, the Str into a tuple, it can be used directly.

4, the frequent operation of MySQL more censored data, it is best to use multi-threaded database, to avoid the trouble caused by my.conf configuration problems.

Here is a MySQL multithreaded operation class:

1 classMYSQL:2     def __init__(self,sql):3Self.sql =SQL4Self.conn = MySQLdb.connect (charset='UTF8', user='yourname', passwd='passwd', db='your dbname')            5Self.cursor =self.conn.cursor ()6  7     defInsert (self):8 Self.cursor.execute (self.sql)9 Self.conn.commit ()Ten self.cursor.close () One self.conn.close () A         returnTrue -   -     defSelect (self): the Self.cursor.execute (self.sql) -AllData =Self.cursor.fetchall () - self.cursor.close () - self.conn.close () +         returnAllData -   +     defUpdate (self): A Self.cursor.execute (self.sql) at Self.conn.commit () - self.cursor.close () - self.conn.close () -         returnTrue

Python frequently reads MySQL related issues