14. MysSQL for Python3

Database concept

Database: A warehouse that organizes, stores, and manages data according to the data structure`

Born

The computer was invented for scientific calculations, and scientific calculations require a lot of input and output. In the early days, the holes of the punched card and the on and off of the light bulbs could be used to indicate data input and output. Later, the data can be stored on tapes in sequence Reading and writing tapes. In 1956, IBM invented the revolutionary product of disk drives, which supports random access. With the advent of the information age, with the development of hardware storage technology, there is a large amount of data that needs to be stored and managed, and database management.

classification

 # Classification by data model: 
 #  Mesh database
 #  Hierarchical database
 #  Relational Database

Hierarchical database

Represent entities and their relationships in a tree structure. The relationship supports one-to-many, representing the database IMM IMS, which cannot be crossed, and is very suitable for use in some places.

Mesh database

General Electric developed a mesh database IDS as early as 1964, which can only run on GE's own mainframe.

Nodes describe data, and the connection of nodes is the relationship of data. It can directly describe the objective world and can represent a variety of complex relationships between entities, which is not possible with the hierarchical data model. For example, a node can have multiple parent nodes. Supports many-to-many associations between nodes.

relational database

Use a two-dimensional table composed of rows and columns to organize data and relationships. Rows (records) in the table can describe both data entities and relationships between entities. The relational model is simpler than the mesh model and the hierarchical model, and does not require relational data storage Physics looting, focusing on the logical construction of data, and the relational model is supported by the strict data theory foundation of the paper. In 1970, IBM researcher EF Codd published a paper called'A Relational Model of Data for Large Shared Data Banks', The concept of relational model was put forward and the theoretical foundation of relational model was laid. The relational model has a strict data foundation, high abstraction level, simple and clear, easy to understand and use. After decades of development, relational databases have flourished and technology Mature and perfect. The database system based on the relational model has become RDBMS (Relational DataBase Sytem). IBM DB2, Oracle's Oracle and Mysql, Microsoft's MS SQL, formerly Infomix, Sybase, etc.

Oracle's development

Larry Ellison (Larry EMison) carefully read IBM's relational database papers, and was sensitive to realize that software systems can be used by developers based on this research. They decided to develop a general-purpose commercial database system called Oracle. The name of the project that the CIA did. A few months later, they developed Oracle 1.0. Oracle was quickly promoted, but it was very unstable. It was not until 1992 that Oracle 7 gradually stabilized and achieved great success. The 2001 version of 9i is widely used.

2009 On April 20, 2010, Oracle announced the acquisition of Sun (Computer Systems) at a price of US$9.50 per share, totaling US$7.4 billion. It was successfully acquired in January 2010. In 2013, Oracle surpassed IBM and became the second largest in the world after Microsoft software company.

Mysql development

1985 Several Swedes designed a software for the projects of large retailers to access data sequentially using indexes. He was the predecessor of MyISAM. In 1996, MySQL 1.0 was released, followed by the release of version 3.11.1, and began to use other platforms Migration, in 2000 MySQL adopted GPL agreement to open source, MySQL 4.0 adopted GPL agreement to open source, MySQL 4.0 began to support MyISAM, InnoDB. In October 2005, MySQL 5.0 became a milestone version. In January 2008, it was acquired by Sun in 2009. In August, before Oracle acquired Mysql, Monty Widenius was worried about the acquisition, and started a new GPL branch from Mysql Server 5.5, named MariaDB. MySQL’s engine is plug-in and can support many types of engines: MyISASM, does not support transactions. Insert, fast query speed. InnoDB, support transaction, row-level lock, default engine from Mysql5.5.

Go to IOE

He is a concept created by Alibaba. Its original intention is to remove IBM minicomputers, Oracle databases, and EMC storage devices from Alibaba’s IT architecture and replace them with systems developed on the basis of open source software. Traditionally, A high-end and atmospheric data center, IBM minicomputers, Oracle databases, EMC storage devices, can be said to be indispensable, and enterprises using these architectures not only have extremely high maintenance costs, but the core architecture is also in the hands of others.

For large-scale Internet applications such as Alibaba, the use of open source and open system architecture is not invented by Alibaba. Foreign companies such as Google, Facebook, and Amazon have already done so, but they almost did not adopt IT commercial companies in the beginning. Architecture, so they don’t have to "go to IOE"

Go to IOE and switch to a cheap architecture, the stability will definitely decrease, and a higher level of operation and maintenance is required.

NoSQL

NoSQL is a collective term for non-SQL, non-traditional relational databases. The term NoSQL was born in 1998. In 2009, this term was again proposed to refer to non-relational, distributed, and no ACID database design patterns.
With the advent of the Internet era, the explosive growth of data and the rapid development of database technology have to adapt to new business needs. With the advent of the mobile Internet and the Internet of Things, NoSQL is equally important in the technology of big data.

What is a database?

What is data?
Data is the result of facts or observations. It is a logical induction of objective things. It is raw raw material used to represent objective things. Data is the manifestation and carrier of information. It can be symbols, words, numbers, Voice, image, video, etc., data and information are inseparable, data is the expression of information, information is the connotation of data, data itself has no meaning, and data only becomes information when it has an impact on the behavior of the entity. In a computer system, data is represented in the form of binary information unit 0, 1.
Database is a warehouse that organizes, stores and manages data according to data structure.
Each database has one or more different APIs for creating, accessing, managing, searching and copying the saved data.
We can also store data in files, but the speed of reading and writing data in files is relatively slow.
So, now we use [relational database] (https://cloud.tencent.com/product/cdb-overview?from=10680) management system (RDBMS) to store and manage the large amount of data. The so-called relational database is a database built on the basis of a relational model, which uses mathematical concepts and methods such as set algebra to process data in the database.
RDBMS is the characteristics of relational database management system (Relational Database Management System):

# 1. The data appears in the form of a table
# 2. Various record names per line
# 3. Each column is the data field corresponding to the record name
# 4. Many rows and columns form a form
# 5. Several forms form a database

RDBMS terminology

# database: database是一些关联表的集合。.  
# data sheet:The table is a matrix of data. A table in a database looks like a simple spreadsheet.
# Column:一Column(Data element)Contains the same data,For example, postal code data.
# Line: one line (=Tuples, or records) are a group of related data, such as a piece of data subscribed by a user.
# Redundancy: Store twice the data, redundancy can make the system faster.(The higher the degree of normalization of the table, the more the relationship between the table and the table; the query may often require a connection query between multiple tables; and the connection operation will reduce the query speed. For example, student information is stored in the student table, and department information is stored in the department table. Pass dept in the student table_The id field establishes an association relationship with the department table. If you want to query the name of a student’s department, you must find the student’s department number (dept_id), and then find the department name according to this number. If you often need to perform this operation, the connection query will waste a lot of time. Therefore, a redundant field dept can be added to the student table_name, this field is used to store the name of the student's department. In this way, there is no need to connect every time.)

# Primary key: The primary key is unique. A data table can only contain one primary key. You can use the primary key to query data.
# Foreign key: A foreign key is used to associate two tables.
# Composite key: A composite key (composite key) uses multiple columns as an index key and is generally used for composite indexes.
# Index: Use indexes to quickly access specific information in database tables. An index is a structure for sorting the values of one or more columns in a database table. Similar to a catalog of books.

# Referential integrity:Referential integrity requires that non-existent entities are not allowed in the relationship. Integrity with entity is the integrity constraint condition that the relational model must meet, the purpose is to ensure the consistency of data.

Database classification?

Relational database: There are tables in the database, and several tables are related and have common columns, which is called a relational database.
Oracle<1521>,DB2<5000>,Mysql,Mariadb<3306>,SQLServer<1433>

> Non-relational database:Usually based on Key-Stored in value form, does not support SQL statements, has no table structure, simple configuration, low learning cost, and can be used as a Mysql middle layer:  

​```python
# 1. Key-value storage: Redis<6379> Memcached<11211>, because it has no data structure compared to other data storage, and it works in memory, so the performance is very high.
# 2. Columnar storage: HBase:
# 3. Document storage: Documentation, MongoDB<27017>

