I have previously written an article on querying mysql data in python. today, I want to insert data to the mysql database using python. 1. create a database First, create a table, and create the user mysql & gt; create database top_ten; mysql & gt; use top_tenmysql & gt ;.. I have previously written an article on querying mysql data in python. today, I want to insert data to the mysql database using python.
Create a database, a table, and a user
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;
Insert a statement in python for testing.
>>> 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', '123', '66') "> cursor.exe cute (SQL) 1L> SQL =" insert into log (ip, url, status, total) values ('2. 2.2.2 ', 'http', '123', '66') "> cursor.exe cute (SQL) 1L # Only one result can be queried >>> cursor.exe cute ('select * from log') 1L >>> cursor. fetchone () (1L, u'1. 1.1.1 ', u'http', 200L, 66L) # Query all the data and obtain results one by one >>> cursor.exe cute ('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. fetchiterator () # Query all data. a single tuple displays all results >>> cursor.exe cute ('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 ))
Insert script
[root@python ~]# mysql_insert.py#!/usr/bin/env python# -*- coding: utf-8 -*-'''Date:2017-03-28Author: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()
Execute scripts
[root@python ~]# python mysql_insert.py
Query verification
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)
Test data
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"
The preceding section describes how to insert data to mysql using Python. For more information, see other related articles in the first PHP community!