python往mysql存入数据_Python操作mysql之插入数据

之前有写过一篇python查询mysql数据的文章，今天写通过python插入数据到mysql数据库。

相关mysql视频教程推荐：《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 '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;

在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))

插入脚本[root@python ~]# 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()

执行脚本[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中文网其它相关文章！

本文原创发布php中文网，转载请注明出处，感谢您的尊重！