Python operates the insert data of mysql
I wrote an article about python querying mysql data before, and today I write about inserting data into mysql database through python.
1. Build database, table and user first.
Mysql > CREATE DATABASE tda DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;mysql > use top_tenmysql > 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
two。 Insert a statement under python to do the 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', '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# can only query one result > cursor.execute ('select * from log') 1L > cursor.fetchone () (1L, upright 1.1.1.1, utilisation, 200L, 66L) # query all data Then get the results one by one > cursor.execute ('select * from log') 2L > cursor.fetchmany () ((1L, upright 1.1.1.1, utilising httptiles, 200L, 66L),) > cursor.fetchmany () ((2L, uplink 2.2.2.2, utilising httptiles, 200L, 66L),) > cursor.fetchmany () () # query all data A tuple shows all the results > cursor.execute ('select * from log') 2L > cursor.fetchall () ((1L, upright 1.1.1.1percent, upright https, 200L, 66L), (2L, uplink 2.2.2.2 percent, upright https, 200L, 66L)
3. Insert script
[root@python ~] # cat mysqlholders insert. Pyknotes import MySQLdb def mysql_insert import MySQLdb def mysql_insert (): # Open the database connection db= MySQLdb.connect (): # 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 in res.iteritems v in res.iteritems () # Print the top ten lines # for k in sorted (res_list,key=lambda XRX [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') '% sails,'% sails,'% s') "% (I [0], I [1], I [2], I [3]) try: # Execute the SQL statement cursor.execute (sql) except Exception as e: print" Error: " E # Commit db.commit () # Close the cursor cursor.close () # Close the database connection db.close () if _ _ name__ = ='_ main__': mysql_insert ()
4. Execute script
[root@python ~] # python mysql_insert.py
5. Query verification
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 | 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 | | | 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 "200229" 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 "200245" http://10.200.42.52/"Mozilla/5.0 (Windows NT 6.1; WOW64; rv:51.0) Gecko/20100101 Firefox/51.0"
