Python3 Crawler (ix) relational database of data storage MySQL

Infi-chu:

http://www.cnblogs.com/Infi-chu/

relational database
Relational database is a database based on relational model, and the relational model is saved by a two-dimensional table, so the storage of relational database is a table composed of columns and rows.
Each column is a field, and each row is a record.
A table can be seen as a collection of entities, and there are relationships between entities, such as primary and foreign keys
Common relational databases are: Oracle, MySQL, SQLite, SQL Server, DB2, etc.

Mysql
In Python2, the MySQL link library is mysqldb,

In Python3, the official does not support the use of Mysqldb,python3 in the use of Pymysql

1. Connect to the database

Import pymysqldb = Pymysql.connect (host= ' 127.0.0.1 ', user= ' root ', password= ' 123456 ', port=3306) cursor=db.cursor () Cursor.execute (' Selete VERSION () ')    # Execute SQL statement using Execute method data = Cursor.fetchone ()    # using Fetchone () method to get the first Data print (' Version is: ', data) cursor.execute (' CREATE DATABASE spiders DEFAULT charcter SET uft-8 ')   # Create a spiders database with the default encoding Utf-8db.close ()

2. Create a table

Import pymysqldb = Pymysql.connect (host= ' 127.0.0.1 ', user= ' root ', password= ' 123456 ', port=3306,db= ' spiders ') cursor = Db.cursor () sql = ' CREATE TABLE IF not EXISTS tests (ID varchar (255) is not NULL, name varchar (255) is not NULL, and age INT is not null , PRIMARY KEY (id)) ' cursor.execute (SQL) Db.close ()

Note
The database should be designed according to the actual situation in the Real crawler project.

3. Inserting data

import pymysqlid = ' 123 ' user = ' infichu ' age = 23db = Pymysql.connect (host= ' 127.0.0.1 ', user= ' root ', password= ' 123456 ', port=3306,db= ' spiders ') cursor = db.cursor () sql = ' INSERT into tests (id,name    , age) VALUES (%s,%s,%s) ' Try:cursor.execute (SQL, (Id,user,age)) Db.commit () # Commit () method, Database commit except:db.rollback () # rollback (), database rollback db.close () 

4 properties of a thing
Property                                                        ,         &NB Sp       Description
atomicity (atomicity)             Things are an inseparable unit of work, things that involve many operations that either do or do not
Consistency (consistency)         Things must change the database from one consistent state to another. Consistency is closely related to atomicity
Isolation (isolation)               The execution of a thing cannot be interfered with by other things
persistence (durability) & nbsp           sustainability, once a thing is submitted, his changes to the data in the database should be permanent. The subsequent operation or failure does not affect it

# Universal Insert Method Import Pymysqldata = {' id ': ' 1 ', ' name ': ' Infi-chu ', ' age ': 23}table = ' tests ' keys = ', '. Join (Data.keys ()) values = ', '. Join (['%s ' *len (data)]) sql = ' INSERT into {table} ({keys}) ' Values ({VALUES}) '. Format (table=table,keys=keys,values= Values) Try:    if Cursor.execute (Sql,tuple (Data.values ())):p rint (' successful ') except:    print (' Failed ') Db.rollback () Db.close ()

4. Updating data

sql = ' UPDATE tests SET age=%s WHERE name=%s ' try:    cursor.execute (SQL, (+, ' Infi-chu ')) Db.commit () except:    Db.rollback () Db.close ()

# General Update method
Import Pymysql
data = {
' ID ': ' 1 ',
' Name ': ' Infi-chu ',
' Age ': 23
}
Table = ' tests '
Keys = ', '. Join (Data.keys ())
Values = ', '. Join (['%s ']*len (data)]
# on DUPLICATE key update indicates that the update operation is performed if the primary key already exists
sql = ' INSERT into {table} ({keys}) VALUES ({VALUES}) on DUPLICATE KEY UPDATE '. Format (table=table,keys=keys,values=values )
Update = ', '. Join (["{keys}=%s". Format (Key=key) for key in data])
SQL + = Update
Try
If Cursor.execute (Sql,tuple (Data.values ()):
Print (' successful ')
Db.commit ()
Except
Print (' Failed ')
Db.rollback ()
Db.close ()

5. Delete data:

Table = ' tests ' condition = ' age>20 ' sql = ' DELETE from {table} WHERE {condition} '. Format (table=table,condition= Condition) Try:    cursor.execute (SQL) Db.commit () Except:cursor.rollback () Db.close ()

6. Query data:

sql = ' SELECT * from tests WHERE age>=20 ' try:    cursor.execute (SQL) print (' Count: ', cursor.rowcount) one = Cursor.fetchone () print (' One: ', one) results = Cursor.fetchall () print (' results: ', results) print (' Results type: ', type ( Results))) for row in results:    print (row) except:    print (' Error ')

Python3 Crawler (ix) relational database of data storage MySQL