Distributed database:Data distribution through the fragmentation mechanism, each node can receive client requestsAnd holds part of the global metadata.

# Hadoop (HDFS): suitable for large file storage, Apache products, java programming
# FastDFS (open source software): Suitable for small file storage (network disk, short video, images), and has good support for high concurrency.

**Mysql is a relational database management software that supports network access. The default service port is 3306.**MySQL communication uses mysql protocol.Because the database must ensure data security and integrity, TCP is generally used.

Mysql is the most popular relational database management system, MySQL is the best RDBMS in terms of web applications(Relational Database Management System: Relational Database Management System)One of the application software. Developed by the Swedish MySQL AB company, currently belongs to Oracle company. MySQL is a relational database management system. The relational database stores data in different tables instead of putting all the data in a large warehouse, which increases speed and flexibility.

# Mysql is open source, so you don't need to pay additional fees.
# Mysql supports large databases. It can handle large databases with tens of millions of records.
# MySQL uses the standard SQL data language format.
# Mysql can be allowed on multiple systems and supports multiple languages. These programming languages include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby and Tcl.
# Mysql has good support for PHP, which is currently the most popular web development language.
# MySQL supports large databases, a data warehouse with 50 million records, a 32-bit system table file can support a maximum of 4GB, and a 64-bit system supports a maximum table file of 8TB.
# Mysql can be customized, using the GPL agreement, you can modify the source code to develop your own Mysql system.

