Shulou(Shulou.com)05/31 Report--

This article is about how to use the pymysql module to connect to the mysql database in python, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Install pymysql

Pip install pymysql

| 2 | 0 uses pymysql |

| 2 | 1 use data query statements |

Query a piece of data fetchone ()

From pymysql import * conn = connect (host='127.0.0.1', port=3306, user='root', password='123456', database='itcast', charset='utf8') # create cursor c = conn.cursor () # execute sql statement c.execute ("select * from student") # query a row of data result = c.fetchone () print (result) # close cursor c.close () # close database connection conn.close () "" (1 'Zhang San', 18, b'\ x01') ""

Query multiple pieces of data fetchall ()

From pymysql import * conn = connect (host='127.0.0.1', port=3306, user='root', password='123456', database='itcast' Charset='utf8') # create cursor c = conn.cursor () # execute sql statement c.execute ("select * from student") # query multi-row data result = c.fetchall () for item in result: print (item) # close cursor c.close () # close database connection conn.close () "(1, 'Zhang San', 18, b'\ x01') (2,'Li Si', 19, b'\ x00') (3 'Wang Wu', 20, b'\ x01') "

Change the default setting of the cursor and return a dictionary

From pymysql import * conn = connect (host='127.0.0.1', port=3306, user='root', password='123456', database='itcast', charset='utf8') # create cursors The operation is set to dictionary type c = conn.cursor (cursors.DictCursor) # execute sql statement c.execute ("select * from student") # query multi-row data result = c.fetchall () for item in result: print (item) # close cursor c.close () # close database connection conn.close () "{'id': 1,' name': 'Zhang San', 'age': 18 'sex': b'\ x01'} {' id': 2, 'name':' Li Si', 'age': 19,' sex': b'\ x00'} {'id': 3,' name': 'Wang Wu,' age': 20, 'sex': b'\ x01'} ""

It's the same when returning a piece of data. Return to the dictionary or tuple to see the individual needs.

| 2 | 2 use data manipulation statements |

The operations to add, delete, and update statements are actually the same. Write only one as a demonstration.

From pymysql import * conn = connect (host='127.0.0.1', port=3306, user='root', password='123456', database='itcast', charset='utf8') # create cursor c = conn.cursor () # execute the sql statement c.execute ("insert into student (name,age,sex) values (% s)% s)", ("waiter" 2881)) # commit transaction conn.commit () # close cursor c.close () # close database connection conn.close ()

Unlike query statements, you must use commit () to commit the transaction, otherwise the operation is invalid.

| 3 | 0 write database connection class |

Ordinary version

MysqlHelper.py

From pymysql import connect,cursorsclass MysqlHelper: def _ init__ (self, host= "127.0.0.1", user= "root", password= "123456", database= "itcast", charset='utf8' Port=3306): self.host = host self.port = port self.user = user self.password = password self.database = database self.charset = charset self._conn = None self._cursor = None def _ open (self): # print ("connection is open") self._conn = connect (host=self.host, port=self.port, user=self.user, password=self.password Database=self.database, charset=self.charset) self._cursor = self._conn.cursor (cursors.DictCursor) def _ close (self): # print ("connection closed") self._cursor.close () self._conn.close () def one (self, sql, params=None): result: tuple = None try: self._open () self._cursor.execute (sql Params) result = self._cursor.fetchone () except Exception as e: print (e) finally: self._close () return result def all (self, sql, params=None): result: tuple = None try: self._open () self._cursor.execute (sql Params) result = self._cursor.fetchall () except Exception as e: print (e) finally: self._close () return result def exe (self, sql, params=None): try: self._open () self._cursor.execute (sql, params) self._conn.commit () except Exception as e: print (e) finally: self._close ()

This class encapsulates fetchone, fetchall and execute, eliminating the opening and closing of database connections and the opening and closing of cursors.

The following code is a small example of calling this class:

From MysqlHelper import * mysqlhelper = MysqlHelper () ret = mysqlhelper.all ("select * from student") for item in ret: print (item) "{'id': 1,' name': 'Zhang San', 'age': 18,' sex': b'\ x01'} {'id': 2,' name':'Li Si', 'age': 19,' sex': b'\ x00'} {'id': 3,' name': 'Wang Wu', 'age': 20' 'sex': b'\ x01'} {' id': 5, 'name':' waiter, 'age': 28,' sex': b'\ x01'} {'id': 6,' name': 'Wa,' age': 28, 'sex': b'\ x01'} {'id': 7,' name': 'Wa,' age': 28 'sex': b'\ x01'} "" context Manager version mysql_with.pyfrom pymysql import connect, cursorsclass DB: def _ _ init__ (self, host='localhost', port=3306, db='itcast', user='root', passwd='123456', charset='utf8'): # establish connection self.conn = connect (host=host, port=port, db=db, user=user Passwd=passwd, charset=charset) # create cursors Operation is set to dictionary type self.cur = self.conn.cursor (cursor=cursors.DictCursor) def _ _ enter__ (self): # return cursor return self.cur def _ _ exit__ (self, exc_type, exc_val, exc_tb): # submit database and execute self.conn.commit () # close cursor self.cur.close () # close database connection self.conn.close ()

How to use:

From mysql_with import DBwith DB () as db: db.execute ("select * from student") ret = db.fetchone () print (ret) "" {'id': 1,' name': 'Zhang San', 'age': 18,' sex': b'\ x01'} "" above is how to use pymysql module in python to connect to mysql database. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.