Python MySQL API

1: Inserting data

ImportMySQLdb#Create a connected variableconn = MySQLdb.connect (host='127.0.0.1', user='Root', passwd='123456', db='MySQL')#Open Connection ChannelCur =conn.cursor ()#execute the EXECUTE statement, insert into a piece of datarecount = Cur.execute ('INSERT INTO UserInfo (name,address) VALUES (%s,%s)',('Caoxiaojian','CN'))#recount = Cur.execute (' INSERT into UserInfo (name,address) values (% (ID) s,% (Name) s) ', {' id ': 12345, ' Name ': ' Caoxiaojian '})#Data SubmissionConn.commit ()#Channel connection offcur.close ()#Database connection Shutdownconn.close ()PrintRecount

Insert multiple data at once

#!/usr/bin/env python# Coding:utf-8import mysqldbconn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' 123456 ', db= ' mydb ') cur = conn.cursor () # put data into a list of Li =[     (' CCC ', ' CN '),     GGG use]# to BULK insert multiple data executemany = Cur.executemany (' INSERT into UserInfo (name,address) VALUES (%s,%s) ', Li) conn.commit () Cur.close () conn.close () print Recount

2: Delete Data

#!/usr/bin/env python# coding:utf-8import mysqldb# FIRST CONNECTION Database conn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd = ' 123456 ', db= ' MyDB ') # Open connection Channel cur = conn.cursor () # Use Execute to execute command recount = Cur.execute (' Delete from UserInfo ') # Submit and Close Conn.commit () Cur.close () conn.close () print recount

3: Modify Data

#!/usr/bin/env python# Coding:utf-8import mysqldbconn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' 1234 ', db= ' mydb ') cur = conn.cursor () recount = Cur.execute (' update UserInfo set Name =%s ', (' Alin ',)) Conn.commit () Cur.close () Conn.close () Print recount

4: Querying data

is divided into two categories: one is Fetchone/fetchmany (num)

Instance

#!/usr/bin/env python# Coding:utf-8import mysqldbconn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' 1234 ', db= ' mydb ') cur = conn.cursor () recount = Cur.execute (' select * from UserInfo ') # Use Fetchone to fetch only one piece of data at a time, get the post pointer position to move, To the location of the next data print Cur.fetchone () print Cur.fetchone () # indicates that the country gets several print Cur.fetmany (2) # Scroll from the current position of the pointer to seek that is similar to the previous one, you can specify to navigate to a location # location is specified in two ways: relative position and absolute position # There is no egg to use, it is better to read directly from the database. Cur.scroll ( -1,mode= ' relative ') cur.scroll (0,mode= ' absolute ') cur.close () conn.close () print recount

Examples of Fetchall

#!/usr/bin/env python# Coding:utf-8import mysqldbconn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' 1234 ', db= ' MyDB ') # Use the following method to get the format of the data into a dictionary, which is originally a tuple form cur = conn.cursor (Cursorclass = MySQLdb.cursors.DictCursor) "" "gets the style [   {' id ': 1, ' name ': ' CCC '}] "" "cur = conn.cursor ()" "" gets the Style [    (1,CCC),    (2,GGG)] "" "Recount = Cur.execute (' Select name,address from UserInfo ') nret = Cur.fetchall () cur.close () conn.close () print recountprint nretfor i in Nret:    Print I[0],i[1]

Python MySQL API