Python converts JSON-formatted data to SQL statements for import into MySQL database

in the previous article, we saved the data crawled by the crawler in JSON format, but in order to be able to process the data more conveniently. We want to import this data into the MySQL database. phpMyAdmin can export data from a MySQL database to a JSON-formatted file, but cannot import a JSON-formatted file into a MySQL database. To achieve this goal, you can write a Python script to convert the JSON format data into an SQL statement to import the MySQL database.

JSON file T Encent.json part of the content:

{"Recruitnumber": "1", "name": "Sd10-fps Russian game overseas PM (Shenzhen)", "Detaillink": "Http://hr.tencent.com/position_detail.php?id= 9587&keywords=&tid=0&lid=0 "," Publishtime ":" 2013-11-13 "," Catalog ":" Product/Project Class "," Worklocation ":" Shenzhen "}
{"Recruitnumber": "2", "Name": "hy2-Interactive entertainment game Online Financial safety Operation Specialist (Shenzhen)", "Detaillink": "http://hr.tencent.com/position_detail.php ? id=9482&keywords=&tid=0&lid=0 "," Publishtime ":" 2013-11-28 "," Catalog ":" Product/Project Class "," Worklocation ":" Shenzhen "}

in the PHP MyAdmin To create the database and table structure:

CREATE DATABASE Itzhaopin;

CREATE TABLE IF not EXISTS ' Tencent ' (' id ' int (one) not null auto_increment, ' name ' varchar () default NULL, ' Catalo G ' varchar (+) default NULL, ' worklocation ' varchar (+) default NULL, ' recruitnumber ' varchar (+) default NULL, ' detail Link ' varchar (1024x768) default null, ' publishtime ' varchar (+) default NULL, PRIMARY KEY (' ID ')) Engine=myisam default CHA Rset=utf8 auto_increment=1;

writing a Python scriptjson2sql.pyTo convert the JSON format data to an SQL statement:

#-*-coding:utf-8-*-import jsondata = []with open (' Itzhaopin/tencent.json ') as F: For line in    F:        data.append (JSO N.loads (line)) #print json.dumps (data, ensure_ascii=false) str = "\ r \ n" for item in data:    #print json.dumps (item)    str = str + "INSERT into Tencent (Name,catalog,worklocation,recruitnumber,detaillink,publishtime) values"    str = str + "('%s ', '%s ', '%s ', '%s ', '%s ', '%s '); \ r \ n"% (item[' name '],item[' catalog '],item[' worklocation '],item[' Recruitnumber '],item[' detaillink '],item[' publishtime ']) Import codecsfile_object = Codecs.open (' Tencent.sql ', ' W ', " Utf-8 ") file_object.write (str) file_object.close () print" Success "

Run the python script. A file named Tencent.sql is generated under the current folder . When the file is imported and run in phpmyadmin , the data crawled by the crawler will be imported into the MySQL database.

Python converts JSON-formatted data to SQL statements for import into MySQL database