Install Mysql5.7

Install mysql of rpm package#####

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

# It is recommended to use mysql5.7.23 version, the following uses 5.7.23 version
yum -y install perl libaio expect  
tar xvf mysql-5.7.23-1.el7.x86_64.rpm-bundle.tar  
rpm -ivh mysql-community-server-5.7.23-1.el7.x86_64.rpm \  
mysql-community-client-5.7.23-1.el7.x86_64.rpm \  
mysql-community-common-5.7.23-1.el7.x86_64.rpm \  
mysql-community-libs-5.7.23-1.el7.x86_64.rpm  
systemctl start mysqld  
  
changepass(){  
sed -i '/\[mysqld]/ a skip-grant-tables'/etc/my.cnf  
systemctl restart mysqld  
mysql <<EOF  
 update mysql.user set authentication_string='' where user='root' and Host='localhost';  
 flush privileges;  
EOF  
sed -i '/skip-grant/d'/etc/my.cnf  
systemctl restart mysqld  
yum -y install expect ntp  
  
expect <<-EOF  
spawn  mysqladmin -uroot -p password "ZHOUjian.20"  
 expect {"password"{ send "\r"}}  
 expect eof  
EOF  
systemctl restart mysqld  
}  
changepass  
  
# Authorize remote login
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'ZHOUjian.21' WITH GRANT OPTION;   
  
FLUSH PRIVILEGES;

Mysql.Install by tar package#####

Install the source code Mysql
#! /usr/bin/env bash  
# Author: ZhouJian  
# Mail:[email protected]  
# Time:2019-9-3  
# Describe: CentOS 7 Install Mysql.tar Script # Here is the compiled tar package, which is under compilation and maintenance
Deplay(){  
rpm -e mariadb-libs --nodeps  
setenforce 0  
systemctl stop firewalld  
systemctl enable firewalld  
sed -i '/^SELINUX=/ s/enforcing/disabled'/etc/ssh/sshd_config  
sed -i '/^GSSAPIAu/ s/yes/no/'/etc/ssh/sshd_config  
sed -i '/^#UseDNS/ {s/^#//;s/yes/no}'/etc/ssh/sshd_config  
  
id mysql >/dev/nullif[ $?-eq 0];then  
echo "mysql user exist"else  
groupadd mysql  
useradd -M -s /sbin/nologin mysql -g mysql  
fi  
if[!-d /usr/local/mysqld ];then  
tar xf mysql-5.7.26-bin.tar.xz -C /usr/local/  
chown mysql.mysql /usr/local/mysqld/-R  
fi  
echo "export PATH=$PATH:/usr/local/mysqld/mysql/bin">>/etc/profile  
source /etc/profile  
cat >/etc/my.cnf <<EOF  
[ mysqld]  
basedir =/usr/local/mysqld/mysql  
datadir =/usr/local/mysqld/data  
tmpdir =/usr/local/mysqld/tmp  
socket =/usr/local/mysqld/tmp/mysql.sock  
pid_file =/usr/local/mysqld/tmp/mysqld.pid  
log_error =/usr/local/mysqld/log/mysql_error.log  
slow_query_log_file =/usr/local/mysqld/log/slow_warn.log  
user = mysql  
port =3306  
bind-address =0.0.0.0  
character-set-server = utf8  
default_storage_engine = InnoDB  
EOF  
  
ln -s /usr/local/mysqld/mysql/support-files/mysql.server /usr/bin/mysqldctl  
mysqldctl start  
ln -s /usr/local/mysqld/tmp/mysql.sock /tmp/mysql.sock  
mysqldctl restart  
sed -i '/\[mysqld]/ a skip-grant-tables'/etc/my.cnf  
mysqldctl restart  
mysql <<EOF  
update mysql.user set authentication_string='' where user='root' and Host='localhost';  
flush privileges;  
EOF  
sed -i '/skip-grant/d'/etc/my.cnf  
mysqldctl restart  
yum -y install expect ntp  
cat >/etc/ntp.conf << EOF  
restrict default nomodify  
server 127.127.1.0  
fudge 127.127.1.0 stratum 10  
EOF  
systemctl start ntpd ; systemctl enable ntpd  
expect <<-EOF  
spawn mysqladmin -uroot -p password "ZHOUjian.20"  
expect {"password"{ send "\r"}}  
expect eof  
EOF  
mysqldctl restart  
}  
Deplay  

Install Yum's mysql#####

# Use wget to download yum source installation
wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm  
  
# Install yum source
rpm -ivh mysql57-community-release-el7-8.noarch.rpm   
  
# Install mysql
yum -y install mysql-server  

Modify Mysql password#####

