之前有写过一篇python查询mysql数据的文章，今天写通过python插入数据到mysql数据库。
先建库，建表，建用户
mysql> create database top_ten;
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;
在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))
插入脚本
[root@python ~]# 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.items()]
# 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
#close the cursor
cursor.close()
#close the database connection
db.close()
if __name__ == ‘__main__’:
mysql_insert()
执行脚本
[root@python ~]# python mysql_insert.py
查询验证
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)
测试数据
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”
以上就是python操作mysql之插入数据 的详细内容，更多请关注 第一php社区 其它相关文章！