Navicat tool and pymysql module of mysql

In order to facilitate the test, use the IDE tool, Navicat, which is essentially a socket client, a visual tool for connecting mysql server, and a graphical interface version. The difference between it and using the command line directly is similar to the difference between linux and windows.

Navicat download connection: https://pan.baidu.com/s/1c9AKJsZCbRqpXbCKrj3cdg

Installation tutorial: https://www.cnblogs.com/q455674496/p/10305890.html

2.Pymysql module

How to operate mysql in python program? It is the same as the client with mysql and navicat. In essence, it is a socket client, but this socket client is used in python program

#install, Execute the following code in the command line window pip3 install pymysql

import pymysql user=input('User name: ').strip() pwd=input('Password: ').strip() #Links, specifying ip Address and port, when testing on this machine ip Address can be written localhost Or your own ip Address or 127.0.0.1，Then you need to specify the user name and password when you operate the database. You need to specify which database you operate, the name of the database, and the character set. Otherwise, there will be confusion conn=pymysql.connect(host='localhost',port=3306,user='root',password='123',database='student',charset='utf8') #Specify code as utf8 When I was there, I didn't notice-，Do not write utf-8，Database for #obtain conn This connection object #cursor cursor=conn.cursor() #That's what I think mysql The cursor of the client mysql> Enter the command after this, enter to execute #cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #Get the result represented by dictionary data type: {'sid': 1, 'gender': 'male','class_id ': 1,' sname ':' understand '}' {'field name': value} #Then input the cursor sql Statement and execute sql Sentence execute sql='select * from userinfo where name="%s" and password="%s"' %(user,pwd) #Be careful%s You need to quote to execute this sentence sql The premise is that medicine has a userinfo Watch, it has name and password Two fields, and some data. Add data by yourself print(sql) res=cursor.execute(sql) #implement sql Statements, returning sql The number of records successfully queried, which is a number, is the number of records successfully queried sql In fact, in addition to the number of records affected by the statement, the data of these records are also returned to the cursor,This is equivalent to us subprocess Pipes in the module PIPE，Multiply the returned data #all_data=cursor.fetchall() #Obtain all the returned data. Note that for all the retrieved data, there is no retrieved data. The result is in the primitive format #many_data=cursor.fetchmany(3) #Take out three pieces of data, #one_data=cursor.fetchone() #According to the order of data, only one data is taken at a time, and the next time you go, you will get it from the second one. Because the first one is taken out, the first one is taken out, and the results are all in the format of Yuanzu fetchone: (1, 'male', 1, 'Understand') fetchone: (2, 'female', 1, 'Steel egg') fetchall: ((3, 'male', 1, 'Zhang San'), (4, 'male', 1, 'Zhang Yi')) #Above fetch The results are all in Yuanzu format. It's impossible to see which data corresponds to which field. Isn't it not very nice? Think about it. We can use python Which data type of can the fields and corresponding data be represented most clearly? Of course, dictionary{'Field name':value} #When we can create the cursor again, we can cursor Add a parameter: cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)The result is in dictionary format, fetchall perhaps fetchmany The result is the data form of the list set dictionary //As we said above, if our data is fetched once, it will be gone. In fact, it is not. The operation of fetching data is like reading the contents of a file. After each read, the cursor moves to the corresponding position. We can move the cursor through seek //Similarly, we can move the cursor position and continue to take the data in front of us. Through cursor.scroll (number, mode), the first parameter is an int type number, indicating the number of records to move back. The second parameter is the moving mode, with two values: absolute: absolute, relative: relative #Absolute move: it starts to move backward relative to the starting position of all data #Relative movement: it starts to move backward relative to the current position of the cursor #Demonstration of absolute mobility #print(cursor.fetchall()) #cursor.scroll(3,'absolute') #Move three pieces backward from the initial position, and the data to be fetched next time is the fourth piece of data #print(cursor.fetchone()) #Demonstration of relative movement #print(cursor.fetchone()) #cursor.scroll(1,'relative') #The cursor position is at the beginning of the second item. Now I have moved one record relative to the previous item. Next time I retrieve the third item, I have moved one record down relative to the previous item #print(cursor.fetchone()) print(res) #One digit cursor.close() #Close cursor conn.close() #Close connection if res: print('Login successfully') else: print('Login failed')

3 December 2019, 13:07 | Views: 9944