# There are three ways to modify the Mysql password
# 1. You can get the temporary password from /var/log/mysqld.log for the newly installed mysql
grep  "password"/var/log/mysqld.log   
 [ root@mysql ~]# mysql -uroot -p  
 Enter password:  
 mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'ZHOUjian.22';  
  
# 2. mysqladmin -uroot -p password "Baidu.123.com"  
 Enter password:  
  
# 3. The experimental environment does not know the root password. The operation method is as follows
sed -i '/\[mysqld]/ a skip-grant-tables'/etc/my.cnf  
systemctl restart mysqld  
mysql <<EOF  
 update mysql.user set authentication_string='' where user='root' and Host='localhost';  
 flush privileges;  
EOF  
sed -i '/skip-grant/d'/etc/my.cnf  
systemctl restart mysqld  
mysqladmin -uroot -p password "ZHOUjian.20"  
Enter password: # press enter here
  
# 4. mariadb change password
use mysql   
UPDATE user SET password=password('ZHOUjian.20') WHERE user='root';   
MariaDB [mysql]> flush privileges;  
  
# If you feel that it is troublesome to log in to Mysql to enter the password, you can use the following methods, you can enter the database only with mysql
vim /etc/my.cnf  
[ client]  
password=admin  
user=root  
systemctl restart mysqld Or mariadb  

Remember to authorize a remote access user to Mysql before doing the following experiment

grant all privileges on *.* to admin@"%" identified by 'ZHOUjian.21' withth grant option;

flush privileges;

SQL introduction:

# SQL language is mainly used to access data, query data, update data and manage relational database systems, developed by IBM, and divided into four types
# DDL statement database definition language (Create, Alter, Drop, Declare)
# Used to define or change the structure of the table, data types, connections and constraints between tables and other initialization work, they are mostly used when creating tables.
  
# DML statement database operation language (Select, Delete, Update, Insert) # The language used to manipulate the data in the database.
  
# DCL statement database control language (GRANT, REVOKE, COMMIT, ROLLBACK)
# Statements used to set or change database or role permissions can only be executed by sysadmin, dbcreator, db_owner, etc.
  
# DQL statement database query language (select)

If you want to know more usages of mysql, please see my mysql column

https://www.cnblogs.com/you-men/tag/Mysql/

Mysql data type####

The type of data field defined in Mysql is very important for the optimization of your database.
Mysql supports multiple types, which can be roughly divided into three categories:Value, date/Time and string type

Numeric type#####

Mysql supports all standard SQL numeric data types
These types include strict numeric types(INTEGER, SMALLINT, DECIMAL and NUMERIC),And approximate numeric types(FLOAT, REAL and DOUBLE PRECISION),The keyword INT is a synonym for INTEGER, and the keyword DEC is a synonym for DECIMAL.

The BLT data type saves bit field values and supports MyISAM, MEMORY, InnoDB and BOB tables.
As an extension of the SQL standard,Mysql also supports the integer types TINYINT, MEDIUMINT and BIGINT,The following table shows the storage and range of each integer type required

Date and time type#####

The date and time types that represent time values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.

Each time type has a valid value range and a"zero"Value, used when specifying an invalid value that MySQL cannot represent"zero"value.

The TIMESTAMP type has a proprietary automatic update feature, which will be described later.

String type#####

String types refer to CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. This section describes how these types work and how to use them in queries.
CHAR and VARCHAR types are similar, but they are stored and retrieved in different ways. Their maximum length and whether trailing spaces are preserved are also different. No case conversion is performed during storage or retrieval.
The BINARY and VARBINARY classes are similar to CHAR and VARCHAR, except that they contain binary strings rather than non-binary strings. That is, they contain byte strings rather than character strings. This means that they do not have a character set, and sorting and comparison are based on the numeric value of the column value byte.
BLOB is a binary large object that can hold a variable amount of data. There are 4 BLOB types: TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB. They differ only in the maximum length of the value that can be accommodated.
There are 4 TEXT types: TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT. These correspond to 4 BLOB types and have the same maximum length and storage requirements.

mysql-connector library####

We can use the pip command to install mysql-connector

python -m pip install mysql-connector

Use the following code to test mysql-Whether the connector is installed successfully:

import mysql.connector   
    
# Execute the above code, if no error is reported, the installation is successful
# Note: If your MySQL is version 8.0, the password plug-in authentication method has changed. The earlier version is mysql_native_password,
# 8.0 The version is caching_sha2_password, so some changes are needed:

# First modify the my.ini configuration:
[ mysqld]   
default_authentication_plugin=mysql_native_password   
    
# Then execute the following command under mysql to modify the password:
ALTER USER'root'@'localhost' IDENTIFIED WITH mysql_native_password BY'new password';

Create database connection#####

