PY all the way east of MySQL

I. Overview

MySQL is an associated database management system, and the associated database stores the data in different tables. The python operation MySQL mainly uses the Pymsql module and the ORM Framework Sqlachemy.

Second, MySQL installation

MySQL official: dot here

Windows installation:

1. Download MySQL Community Server 5.7.232, unzip to D:\mysql-5.7.23-winx64 directory 3, Initialize (default does not set the password for the root user) D:\>CD MYSQL-5.7.23-WINX64D:\MYSQL-5.7.23-WINX64>CD Bind:\mysql-5.7.23-winx64\bin>mysqld--initialize-insecure4, Start service D:\mysql-5.7.23-winx64\bin>mysqld.exe5, Client Connection server-side d:\mysql-5.7.23-winx64\bin>mysql.exe-u Root-penter  Password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 2Server version:5.7.23 mysql Community Server (GPL) Copyright (c) #, 2018, Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql>6, add environment variable "Right-click this computer"-"attribute"-"Advanced system Settings"-"Advanced"-"Environment variable"-" In the second content box, find a row with the variable named path, double-click "-" and "new" to "copy the MySQL Bin directory path" 7, registering the Windows service d:\mysql-5.7.23-winx64\bin> Mysqld.exe--installservice successfully installed. If you want to remove a service, you can follow the parameters--remove8, use NET to manage MYsql Service # Start MySQL service net start mysql# shut down MySQL service net stop MySQL 

Linux Installation:

安装：yum install mariadb-server -yyum install mariadb -y服务端启动systemctl start mariadb-server

Iii. basic knowledge of databases 1, database management

 1. Display Database mysql> show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | SYS |+--------------------+4 rows in Set (0.00 sec) 2, user management If you need to add a MySQL user, you only need to add new users to the user table in the MySQL database. Create user ' username ' @ ' IP address ' identified by ' password ', delete user ' username ' @ ' IP address ', modify user rename user ' username ' @ ' IP address '; To ' new user name ' @ ' IP address ';; Modify password Set password for ' username ' @ ' IP address ' = password (' New password ') Overload: Note the FLUSH privileges statement that needs to be executed. This command will reload the authorization table when it is executed. The FLUSH privileges user name @ip address indicates that the user can only access the user name under that IP @192.168.1.% the user name is only allowed under that IP segment (the wildcard% represents any) Username @% means that the user is allowed to access under any IP (default IP address is%) 3, Authorization Admin grants all permissions for JYM users for all databases grant all privileges on *. * to ' jym ' @ ' 192.168.%.% '; View permissions show grants for ' JYM ' @ ' 192.168.%.% '; Permission list all privileges: All permissions except Grant select: Check Permissions only Select,insert: Check and insert Permissions ... For the target database database name. * All database names in the database. The table specifies a table database name in the database. Stored procedures in the specified database for stored procedures * * All databases 

2. Basic operation of the database

1. Create database pydb;2, delete database mysql> drop db pydb;3, mysql> database mysql> use pydb;4, CREATE TABLE mysql> CREATE TABLE IF not EXISTS ' py_tbl ' (-, ' py_id ' INT UNSIGNED auto_increment, ' py_title ' VARCHAR (100) Not null, ' Py_author ' VARCHAR (+) NOT null, ' submission_date ' date,--PRIMARY KEY (' Py_ Id ') Engine=innodb DEFAULT Charset=utf8; Query OK, 0 rows affected (0.02 sec) Resolution: 1.null means null, non-string, not NULL means not nullable. 2.auto_increment defines a property that is self-increasing, typically used for a primary key, and the value is automatically added to 1. The 3.PRIMARY key keyword is used to define the column as the primary key. A primary key is a special unique index that does not allow null values, and if a primary key uses a single column, its value must be unique, and if it is multiple columns, its combination must be unique. 4.ENGINE set the storage engine, CHARSET set the encoding. 5. Delete Tables mysql> drop table py_tb1;6, add mysql> CREATE table py_tb1 (nid int,name varchar (), password varchar); mysql& Gt Insert into PY_TB1 (Nid,name,password) VALUES (1, ' aaa ', ' 123 ');mysql> inserts into PY_TB1 (Nid,name,password) VALUES (2, ' BBB ', ' 123 '); 7, check mysql> SELECT * from py_tb1;+------+------+----------+| Nid | name |    Password |+------+------+----------+| 1 | AAA |    123 | | 2 | BBB | 123 |+------+------+----------+2 rows in Set (0.00 sec) 8, delete mysql> delete from py_tb1 where nid=2 and Name= ' BBB '; 9, change mysql> Update py_tb1 set name= ' newaaa ' where nid=1;

Iv. Pymysql

Pymysql is the module that operates MySQL in Python.

1. Installation

pip3 install pymysql

2. Inserting data

import pymysql# 创建连接conn = pymysql.connect(host=‘127.0.0.1‘, port=3306, user=‘root‘, passwd=‘‘, db=‘pydb‘)# 创建游标cursor = conn.cursor()# 执行SQL，并返回受影响行数ret = cursor.execute("INSERT INTO py_tbl (py_title, py_author, submission_date) VALUES (‘python‘, ‘admin‘, NOW());")# 提交，不然无法保存新建或者修改的数据conn.commit()# 关闭游标cursor.close()# 关闭连接conn.close()

3. Query data

import pymysql.cursors# 创建连接conn = pymysql.connect(host=‘127.0.0.1‘, port=3306, user=‘root‘, passwd=‘‘, db=‘pydb‘)# 创建游标cursor = conn.cursor()sql = "SELECT * FROM py_tbl;"# 执行SQLcursor.execute(sql)# 查询数据库单条数据#ret = cursor.fetchone()#print(ret)# 查询数据库多条数据ret = cursor.fetchall()for data in ret:    print(data)# 关闭数据连接conn.close()

Query results

(1, ‘MySQL‘, ‘root‘, datetime.date(2018, 8, 3))(2, ‘python‘, ‘admin‘, datetime.date(2018, 8, 3))

4. Main methods of Pymysql

cursor()：创建数据库游标。execute()：执行SQL语句。commit()：将数据库的操作真正的提交到数据。fetchone() ：查询单条数据。fetchall() ：查询多条数据。close() ：关闭数据连接。

PY all the way east of MySQL