Python operation MySQL Insert data

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

1. Build a library, build a table, build a user

mysql> create database top_ten;mysql> use top_tenmysql> 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 (one)      | NO    | pri | null    | auto_increment | |  ip     | char (20)     | YES  |     | NULL     |                | |  url    | char ()     | YES  |      | NULL    |                 | |  status | int (one)      | YES  |      | NULL    |                 | |  total  | int (one)      | YES  |      | NULL    |                 |+--------+-------------+------+-----+---------+----------------+mysql> grant all on top_ten.* to [email  protected] identified by  ' talent ';mysql> flush privileges;

2. Insert a statement under Python to do a 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>>> sql =  "Insert into log (Ip, url, status,  total)  values (' 2.2.2.2 ',  ' http ',  ',  ') ">>> cursor.execute (SQL) 1l# can only query one result >>> cursor.execute (' Select * from log ') 1l>>>  Cursor.fetchone () (1l, u ' 1.1.1.1 ',  u ' http ',  200l, 66l) #查询所有数据, then 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))

3. Inserting a script

[[email protected] ~]# 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 ()

4. Execute the Script

[email protected] ~]# python mysql_insert.py

5. 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)

6. 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 "

This article is from "Kaka West" blog, please be sure to keep this source http://whnba.blog.51cto.com/1215711/1911066

Python operation MySQL Insert data