# We can use the following code to connect to the database
import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="121.36.43.123", # Database host address
 user="admin", # database user name
 passwd="ZHOUjian.21" # Database password
 # database ="youmen_db" # Here you can directly connect to the specified database, if the database does not exist, an error message will be output
) print(mydb)

Create database#####

Create a database to use"create database"Statement, the following creates a named youmen_db database:

import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21")  
  
mycursor = mydb.cursor()  
mycursor.execute("create database youmen_db")

Output a list of all databases

import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21")  
  
mycursor = mydb.cursor()  
mycursor.execute("show databases")for x in mycursor:print(x)('information_schema',)('mysql',)('performance_schema',)('sys',)('youmen_db',)

Mysql common operations#####

Create data table#####

grammar

create table table_name(column_name column_type);

Create a data table to use"create table"Statement, before creating a data table, make sure that the database already exists,The following creates a data table named sites:

import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21",  
 database="youmen_db")  
  
mycursor = mydb.cursor()  
mycursor.execute("create table sites(name VARCHAR(255),url VARCHAR(255))")

Example1

#! /usr/bin/env python3  
#- *- coding:utf-8-*-  
# 2020 /2/1220:24import mysql.connector  
  
mydb = mysql.connector.connect(  
 host='116.196.83.113',  
 user='root',  
 passwd='ZHOUjian.20',  
 database='youmen_db')  
  
mycursor=mydb.cursor()  
mycursor.execute("create table student(stu_id INT NOT NULL AUTO_INCREMENT,name CHAR(32) NOT NULL ,age INT NOT NULL,register_date DATE,PRIMARY KEY(stu_id))")

If you don't want the field to be NULL,The attribute of the field can be set to NOT NULL, and if the data entered in the field is NULL during data manipulation, an error will be reported.
AUTO_INCREMENT defines the column as a self-incrementing attribute, generally used for the primary key, and the value will automatically increase by one.
The PRIMAPY KEY keyword is used to define the column as the primary key. It can use multiple columns to define the primary key. The columns are separated by commas..

Primary key settings

**When creating a table, we generally set a primary key (PRIMARY KEY), we can use"INT AUTO_INCREMENT PRIMARY KEY"Statement to create a primary key, the initial value of the primary key is 1, gradually increasing.**If our table has been created, we need to use ALTER TABLE to add a primary key to the table

# Add a primary key to the sites table
import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21",  
 database="youmen_db")  
  
mycursor = mydb.cursor()  
  
mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")  
# If you have not created the sites table, you can directly use the following code to create
mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")

Insert data

Insert data use"INSERT INTO"Statement:

# Insert data using INSERT INTO statement
# Insert a record into the sites table
import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21",  
 database="youmen_db")  
  
mycursor = mydb.cursor()  
  
sql ="INSERT INTO sites (name,url) VALUES (%s,%s)"  
val =("youmen_db","https:/www.youmen.com")  
mycursor.execute(sql,val)  
  
mydb.commit() # The data table content is updated, this statement must be used
  
print(mycursor.rowcount,"Record inserted successfully.")1 Record inserted successfully.

Bulk insert

# Batch insertion uses the executemany() method. The second parameter of the method is a list of tuples, which contains the data we want to insert
import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21",  
 database="youmen_db")  
  
mycursor = mydb.cursor()  
  
sql ="INSERT INTO sites (name,url) VALUES (%s,%s)"  
val =[("youmen_db","https://www.youmen.com"),("GitHub","https://www.github.com"),("Taobao","https://www.taobao.com"),]  
mycursor.executemany(sql,val)  
  
mydb.commit() # The data table content is updated, this statement must be used
  
print(mycursor.rowcount,"Record inserted successfully.")3 Record inserted successfully.
# If you want to get the ID of the record after inserting the data record, you can use the following code:
# print(mycursor.rowcount,"The record is inserted successfully.",mycursor.lastrowid)
# 3 The record is inserted successfully. 5

Query data#####

# Query data using SELECT statement
import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21",  
 database="youmen_db")  
  
mycursor = mydb.cursor()  
  
mycursor.execute("SELECT * FROM sites")  
  
myresult = mycursor.fetchall() # fetchall() fetch all records
  
for i in myresult:print(i)('youmen_db','https:/www.youmen.com',1)('youmen_db','https://www.youmen.com',2)('GitHub','https://www.github.com',3)('Taobao','https://www.taobao.com',4)  
  
# You can also read only the specified field data
# Replace the * in mycursor.execute("SELECT * FROM sites") with the field you need to check. If there are multiple fields, separate them with','

If we only want to read a piece of data,Can use fetchone()method:

# Only query one piece of data
import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21",  
 database="youmen_db")  
  
