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

This article mainly explains "how to operate pymysql database by python". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how python operates the pymysql database.

First, install pip install pymysql second, connect to the database

Three ways to connect to the database

Import pymysql# mode 1: conn = pymysql.connect ('localhost',' root',' root') # mode 2 Conn = pymysql.connect (host='127.0.0.1', port=3306, user='root', passwd='root', db='', charset='utf8') # mode 3 config = {'host':' 127.0.0.1 "root','passwd':" root' 'charset':' utf8'} conn = pymysql.connect (* * config) 3. Create a database

Create a test database and enter the

Import pymysqldb = pymysql.connect ("localhost", "root", "root", "test") # create a cursor object using the cursor () method cursorcursor = db.cursor () cursor.execute ('DROP DATABASE IF EXISTS test') cursor.execute (' CREATE DATABASE IF NOT EXISTS test') conn.select_db ('test') 4. Create a data table

Create a user table

Import pymysqldb = pymysql.connect ("localhost", "root", "root", "test") cursor = db.cursor () cursor.execute ('CREATE TABLE user (id int primary key,name varchar (30))') 5. Insert a piece of data import pymysqldb = pymysql.connect ("localhost", "root", "root", "test") cursor = db.cursor () try:# execute SQL statement sql = 'INSERT INTO user values ("% d", "% s")'% (1) "autofelix") cursor.execute (sql) # rollback db.rollback () finally:# when error occurs when submitting to database execution db.commit () except:# close cursor connection cursor.close () # close database connection conn.close () VI. Insert multiple pieces of data import pymysqldb = pymysql.connect ("localhost", "root", "root", "test") cursor = db.cursor () try:# execute SQL statement values = [(1, 'autofelix')) (2, 'flying rabbit')] cursor.executemany ('INSERT INTO user values (% s)', values) # rollback db.rollback () finally:# when an error occurs when it is submitted to the database to execute db.commit () except:#. Close the cursor connection cursor.close () # close the database connection conn.close () 7. Data statistics import pymysqldb = pymysql.connect ("localhost", "root", "root") "test") cursor = db.cursor () count = cursor.execute ('SELECT * FROM user') # Total statistics print (' total records:% d'% count) # number of statistical fields print ('total records:', cursor.rowcount) 8, get table name information import pymysqldb = pymysql.connect ("localhost", "root", "root", "test") cursor = db.cursor () desc = cursor.descriptionprint ("% s% 3s"% (desc [0] [0]) Desc [1] [0]) IX. Get a single data

Use the fetchone method to get a single piece of data

Import pymysqldb = pymysql.connect ("localhost", "root", "root", "test") cursor = db.cursor () # use execute () method to execute SQL query cursor.execute ("SELECT VERSION ()") # use fetchone () method to get a single piece of data. Data = cursor.fetchone () print ("Database version:% s"% data) # close database connection db.close () 10, query multiple data import pymysqldb = pymysql.connect ("localhost") "root", "root", "test") cursor = db.cursor () cursor.execute ('SELECT * FROM user') results = cursor.fetchmany (5) for rin results:print (r) 11. Query all data import pymysqldb = pymysql.connect ("localhost", "root", "root", "test") cursor = db.cursor () cursor.execute (' SELECT * FROM user') results = cursor.fetchall () for rin results:print (r) 12, context management

It is troublesome to close the connection every time. Use context management to simplify the connection process.

Import pymysqlimport contextlib# definition context Manager Automatically close the connection after connection @ contextlib.contextmanagerdef mysql (host='127.0.0.1', port=3306, user='root', passwd='', db='test',charset='utf8'): conn = pymysql.connect (host=host, port=port, user=user, passwd=passwd, db=db) Charset=charset) cursor= conn.cursor (cursor=pymysql.cursors.DictCursor) try:yield cursorfinally:conn.commit () cursor.close () conn.close () # execute sqlwith mysql () as cursor:print (cursor) count = cursor.execute ("select * from user") row_1 = cursor.fetchone () print row_count, row_1 so far I believe you have a deeper understanding of "how python operates the pymysql database", so you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue 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.