以下demo均以python2中的mysqldb模块
一、插入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
import MySQLdb
conn = MySQLdb.connect(host = '127.0.0.1' ,user = 'root' ,passwd = '1234' ,db = 'mydb' )
cur = conn.cursor()
reCount = cur.execute( 'insert into UserInfo(Name,Address) values(%s,%s)' ,( 'alex' , 'usa' ))
# reCount = cur.execute('insert into UserInfo(Name,Address) values(%(id)s, %(name)s)',{'id':12345,'name':'wupeiqi'}) conn.commit() cur.close() conn.close() print reCount
|
import MySQLdb conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='',db='mydb') cur = conn.cursor() li =[
('alex','usa'),
('sb','usa'),
]
reCount = cur.executemany('insert into UserInfo(Name,Address) values(%s,%s)',li) conn.commit()
cur.close()
conn.close() print reCount 批量插入数据
批量插入数据
二、删除数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
import MySQLdb
conn = MySQLdb.connect(host = '127.0.0.1' ,user = 'root' ,passwd = '1234' ,db = 'mydb' )
cur = conn.cursor()
reCount = cur.execute( 'delete from UserInfo' )
conn.commit() cur.close() conn.close() print reCount
|
三、修改数据
1
2
3
4
5
6
7
8
9
10
11
12
13
|
import MySQLdb
conn = 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
|
四、查数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
# ############################## fetchone/fetchmany(num) ############################## import MySQLdb
conn = MySQLdb.connect(host = '127.0.0.1' ,user = 'root' ,passwd = '1234' ,db = 'mydb' )
cur = conn.cursor()
reCount = cur.execute( 'select * from UserInfo' )
print cur.fetchone()
print cur.fetchone()
cur.scroll( - 1 ,mode = 'relative' )
print cur.fetchone()
print cur.fetchone()
cur.scroll( 0 ,mode = 'absolute' )
print cur.fetchone()
print cur.fetchone()
cur.close() conn.close() print reCount
# ############################## fetchall ############################## import MySQLdb
conn = MySQLdb.connect(host = '127.0.0.1' ,user = 'root' ,passwd = '1234' ,db = 'mydb' )
#cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor) cur = conn.cursor()
reCount = cur.execute( 'select Name,Address from UserInfo' )
nRet = cur.fetchall()
cur.close() conn.close() print reCount
print nRet
for i in nRet:
print i[ 0 ],i[ 1 ]
|