mycursor = mydb.cursor()  
mycursor.execute("SELECT * FROM sites")  
myresult = mycursor.fetchone() # fetchall()Get all records
print(myresult)('youmen_db','https:/www.youmen.com',1)

where conditional statement#####

If we want to read data with specified conditions, we can use the where statement:

# Read the record whose name field is youmen_db:
import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21",  
 database="youmen_db")  
  
mycursor = mydb.cursor()  
sql ="SELECT * FROM sites WHERE name = 'youmen_db'"  
# Wildcards can be used for condition search here
# ` sql = "SELECT * FROM sites WHERE url LIKE '%men%'"`  
  
mycursor.execute(sql)  
myresult = mycursor.fetchall()for x in myresult:print(x)('youmen_db','https:/www.youmen.com',1)('youmen_db','https://www.youmen.com',2)('youmen_db','https://www.youmen.com',5)

To prevent SQL injection attacks from database queries, we can use%s placeholder to escape the query conditions:

import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21",  
 database="youmen_db")  
  
mycursor = mydb.cursor()  
sql ="SELECT * FROM sites WHERE name = %s"  
na =("youmen_db",)  
mycursor.execute(sql,na)  
myresult = mycursor.fetchall()for x in myresult:print(x)('youmen_db','https:/www.youmen.com',1)('youmen_db','https://www.youmen.com',2)('youmen_db','https://www.youmen.com',5)

Sort#####

You can use the ORDER BY statement to sort the query results. The default sorting method is ascending, and the keyword is ASC,If you want to set descending order, you can set the keyword DESC.

# Sort in ascending alphabetical order of the name field
import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21",  
 database="youmen_db")  
  
mycursor = mydb.cursor()  
sql ="SELECT * FROM sites ORDER BY name"  
mycursor.execute(sql)  
myresult = mycursor.fetchall()for i in myresult:print(i)('GitHub','https://www.github.com',3)('GitHub','https://www.github.com',6)('Taobao','https://www.taobao.com',4)('Taobao','https://www.taobao.com',7)('youmen_db','https:/www.youmen.com',1)

Sort descending

# Sort in ascending alphabetical order of the name field
import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21",  
 database="youmen_db")  
  
mycursor = mydb.cursor()  
sql ="SELECT * FROM sites ORDER BY name DESC"  
mycursor.execute(sql)  
myresult = mycursor.fetchall()for i in myresult:print(i)('youmen_db','https:/www.youmen.com',1)('youmen_db','https://www.youmen.com',2)('youmen_db','https://www.youmen.com',5)('Taobao','https://www.taobao.com',4)('Taobao','https://www.taobao.com',7)('GitHub','https://www.github.com',3)

Limit

We want to set the amount of data to be queried, you can pass"LIMIT"Statement to specify

import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21",  
 database="youmen_db")  
  
mycursor = mydb.cursor()  
sql ="SELECT * FROM sites LIMIT 3"  
# Read the first 3 records from the second
# sql ="SELECT * FROM sites LIMIT 3 OFFSET 1"   
  
mycursor.execute(sql)  
myresult = mycursor.fetchall()for i in myresult:print(i)('youmen_db','https:/www.youmen.com',1)('youmen_db','https://www.youmen.com',2)('GitHub','https://www.github.com',3)

Delete Record#####

Delete record use"DELETE FROM"Statement

# Delete the record whose name is youmen_db
import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21",  
 database="youmen_db")  
  
mycursor = mydb.cursor()  
sql ="DELETE FROM sites WHERE name = 'youmen_db'"  
  
# In order to prevent SQL injection attacks in database queries, we can use the %s placeholder to escape the condition of the delete statement.
# sql ="DELETE FROM sites WHERE name = %s"  
# na =("youmen_db",)  
# mycursor.execute(sql,na)  
  
mycursor.execute(sql)  
mydb.commit()print(mycursor.rowcount,"record delete") 3 records deleted
  
# Use the delete statement carefully. Make sure to specify the WHERE conditional statement for the delete statement, otherwise the entire table data will be deleted.

Update table data#####

Database update usage"UPDATE"Statement

import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21",  
 database="youmen_db")  
  
mycursor = mydb.cursor()  
sql ="UPDATE sites SET name = '%ao%' WHERE name = 'ZHOU'"  
mycursor.execute(sql)  
mydb.commit()print(mycursor.rowcount," records were modified")
  
# The UPDATE statement ensures that the WHERE conditional statement is specified, otherwise the entire table data will be updated
# In order to prevent SQL injection in database queries, we can use the %s placeholder to escape the condition of the update statement:

import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21",  
 database="youmen_db")  
  
mycursor = mydb.cursor()  
sql ="UPDATE sites SET name = %s WHERE name = %s"  
val =("GitHub","ZHOU")  
mycursor.execute(sql,val)  
mydb.commit()print(mycursor.rowcount," records were modified")

