MySQL

This section describes how to use MySQL in Python (PyMySQL library).

Connecting to the database

Let’s now connect to the local MySQL database, after making sure that the local MySQL database is running and accessible.

import pymysql  # import libraries

db = pymysql.connect(host='localhost', user=The '*', password='* * *') To connect to the database, you can also specify the port by passing in port = an integer value. The default value (3306) is used here.
cursor = db.cursor()    # Obtain MySQL operation cursor to execute SQL statements
cursor.execute('SELECT VERSION()')  Execute the SQL statement
data = cursor.fetchone()    # Get the first data
print("Database Version:", data)
cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER SET utf8")    # Create a new database for crawlers
db.close()
Copy the code

Create a table

import pymysql

db = pymysql.connect(host="localhost", user="*", password="* * *", port=3306, db='spiders')    Connect directly to the database you just created
cursor = db.cursor()
sql = 'CREATE TABLE IF NOT EXISTS students (id VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY (id))'
cursor.execute(sql)
db.close()
Copy the code

Insert data

Use a series of variable inserts

import pymysql

id = '2099133201'
name = 'Foo.Bar'
age = 101

db = pymysql.connect(host='localhost', user=The '*', password='* * *', db='spiders')
cursor = db.cursor()

sql = 'INSERT INTO students (id, name, age) VALUES (%s, %s, %s)'

try:
    cursor.execute(sql, (id, name, age))
    db.commit()
except:
    db.rollback()

db.close()
Copy the code

⚠️ [Note] :

1. We don’t have to use Python’s method to pre-construct the string when we execute, we can use it where we need to insert it%s, and then execute with a list of parameters passed in as a tuple. However, the main point of doing this is:Avoid quotation marks!.
2. Note MySQL’s transaction mechanism. The standard template for insert, update, and delete operations is:

try: cursor.execute(' alter operation statement ') db.com MIT ()except:
    db.rollback()
Copy the code

Dictionary insertion

import pymysql

db = pymysql.connect(host='localhost', user=The '*', password='* * *', db='spiders')
cursor = db.cursor()

data = {
        'id': '2099133202'.'name': 'Fuzz.Buzz'.'age': '104'
        }

table = 'students'

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())):
        print("Success!")
        db.commit()
except Exception as e:
    print('Failed:\n', e)
    db.rollback()

db.close()
Copy the code

In this example, if you don’t use %s + to dynamically construct the statement, instead of using values = ‘, ‘.join(data.values()), then cursor.execute(SQL) will fail.

Update the data

Simple data updates

sql = 'UPDATE students SET age = %s WHERE name = %s'

try:
    cursor.execute(sql, (999.'Foo.Bar'))
    db.commit()
except:
    db.rollback()

db.close()
Copy the code

More practical dictionary updates

In the actual data fetching process, if duplicate data occurs, we prefer to update the data: update the data if duplicate, insert the data if it doesn’t exist, and support flexible dictionary passing.

import pymysql

db = pymysql.connect(host='localhost', user=The '*', password='* * *', db='spiders')
cursor = db.cursor()

data = {
        'id': '2099133203'.'name': 'SomeOne'.'age': '0'
        }

table = 'students'

keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data))

sql = 'INSERT INTO {table} ({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE'.format(table=table, keys=keys, values=values)
update = ', '.join([' {key} = %s'.format(key=key) for key in data])

sql += update

try:
    if cursor.execute(sql, tuple(data.values()) * 2) :print("Success!")
        db.commit()
except Exception as e:
    print('Failed:\n', e)
    db.rollback()

db.close()
Copy the code

Delete the data

import pymysql

db = pymysql.connect(host='localhost', user=The '*', password='* * *', db='spiders')
cursor = db.cursor()

table = 'students'
condition = 'age > 200'

sql = 'DELETE FROM {table} WHERE {condition}'.format(table=table, condition=condition)

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

db.close()
Copy the code

Query data

