Use mysql to record the http GET request data returned from the url

Business scenario requirements and implementation logic analysis

In business, we often encounter situations where we need to use HTTP GET to request data. For example, the result returned by the http request is as follows:

So, if we want to store this data in MySQL, how can we do it?

In fact, calling Python's httplib and MySQLdb packages will be very easy to implement. httplib is responsible for obtaining the return of the URL, and MySQLdb is responsible for operating the MySQL database. The following is a logical diagram of the relationship:

So, let's start code development:

Python dependency package preparation

Python needs to use at least the following packages. For the installation of MySQLdb dependency packages, please refer to Python installation of mysql dependency package mysql-python

# This is the python text editor interface #!/usr/bin/python
# coding=utf-8
import httplib
import json
import time
import MySQLdb

Get the URL request return using httplib

The httplib package supports the following methods and functions:

url = "http://www.testtesttest.com/mobile/kit?token=yyyyyyyyyy&key=tttttt&size=1" #Specific url link conn = httplib.HTTPConnection("www.testtesttest.com")
conn.request(method="GET", url=url) #Specify the GET method and url object response = conn.getresponse() #Create a response object res = response.read() #Read the content returned by url # Use the json.loads method to decode json into a python object json_repose = json.loads(res)
data = json_repose['data']

At this point, all the information returned by the http request is stored in the object res. Because the request returns a json string, we use the json.loads method to parse it.

It can be noticed that we finally store the json content returned by the request in the python object. Once the data is converted into an array or tuple, we can use python's own functions to parse it or perform other operations on it.

Parsing JSON returned by URL request with Python

# Here we define a function for parsing json
def data_list_analyze(i):
 data_dict = data[i]
 status = data_dict['status']
 devi_id = data_dict['devi_id']
 update_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(data_dict['update_time']))
 actived_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(data_dict['actived_time']))
 return devi_id, imei, device_type, actived_time

Python connects to the database and writes data

The following is an example of connecting to the database:

# Open the database connection, specify the database IP, username, password, and connection library name db = MySQLdb.connect("192.168.xxx.xxx", "db_user", "db_password", "db_database", charset='utf8')
# Use the cursor() method to get the operation cursor cursor = db.cursor()
# Create table sql statement sql = """create table if not exists `gergsmart_list`(
`devi_id` varchar(255) NOT NULL COMMENT 'IMEI, //Hardware device IMEI',
`imei` varchar(255) COMMENT 'ICCID, //Hardware device SIM card',
`device_type` varchar(255) COMMENT 'Device type',
`actived_time` datetime COMMENT 'First activation time',
PRIMARY KEY(`devi_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
"""
# Use the execute method to execute SQL statements and create tables cursor.execute(sql)
# Insert data sql statement insert_sql = "insert into `gergsmart_list` \
(devi_id,imei,device_type,actived_time) \
values ​​(%s, %s, %s, %s,)"

From the above, we can see that we can put the objects devi_id, imei, device_type, and actived_time parsed by python into the insert statement of mysql, so that we can achieve the operation of "getting data from url, storing it in python object, and then inserting python object into mysql record".

In summary, we can actually regard Python as a relay that receives URL request returns and writes them to MySQL. Among them, httplib is responsible for receiving operations, and MySQLdb is responsible for writing operations.

Supplement: mysql request timed out! What is the difference between extending to get and post requests? Super detailed! One article is enough to solve all the problems! !

Just change the database connection address from 127.0.0.1 to localhost! Proven and effective! !

After consulting a lot of information, I summarized the differences between get requests and post requests.

The following conclusions were made:

It is well known that the parameters of the get request are directly exposed on the URL, which has low security. The parameters of the post request are stored in the body, which is more secure. Next, let's look at a slightly more comprehensive explanation.

Let’s first look at the explanations of our predecessors.

1. What is the difference between get and post requests:

Get is to obtain data from the server, and post is to send data to the server.

Get adds the parameter data queue to the URL pointed to by the ACTION attribute of the submitted form. The value corresponds to each field in the form one by one and can be seen in the URL. Post uses the HTTP post mechanism to place the various fields in the form and their contents in the HTML HEADER and transmit them to the URL address pointed to by the ACTION attribute. This process is invisible to the user.

For the get method, the server uses Request.QueryString to obtain the value of the variable. For the post method, the server uses Request.Form to obtain the submitted data.

The amount of data transmitted by get is small and cannot be larger than 2KB. The amount of data transmitted by post is large and is generally assumed to be unrestricted. But in theory, the maximum amount is 80KB in IIS4 and 100KB in IIS5.

The security of get is very low, while the security of post is higher.

GET requests can be cached POST requests are not cached

GET requests are retained in the browser history. POST requests are not retained in the browser history.

GET requests can be bookmarked. POST requests cannot be bookmarked.

GET requests can only be url-encoded (application/x-www-form-urlencoded). POST supports multiple encodings (application/x-www-form-urlencoded or multipart/form-data. Multiple encodings are used for binary data.)

The most intuitive difference is that GET includes parameters in the URL, and POST passes parameters through the request body.

Next, let’s take a look at what the official statement is:

2. w3schools also distinguishes between the two and gives an official answer

GET is harmless if the browser falls back, while POST will submit the request again.

The URL generated by GET can be bookmarked, but POST cannot.

GET requests will be actively cached by the browser, but POST will not, unless manually set.

GET requests can only be url encoded, while POST supports multiple encoding methods.

The GET request parameters will be completely retained in the browser history, but the parameters in the POST will not be retained.

The parameters transmitted in the URL of a GET request are limited in length, but not in a POST request.

For parameter data types, GET only accepts ASCII characters, while POST has no restrictions.

GET is less secure than POST because the parameters are exposed directly in the URL and therefore cannot be used to pass sensitive information.

GET parameters are passed through the URL, and POST parameters are placed in the Request body.

GET generates one TCP packet; POST generates two TCP packets.

Make a summary based on the above and the information consulted

HTTP is a protocol based on TCP/IP that describes how data is communicated on the World Wide Web. The underlying layer of HTTP is TCP/IP. Therefore, the underlying layer of GET and POST is also TCP/IP, that is to say, GET/POST are both TCP links. GET and POST can do exactly the same thing. You need to add a request body to GET and URL parameters to POST, which is technically feasible.

Both post requests and get requests are HTTP request methods. There is no essential difference between them. The underlying implementation is based on the TCP/IP protocol. However, there are various ways to make requests, so HTTP divides and regulates the request methods, resulting in the division of labor and distinction between get and post request processing.

In addition, another difference was found: GET generates one TCP packet; POST generates two TCP packets. For GET requests, the browser will send the http header and data together, and the server will respond with 200 (return data). For POST, the browser will send the header first, the server will respond with 100 continue, the browser will then send the data, and the server will respond with 200 ok (return data).

GET and POST have their own semantics and cannot be mixed.

According to research, under good network conditions, the difference between the time it takes to send one packet and the time it takes to send two packets can be basically ignored. In poor network conditions, TCP with two packets has a great advantage in verifying the integrity of data packets.

Not all browsers send the packet twice in POST, Firefox only sends it once.

Summary:

"The maximum amount of data submitted by GET is 1024 bytes." Because GET submits data through a URL, the amount of data that GET can submit is directly related to the length of the URL. In fact, there is no problem with the upper limit of URL parameters, and the HTTP protocol specification does not limit the length of URLs. This limitation is a limitation imposed by specific browsers and servers. IE limits the URL length to 2083 bytes (2K+35). For other browsers, such as Netscape, FireFox, etc., there is no length limit in theory, and the limit depends on the support of the operating system. Note that this limit is the length of the entire URL, not just the length of your parameter value data.

Theoretically, there is no size limit for POST, and the HTTP protocol specification does not impose any size limit. It is inaccurate to say that "there is a size limit of 80K/100K for POST data". There is no limit on POST data. What limits it is the processing power of the server's processing program. For ASP programs, there is a 100K data length limit when the Request object processes each form field. But if you use Request.BinaryRead, there is no such limitation.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.