6.11 Delete table#####

Delete table use"DROP TABLE"Statement, the IF EXISTS keyword is used to determine whether the table exists, and only delete it if it exists

import mysql.connector  
  
mydb = mysql.connector.connect(  
 host="116.196.83.113",   
 user="root",   
 passwd="ZHOUjian.21",  
 database="youmen_db")  
  
mycursor = mydb.cursor()  
sql ='DROP TABLE IF EXISTS sites' # Delete data table sites
mycursor.execute(sql)

PyMySQL for Python3####

What is PyMySQL?

PyMySQL is in Python3.A library used to connect to MySQL services in the x version, Python2 uses mysqldb.
PyMySQL follows the Python database API v2.0 specification, and includes pure-Python Mysql client library

PyMySQL installation****Before using PyMySQL, we need to make sure that PyMySQL has been installed.

PyMySQL download link:https://github.com/PyMySQL/PyMySQL。
If it is not already installed, we can install the latest version of PyMySQL using the following command:

pip3 install PyMySQL

If your system does not support the pip command, you can install it in the following way:

1. Use the git command to download the installation package and install it (you can also download it manually)

$ git clone https://github.com/PyMySQL/PyMySQL
$ cd PyMySQL/
$ python3 setup.py install

1. If you need to specify the version number, you can use the curl command to install**

$ # XX is the version number of PyMySQL
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python3 setup.py install
$ # Now you can delete the PyMySQL* directory

# The error message "ImportError: No module named setuptools" may appear during the installation process,
# It means you don’t have setuptools installed, you can visit
# [ https://pypi.python.org/pypi/setuptools] Find out how to install each system.

# Linux system installation example:
$ wget https://bootstrap.pypa.io/ez_setup.py
$ python3 ez_setup.py

Database Connectivity#####

# Before connecting to the database, please confirm the following:
# The Python MySQLdb module is installed on your machine:

Example1:Connect to the database

import pymysql
# Open database connection
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21")

# Use the cursor() method to create a cursor object cursor
cursor = db.cursor()

# Use execute() method to execute SQLc query
cursor.execute("SELECT VERSION()")

# Use the fetchone() method to get a single piece of data.
data = cursor.fetchone()print("Database version: %s"% data)

# Close database connection
db.close()

Database version:5.7.23

2.1 Create database table#####

If the database connection exists, we can use excute()Method to create a library for the database, table.

Create database youmen_db

import pymysql
# Open database connection
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21")

# Use the cursor() method to create a cursor object cursor
cursor = db.cursor()

# Use execute() method to execute SQLc query
cursor.execute("CREATE DATABASE youmen_db")

Create database table

import pymysql
# Open database connection
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21","youmen_db")

# Use the cursor() method to create a cursor object cursor
cursor = db.cursor()

# Use execute() method to execute SQL, delete if it exists
cursor.execute("DROP TABLE IF EXISTS NOTES")

# Create a table using prepared statements
sql ="""CREATE TABLE NOTES(
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,  
   SEX CHAR(1),
   INCOME FLOAT )"""
 
cursor.execute(sql)

# Close database connection
db.close()

2.2 Database insert operation#####

The following example uses the SQL INSERT statement to insert records into the NOTES table:

import pymysql
# Open database connection
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21","youmen_db")

# Use the cursor() method to create a cursor object cursor
cursor = db.cursor()

# Use cursor() method to get operation cursor
cursor = db.cursor()
 
# SQL insert statement
sql ="""INSERT INTO NOTES(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)VALUES('Mac','DELL',20,'M',2000)"""
try:
 # Execute sql statement
 cursor.execute(sql)
 # Submit to the database for execution
 db.commit()
except:
 # Roll back if an error occurs
 db.rollback()
 
# Close database connection
db.close()

Example2****The above example can be written in the following format:

import pymysql
# Open database connection
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21","youmen_db")

# Use the cursor() method to create a cursor object cursor
cursor = db.cursor()

# Use cursor() method to get operation cursor
cursor = db.cursor()
 
# SQL insert statement
sql ="INSERT INTO NOTES(FIRST_NAME,\
 LAST_NAME,AGE,SEX,INCOME) \
 VALUES ('%s','%s',%s,'%s',%s)"% \
 (' air','level',21,'M',1999)try:
 # Execute SQL statement
 cursor.execute(sql)
 # Execute sql statement
 db.commit()
except:
 # Roll back when an error occurs
 db.rollback()

# Close database connection
db.close()

The following code uses variables to pass parameters to SQL

..................................
user_id ="test123"
password ="password"

con.execute('insert into Login values( %s,  %s)'% \
    ( user_id, password))..................................

2.3 Database query operation#####

Python query MySQL using fetchone()Method to get a single piece of data, use fetchall()Method to get multiple data.

