Python operation MySQL Insert data

Previously wrote a python query MySQL data article, today writes through Python inserts the data to the MySQL database.

First build the library, build the table, build the user

mysql> create database Top_ten; mysql> use Top_ten mysql> CREATE table Log (id int PRIMARY KEY auto_increment, IP char (+), url char (+), 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 statement under Python to test

>>> 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 ', ' $ ', ' $ ') ' >>> cursor.execute (SQL) 1L >&gt ;> sql = "INSERT into log (IP, URL, status, total) values (' 2.2.2.2 ', ' http ', ' $ ', ' $ ')" >>> cursor.execute (sq L) 1L #只能查询一条结果 >>> cursor.execute (' select * from log ') 1l>>> cursor.fetchone (1L, U ' 1.1.1.1 ', U ' http ') , 200L, 66L) #查询所有数据, then strips get results >>> 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 () () #查询所有数据, a tuple displays all results >>> 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)) 

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 Opera tion cursor cursor = db.cursor () with open (' access_log-20170217 ', ' R ') as F:res = {} #Get IP, u RL, status for line in F.readlines (): line = Line.split (") IP = line[0] url = li            NE[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) fo        R 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 to 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 t He database connection Db.close () if __name__ = = ' __main__ ': Mysql_insert ()

Execute script

[Root@python ~]# python mysql_insert.py

Query validation

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" 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 2 "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 " 245 "http://10.200.42.52/" "mozilla/5.0 (Windows NT 6.1; WOW64; rv:51.0) gecko/20100101 firefox/51.0 "