Shulou(Shulou.com)06/02 Report--

This article mainly introduces mysql client how to operate MySQL relational database, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Mainly explain the operation of mysqlclient MySQL relational database, install mysqlclient command line: pip install mysqlclient

Then create a database named XKD_Python_Course and a database table named students. We first check whether the table name exists in the command line tool. Log in to the command line of the mysql database: mysql-uroot-p, and then show databases;, finds that there is no XKD_Python_Course database, then we create one: create database XKD_Python_Course;, after creation: show databases Once, you can see that the XKD_Python_Course database already exists.

Then we can use the command line: use XKD_Python_Course; to enter the library and start creating database tables

CREATE TABLE `students` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (255) NOT NULL, `age` int (11) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

You can then view the creation of the creation table from the command line: show create table students;,

You can also view the structure of the table through the command line: desc students;,

At this point, we have created the database and database tables, and then we need to authorize the user, set the password, and be sure to refresh the permissions after the authorization is successful.

Grant all on XKD_Python_Course.* to 'zengzeng'@'%' identified by' 123456refresh permission flush privileges

After authorization, we can log out, and then log in to the user we just authorized: mysql-uzengzeng-p123456, and we can log in successfully. After logging in, we can show databases; to view the database we just created.

Then we can see that there are two databases inside. If we want to operate XKD_Python_Cours, then we need to use: use XKD_Python_Course

Operate the database

There is a MySQLdb class in the mysqlclient we just installed, which we can use to manipulate the mysql database.

Insert operation. Before inserting, we can check whether this piece of data already exists in the data table: select * from students;, and then we can insert it with confidence.

Import MySQLdbconnect = None # connection object cursor = None # Vernier object try: # connection object connect = MySQLdb.connect (host='localhost', # host address user='zengzeng', # account password='123456' # password database='XKD_Python_Course', # Database name use_unicode=True Charset='utf8') # specify character set # Vernier object cursor = connect.cursor () # call cursor () except Exception as e: print (e) connect.close () try: if cursor: result = cursor.execute ("insert into students (name, age) values ('Angle') 18) ") # insert operation print ('result = {}' .format (result)) connect.commit () # submit except Exception as e: print (e) connect.rollback () # rollback finally: if cursor: cursor.close () if connect: connect.close ()

When the code is executed, the returned result is result = 1, indicating that a row of data has been operated. At this time, we check the database table select * from students;, to see if there is an extra piece of data in the table.

To insert data in bulk is to add a for loop where the data is inserted. Let's try inserting 10 pieces of data at a time.

Import MySQLdbconnect = None # connection object cursor = None # Vernier object try: # connection object connect = MySQLdb.connect (host='localhost', # host address user='zengzeng', # account password='123456' # password database='XKD_Python_Course', # Database name use_unicode=True Charset='utf8') # specify character set # Vernier object cursor = connect.cursor () # call cursor () except Exception as e: print (e) connect.close () try: if cursor: for i in range (10): result = cursor.execute ("insert into students (name, age) values ('Angle') {}) ".format (I) # insert operation connect.commit () # submit except Exception as e: print (e) connect.rollback () # rollback finally: if cursor: cursor.close () if connect: connect.close ()

Query the data, and the id of the query result will go down with the cursor

Import MySQLdbfrom pprint import pprint # newline connect = None # connection object cursor = None # Vernier object try: # connection object connect = MySQLdb.connect (host='localhost', # host address user='zengzeng', # account password='123456' # password database='XKD_Python_Course', # Database name use_unicode=True Charset='utf8') # specified character set # Vernier object cursor = connect.cursor () # calling cursor () except Exception as e: print (e) connect.close () try: if cursor: cursor.execute ('select * from students') # through a connection object will not return any object To get the data, you can use cursor. Method name one_result = cursor.fetchone () # next data of the query result set many_result = cursor.fetchmany (5) # next five data of the query result set all_result = cursor.fetchall () # all remaining data of the query result set # Wrap print pprint (one_result) print ('*'* 100) Pprint (many_result) print ('*'* 100) pprint (all_result) connect.commit () # submit except Exception as e: print (e) connect.rollback () # rollback finally: if cursor: cursor.close () if connect: connect.close ()

Cursor

Cursor is a cursor object that is used to execute queries and get results

Methods supported by cursor cursors

Execute (op [, args]): execute queries and commands for a database

Fetchmany (size): the next few lines to get the result set

Fetchone (): gets the next row of the result set

Fetchall (): get all the remaining rows in the result set

Rowcount (): the number of rows returned or affected by the last execute

Close (): closes the cursor object

Query parameterization

When using subqueries, we can use query parameters

# location parameter cursor.execute ('select * from students where id =% slots, args= (10,)) # keyword parameter cursor.execute (' select * from students where id =% (id) slots, args= {'id': 10}) uses context management

Automatically closes the cursor

