Python Learning Notes-database

First,MySQL database:

Python operation MySQL Database python3 operation MySQL data need to install a third-party module, Pymysql, using pip install Pymysql installation, in Python2 is the MySQLdb module, There are no MYSQLDB modules in Python3, so use Pymysql.

To connect to a database:

1. Linked database: Account, password, IP, port number, database;

2. Creating cursors;

3. Execute SQL (must commit when executing INSERT, delete, UPDATE statement)

4. Obtain the results;

5. Close the cursor;

6. Connection closed;

ImportPymysqlcoon= Pymysql.connect (host='118.24.3.40', user='Jxz', passwd='123456', port=3306,db='Jxz', charset='UTF8')    #Port must write int type, CharSet must write UTF8 herecur= Coon.cursor ()#Creating CursorsCur.execute ('select * from Stu;')#Execute SQL statementCur.execute ('insert INTO Stu (Id,name,sex) VALUE (1, "Nuo Hanyang", "female");')res = Cur.fetchall ()#get all returned resultsPrint(RES) cur.close ()#Close CursorsCoon.close ()#Close Connection

Get the meta-ancestor operation:

 ImportPymysql Conn= Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd='123456', db='Data', charset='UTF8')#Create a connection, specify the IP address of the database, account number, password, port, database to manipulate, character setcursor = Conn.cursor ()#Creating CursorsEffect_row = Cursor.execute ("Update students set name = ' Niuhy ' where id = 1;")#executes SQL and returns the number of rows affectedEffect_row = Cursor.execute ("Update students set name = ' Niuhy ' where id =%s;", (1,))#executes SQL and returns the number of rows affectedEffect_row = Cursor.executemany ("INSERT into students (Name,age) values (%s,%s);", [("Andashu", 18), ("12345", 20)]) Cursor.execute ("SELECT * from students;")#Execute SELECT statementRow_1 = Cursor.fetchone ()#gets the first data for the query result, and returns a tupleRow_2 = Cursor.fetchmany (3)#get top N rows of dataRow_3 = Cursor.fetchall ()#Get all dataConn.commit ()#Commit, or you cannot save the newly created or modified datanew_id = Cursor.lastrowid#get the latest self-increment ID    Print(new_id) cursor.close ()#Close CursorsConn.close ()#Close Connection

Get dictionary operation:

ImportPymysql Conn= Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd='123456', db='Data', charset='UTF8')#Create a connection, specify the IP address of the database, account number, password, port, database to manipulate, character setcursor = Conn.cursor ()#Creating Cursorscursor = Coon.cursor (cursor=pymysql.cursors.dictcursor)#you need to specify the type of cursor, dictionary typeCursor.execute ("select * from user;")#Execute SQL    #gets the return result, this time the return result is a dictionaryres = Cursor.fetchone ()#returns a single piece of dataRes2= Cursor.fetchall ()#all the data is returned together

Write the operations database as a function:

defmy_db (host,user,passwd,db,sql,port=3306,charset='UTF8'):    ImportPymysql Coon= Pymysql.connect (user=user,host=host,port=port,passwd=passwd,db=db,charset=charset) Cur= Coon.cursor ()#Creating CursorsCur.execute (SQL)#Execute SQL    ifSql.strip () [: 6].upper () = ='SELECT': Res=Cur.fetchall ()Print(RES)Else: Coon.commit () Res='OK'cur.close ()returnRes

Second,Redis database:

Operation Redis Redis is a nosql type of database with data in memory, fast read and write speeds, Python operation Redis using Redis module, PIP installation

ImportPYMYSQL,JSON,REDISR= Redis. Redis (host='118.24.3.40', password='hk139bc&*', db=1,port=6379)#Specify the port and IP of the linked Redis and which database#Change and deleteR.set ('hh','hello!')#A new value is added to the database#modification is also setR.delete ('Scorpio: HH')#Delete the specified keyR.setex ('hh','Hello', 10)#set the expiry time of key, the last parameter is seconds, 10 seconds after the session expires, deleteHh= R.get ('hh')Print(HH) hh.decode ()#Turn the binary into a stringPrint(R.keys ())#get to all the keysPrint(R.keys ('h*'))#get to the beginning of the keyPrint(R.keys ('*h*'))#get the key containing TPrint(R.get ('hehe'))#returns none when obtaining a nonexistent keyR.set ('Scorpio: HH','hehe')#there is a colon in key, the name of the folder precedes the colonPrint(R.get ('Scorpio: HH'))

Python Learning Notes-database