sql = 'SELECT * FROM students'

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')
Copy the code

MongoDB

This section describes how to use MongoDB in Python (PyMongo library).

Directing a profile

MongoDB is a non-relational database written by C++ language. It is an open source database system based on distributed file storage. Its content storage form is similar to Json object, and its field values can contain other documents, arrays and document arrays, which is very flexible.

Before use, ensure that MongoDB has been installed and the service has been started:

$ brew services start mongodb
$ sudo mongod
$ mongo
Copy the code

The preceding three commands enable the MongoDB service and open the client.

Connecting to the database

import pymongo
client = pymongo.MongoClient(host='localhost', port=27017)
# client = pymongo. MongoClient (' mongo: / / localhost: 27017 /) effect is the same
Copy the code

Specified database

db = client.test
# db = client['test'
Copy the code

Specify the collection

collection = db.students    You can also use '[']'
Copy the code

Insert data

There are three ways to insert data:

• collection.insert(): Insert one or more data, pass one object to insert one, pass one object list can insert multiple (not recommended);
• collection.insert_one(): Insert data, pass a dict;
• collection.insert_many(): Insert multiple data, passing a dict list;

import pymongo

client = pymongo.MongoClient(host='localhost', port=27017)
db = client.test
collection = db.students

student1 = {
        'id': '2099133201'.'name': 'Foo'.'age': 10.'gender': 'male'
        }

student2 = {
        'id': '2099133202'.'name': 'Bar'.'age': 11.'gender': 'male'
        }

student3 = {
        'id': '2099133203'.'name': 'Moo'.'age': 12.'gender': 'female'
        }

result0 = collection.insert_one(student1)
result1 = collection.insert_many([student2, student3])
print(result0, result0.inserted_id, sep='\n')
print(result1, result1.inserted_ids, sep='\n')
Copy the code

The query

The query method is as follows:

• find_one(): A single result is found
• find(): a generator that returns a result

If the result of the query does not exist, None is returned.

res_of_find = collection.find()     # Get all data
print('find:\n', res_of_find)
for i in res_of_find:
    print(i)

res_of_findone = collection.find_one({'name': 'Foo'})   # Query by passing in a set of dictionary keys
print('find_one:\n', res_of_findone)
Copy the code

If you want to use the _ID attribute MongoDB adds for each piece of data, you need to do this:

res_of_find_by_id = collection.find_one({'_id': ObjectId('5c78e0c6b92a4e5f17d70cfa')})
print('find by id:\n', res_of_find_by_id)
Copy the code

To query data with age > 10:

collection.find({'age': {'$gt': 10}})
Copy the code

Here the value of the query condition becomes a dictionary of symbolic plus operation values. The available operations are as follows:

symbol	meaning	The sample
$lt	Less than	{'age': {'$lt': 20}}
$gt	Is greater than	{'age': {'$gt': 20}}
$lte	Less than or equal to	{'age': {'$lte': 20}}
$gte	Greater than or equal to	{'age': {'$gte': 20}}
$ne	Is not equal to	{'age': {'$ne': 20}}
$in	Within the scope of	{'age': {'$in': [20, 23]}}
$nin	Out of range	{'age': {'$nin': [20, 23]}}

There are several ways to query:

collection.find({'name': {'$regex': '^M.*'}})
Copy the code

Common operations are as follows:

symbol	meaning	The sample	The sample mean
$regex	Matches a regular	{'name': {'$regex': '^M.*'}}	Name starts with M
$exists	Whether the attribute exists	{'name': {'$exists': True}}	Name attribute exists
$type	Type judgment	{'age': {'$type': 'int'}}	The type of age is int
$mod	Digital mode operation	{'age': {'$mod': [5, 0]}}	Mod 5 has 0 remaining
$text	Text query	{'$text': {'$search': 'Mike'}}	The attribute of type text contains the Mike string
$where	Advanced Search	{'$where': 'obj.fans_count == obj.follows_count'}	The number of followers equals the number of attention

