MySQL creation and user authorization, visual Tools Navicat Basic use, Pymysql module use

First, create MySQL, authorize

Rights Management

We know that our top authority manager is the root user, and it has the highest permissions. Includes select, Update, delete, update, Grant, and so on. So in general, after the company, the DBA engineer creates a user and password that allows you to connect to the database and set permissions (or all permissions) for an operation to the current user. Then we need to take a brief look at this:

• How to create a user and password
• Authorizing the current user
• Remove permissions from the current user

If you want to create a new user, the following actions are required:

1. Go to MySQL Database

mysql> Use mysqldatabase changed

2. New User deletion

1. Create User: # Specify ip:192.118.1.1 's MJJ User login create username ' mjj ' @ ' 192.118.1.1 ' identified by ' 123 '; Specify ip:192.118.1. MJJ User Login Create username ' mjj ' @ ' 192.118.1.% ' identified by ' 123 '; # Specify any IP mjj user login Create user ' mjj ' @ '% ' Identified by ' 123 '; 2. Delete user ' user name ' @ ' IP address '; 3. Modify user Rename user ' username ' @ ' IP address ' to ' new username ' @ ' IP address '; 4. Modify password Set password for ' Username ' @ ' IP address ' =password (' new password ');

3. Management of Current user authorization

#查看权限show grants for ' user ' @ ' IP address ' #授权 MJJ the user only has query, insert, and update for DB1.T1 file, grant Select, Insert,update on Db1.t1 to "MJJ" @ '% '; # indicates something Permission, except for Grant, which is the root of the command. MJJ User has any action on the T1 file under DB1 Grant all privileges on  db1.t1 to "mjj" @ '% ';
#mjj用户对db1数据库中的文件执行任何操作

Grant all privileges the  db1.* to "mjj" @ '% ';
#mjj用户对所有数据库中文件有任何操作

Grant all privileges on  * * to  "mjj" @ '% ';

#取消权限

# Cancel any action of MJJ user on db1 t1 file
Revoke all on  db1.t1 from ' mjj ' @"%"; 

# Cancel all permissions of MJJ users from the remote server to all tables DB1 the database

Revoke all on  db1.* from ' mjj ' @"%"; 

Remove all tables from the remote server's MJJ user all databases revoke all privileges on * * from ' mjj ' @ '% ';

PS: In a company, it is generally the DBA engineer to do the authorization work. Give you a username and password, you can connect on it.

4.MYSQL Backup command Line Operations

# Backup: Data table structure + data mysqdump-u root db1 > db1.sql-p# Backup: Data table structure mysqdump-u root-d db1 > db1.sql-p# Import existing data to a database # # # first create a new Database db10;# 2. Import an existing database file into the DB10 database mysqdump-u root-d DB10 < db1.sql-p

Second, the basic use of visual Tools Navicat

This section focuses on:

• Master the basic use of navicat

PS: Operating MySQL database in a production environment or using the command line tool MySQL, but when we develop our own tests, we can use the visualizer navicat to manipulate the MySQL database as a graphical interface.

Official website Download: https://www.navicat.com/en/products/navicat-for-mysql

NET disk Download: HTTPS://PAN.BAIDU.COM/S/1BPO5MQJ

Need to master the basic operation

Master: #1. Test + link Database # #. Create a new library. New table, new field + type + constraint # #. Design table: Foreign Key # #. New query. Backup Library/Table # Note: Batch add Comment: Ctrl +? Key batch to comment: ctrl+shift+? Key

Third, the use of Pymysql module

This section focuses on:

• Download and use of Pymysql
• SQL injection of Execute ()
• Add, Delete, change: Conn.commit ()
• Check: Fetchone, Fetchmany, Fetchall

First, the download and use of Pymysql

Before we were all using MySQL's own command-line client tool MySQL to operate the database, how to manipulate the database in a python program? This uses the Pymysql module, which is essentially a socket client software that needs to be installed prior to use.

