python connects mysql

Install pymysql

Guide Kit:

import pymysql

Establishing the connection of database
Parameters: host name, user name, password, database name

conn = pymysql.connect('localhost','root','root','database')

Get cursor

cursor = conn.cursor()

All additions, deletions and modifications are written in sql
Writing sql statements

sql = ' '

#Dynamic query
sql = 'select * form stu where id = %d'%(d)

#sql statements are too many, such as table building, nested queries can be written as follows
sql = """
   #sql statement carriage return
"""

Execute sql statements

cursor.execute(sql)

Opening things

db.begin()
try:
    cursor.execute(sql)
    db.commit()#Modifications need to be submitted, queries need not be submitted
    print("cg")
except Exception as e:#pymysql.Error=>Exception
    print(e)

    #RollBACK
    db.rollback()

Get a single piece of data

res = cursor.fetchone()

Get all the results

res = cursor.fetchall()

Get the number of affected rows

num = cursor.rowcount()

Close cursors and databases

cursor.close()
conn.close()

python connects mongoDB

Install pip install pymongo

Guide bag

import pymongo

Establish connection

connet = pymongo.MongoClient('localhost',27017)  #Designated address port
connet = pymongo.MongoClient() #Default address port
connet = pymongo.MongoClient('mongodb://127.0.0.1:27017/')#url form

Get all database names

all_database=connet.list_database_names()

Getting the database

database = connet.school
#Dictionary access to prevent duplication with system variables
 database = connet['school']

#Getting sets is also called tables
table = database.student
 table = database['student']

The following two insertions are available in both Python 3.6 and 3.7, with warnings

#Add document (insert) python3.6:
table.insert({"name":"abc", "age":19})
table.insert([{"name":"abc1", "age":19},{"name":"abc2", "age":19}])

#After Python 3.7, the following is recommended
#Insert a single bar, you can add insert_id to return id, without returning an instance of InsertOneResult
res = table.insert_one({"name":"ll","age":25}).inserted_id
#Insert multiple
res = table.insert_many([{"name":"dasf","age":30},{"name":"fsaf","age":45}])
dict = [{"name":"tuyh","age":87},{"name":"oikujh","age":65}]
res= table.insert_many(dict)

View documents

res=table.find() #fine can specify query conditions

#ergodic
for row in res:
    print(row)
    name = row.get('name',(None|'no key'))#get() has return content, no return None or no key after setting, and no error will be reported if it is used directly.
    print(name)

Statistical query

res = table.find({"age":{"$gt":20}}).count()

sort

res = table.find().sort("age")#Ascending order
res = table.find().sort("age", pymongo.DESCENDING)#Descending order

Paging query

res = table.find().skip(3).limit(5)

Update document

#upset did not find whether to insert or not, and whether multi changed multiple entries
collection.update({"name":"lilei"},{"$set":{"age":25}},upsert=False,multi=False)
#After Python 3.7, it is still recommended to use updata_one or updata_man to modify one or more items

#upset did not find whether to insert
res = table.update_one({"dasf":'okk'},{'$set':{'name':'666','age':166}},upsert=False)
res = table.update_many({"dasf":'okk'},{'$set':{'name':'666','age':166}},upsert=False)

remove document

#Multiple deletes by default
res = table.remove({'name':'fsaf'},multi=False)
#After Python 3.7
res = table.delete_one({'name':'fsaf'})
res = table.delete_many({'name':'fsaf'})

python connects redis

Import

import redis

Connect to local machine by default, port 6379 database is 0

r = redis.Redis()
#You can also specify
r = redis.Redis(host="localhost", port=6379,db=1)

Set key name = okk to expire in 100 seconds

r.set('name','okk',ex=100)

Get value

name = r.get('name')
print(name.decode()) #Instead of adding output, b'okk'adds output okk.

list use

r.lpush('mylist1',1,9,34,78)

Insert the array in

ll = [1,8,0,9]
#Serialization of array objects and transformation of objects into strings
import json
ll_str = json.dumps(ll)
print(ll_str,type(ll_str))   #[1, 8, 0, 9] <class 'str'>
r.set('myll',ll_str)
myll=r.get('myll').decode()

#Deserialize, changing characters into list s
myll_obj = json.loads(myll)
print(myll_obj,type(myll_obj))   #[1, 8, 0, 9] <class 'list'>

One-time Insertion with Pipe

pipe = r.pipeline()
pipe.set('age',18)
pipe.set('class','ssdaf')

#Execute the commands in the pipeline
pipe.execute()