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 &＃39;bob&＃39;@&＃39;10.200.42.52&＃39; identified by &＃39;talent&＃39;;
 
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 &＃39;talent&＃39;;
 
mysql> flush privileges;

在python下插入语句做下测试

>>> import MySQLdb
 
>>> db = MySQLdb.connect(host=&＃39;localhost&＃39;,user=&＃39;bob&＃39;,passwd=&＃39;talent&＃39;,db=&＃39;top_ten&＃39;,port=3306, charset=&＃39;utf8&＃39;)
>>> db.autocommit(True)
>>> cursor = db.cursor()
 
>>> sql = "insert into log(ip, url, status, total) values(&＃39;1.1.1.1&＃39;, &＃39;http&＃39;, &＃39;200&＃39;, &＃39;66&＃39;)"
>>> cursor.execute(sql)
1L
 
>>> sql = "insert into log(ip, url, status, total) values(&＃39;2.2.2.2&＃39;, &＃39;http&＃39;, &＃39;200&＃39;, &＃39;66&＃39;)"
>>> cursor.execute(sql)
1L
 
#只能查询一条结果
>>> cursor.execute(&＃39;select * from log&＃39;)
1L
>>> cursor.fetchone()
(1L, u&＃39;1.1.1.1&＃39;, u&＃39;http&＃39;, 200L, 66L)
 
 
#查询所有数据，然后一条条获取结果
>>> cursor.execute(&＃39;select * from log&＃39;)
2L
>>> cursor.fetchmany()
((1L, u&＃39;1.1.1.1&＃39;, u&＃39;http&＃39;, 200L, 66L),)
>>> cursor.fetchmany()
((2L, u&＃39;2.2.2.2&＃39;, u&＃39;http&＃39;, 200L, 66L),)
>>> cursor.fetchmany()
()
 
#查询所有数据，一个元组显示所有结果
>>> cursor.execute(&＃39;select * from log&＃39;)
2L
>>> cursor.fetchall()
((1L, u&＃39;1.1.1.1&＃39;, u&＃39;http&＃39;, 200L, 66L), (2L, u&＃39;2.2.2.2&＃39;, u&＃39;http&＃39;, 200L, 66L))

插入脚本

[root@python ~]# mysql_insert.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
&＃39;&＃39;&＃39;
Date:2017-03-28
Author:Bob
&＃39;&＃39;&＃39;
  
import MySQLdb
  
def mysql_insert():
  
    #Open the database connection
    db = MySQLdb.connect(host=&＃39;localhost&＃39;,user=&＃39;bob&＃39;,passwd=&＃39;talent&＃39;,db=&＃39;top_ten&＃39;,port=3306, charset=&＃39;utf8&＃39;)
     
    #Automatic submission
    db.autocommit(True)
 
    #Gets the operation cursor
    cursor = db.cursor()
 
     
    with open(&＃39;access_log-20170217&＃39;, &＃39;r&＃39;) as f:
        res = {}
        #Get ip, url, status
        for line in f.readlines():
            line = line.split(&＃39; &＃39;)
            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(&＃39;%s&＃39;, &＃39;%s&＃39;, &＃39;%s&＃39;, &＃39;%s&＃39;)" %(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__ == &＃39;__main__&＃39;:
    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社区 其它相关文章！