"Student Management System" 0X04 database connection optimization

"Learner Management System" 0x04 pymysql database connection optimization write in front

For Detailed project requirements, see : The "Student management system" of the Django Project

Optimized Implementation encapsulate the operation into a function

We previously used Pymysql to manipulate the database by writing dead in the View function, and many of them were duplicated code.

We can optimize it, extract the duplicated code and write the 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

Code: Optimization 

So as long as the need to connect to the database to do the operation, only need to call our definition of the above function is OK.

Reusing database connections

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 continue to optimize, the database connection reuse, so that only a single connection can perform multiple operations.

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 ()

Code: OptimizationEncapsulate the operation into a class

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 ()

Bulk operations

However, if I want to perform multiple creation operations in bulk, although only one database connection is established but it will be submitted multiple times, can it be changed to one connection and one commit?

Yes, just use the Pymysql executemany () method.

Add a batch-executed multi_modify () method to our Sqlmanager class.

# Execute multiple SQL statements def multi_modify (self, SQL, Args=none):    self.cursor.executemany (sql, args)    self.conn.commit ()

Using the WITH statement operation

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

Finally, we have to manually close each time after the database operation, can it be written off automatically?

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.

Let us then 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 ()

Code: Optimization

"Student Management System" 0X04 database connection optimization