Python操作mysql之插入數據

之前有寫過一篇python查詢mysql數據的文章，今天寫通過python插入數據到mysql數據庫。

1.先建庫，建表，建用戶

mysql>?CREATE?DATABASE?tda?DEFAULT?CHARACTER?SET?utf8?COLLATE?utf8_general_ci;

mysql>?use?top_ten

mysql>?create?table?log?(id?int?PRIMARY?KEY??AUTO_INCREMENT,?ip?char(20),?url?char(30),?status?int,?total?int)?charset=utf8;

mysql>?create?user?'bob'@'10.200.42.52'?identified?by?'talent';

mysql>?desc?log;
+--------+-------------+------+-----+---------+----------------+
|?Field??|?Type????????|?Null?|?Key?|?Default?|?Extra??????????|
+--------+-------------+------+-----+---------+----------------+
|?id?????|?int(11)?????|?NO???|?PRI?|?NULL????|?auto_increment?|
|?ip?????|?char(20)????|?YES??|?????|?NULL????|????????????????|
|?url????|?char(30)????|?YES??|?????|?NULL????|????????????????|
|?status?|?int(11)?????|?YES??|?????|?NULL????|????????????????|
|?total??|?int(11)?????|?YES??|?????|?NULL????|????????????????|
+--------+-------------+------+-----+---------+----------------+


mysql>?grant?all?on?top_ten.*?to?bob@localhost?identified?by?'talent';

mysql>?flush?privileges;

2.在python下插入語句做下測試

>>>?import?MySQLdb

>>>?db?=?MySQLdb.connect(host='localhost',user='bob',passwd='talent',db='top_ten',port=3306,?charset='utf8')
>>>?db.autocommit(True)
>>>?cursor?=?db.cursor()

>>>?sql?=?"insert?into?log(ip,?url,?status,?total)?values('1.1.1.1',?'http',?'200',?'66')"
>>>?cursor.execute(sql)
1L

>>>?sql?=?"insert?into?log(ip,?url,?status,?total)?values('2.2.2.2',?'http',?'200',?'66')"
>>>?cursor.execute(sql)
1L

#只能查詢一條結果
>>>?cursor.execute('select?*?from?log')
1L
>>>?cursor.fetchone()
(1L,?u'1.1.1.1',?u'http',?200L,?66L)


#查詢所有數據，然后一條條獲取結果
>>>?cursor.execute('select?*?from?log')
2L
>>>?cursor.fetchmany()
((1L,?u'1.1.1.1',?u'http',?200L,?66L),)
>>>?cursor.fetchmany()
((2L,?u'2.2.2.2',?u'http',?200L,?66L),)
>>>?cursor.fetchmany()
()

#查詢所有數據，一個元組顯示所有結果
>>>?cursor.execute('select?*?from?log')
2L
>>>?cursor.fetchall()
((1L,?u'1.1.1.1',?u'http',?200L,?66L),?(2L,?u'2.2.2.2',?u'http',?200L,?66L))

3.插入腳本

[root@python?~]#?cat?mysql_insert.py
#!/usr/bin/env?python
#?-*-?coding:?utf-8?-*-
'''
Date:2017-03-28
Author:Bob
'''
?
import?MySQLdb
?
def?mysql_insert():
?
????#Open?the?database?connection
????db?=?MySQLdb.connect(host='localhost',user='bob',passwd='talent',db='top_ten',port=3306,?charset='utf8')
????
????#Automatic?submission
????db.autocommit(True)

????#Gets?the?operation?cursor
????cursor?=?db.cursor()

????
????with?open('access_log-20170217',?'r')?as?f:
????????res?=?{}
????????#Get?ip,?url,?status
????????for?line?in?f.readlines():
????????????line?=?line.split('?')
????????????ip?=?line[0]
????????????url?=?line[6]
????????????status?=?line[8]
????????????#print?ip,?url,?status
????????????#ip,?url,?status?as?key,?each?time?plus?1
????????????res[(ip,?url,?status)]?=?res.get((ip,?url,?status),0)+1
????#Generate?a?list
????res_list?=?[(k[0],k[1],k[2],v)?for?k,v?in?res.iteritems()]
????#?Print?the?top?ten?lines
????#for?k?in?sorted(res_list,key=lambda?x:x[3],reverse=True)[:10]:
????????#print?k