(1) Download of Pymysql module

PIP3 Install Pymysql

(2) Use of Pymysql

both the database and the data already exist

# implementation: Use Python to implement user login, if the user exists the login succeeds (assuming that the user is already in the database) import Pymysqluser = input (' Please enter user name: ') pwd = input (' Please enter password: ') # 1. Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', password= ', db= ' DB8 ', charset= ' UTF8 ') # 2. Creating Cursors cursor = Conn.cursor () #注意%s needs to be quoted sql = "SELECT * from UserInfo where username= '%s ' and pwd= '%s '"% (user, PWD) print (SQL) # 3. Execute SQL statement CU Rsor.execute (SQL) result=cursor.execute (SQL) #执行sql语句, returns the number of records for a successful SQL query print (result) # Close the connection, both the cursor and the connection are closed Cursor.close () Conn.close () If result:    print (' login successful ') Else:    print (' Login failed ')

Ii. SQL injection of Execute ()

The last space, in an SQL statement, if you encounter a select * from UserInfo where username= ' mjj '--asadasdas ' and pwd= '--after the condition is commented out (note-there is a space behind) #1, SQL injection: User exists, bypassing password mjj '--Any character # #, SQL injection: User does not exist, bypassing user with password xxx ' or 1=1--any character

Workaround:

# It turns out we're string concatenation of SQL # sql= "SELECT * from UserInfo where name= '%s ' and password= '%s '"% (username,pwd) # Print (SQL) # Result=cur Sor.execute (SQL) #改写为 (execute string concatenation for us, we do not need and must not quote the%s again) sql= "SELECT * from UserInfo where name=%s and password=%s" #!!! Note that%s needs to be stripped of the quotation marks, because Pymysql will automatically add Result=cursor.execute (Sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题, as long as we follow the rules of Pymysql.

Iii. increase, deletion and modification: conn.commit ()

Commit () method: When the database is added, deleted, changed, must be committed, otherwise the inserted data does not take effect.

Import pymysqlusername = input (' Please enter user name: ') pwd = input (' Please enter password: ') # 1. Connect conn = pymysql.connect (host= ' localhost ', port=3306, User= ' root ', password= ', db= ' DB8 ', charset= ' UTF8 ') # 2. Create a cursor cursor = conn.cursor () # operation # add # sql = "INSERT into userinfo (user NAME,PWD) VALUES (%s,%s) "

# effect_row = Cursor.execute (sql, (USERNAME,PWD))
#同时插入多条数据

# print (effect_row) # # change # sql = "Update userinfo set username =%s  where id = 2" # Effect_row = Cursor.execute (sql,usern AME) # print (effect_row) # censored sql = "Delete from userinfo  where id = 2" effect_row = Cursor.execute (SQL) print (effect_row) # Must remember Commitconn.commit () # 4. Close Cursor Cursor.close () # 5. Close Connection Conn.close ()

Four, check: Fetchone, Fetchmany, Fetchall

Fetchone (): Gets the next line of data, first first row; Fetchall (): Get all row data sources Fetchmany (4): Get 4 rows of data

Look at the contents of the table:

Mysql> SELECT * from userinfo;+----+----------+-----+| ID | Username | PWD |+----+----------+-----+|  1 | MJJ      | 123 | |  3 | Zhang San |     |  4 | John Doe     | 119 |+----+----------+-----+3 rows in Set (0.00 sec)

Using Fetchone ():

Import pymysql# 1. Connect conn = pymysql.connect (host= ' localhost ', port=3306, user= ' root ', password= ', db= ' DB8 ', charset= ' UTF8 ') # 2. Creating Cursors cursor = conn.cursor () sql = ' select * from UserInfo ' Cursor.execute (SQL) # Query the first row of data row = Cursor.fetchone () pri NT (Row) # (1, ' mjj ', ' 123 ') # Query the second row of the data row = Cursor.fetchone () print (Row) # (3, ' Zhang San ', ' 110 ') # 4. Close the cursor cursor.close () # 5. Close Connection Conn . Close ()

