之前有写过一篇 python 查询 mysql 数据的文章,今天写通过 python 插入数据到 mysql 数据库。
1. 先建库,建表,建用户
- 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 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 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. 插入脚本
- [ ~]# 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()
4. 执行脚本
- [ ~]# 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=/ | 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"
来源: http://www.bubuko.com/infodetail-1999173.html