To get rid of_id

Mongo returns data with an item called _id (the field that Mongo automatically adds to identify objects). We don’t want to look at this, so we can filter the results when we query:

collection.find(projection={'_id': False})
Copy the code

count

To count the number of query results, you can directly call the count() method of the query results.

collection.find({'age': {'$gt': 0}}).count()
Copy the code

---

P.S. A digression:

Writing here, another beautiful night after lights out. Dim the screen backlight, carefully pick up the white cherry mechanical keyboard and place it out of the light, away from the red, green and blue disputes. Take advantage of the colorful darkness to present the next line of fascinating code as a pious gift to the hungry compiler. Waiting for her to work her magic, creating an unbroken string of binary bits, and in the combinations of zeros and ones, light and shadow flowed like a heron, like a mountain. In the moment of longing, might as well divide a god again, appreciate between open and close, a door of primitive simplicity back to the future.

---

The sorting

We can sort the results of a query by calling the sort() method, passing in the fields to be sorted, and flags to lift the order.

• pymongo.ASCENDING: specifies the ascending order.
• pymongo.DESCENDING: Specifies the descending order;

r = collection.find().sort('name', pymongo.ASCENDING)
for i in r:
    print(i['name'])
Copy the code

The offset

Offset: offset x elements, i.e. ignore (skip) the previous X elements, to get the x+1 element. This operation calls the skip() method of the query results.

r = collection.find().skip(2)
print([i for i in r])
Copy the code

update

Update is similar to insert, there are three main methods:

• update(): Updates one or more data. This command is not recommended
• update_one(): Updates a piece of data
• update_many(): Updates multiple pieces of data

condition = {'name': 'Foo'}
student = collection.find_one(condition)
student['age'] = 25
result = collection.update(condition, {'$set': student})
print(result)
Copy the code

Here, we query the target entry, modify it to the new data, and call Update, passing in the query criteria and a dictionary representing the operation to complete the update. Common operations are as follows:

• $set: {'$set': value}: Updates all attributes in value to new ones
• $inc: {'$inc': {'age': 1}}: Add age by 1

delete

Remove (), delete_one(), delete_many()

collection.remove({'name': 'Kevin'})
Copy the code

More and more

More perfect MongoDB is still waiting for us to learn on the official website: link to the official document 🔗.

Redis

This section describes the use of Redis in Python (RedisPy library).

Introduction of Redis

Redis is an efficient, memory-based, non-relational, key-value database with high access efficiency and support for a variety of storage data structures.

The RedisPy library provides two classes Redis and StrictRedis to implement Redis’ command operations. StrictRedis is recommended. StrictRedis implements most Redis commands and corresponds to parameters one by one. Redis is primarily used for compatibility with older versions.

Connect the Redis

First, you need to make sure that Redis is properly installed locally and the service is enabled.

Use StrictRedis to connect

from redis import StrictRedis

redis = StrictRedis(host='localhost', port=6379, db=0, password=None)
# StrictRedis() = StrictRedis()

redis.set('name'.'Foo')    # sets a key-value pair
print(redis.get('name'))    # Get a data
Copy the code

Connect using ConnectionPool

from redis import StrictRedis, ConnectionPool

pool = ConnectionPool(host='localhost', port=6379, db=0, password=None)
redis = StrictRedis(connection_pool=pool)

redis.set('age'.100)
print(redis.get('age'))
Copy the code

ConnectionPool can also be built using urls:

There are three URL formats available:

• Redis TCP connection:redis://[:password]@host:port/db
• Redis TCP+SSL connection:rediss://[:password]@host:port/db
• Redis Unix Socket connection:unix://[:password]@/path/to/socket.sock? db=db

The gestures used are as follows:

from redis import StrictRedis, ConnectionPool
Note that an extra ConnectionPool has been imported.

url = 'redis://@localhost:6379/0'
pool = ConnectionPool.from_url(url)
redis = StrictRedis(connection_pool=pool)

