Python_Note_Day 11_Mysql02

Python_Note_Day 11_Mysql02_Python MySQL API

一、插入数据

 1 import MySQLdb
 2 
 3 # 创建连接   
 4 conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')        
 5 # 创建游标
 6 cur = conn.cursor()        
 7 
 8 # 执行SQL，并返回收影响行数  
 9 reCount = cur.execute('insert into UserInfo(Name,Address) values(%s,%s)',('alex','usa'))
10 # reCount = cur.execute('insert into UserInfo(Name,Address) values(%(id)s, %(name)s)',{'id':12345,'name':'wupeiqi'})
11 
12 # 提交，不然无法保存新建或者修改的数据  
13 conn.commit()
14 
15 # 关闭游标  
16 cur.close()
17 # 关闭连接
18 conn.close()
19   
20 print reCount

View Code

插入批量数据

 1 import MySQLdb
 2 
 3 conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
 4 cur = conn.cursor()
 5 
 6 li =[
 7      ('alex','usa'),
 8      ('sb','usa'),
 9 ]
10 reCount = cur.executemany('insert into UserInfo(Name,Address) values(%s,%s)',li)        #excutemany是执行多条SQL，并返回受影响行数
11 
12 
13 conn.commit()
14 
15 cur.close()
16 conn.close()
17 
18 print reCount

View Code

注意：cur.lastrowid

二、删除数据

 1 import MySQLdb
 2  
 3 conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
 4  
 5 cur = conn.cursor()
 6  
 7 reCount = cur.execute('delete from UserInfo')
 8  
 9 conn.commit()
10  
11 cur.close()
12 conn.close()
13  
14 print reCount

View Code

三、修改数据

 1 import MySQLdb
 2  
 3 conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
 4  
 5 cur = conn.cursor()
 6  
 7 reCount = cur.execute('update UserInfo set Name = %s',('alin',))
 8  
 9 conn.commit()
10 cur.close()
11 conn.close()
12  
13 print reCount

View Code

注：commit（）是在插入、删除和修改是才要使用，即更新的时候。（查询不需要）

四、查数据

fetchone/fetchmany(num)

 1 import MySQLdb
 2  
 3 conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
 4 cur = conn.cursor()
 5  
 6 reCount = cur.execute('select * from UserInfo')
 7  
 8 print cur.fetchone()
 9 print cur.fetchone()
10 cur.scroll(-1,mode='relative')
11 print cur.fetchone()
12 print cur.fetchone()
13 cur.scroll(0,mode='absolute')
14 print cur.fetchone()
15 print cur.fetchone()
16  
17 cur.close()
18 conn.close()
19  
20 print reCount
21

View Code

fetchall

 1 import MySQLdb
 2  
 3 conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')
 4 #cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
 5 cur = conn.cursor()
 6  
 7 reCount = cur.execute('select Name,Address from UserInfo')
 8  
 9 nRet = cur.fetchall()
10  
11 cur.close()
12 conn.close()
13  
14 print reCount
15 print nRet
16 for i in nRet:
17     print i[0],i[1]

View Code

五、事务-回滚

在程序没有提交（commit()）前代码出现错误，程序会自动回滚。不需要手动（roolback()）回滚。

六、其他

查询时，获取数据使用字典：

 1 import MySQLdb
 2 
 3 conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='Tl198312!',db='mydb')
 4 
 5 cur = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)        #将输出的数据改为字典形式输出。
 6 
 7 reCount = cur.execute('select * from students')
 8 
 9 #print (cur.fetchone())
10 print(cur.fetchall())
11 
12 cur.close()
13 conn.close()
14 
15 print (reCount)

View Code