MySQL in python

1> Installation of mariadb under. Linux

1.Find and mariadb Relevant security subcontracting version
yum search mariadb
2.install mariadb Customer and service packages for
yum install mariadb mariadb-server.x86_64 -y
3.start-up mariadb service
systemctl start mariadb
systemctl enable mariadb
4.mariadb Listening port
netstat -antlpe | grep mysql
ss -antlpe | grep mysql
vim /etc/services		#Default correspondence between all services and ports
5.Only local connections are allowed,Block all connections from the network
vim /etc/my.cnf
	skip-networking=1
systemctl restart mariadb
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

2> Simple operation of. MariaDB

1. Basic operation of MariaDB
1.Set up mysql Login password of
mysql_secure_installation		
2.Get into mysql data base
mysql -uroot -pyy
3.Show all databases included
 show databases;
4.Get into work data base
use work;
5.display work All tables in the database
show tables;
6.display work Database home All data structures in the table
desc home;
7.display work Database home All data contents in the table
 select * from home;
8.display work Database home In table age Column data content
select age from home;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
2. Creation, insertion, update and deletion of MariaDB
1.Create a named py_stud Database
create database py_stud;
2.stay py_stud Create a database named python1 Table structure, support Chinese display
create table python1(name varchar(20),age int) default charset=utf8;
3.stay py_stud Create a database named python1 The table structure of supports Chinese display. If it does not exist, it will be created. Otherwise, it will not be created
create table if not exists  python1(name varchar(20),age int) default charset=utf8;
4.towards py_stud In the database python1 Table inserts; inserts data into a table in the order specified for the table structure
insert into python1 values("ming",23);
5.towards py_stud In the database python1 Table inserts; inserts data into a table in the specified order
insert into python1 (age,name) values(18,"xiao");
6.To update py_stud In the database python1 Table contents, if name equals“ xiao",Update age=21
update python1 set age=21 where name="xiao";
7.towards py_stud In the database python1 Table structure add a column structure[ score int]
alter table python1 add score int;
8.delete py_stud In the database python1 Table contents name="ming"All lines of;
delete from python1 where name="ming";
9.Delete database or table
drop table python;		# Delete data table
drop database work;	# Delete database work
10.stay py_stud Create a database named python Use the command to python Table adding supports Chinese display
create table python( name varchar(20) not null, passwd varchar(5));
alter table python default charset=utf8;
11.stay py_stud Create a database named jave Table structure, and make the header id Is the only key
create table jave(id int primary key,name varchar(20));
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34

3> Operation of users and access rights of. MariaDB

1.Create users in the database kai,Can log in locally,The password is kai
create user kai@localhost identified by "kai";
2.View users who can log in in the database
select * from mysql.user
3.Create users in the database kai,Remote login available,The password is kai
create user kai@"%" identified by "kai";
4.Give users local users in the database kai Grant database bank All permissions for
grant all on *.* to kai@localhost;  # *. * indicates all databases, all data tables
grant all on bank.* to kai@localhost;
5.Refresh in database, overload authorization table
flush privileges;
6.View local users kai User authorization for
show grants for kai@localhost;
7.Delete local user kai In the database bank Delete permission for
revoke delete on bank.* from kai@localhost;
8.Delete local user in database kai
drop user kai@localhost;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

4> . when you forget the mysql user password, retrieve the mysql password

1.Close mariadb service
systemctl stop mariadb.service
2.Skip authorization table,And the program runs in the background so that anyone can log in mysql data base
mysqld_safe --skip-grant-table &
3.Get into mysql Databases, updating mysql Users, setting users[ root]The password for is[ yu],
mysql
update mysql.user set Password=password("yu") where User="root";
4.Close the process of skipping authorization table,start-up mariadb service,Just use the new password
ps aux | grep mysql   #View mysql background process
kill -9 pid					  # Close mysql related background processes
systemctl start mariadb.service		# Enable mariadb service
mysql -uroot -pyu	 # Use password to enter mysql database
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

5> . MySQL backup and recovery

1.take bank Backup database (including database structure) to current path bank.dump in
mysqldump -uroot -pyu bank >bank.dump
2.First create a database bank1 Database; then backup the file bank.dump Revert to bank1 Medium;
mysqladmin -uroot -pyu create bank1
mysql -uroot -pyu bank1<bank.dump
3.take bank Backup database [excluding database structure] to current path nobank.dump The recovery method is the same as that of [including database structure]
mysqldump -uroot -pyu --no-data bank >nobank.dump
4.Back up all databases in the database to the current path alldatabases.dump in
mysqldump -uroot -pyu --all-databases >alldatabases.dump
5.Files to be backed up alldatabases.dump Restore to database
mysql -uroot -pyu < alldatabases.dump
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

<2> . Python - mariadb

In python, you can use the pymysql.connect() method to create a Connection object. The common parameters of this method are as follows:

object usage
host The host name of the connected database server, which is the local host by default; String type
user User name, default to current user; String type (String)
passwd Password, no default value; String class (String)
db Database name, no default value; String type (String)
port Specifies the connection port of the database server, the default is 3306; integer (int)
charset Connection character set; String type (String)
  • Common methods of Connection object