redis.set("test".'test')
r = redis.get('test')
print(type(r), r, sep='\n')
Copy the code

The Key operation

Here are some common operations on keys, using them by using statements such as redis.exists(‘neme’) :

methods	role	Parameters that	The sample	example	The sample results
exists(name)	Determine whether a key exists	Name: the key name	redis.exists(‘name’)	Is there a name key	1
delete(name)	Deleting a key	Name: the key name	redis.delete(‘name’)	Delete the name key	1
type(name)	Determining the key type	Name: the key name	redis.type(‘name’)	Determine the key type of name	b’string’
keys(pattern)	Get all keys that match the rule	Pattern: indicates the matching rule	redis.keys(‘n*’)	Gets all keys beginning with n	[b’name’]
randomkey()	Get a random key		randomkey()	Get a random key	b’name’
rename(src, dst)	Rename the key	SRC: original key name DST: new key name	redis.rename(‘name’, ‘nickname’)	Rename name to nickname	True
dbsize()	Gets the number of keys in the current database		dbsize()	Gets the number of keys in the current database	100
expire(name, time)	Set the expiration time of the key, in seconds	Name: indicates the key name. Time: indicates the number of seconds	redis.expire(‘name’, 2)	Set the expiration time of the key name to 2 seconds	True
ttl(name)	Obtain the expiration time of the key, in seconds. -1 indicates that the key will not expire permanently	Name: the key name	redis.ttl(‘name’)	Get the expiration time of the key name	– 1
move(name, db)	Move the key to another database	Name: key name. Db: database code	move(‘name’, 2)	Move name to database 2	True
flushdb()	Deletes all keys in the currently selected database		flushdb()	Deletes all keys in the currently selected database	True
flushall()	Delete all keys in all databases		flushall()	Delete all keys in all databases	True

String operations

String is the most basic Value store in Redis. It operates as follows:

methods	role	Parameters that	The sample	example	The sample results
set(name, value)	Assign a value to the string whose key is name in the database	Name: key name value: value	redis.set(‘name’, ‘Bob’)	Assign the value of the key name to Bob	True
get(name)	Return the value of a string whose key is name in the database	Name: the key name	redis.get(‘name’)	Return the value of the key name	b’Bob’
getset(name, value)	Assign a value to a string whose key is name in the database and return the last value	Name: key name value: new value	redis.getset(‘name’, ‘Mike’)	Assign name to Mike and get the last value	b’Bob’
mget(keys, *args)	Returns the value corresponding to multiple keys	Keys: A list of keys	redis.mget([‘name’, ‘nickname’])	Return the value of name and nickname	[b’Mike’, b’Miker’]
setnx(name, value)	Set value if the key does not exist	Name: the key name	redis.setnx(‘newname’, ‘James’)	If newname does not exist, set the value to James	Run True the first time, False the second time
setex(name, time, value)	Set the corresponding value to string value and specify the validity period for this key value	Name: indicates the key name. Time: indicates the validity period. Value: indicates the value	redis.setex(‘name’, 1, ‘James’)	Set the value of name key to James, valid for 1 second	True
setrange(name, offset, value)	Sets a substring of the value of the specified key	Name: indicates the key name. Offset: indicates the offset. Value: indicates the value	redis.set(‘name’, ‘Hello’) redis.setrange(‘name’, 6, ‘World’)	Set name to the Hello string and fill in World at index 6	11. The new string length
mset(mapping)	Batch assignment	Mapping: a dictionary	redis.mset({‘name1’: ‘Durant’, ‘name2’: ‘James’})	Set name1 to Durant and name2 to James	True
msetnx(mapping)	Batch assignment is performed only when both keys do not exist	Mapping: a dictionary	redis.msetnx({‘name3’: ‘Smith’, ‘name4’: ‘Curry’})	Set name3 and name4 only if neither exists	True
incr(name, amount=1)	The default value is 1. If the key does not exist, it is created and set to amount	Name: key name Amount: increased value	redis.incr(‘age’, 1)	The value of age is incremented by 1. If it does not exist, it is created and set to 1	1, that is, the modified value
decr(name, amount=1)	The default value is 1. If the key does not exist, the value is created and set to -amount	Name: key name Amount: reduced value	redis.decr(‘age’, 1)	The value corresponding to age is reduced by 1. If it does not exist, it is created and set to -1	-1 is the new value
append(key, value)	Key appends value to the string value of name	Key: the key name	redis.append(‘nickname’, ‘OK’)	Append OK to the value of “nickname”	13, that is, the new string length
substr(name, start, end=-1)	Returns a substring of value of a string whose key is name	Name: indicates the key name. Start: indicates the start index. End: indicates the end index	redis.substr(‘name’, 1, 4)	Returns a string with key as name, truncating characters with indexes 1-4	b’ello’
getrange(key, start, end)	Gets the substring from start to end of the value of key	Key: key name start: start index end: end index	redis.getrange(‘name’, 1, 4)	Returns a string with key as name, truncating characters with indexes 1-4	b’ello’