• fetchone():This method gets the next query result set, which is an object.
• fetchall():Accept all returned result rows.
• rowcount:This is a read-only attribute and returns to execute execute()The number of rows affected after the method.

**Example3:**Query salary in the NOTES table(wage)All data with a field greater than 1000

import pymysql
# Open database connection
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21","youmen_db")

# Use cursor() method to get operation cursor
cursor = db.cursor()
 
# SQL query
sql ="SELECT * FROM EMPLOYEE \
  WHERE INCOME > %s"%(1000)try:
 # Execute SQL statement
 cursor.execute(sql)
 # Get a list of all records
 results = cursor.fetchall()for row in results:
  fname = row[0]
  lname = row[1]
  age = row[2]
  sex = row[3]
  income = row[4]
  # Print result
  print("fname=%s,lname=%s,age=%s,sex=%s,income=%s"% \
    ( fname, lname, age, sex, income ))

except:print("Error: unable to fetch data")
 
# Close database connection
db.close()

# The script execution results are as follows:
fname=Mac, lname=Mohan, age=20, sex=M, income=2000

2.4 Database update operation#####

**The update operation is used to update the data in the data table. The following example increments the field of SEX as M in the NOTES table by 1.**Example4

import pymysql
# Open database connection
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21","youmen_db")

# Use cursor() method to get operation cursor
cursor = db.cursor()
 
# SQL update statement
sql ="UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'"%('M')try:
 # Execute SQL statement
 cursor.execute(sql)
 # Submit to the database for execution
 db.commit()
except:
 # Roll back when an error occurs
 db.rollback()
 
# Close database connection
db.close()

2.5 Delete operation#####

The delete operation is used to delete the data in the data table. The following example demonstrates deleting all data in the data table EMPLOYEE with AGE greater than 20

import pymysql
# Open database connection
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21","youmen_db")

# Use cursor() method to get operation cursor
cursor = db.cursor()
 
# SQL delete statement
sql ="DELETE FROM EMPLOYEE WHERE AGE > %s"%(20)try:
 # Execute SQL statement
 cursor.execute(sql)
 # Submit changes
 db.commit()
except:
 # Roll back when an error occurs
 db.rollback()
 
# Close the connection
db.close()

2.6 Execute transaction#####

Transaction mechanism can ensure data consistency****The transaction should have 4 attributes:Atomicity, consistency, isolation, and durability, these four attributes are usually called ACID characteristics
**d

**Atomicity(atomicity)**A feature is an indivisible unit of work,All operations included in the transaction are either done or not done

consistency(consistency) 事务必须使数据库从一个consistency状态到另一个consistency状态，consistency与原子性是密切相关的

**Isolation(isotation)**The execution of a transaction cannot be interfered by other transactions. That is to say, the internal operations of a transaction and the data used are isolated from other concurrent transactions, and each transaction executed concurrently cannot interfere with each other.

**Persistence(durability).**Persistence is also called permanent(permanence),Refers to once a transaction is committed, his changes to the data in the database should be permanent.The following other operations or failures should not have any effect on it.

Python DB API2.0 transaction provides two methods commit or rollback.

import pymysql
# Open database connection
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21","youmen_db")

# Use cursor() method to get operation cursor
cursor = db.cursor()
 
# SQL delete record statement
sql ="DELETE FROM NOTES WHERE AGE > %s"%(20)try:
 # Execute SQL statement
 cursor.execute(sql)
 # Submit to the database
 db.commit()
except:
 # Roll back when an error occurs
 db.rollback()

For databases that support transactions, in Python database programming, when a cursor is created, an invisible database transaction is automatically started.

commit()All update operations of the method cursor, the rollback() method rolls back all operations of the current cursor. Each method starts a new transaction.

2.7 Error handling#####

Some database operation errors and exceptions are defined in the DB API. These errors and exceptions are listed below

2.8 with to simplify database operations#####

import pymysql

classDB():
 def __init__(self, host='localhost', port=3306, db='', user='root', passwd='root', charset='utf8'):
  # establish connection
  self.conn = pymysql.connect(host=host, port=port, db=db, user=user, passwd=passwd, charset=charset)
  # Create a cursor, the operation is set to dictionary type
  self.cur = self.conn.cursor(cursor = pymysql.cursors.DictCursor)

 def __enter__(self):
  # Return cursor
  return self.cur

 def __exit__(self, exc_type, exc_val, exc_tb):
  # Submit the database and execute
  self.conn.commit()
  # Close cursor
  self.cur.close()
  # Close database connection
  self.conn.close()if __name__ =='__main__':withDB(host='192.168.68.129',user='root',passwd='zhumoran',db='text3')as db:
  db.execute('select * from course')print(db)for i in db:print(i)

1