Method Explain
cursor() Creates and returns a cursor using the current connection
commit() Commit current transaction
rollback() Rollback current transaction
close() Close current connection
cursor object usage
execute() Execute a database query or command
executemany() Insert multiple
import pymysql
#1. Connect to database
conn=pymysql.connect(
    host="localhost",       # Connection host name
    user="root",            # Connect users
    passwd="yu",            # User password
    db="py_stud",           # Connect to database
    charset="utf8",         # Specify encoding format
    autocommit=True,        # If you insert data, do you want to submit it automatically? It is consistent with the function of conn.commit().
)
#2. Create a cursor object. There must be a cursor object to send sql statements to the database and execute the
cur=conn.cursor()
try:
    crea_sql1="create table if not exists " \
              "php(name varchar(20),age int) default charset=utf8;"
    crea_sql2="insert into php values('Xiao Ming',13);"
    cur.execute(crea_sql1)
    cur.execute(crea_sql2)
except Exception as e1:
    print("Failed to create data table:", e)
else:
    print("Data table created successfully:")
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

2> . use python to create a datasheet in the database and insert the datasheet data

import pymysql
# 1. Connect to database
conn=pymysql.connect(
    host="localhost",       # Connection host name
    user="root",            # Connect users
    passwd="yu",            # User password
    db="py_stud",           # Connect to database
    charset="utf8",         # Specify encoding format
    autocommit=True,        # If you insert data, do you want to submit it automatically? It is consistent with the function of conn.commit().
)
# 2. Create a cursor object. There must be a cursor object to send sql statements to the database and execute the
cur=conn.cursor()
try:
    info=[("kai%s" %(i) ,i) for i in range(4)]
    ##############Method 1##################
    # insert_sql1="insert into php values('%s',%s);"
    # for item in info:
    #     print("insert statement:", insert_sql1% item)
    #     cur.execute(insert_sql1 %item)
	##############Method two##################
    insert_sql1="insert into php values(%s,%s);"
    cur.executemany(insert_sql1,info)
except Exception as e2:
    print("Insert multiple data failed:",e)
else:
    print("Insert multiple data successfully:")
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27

3> . use python to query database content and move cursor pointer

import pymysql
# 1. Connect to database
conn=pymysql.connect(
    host="localhost",       # Connection host name
    user="root",            # Connect users
    passwd="yu",            # User password
    db="py_stud",           # Connect to database
    charset="utf8",         # Specify encoding format
    autocommit=True,        # If you insert data, do you want to submit it automatically? It is consistent with the function of conn.commit().
)
# 2. Create a cursor object. There must be a cursor object to send sql statements to the database and execute the
cur=conn.cursor()
try:
    sqli="select * from php;"
    resu=cur.execute(sqli)
############### 1.Database query ##################
    print(resu)         # By default, the query result set is not returned, and the number of data records is returned
    print(cur.fetchone())   # Get next query result set
    print(cur.fetchmany(3)) # Get several query result sets
    print(cur.fetchall())   # Get all query results
    print(cur.rowcount)     # Returns the number of rows affected by executing sql statements
###############  2.Movement of database cursor pointer#############
    print("Moving pointer to start......")
    print(cur.scroll(0,"absolute"))
    print(cur.fetchmany(3))
    print("Moving pointer to penultimate......")
    # 2: Indicates that the current position moves backward by 2 lines; - 2: indicates that the current position moves forward by 2 lines
    cur.scroll(2, mode='relative')
    print(cur.fetchall())
except Exception as e2:
    print("Insert multiple data failed:",e)
else:
    print("Data success:")
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37

4> . use python to get the field name and information of the data table of the database

import time
import pymysql
# 1. Connect to the database,
conn = pymysql.connect(
    host="localhost",       # Connection host name
    user="root",            # Connect users
    passwd="yu",            # User password
    db="py_stud",           # Connect to database
    charset="utf8",         # Specify encoding format
    autocommit=True,        # If you insert data, do you want to submit it automatically? It is consistent with the function of conn.commit().
)
# python, there must be a cursor object to send sql statements to the database and execute
# The effect of the with statement is: if the with statement is executed successfully, the changed data will be submitted; if not, the data will be rolled back
with conn:
    # 1. Judge whether it is connected?
    print(conn.open)
    # 2. Create a cursor object,
    cur = conn.cursor()
    # 3) . execute database statement
    sqli = "select * from php;"
    result = cur.execute(sqli)  # By default, the query result set is not returned, and the number of data records is returned.
   # Show details for each column
    des = cur.description
    print("Table description:", des)
    # Get table header
    print("Header:", ",".join([item[0] for item in des]))
    cur.close()
conn.close()
print("with Sentence beyond:", conn.open)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34

57 original articles published, praised 0, and 819 visitors
Private letter follow

Tags: Database MariaDB MySQL Python

Posted on Fri, 07 Feb 2020 06:26:53 -0500 by samyl