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

大安ssl適用于網(wǎng)站、小程序/APP、API接口等需要進行數(shù)據(jù)傳輸應用場景，ssl證書未來市場廣闊！成為創(chuàng)新互聯(lián)的ssl證書銷售渠道，可以享受市場價格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：028-86922220（備注：SSL證書合作）期待與您的合作！

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 #只能查詢一條結(jié)果 >>>?cursor.execute('select?*?from?log') 1L >>>?cursor.fetchone() (1L,?u'1.1.1.1',?u'http',?200L,?66L) #查詢所有數(shù)據(jù)，然后一條條獲取結(jié)果 >>>?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() () #查詢所有數(shù)據(jù)，一個元組顯示所有結(jié)果 >>>?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.執(zhí)行腳本

[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.測試數(shù)據(jù)

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"

另外有需要云服務器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。

本文標題：Python操作mysql之插入數(shù)據(jù)-創(chuàng)新互聯(lián)
當前URL：http://jinyejixie.com/article34/ccpope.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站策劃、網(wǎng)站維護、營銷型網(wǎng)站建設、定制開發(fā)、商城網(wǎng)站、域名注冊

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)