Import MySQLdbconnect = MySQLdb.connect (host='localhost', user='zengzeng', password='123456', database='XKD_Python_Course', use_unicode=True Charset='utf8') with connect as cursor: # automatically closes the cursor object cursor.execute ("insert into students (name, age) values ('zengzeng', 22)") # while the connection has not closed the cursor.execute ("insert into students (name, age) values (' Mark', 23)") connect.close () Queue module

The Queue module implements multi-producer and multi-consumer queues, which is especially suitable for multithreaded programming. All the required lock primitives are implemented in the Queue class, and the Queue module implements three types of queues:

First, FIFO (first-in, first-out) queue, the first task to join the queue is taken out by the first one.

Second, the LIFO (last-in-first-out) queue, the last task to join the queue is taken out by the first (the operation is similar to the stack, it is always taken out from the top of the stack, and the internal implementation of this queue is not clear)

The third is the PriorityQueue (priority) queue, which keeps the queue data in order and the minimum value is taken out first.

The difference between Queue in queue module and Queue in multiprocessing module

Queue in queue module: normal queue mode, FIFO mode. Get method blocks requests until data get is available, which is suitable for multithreaded scenarios.

From threading import Thread, Eventfrom queue import Queueimport timedef write (Q: Queue, e: Event): for value in range: print ('put {} to queue'.format (value)) q.put (value) time.sleep (0.5) else: e.set () def read (Q: Queue E: Event): while True: if not q.empty () or not e.is_set (): value = q.get () print ('get {} from queue'.format (value)) time.sleep (1) else: breakif _ _ name__ = =' _ main__': Q = Queue () e = Event () tw = Thread (target=write, args= (Q) E)) tr = Thread (target=read, args= (QMague)) tw.start () tr.start () tw.join () tr.join () print ('finished')

The Queue of the multiprocessing module is a concurrent Queue queue for multiple processes, which is used to solve the communication problems between multiple processes.

From multiprocessing import Process,Queue, Eventimport timedef write (Q: Queue, e: Event): for value in range: print ('put {} to queue'.format (value)) q.put (value) time.sleep (0.5) else: e.set () def read (Q: Queue E: Event): while True: if not q.empty () or not e.is_set (): value = q.get () print ('get {} from queue'.format (value)) time.sleep (1) else: breakif _ _ name__ = =' _ main__': Q = Queue () e = Event () pw = Process (target=write, args= (Q) E)) pr = Process (target=read, args= (QMague)) pw.start () pr.start () pw.join () pr.join () print ('finished') Queue queue object method

Qsize (): returns the approximate size of the queue

Empty (): determines whether the queue is empty, and returns True if the queue is empty, and vice versa False

Full (): judge whether it is full

Put (): put items in the queue

Put_nowait: equivalent to put (item, False)

Get (): removes from the queue and returns an item

Get_nowait (): provides two methods to support tracking whether the daemon consumer thread has fully handled the queuing task

Task_done (): indicates that the previously queued task has been completed

Join (): block until all items in the queue have been acquired and processed

Use Queue to build a connection pool from queue import Queueimport MySQLdbclass ConnectPool: def _ init__ (self, size=5, * args, * * kwargs): if not isinstance (size, int) or size < 1: size= 10 self.__pool = Queue (size) for i in range (size): self.__pool.put (MySQLdb.connect (* args) * * kwargs) @ property def connect (self): return self.__pool.get () @ connect.setter def connect (self, conn): self.__pool.put (conn) if _ _ name__ ='_ _ main__': # build connection pool pool = ConnectPool (host='localhost', user='zengzeng', password='123456' Database='XKD_Python_Course', use_unicode=True Charset='utf8') # get a connection connect = pool.connect # with connect as cursor: with cursor: sql = 'select * from students' cursor.execute (sql) print (cursor.fetchall ()) thread connection pool to implement from queue import Queueimport MySQLdbimport threadingclass ConnectPool: def _ init__ (self, size=5, * args) * * kwargs): if not isinstance (size, int) or size < 1: size = 10 self.__pool = Queue (size) for i in range (size): self.__pool.put (MySQLdb.connect (* args) * * kwargs)) # create a local object self.local = threading.local () @ property def connect (self): return self.__pool.get () @ connect.setter def connect (self, conn): self.__pool.put (conn) def _ _ enter__ (self): if getattr (self.local, 'conn') None) is None: self.local.conn = self.connect return self.local.conn.cursor () def _ _ exit__ (self * exc_info): if exc_info: self.local.conn.rollback () else: self.local.conn.commit () # return the connection object to the connection pool self.connect = self.local.conn # connection object reference calculator at thread level minus one self.local.conn = Noneif _ _ name__ ='_ _ main__': pool = ConnectPool (host='localhost' User='zengzeng', password='123456', database='XKD_Python_Course', use_unicode=True Charset='utf8') def foo (pool): with pool as cursor: with cursor: # context management for cursor sql = 'select * from students' cursor.execute (sql) print (cursor.fetchall ()) for i in range (5): t = threading.Thread (target=foo, args= (pool) () t.start () Thank you for reading this article carefully I hope the article "mysql client how to operate MySQL Relational Database" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.