Optimization of the database

Before using the Pymysql to manipulate the database is written dead in the view function, and there are a lot of duplicate code.

We can optimize it and extract the duplicated code. Write function:

ImportPymysql#define a database-related configuration itemDb_config = {    "Host":"127.0.0.1",    "Port": 3306,    "User":"Root",    "passwd":"root1234",    "DB":"MySite",    "CharSet":"UTF8"}#querying more than one data functiondefGet_list (SQL, args=None): Conn=Pymysql.connect (Host=db_config["Host"], Port=db_config["Port"], user=db_config["User"], passwd=db_config["passwd"], DB=db_config["DB"], CharSet=db_config["CharSet"]) Cursor= Conn.cursor (cursor=pymysql.cursors.DictCursor) cursor.execute (sql, args) result=Cursor.fetchall () cursor.close () Conn.close ( )returnresult#querying heads-up data functionsdefGet_one (SQL, args=None): Conn=Pymysql.connect (Host=db_config["Host"], Port=db_config["Port"], user=db_config["User"], passwd=db_config["passwd"], DB=db_config["DB"], CharSet=db_config["CharSet"]) Cursor= Conn.cursor (cursor=pymysql.cursors.DictCursor) cursor.execute (sql, args) result=Cursor.fetchone () cursor.close () Conn.close ( )returnresult#Modify a recorddefModify (SQL, args=None): Conn=Pymysql.connect (Host=db_config["Host"], Port=db_config["Port"], user=db_config["User"], passwd=db_config["passwd"], DB=db_config["DB"], CharSet=db_config["CharSet"]) Cursor= Conn.cursor (cursor=pymysql.cursors.DictCursor) cursor.execute (sql, args) conn.commit () Cursor.close () conn.close ()#Create a recorddefCreate (SQL, args=None): Conn=Pymysql.connect (Host=db_config["Host"], Port=db_config["Port"], user=db_config["User"], passwd=db_config["passwd"], DB=db_config["DB"], CharSet=db_config["CharSet"]) Cursor= Conn.cursor (cursor=pymysql.cursors.DictCursor) cursor.execute (sql, args) conn.commit ()#returns the ID of the piece of data you just createdlast_id =cursor.lastrowid cursor.close () conn.close ()returnlast_id

So as long as you need to connect to the database to do the operation, call the function we defined above can be.

But this still has a problem, when I want to create data in large batches, I need to call the Create method multiple times, the equivalent of multiple connections multiple commits.

Can be optimized, the database connection reuse, so that only a single connection can execute multiple functions.

classSqlmanager (object):#Initialize instance methods    def __init__(self): Self.conn=None self.cursor=None self.connect ()#connecting to a database    defConnect (self): Self.conn=Pymysql.connect (Host=db_config["Host"], Port=db_config["Port"], user=db_config["User"], passwd=db_config["passwd"], DB=db_config["DB"], CharSet=db_config["CharSet"]) Self.cursor= Self.conn.cursor (cursor=pymysql.cursors.DictCursor)#querying more than one piece of data    defGet_list (self, SQL, args=None): Self.cursor.execute (sql, args) result=Self.cursor.fetchall ()returnresult#Querying single Data    defGet_one (self, SQL, args=None): Self.cursor.execute (sql, args) result=Self.cursor.fetchone ()returnresult#execute a single SQL statement    defModdify (self, SQL, args=None): Self.cursor.execute (sql, args) self.conn.commit ()#Create a statement for a single record    defCreate (self, SQL, args=None): Self.cursor.execute (sql, args) self.conn.commit () last_id=Self.cursor.lastrowidreturnlast_id#Close database cursor and connections    defClose (self): Self.cursor.close () self.conn.close ()

We encapsulate the relevant operations of our database into a class, when used, only need to generate an instance, and invoke the corresponding operation method on the instance.

db == db.get_list ("Select ID, name from class"= db.get_list (" SELECT teacher.id, Teacher.name, teacher2class.class_id from teacher  Left JOIN teacher2class on teacher.id = teacher2class.teacher_id WHERE teacher.id=%s; " , [teacher_id]) db.close ()

But if we want to perform multiple creation operations in bulk, although we have only one database connection but there will be multiple commits, can we change to one connection and one commit?

Yes, just use the Pymysql executermany () method.

# execute multiple SQL statements def multi_modify (self, SQL, args=None):    self.cursor.executemany (sql, args)    self.conn.commit () 

Now if we perform multiple creation operations at once, we can use the Multi_modify () method to implement one-time connection commit.

Finally, I have to manually close each time the database is completed, can it be written automatically shut down?

Associating with the file operations we learned before, using the With statement can implement an example of automatically closing a file handle at the end of the indentation.

We re-optimize our database connection class Sqlmanager class to support the WITH statement operation.

classSqlmanager (object):#Initialize instance methods    def __init__(self): Self.conn=None self.cursor=None self.connect ()#connecting to a database    defConnect (self): Self.conn=Pymysql.connect (Host=db_config["Host"], Port=db_config["Port"], user=db_config["User"], passwd=db_config["passwd"], DB=db_config["DB"], CharSet=db_config["CharSet"]) Self.cursor= Self.conn.cursor (cursor=pymysql.cursors.DictCursor)#querying more than one piece of data    defGet_list (self, SQL, args=None): Self.cursor.execute (sql, args) result=Self.cursor.fetchall ()returnresult#Querying single Data    defGet_one (self, SQL, args=None): Self.cursor.execute (sql, args) result=Self.cursor.fetchone ()returnresult#execute a single SQL statement    defModdify (self, SQL, args=None): Self.cursor.execute (sql, args) self.conn.commit ()#execute multiple SQL statements    defMulti_modify (self, SQL, args=None): Self.cursor.executemany (sql, args) self.conn.commit ()#Create a statement for a single record    defCreate (self, SQL, args=None): Self.cursor.execute (sql, args) self.conn.commit () last_id=Self.cursor.lastrowidreturnlast_id#Close database cursor and connections    defClose (self): Self.cursor.close () self.conn.close ()#Go to the WITH statement for automatic execution    def __enter__(self):return Self#exit With statement block automatic execution    def __exit__(self, exc_type, Exc_val, EXC_TB): Self.close ()

Optimization of the database