The List action

List is a kind of data whose value is a List in Redis.

methods	role	Parameters that	The sample	example	The sample results
rpush(name, *values)	Add a value to the end of a list with key as name. You can pass multiple elements	Name: key name values: value	redis.rpush(‘list’, 1, 2, 3)	Add 1, 2, 3 to the end of the list of the key list	3, list size
lpush(name, *values)	Add a value to the list header of key for name. You can pass more than one element	Name: key name values: value	redis.lpush(‘list’, 0)	Add 0 to the list header of the key list	4, list size
llen(name)	Return the length of the list whose key is name	Name: the key name	redis.llen(‘list’)	Returns the length of the list whose key is list	4
lrange(name, start, end)	Return the elements between start and end in a list with key name	Name: key name start: start index end: end index	redis.lrange(‘list’, 1, 3)	Returns a list of index ranges that start at 1 and end at 3	[b’3′, b’2′, b’1′]
ltrim(name, start, end)	Intercept the list with key name and reserve indexes start to end	Name :key name start: start index end: end index	ltrim(‘list’, 1, 3)	Keep the elements with index 1 through 3 whose key is list	True
lindex(name, index)	Return the element in the index position of the list whose key is name	Name: indicates the key name. Index: indicates the index	redis.lindex(‘list’, 1)	Return the list with key list and index 1	b’2′
lset(name, index, value)	Name = index; name = index; name = index	Name: indicates the key name. Index: indicates the index position. Value: indicates the value	redis.lset(‘list’, 1, 5)	Assign a value of 5 to the list index 1 position where key is list	True
lrem(name, count, value)	Delete a list of keys with a value of value	Name: indicates the key name. Count: indicates the number of deleted keys. Value: indicates the value	redis.lrem(‘list’, 2, 3)	Delete 2 3s from the list with key list	1, the number of deleted
lpop(name)	Returns and removes the first element in a list with key as name	Name: the key name	redis.lpop(‘list’)	Returns and removes the first element of the list named list	b’5′
rpop(name)	Returns and removes the last element in the list whose key is name	Name: the key name	redis.rpop(‘list’)	Returns and removes the last element of the list named list	b’2′
blpop(keys, timeout=0)	Returns and removes the first element in the list in keys. If the list is empty, it will block and wait	Keys: indicates the key list. Timeout: indicates the timeout waiting time. 0 indicates the waiting time	redis.blpop(‘list’)	Returns and removes the first element of the list named list	[b’5′]
brpop(keys, timeout=0)	Returns and removes the last element in the list whose key is name. If the list is empty, it will block and wait	Keys: indicates the key list. Timeout: indicates the timeout waiting time. 0 indicates the waiting time	redis.brpop(‘list’)	Returns and removes the last element of the list named list	[b’2′]
rpoplpush(src, dst)	Returns and removes the trailing element of the list named SRC and adds the element to the head of the list named DST	SRC: key of the source list DST: key of the target list	redis.rpoplpush(‘list’, ‘list2’)	Remove and return the list tail element with key list and add it to the list head with key list2	b’2′

