Python crawler Combat (3): Secure room production broker information collection

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/84/02/wKioL1eDSCSRqbyiAAAor3q_cpk181.jpg "title=" Python19.jpg "alt=" Wkiol1edscsrqbyiaaaor3q_cpk181.jpg "/>

1, Introduction
Python comes with a lightweight relational database, SQLite. This database uses the SQL language. As a back-end database, SQLite can be used with Python to build websites or to store data for Python crawlers. SQLite also has a wide range of applications in other fields, such as HTML5 and mobile.

python standard library provides an interface to the database.

2,python working with SQLite example

The following code creates a simple relational database that stores the classification and price of books for a bookstore. The database contains two tables: category is used for record classification, and book is used to record the information of a certain books. A book belongs to a category, so there is a foreign key (foreign key), which points to the primary key ID of the Catogory table.

650) this.width=650; "id=" aimg_1021 "src=" http://www.gooseeker.com/doc/data/attachment/forum/201606/28/ 145108w08yrorf2ess0yer.png "class=" Zoom "width=" 514 "height=" "style=" margin-top:10px; "alt=" 145108w08yrorf2ess0yer.png "/>

2.1 Creating a database

First, create the database, and the tables in the database. After you connect to the database using connect (), you can execute the SQL command by locating the pointer cursor:

import sqlite3# test.db is a file in  the working directory.conn = sqlite3.connect ("Test.db") C = conn.cursor () #  create tablesc.execute ("create table category      " (ID  int primary key, sort int, name text) C.execute ("' CREATE TABLE  book       (id int primary key,        sort int,       name text,        price real,       category int,        FOREIGN KEY  (category)  references category (ID)) #  save the changesconn.commit () # close the connection with the  Databaseconn.close () 

sqlite database is a file on disk, such as the above test.db, so the entire database can be easily moved or copied. Test.db does not exist at first, so SQLite will automatically create a new file.

takes the Execute () command, executes two SQL commands, and creates two tables in the database. After the creation is complete, save and disconnect the database.

2.2 Insert data

The database and table are created and the abstract structure of the database is established. The following will insert the data in the same database:

import sqlite3conn = sqlite3.connect ("test.db") c   = conn.cursor () books = [(1, 1,  ' cook recipe ',  3.12, 1),              (2, 3,  ' Python intro ',  17.5, 2),             (3, 2,  ' OS  intro ',  13.6, 2),           ]#  execute  "INSERT" C.execute ("insert into category values  (1, 1,  ' kitchen ')") #  using the placeholderc.execute ("insert into category values  (?,  ?, &NBSP,?) ",  [(2, 2,  ' computer ')]) # execute multiple commandsc.executemany (' INSERT  INTO book VALUES  (?,  ?, ?, ?, ?) ',  books) conn.commit () conn.close ()

Insert data You can also use Execute () to execute a full SQL statement. The arguments in the SQL statement, using the "?" As an alternative symbol and give a specific value in the following arguments. It is not possible to format strings in Python, such as "%s", because this usage is susceptible to SQL injection attacks.

can also use the Executemany () method to perform multiple insertions, adding multiple records. Each record is an element in the table, such as an element in the books table above.

2.3 query

Import sqlite3conn = Sqlite3.connect (' test.db ') c = conn.cursor () # Retrieve one recordc.execute (' SELECT name from category ORDER by sort ') print (C.fetchone ()) print (C.fetchone ()) # Retrieve all records as a listc.execute (' SELECT * from book WHERE Book.category=1 ') print (C.fetchall ()) # Iterate through the recordsfor row in C.execute (' SELECT name, price from book ORDER by sort '): print (ROW)

2.4 Update and delete

You can update a record, or delete a record:

conn = Sqlite3.connect ("test.db") C = conn.cursor () c.execute (' UPDATE book SET price=? Where id=? ', (1)) C.execute (' DELETE from book WHERE id=2 ') conn.commit () Conn.close ()

You can also delete the entire table directly:

C.execute (' DROP TABLE book ')

If you delete test.db, the entire database is deleted.

3, Summary

Sqlite3 is an interface for SQLite. To be proficient in using SQLite database, you need to learn the knowledge of relational database. In some scenarios, Python crawlers can use SQLite to store the information collected on the Web. Gooseeker Crawler DS dozen will support SQLite in the 7.x version, think about how the Python crawler is connected to the DS number machine.

4, Document modification history
2016-07-11:v1.0, first release

This article from "Fullerhua blog" blog, declined reprint!

Python crawler Combat (3): Secure room production broker information collection