????#SQL?statement?inserted
????for?i?in?res_list:
????????#print?i
????????sql?=?"insert?into?log(ip,?url,?status,?total)?values('%s',?'%s',?'%s',?'%s')"?%(i[0],?i[1],?i[2],?i[3])

????????try:
????????????#Execute?the?SQL?statement
????????????cursor.execute(sql)
?????????
????????except?Exception?as?e:
????????????print?"Error:?",?e
?
???????#Commit
???????db.commit()
???????
????#Close?the?cursor
????cursor.close()

????#Close?the?database?connection
????db.close()
?
if?__name__?==?'__main__':
????mysql_insert()

4.執行腳本

[root@python?~]#?python?mysql_insert.py

5.查詢驗證

mysql>?select?*?from?log;
+----+----------------+---------------------------+--------+-------+
|?id?|?ip?????????????|?url???????????????????????|?status?|?total?|
+----+----------------+---------------------------+--------+-------+
|??1?|?1.1.1.1????????|?http??????????????????????|????200?|????66?|
|??2?|?2.2.2.2????????|?http??????????????????????|????200?|????66?|
|??3?|?10.200.56.80???|?/api/sshpasswd/???????????|????200?|?????1?|
|??4?|?10.201.201.82??|?/business/add?????????????|????200?|????20?|
|??5?|?10.200.56.80???|?/?????????????????????????|????403?|?????1?|
|??6?|?10.200.56.80???|?/account/login?next=%2F???|????200?|?????1?|
|??7?|?10.200.56.80???|?/icons/apache_pb.gif??????|????200?|?????1?|
|??8?|?10.200.56.80???|?/icons/unknown.gif????????|????200?|?????1?|
|??9?|?127.0.0.1??????|?/?????????????????????????|????403?|?????1?|
|?10?|?10.200.56.80???|?/account/login_auth???????|????200?|?????1?|
|?11?|?10.200.56.80???|?/static/js/echarts.min.js?|????304?|?????1?|
|?12?|?10.200.56.80???|?/business/collist?????????|????200?|?????2?|
|?13?|?10.200.56.80???|?/business/chlist??????????|????200?|?????1?|
|?14?|?10.200.56.80???|?/?????????????????????????|????200?|?????1?|
|?15?|?10.200.56.80???|?/icons/text.gif???????????|????200?|?????1?|
|?16?|?10.200.56.80???|?/icons/poweredby.png??????|????200?|?????1?|
|?17?|?10.200.42.50???|?/host/addscan?????????????|????200?|?????1?|
|?18?|?10.200.56.80???|?/icons/blank.gif??????????|????200?|?????1?|
|?19?|?10.200.56.80???|?/?????????????????????????|????302?|?????1?|
|?20?|?10.200.56.80???|?/icons/back.gif???????????|????200?|?????1?|
|?21?|?10.200.56.80???|?/account/is_activate??????|????200?|?????1?|
|?22?|?10.200.56.80???|?/favicon.ico??????????????|????404?|?????4?|
|?23?|?61.159.140.123?|?/favicon.ico??????????????|????404?|?????4?|
+----+----------------+---------------------------+--------+-------+
23?rows?in?set?(0.00?sec)

6.測試數據

61.159.140.123?-?-?[16/Feb/2017:14:45:39?+0800]?"GET?/api/sshpasswd/?HTTP/1.1"?200?1338?"-"?"Mozilla/5.0?(Windows?NT?6.1;?WOW64;?rv:51.0)?Gecko/20100101?Firefox/51.0"
61.159.140.123?-?-?[16/Feb/2017:14:45:39?+0800]?"GET?/icons/text.gif?HTTP/1.1"?200?229?"http://10.200.42.52/"?"Mozilla/5.0?(Windows?NT?6.1;?WOW64;?rv:51.0)?Gecko/20100101?Firefox/51.0"
61.159.140.123?-?-?[16/Feb/2017:14:45:39?+0800]?"GET?/icons/unknown.gif?HTTP/1.1"?200?245?"http://10.200.42.52/"?"Mozilla/5.0?(Windows?NT?6.1;?WOW64;?rv:51.0)?Gecko/20100101?Firefox/51.0"