Set, Sorted Set operations

See the Redis tutorial.

The Hash operation

Redis can use name to specify the name of a hash table that stores key and value pairs.

methods	role	Parameters that	The sample	example	The sample results
hset(name, key, value)	Add a mapping to the hash whose key is name	Name: key name. Key: Mapping key name. Value: mapping key value	hset(‘price’, ‘cake’, 5)	Add a mapping to the hash whose key is price, and cake has a value of 5	1 indicates the number of mappings to be added
hsetnx(name, key, value)	Add a mapping to the hash whose key is name, if the mapping key name does not exist	Name: key name. Key: Mapping key name. Value: mapping key value	hsetnx(‘price’, ‘book’, 6)	Add a mapping to the hash whose key is price and whose book value is 6	1 indicates the number of mappings to be added
hget(name, key)	Returns the value corresponding to the field in the hash with key as name	Name: key name. Key: Mapping key name	redis.hget(‘price’, ‘cake’)	Gets the value of the hash whose key is price and whose key is cake	5
hmget(name, keys, *args)	Returns the value of each key in the hash whose key is name	Name: key name keys: mapping key name list	redis.hmget(‘price’, [‘apple’, ‘orange’])	Gets the values of apple and orange in the hash whose key is price	[b’3′, b’7′]
hmset(name, mapping)	Add mappings in batches to the hash with key as name	Name: key name Mapping: mapping dictionary	redis.hmset(‘price’, {‘banana’: 2, ‘pear’: 6})	Add mappings in batches to the hash whose key is price	True
hincrby(name, key, amount=1)	Increase the amount of the value mapped in the hash with key as name	Name: indicates the name of the key. Key: indicates the name of the mapping key. Amount: indicates the increase	redis.hincrby(‘price’, ‘apple’, 3)	Key increases the value of apple in the hash of price by 3	6, the new value
hexists(name, key)	Key indicates whether there is a mapping of key names in nameHash	Name: key name. Key: Mapping key name	redis.hexists(‘price’, ‘banana’)	Key indicates whether the value of banana in the price hash exists	True
hdel(name, *keys)	Key deletes the mapping of key name in namehash	Name: key name. Key: Mapping key name	redis.hdel(‘price’, ‘banana’)	Example Delete the mapping of the key named banana from the hash whose key is price	True
hlen(name)	Gets the number of mappings from the hash whose key is name	Name: the key name	redis.hlen(‘price’)	Gets the number of mappings from the hash whose key is price	6
hkeys(name)	Gets all mapped key names from the hash whose key is name	Name: the key name	redis.hkeys(‘price’)	Gets all mapped key names from the hash whose key is price	[b’cake’, b’book’, b’banana’, b’pear’]
hvals(name)	Gets all mapped key values from the hash whose key is name	Name: the key name	redis.hvals(‘price’)	Gets all mapped key values from the hash whose key is price	[b’5′, b’6′, b’2′, b’6′]
hgetall(name)	Gets all mapped key-value pairs from the hash whose key is name	Name: the key name	redis.hgetall(‘price’)	Gets all mapped key-value pairs from the hash whose key is price	{b’cake’: b’5′, b’book’: b’6′, b’orange’: b’7′, b’pear’: b’6′}

RedisDump

RedisDump allows you to import and export Redis data.

RedisDump provides two executable commands:

• redis-dumpUsed to export data; As shown in theredis-dump -h
  • e.g. $ redis-dump -u :foobared@localhost:6379 > ./redis_data.jl
• redis-loadUsed to import data; As shown in theredis-load -h
  • e.g. $ redis-load -u :foobared@localhost:6379 < redis_data.json
  • Is equivalent to$ cat redis_data.json | redis-load -u :foobared@localhost:6379