Using Fetchall ():

Import pymysql# 1. Connect conn = pymysql.connect (host= ' localhost ', port=3306, user= ' root ', password= ', db= ' DB8 ', charset= ' UTF8 ') # 2. Creating Cursors cursor = conn.cursor () sql = ' select * from UserInfo ' Cursor.execute (SQL) # get all the data rows = Cursor.fetchall () pri NT (rows) # 4. Close the cursor cursor.close () # 5. Close the connection conn.close () #运行结果 ((1, ' mjj ', ' 123 '), (3, ' Zhang San ', ' 110 '), (4, ' John Doe ', ' 119 '))

By default, the return value we get is a tuple, only see each row of data, but do not know what each column represents, this time you can use the following way to return the dictionary, each row of data will generate a dictionary:

cursor = conn.cursor (cursor=pymysql.cursors.dictcursor)  #在实例化的时候, set the property cursor to Pymysql.cursors.DictCursor

In the Fetchone example, when you get the row data, you can understand that at the beginning, there is a row pointer pointing at the top of the first row, getting a row, it moves down one line, so when the row pointer to the last row, you can no longer get the contents of the row, so we could use the following method to move the row pointer:

Cursor.scroll (1,mode= ' relative ')  # moves relative to the current position cursor.scroll (2,mode= ' absolute ') # relative absolute position moves the first value to move the number of rows, the integer is moved down, Negative numbers move up, mode specifies whether to move relative to the current position or relative to the first row

# 1.Python Implement User Login # 2.Mysql save data Import pymysql# 1. Connect conn = pymysql.connect (host= ' localhost ', port=3306, user= ' root ', Password= ', db= ' DB8 ', charset= ' UTF8 ') # 2. Creating Cursors cursor = conn.cursor (cursor=pymysql.cursors.dictcursor) sql = ' SELECT * From UserInfo ' Cursor.execute (SQL) # Querying the first row of data row = Cursor.fetchone () print (Row) # (1, ' mjj ', ' 123 ') # Query the second row of data row = Cursor.fet Chone () # (3, ' Zhang San ', ' a ') print (row) cursor.scroll ( -1,mode= ' relative ') #设置之后, the cursor moves forward one line ahead of the current position, so the result of printing is the second row of data row = Cursor.fetchone () print (row) cursor.scroll (0,mode= ' absolute ') #设置之后, the cursor does not change with respect to the first row, so the result of printing is the first row of data row = Cursor.fetchone () print (row) # 4. Close Cursor Cursor.close () # 5. Close Connection Conn.close () #结果如下 {' id ': 1, ' username ': ' mjj ', ' pwd ': ' 123 '} { ' ID ': 3, ' username ': ' Zhang San ', ' pwd ': ' + '} {' id ': 3, ' username ': ' Zhang San ', ' pwd ': ' {'} {' id ': 1, ' username ': ' mjj ', ' pwd ': ' 123 ' }

Fetchmany ():

Import pymysql# 1. Connect conn = pymysql.connect (host= ' localhost ', port=3306, user= ' root ', password= ', db= ' DB8 ', charset= ' UTF8 ') # 2. Creating Cursors cursor = conn.cursor (cursor=pymysql.cursors.dictcursor) sql = ' select * from UserInfo ' cursor.execute (SQL) # get 2 Data rows = Cursor.fetchmany (2) print (rows) # 4. Close the cursor # rows = Cursor.fetchall () # print (rows) cursor.close () # 5. Close Connection Conn.close () #结果如下: [{' id ': 1, ' username ': ' mjj ', ' pwd ': ' 123 '}, {' id ': 3, ' username ': ' Zhang San ', ' pwd ': ' 110 '}]

MySQL creation and user authorization, visual Tools Navicat Basic use, Pymysql module use