1, MySQL database
2, mongodb database
1, Introduction to mongodb
- Non relational database is a document structure, json (Dictionary Plus list), which stores large data
- Immune to sql injection statement attacks. The simplest database.
- There are multiple libraries in mongodb. There are multiple collections in the library, and there are multiple json data in each collection.
2, mongodb database operation
- Enter mongo to enter mongo database, port: 27017.
- show dbs -- view the database, db -- view the current location. use database - switch the database and create it if it does not exist.
- Note: mongodb commands are all lowercase and case sensitive, but MySQL and redis are uppercase and case insensitive.
- If the created library has no data, the name of the database will not be displayed.
- db.createCollection('student ') -- create a collection and add data to the current database. db.dropDatabase() -- delete the current database
- show collections -- view all collections, db.student.drop() - delete all collections of students
- db.student.insert({name: 'a', age:18}) -- when inserting a nonexistent set, the set will be created automatically.
- db.student.find() -- find the inserted data in the student collection, as well as insertOne or insertMany
- db.student.find().pretty() -- the content is displayed in sections. Add a specific search condition in the find () bracket to perform a specific search.
- db.student.find({age:{KaTeX parse error: Expected 'EOF', got '}' at position 6: gt:18} ̲})—— Find numbers older than 18 years... gte:18}}) -- find data older than or equal to 18 years
- g t — — large to ， gt - greater than, gt -- greater than, gte -- greater than or equal to, l t — — Small to ， lt - less than, lt -- less than, lte -- less than or equal to, $ne -- not equal to, $eq -- equal to
- Multiple conditions should be separated by commas. db.student.find({$or [{}, {}, {}]}) -- fill in your own writing in braces.
- Full text file replacement -- db.student.update({}, {}) -- the front is the found content, and the back is the modified content. Change all this data to the following content.
- db.student.update({_id:7},{$set:{age:38}) -- only modify the value of the age field, but only the first data that meets the condition.
- db.student.update({_id:7},{$set:{age:38},{multi:true}) -- updates all qualified data in the collection
- db.student.remove({}) -- delete all qualified data by default. Deleting all data without adding data will not really free space, but also use db.repairDatabase() to free space
- db.student.deleteOne({}) - delete one piece of data, db.student.deleteMany({}) - multiple pieces of data.
3, python operation mongodb
import pymongo
class MyMongodb:
def __init__(self,database,collection):
'''
:param database: Specify the data to link
:param collection: Specify collection
'''
self.client = pymongo.MongoClient()
self.db = self.client[database]
self.col = self.db[collection]
def istype(self,data,istype1):
'''
:param data:afferent onlyone
:param istype1: afferent bool
:return: none
'''
if not isinstance(data,istype1):
raise TypeError
def insert(self,data,onlyone = True):
'''
:param data: Data to insert
:param onlyone: Insert only one
:return: none
'''
# if not isinstance(onlyone,bool):
# raise TypeError
self.istype(onlyone,bool)
# if onlyone:
# self.col.insert_one(data)
# else:
# self.col.insert_many(data)
self.col.insert_one(data) if onlyone else self.col.insert_many(data)
def update(self,data,new_data,onlyone =True):
'''
:param data: Incoming data to modify
:param new_data: New data
:param onlyone: Judge whether to change one
:return: none
'''
self.istype(onlyone,bool)
self.col.update_one(data,{'$set':new_data}) if onlyone else self.col.update_many(data,{'$set':new_data})
def find(self,query = None,onlyone = True):
'''
:param query:The default is to query all
:param onlyone: Judge whether to query only one item
:return: none
'''
self.istype(onlyone, bool)
res = self.col.find_one(query) if onlyone else list(self.col.find(query))
return res
def delete(self,data,onlyone = True):
'''
:param data:Data to delete
:param onlyone: Delete one
:return: none
'''
self.istype(onlyone, bool)
self.col.delete_one(data) if onlyone else self.col.delete_many(data)
if __name__=='__main__':
student = MyMongodb('yige','student')
student.insert({'name':'sige','age':85})
# student_list = list(student.find(onlyone=false))
# print(student_list)
# student.insert([{'name': 'liangge', 'age': 15},{'name': 'sange', 'age': 16}],onlyone=False)
student_list = student.find(onlyone=False)
print(student_list)
3, redis database
1, Non relational database
- Most non relational databases are stored in the form of documents, lists, dictionaries, key value pairs
- It does not support SQL syntax, high reading and writing performance and flexible data model
- Hot spot data is stored and can be read concurrently at high speed.
2, redis Foundation
-
Redis server: start the redis service,
-
Redis cli shutdown: shut down the service
-
Port: 6379 -- the configuration file is in / etc/redis/redis.conf
-
Redis cli - H 127.0.0.1 - P 6379 - remote connection to redis database
-
Redis cli -- local connection, ping -- check whether it is normal. Uppercase is recommended
-
echo hello -- output Hello
3, Use of redis
- There are 16 databases, increasing from 0 to 15 database names. Automatically select database No. 0, select 1 - enter database No. 1
- A project corresponds to a redis instance and 0-15 databases
- set num 1 -- set num:1, GET num -- get 1, keys * - view all keys, mset key1 value1 key2 value2 -- set multiple values
- mget key1 key2 -- get multiple values, keys num? -- the question mark represents any character, keys num[0-9] - get num0 to 9
- exists key -- judge whether it exists, return 1 if it exists, return 0 if it does not exist, rename key1 key2 -- rename the key
- expire key seconds - set the expiration time, TTL key - check how many seconds are left to expire
- set key value EX seconds - set the expiration time when creating data. - 1 means permanent, - 2 means nonexistent
- Persistent key -- set the key to permanent. type key -- view the data type
- del key -- delete the key,
- Redis cli keys' num * '| xargs redis cli del -- delete with the linux pipeline symbol.
- Flush -- clear all data. append key data -- add the data after the data before the key.
- incrby key number - plus number, decrby key number - minus number.
4, redis list
It is equivalent to stack, but it is divided into left addition and right addition
Basic command: key value
- lpush li 3 4 5 -- insert 3 4 5 into the li list, lrange li 0 2 -- display the data from 0 to 2
- Subscript index value - LINDEX key subscript value, LPOP key - delete the one on the left.
- lrem key 2 value -- delete 2 values from the left. If the number is 0, delete all values
5, Hash data
The key value of hash (hash type) is also a dictionary structure, but the field value can only be a string, and other types are not supported
- Hset yige name yige, hset yige age 18 -- set yige:{name: 'yige', age: '18'} in redis database
- Hget yige name -- get the name field value,
- hmset yige height 180 width 130 -- set multiple
- hget yige -- get all the fields
- hvals yige -- get all field values
- hgetall yige -- get all fields and field values, hexists yige name -- judge whether the field name exists
- hincrby yige height 2 - add 2 to the height
6, Assemble
The data in the collection is out of order
- Sadd se1 123A B -- add 123A B to se1, and scar se1 -- the number of statistical data
- spop se1 2 -- randomly delete two elements in the se1 set, sismember se1 1 -- judge whether 1 is in the set se1
- smembers se1 -- get the elements of all sets,
- srandmember se1 3 -- when the number is positive, three non repeating numbers are randomly obtained; when it is negative, three repeating numbers are randomly obtained
- sinter se1 se2 -- calculate the intersection of the two, sinter store se3 SE1 se2 -- store the result in se3
- sunion se1 se2 -- union set, sdiff se1 se2 -- calculate the difference set of the two
An ordered set is associated with a valid score
7, Ordered set
- zadd math 100 yige 90 liangge 80 -- set the score value, zcard math -- view its corresponding score.
- zrange math 0 -1 - sort the default weight from small to large, zrrange math 0 - 1 - reverse sort
- zrem math wuge -- delete wuge, zrangebyscore math 70 90 -- get data in the specified range
- zrangebyscore math 70 (90 limit 0 2 -- excluding 90 plus pagination display,
- zcount math 80 100 -- count the number within this range, and zinc math 5 yige -- add 5 points to the weight of yige
- zremrangebyrank test 0 2 -- delete the 1st to 3rd elements.
- The intersection and union set is the same as the unordered set, except that it changes s into z.
4, python operation database (including comprehensive code)
1, python operating mongodb database
import pymongo
class MyMongodb:
def __init__(self,database,collection):
'''
:param database: Specify the data to link
:param collection: Specify collection
'''
self.client = pymongo.MongoClient()
self.db = self.client[database]
self.col = self.db[collection]
def istype(self,data,istype1):
'''
:param data:afferent onlyone
:param istype1: afferent bool
:return: none
'''
if not isinstance(data,istype1):
raise TypeError
def insert(self,data,onlyone = True):
'''
:param data: Data to insert
:param onlyone: Insert only one
:return: none
'''
# if not isinstance(onlyone,bool):
# raise TypeError
self.istype(onlyone,bool)
# if onlyone:
# self.col.insert_one(data)
# else:
# self.col.insert_many(data)
self.col.insert_one(data) if onlyone else self.col.insert_many(data)
def update(self,data,new_data,onlyone =True):
'''
:param data: Incoming data to modify
:param new_data: New data
:param onlyone: Judge whether to change one
:return: none
'''
self.istype(onlyone,bool)
self.col.update_one(data,{'$set':new_data}) if onlyone else self.col.update_many(data,{'$set':new_data})
def find(self,query = None,onlyone = True):
'''
:param query:The default is to query all
:param onlyone: Judge whether to query only one item
:return: none
'''
self.istype(onlyone, bool)
res = self.col.find_one(query) if onlyone else list(self.col.find(query))
return res
def delete(self,data,onlyone = True):
'''
:param data:Data to delete
:param onlyone: Delete one
:return: none
'''
self.istype(onlyone, bool)
self.col.delete_one(data) if onlyone else self.col.delete_many(data)
if __name__=='__main__':
student = MyMongodb('yige','student')
student.insert({'name':'sige','age':85})
# student_list = list(student.find(onlyone=false))
# print(student_list)
# student.insert([{'name': 'liangge', 'age': 15},{'name': 'sange', 'age': 16}],onlyone=False)
student_list = student.find(onlyone=False)
print(student_list)
- Self.col.insert_one (data) if only one else self.col.insert_many (data) -- note that a list should be used when inserting multiple pieces of data.
- self.col.update_one(data,{‘KaTeX parse error: Expected 'EOF', got '}' at position 14: set':new_data} ̲) If only one el... Set ': new_data}) -- you can't update all the data when updating, just pass in the dictionary data to be changed.
- Res = self.col.find_one (query) if only one else list (self.col.find (query)) -- the data should be converted to a list before printing, but if only one is found, the list does not need to be converted, and the original return value is dictionary type data
2, python operating mysql database
'''
python operation mysql
'''
import pymysql
db_config = {
'host': '127.0.0.1',
'port': 3306,
'user': 'admin',
'password': 'qwe123',
'db': 'yige' ,
'charset': 'utf8'
}
conn = pymysql.connect(**db_config)
cur = conn.cursor() #Get cursor object
try:
sql = 'select * from `student`'
cur.execute(sql)
print(cur.fetchone())
print(cur.fetchall())
except Exception as e:
conn.rollback()
print(f'abnormal{e}，The operation was canceled')
else:
conn.commit()
print('Successful execution')
finally:
cur.close()
conn.close()
- Connect MySQL, IP, port, user, password, database and coding method
- Get the cursor object and execute the statement. Remember to add a semicolon.
- The transaction is started in MySQL. If it is not committed, it will not be persisted
- Get a single piece of data queried -- cur.fetchone(), cur.fetchmany(2) -- get two pieces of data and return tuples
3, python operation redis
'''
python operation redis
'''
import redis
redis_config = {
'host':'127.0.0.1',
'port':6379,
'db':0,
'decode_responses':True #The default data returned is byte type data, and we should return string data
}
db = redis.StrictRedis(**redis_config)
db.set('num','1234')
db.lpush('li',1)
db.lrange('li',0,-1)
db.set('test',12,ex=60)
db.keys()
4, Login operation - redis plus MySQL
class User:
def __init__(self,id,name,age,sex,class_id):
self.id = id
self.name =name
self.age = age
self.sex =sex
self.class_id =class_id
def eat(self):
print(f'{self.name}I'm eating')
if __name__=='__main__':
student_id = input('Please enter student number:')
name = input('Please enter your name:')
import pymysql
import redis
import random
redis_config = {
'host': '127.0.0.1',
'port': 6379,
'db': 0,
'decode_responses': True
}
db = redis.StrictRedis(**redis_config)
db.set(f'{student_id}:verfication',str(random.randint(1000,9999)),ex=60) #Generate verification code
verficatiin = input("Please enter the verification code")
if verficatiin==db.get(f'{student_id}:verfication'):
print('Verification code error')
raise Exception
host_ig = {
'host': '127.0.0.1',
'port': 3306,
'user': 'admin',
'password': 'qwe123',
'db': 'yige',
'charset': 'stf-8',
}
conn = pymysql.connect(**host_ig)
cur = conn.cursor()
try:
sql = f'select * FROM `student` where `id`={student_id} and `name`="{name}";'
cur.execute(sql)
res = cur.fetchone()
if res:
now_student = User(**res)
else:
print('Account password error')
raise Exception
except Exception as e:
conn.rollback()
print("An exception occurred")
else:
conn.commit()
finally:
cur.close()
conn.close()
if now_student:
print(f'The currently logged in user is:{now_student.name}')
now_student.eat()