Python betreibt MySQL, um Daten einzufügen

Ich habe bereits einen Artikel über das Abfragen von MySQL-Daten in Python geschrieben. Heute werde ich über das Einfügen von Daten in eine MySQL-Datenbank über Python schreiben.

Empfohlene verwandte MySQL-Video-Tutorials: „MySQL-Tutorial“ Anweisungen unter Python zum Testen einfügen

Skript einfügen

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;

Skript ausführen

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

Abfrageüberprüfung

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

Testdaten

[root@python ~]# python mysql_insert.py

Das obige ist der detaillierte Inhalt vonPython betreibt MySQL, um Daten einzufügen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!