Python utilise MySQL pour insérer des données

J'ai déjà écrit un article sur l'interrogation de données MySQL en Python. Aujourd'hui, je vais écrire sur l'insertion de données dans une base de données MySQL via Python.

Tutoriels vidéo mysql associés recommandés : "tutoriel mysql" Insérer des instructions sous python pour les tests

Insérer un script

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;

Exécuter le script

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

Vérification des requêtes

[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()

Données de test

[root@python ~]# python mysql_insert.py

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!