PyMySQL module & multithreaded programming & Paramiko module | Cloud computing

Posted by social_experiment on Sun, 02 Jan 2022 21:13:08 +0100

1. Add data to the table

1.1 problems

  1. Insert data into the employees table
  2. Insert data into the salary table
  3. The inserted data needs to be commit ted to the database

1.2 steps

To implement this case, you need to follow the following steps.

Step 1: PyMySQL installation

  1. Install gcc. Some software packages are the source code of C
[root@localhost ~]# yum install -y gcc
 Plug in loaded: fastestmirror, langpacks
dvd                                                      | 3.6 kB     00:00     
Loading mirror speeds from cached hostfile
 matching gcc-4.8.5-16.el7.x86_64 Your package is already installed. Checking for updates.
No treatment is required

2) In order to speed up the download, you can use the domestic open source image site

[root@localhost ~]# mkdir ~/.pip
[root@localhost ~]# vim ~/.pip/pip.conf
[global]
index-url = http://pypi.douban.com/simple/
[install]
trusted-host=pypi.douban.com

3) Install pymysql

[root@localhost ~]# pip3 install pymysql

Step 2: install MariaDB server

[root@localhost ~]# yum install –y mariadb-server
....
Installed:
        mariadb-server.x86_64 1:5.5.56-2.el7
 Installed as a dependency:
        mariadb.x86_64 1:5.5.56-2.el7
        perl-DBD-MySQL. x86_64 0:4.023-5.el7
 complete!
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl enable mariadb
[root@localhost ~]# mysqladmin password tedu.cn

Step 3: create a database

1) Create database

[root@localhost ~]# mysql -uroot -ptedu.cn    
MariaDB [(none)]> CREATE DATABASE tedu DEFAULT CHARSET 'utf8';
Query OK, 1 row affected (0.00 sec)

2) Create department table

Department table fields: Department ID, department name

MariaDB [(none)]> USE tedu;
Database changed
MariaDB [tedu]> CREATE TABLE departments(dep_id INT PRIMARY KEY, dep_name VARCHAR(20));
Query OK, 0 rows affected (0.04 sec)

3) Create employee table

Employee table fields: employee number, name, date of birth, Department ID, phone number, email, reference foreign key ID

MariaDB [tedu]> CREATE TABLE employees (emp_id INT PRIMARY KEY, emp_name VARCHAR(20) NOT NULL, birth_date DATE, phone CHAR(11), email VARCHAR(50), dep_id INT, FOREIGN KEY(dep_id) REFERENCES departments(dep_id));
Query OK, 0 rows affected (0.05 sec)

4) Create payroll

Payroll fields: auto_id, employee number, date, basic salary, bonus and total salary

MariaDB [tedu]> CREATE TABLE salary(auto_id INT AUTO_INCREMENT PRIMARY KEY, date DATE, emp_id INT, basic INT, awards INT, FOREIGN KEY(emp_id) REFERENCES employees(emp_id));
Query OK, 0 rows affected (0.05 sec)

Step 4: insert data into the departments table

1) New insert_data.py file. The code is as follows:

[root@localhost day10]# vim insert_data.py
import pymysql
1)Connect to database
conn = pymysql.connect(
    host='127.0.0.1',        #Connection ip
    port=3306,            #Port number
    user='root',            #Database user name
    passwd='tedu.cn',        #Database password
    db='tedu',            #Database name
    charset='utf8'        #Set the character set of the database
)
2)Create cursor
cursor = conn.cursor()
3)Report to Department departments Insert data into
insert1 = "INSERT INTO departments(dep_id, dep_name) VALUES(%s, %s)"
result = cursor.execute(insert1, (1, 'Ministry of Personnel'))        # execute executes the insert statement
4)Commit updates to database
conn.commit()    
5)Close cursor
cursor.close()
6)Close database connection
conn.close()

2) Execute insert_data.py file:

[root@localhost day10]# python3 insert_data.py

3) Log in to mariadb to view the results:

MariaDB [tedu]>> select * from departments;
+--------+-----------+
| dep_id | dep_name  |
+--------+-----------+
|      1  |  Ministry of Personnel    |
+--------+-----------+
1 row in set (0.00 sec) 
  1. You can also insert data into the Department table departments as follows:
#Above insert_ data. Step 3 of Py file can be replaced with the following code:
insert1 = "INSERT INTO departments(dep_id, dep_name) VALUES(%s, %s)"
data = [(2, 'Operation and maintenance department'), (3, 'Development Department')]
cursor.executemany(insert1, data)

The results of mariadb are as follows:

MariaDB [tedu]>> select * from departments;
+--------+-----------+
| dep_id | dep_name  |
+--------+-----------+
|      1  |  Ministry of Personnel    |
|      2  |  Operation and maintenance department    |
|      3  |  Development Department    |
+--------+-----------+
3 rows in set (0.01 sec)

Step 5: insert data into the employees table

1) New insert_emp.py file. The code is as follows:

[root@localhost day10]# vim insert_emp.py
import pymysql
1)Connect to database
conn = pymysql.connect(
    host='127.0.0.1',        #Connection ip
    port=3306,            #Port number
    user='root',            #Database user name
    passwd='tedu.cn',        #Database password
    db='tedu',            #Database name
    charset='utf8'        #Set the character set of the database
)
2)Create cursor
cursor = conn.cursor()
3)Report to Department employees Insert data into
insert1 = "INSERT INTO employees(emp_id, emp_name, birth_date,phone, email, dep_id) VALUES(%s, %s, %s, %s, %s, %s)"
result = cursor.execute(insert1, (1, 'Wang Jun', '2018-9-30',\
 '15678789090', 'wj@163.com', 3))        # execute executes the insert statement
4)Commit updates to database
conn.commit()    
5)Close cursor
cursor.close()
6)Close database connection
conn.close()

2) Execute insert_emp.py file:

[root@localhost day10]# python3 insert_emp.py

3) Log in to mariadb to view the results:

MariaDB [tedu]>> select * from employees;
+--------+----------+------------+-------------+------------+--------+
| emp_id | emp_name | birth_date |  phone       | email      | dep_id |
+--------+----------+------------+-------------+------------+--------+
|      1  |    Wang Jun   | 2018-09-30 | 15678789090 | wj@163.com |      3 |
+--------+----------+------------+-------------+------------+--------+
 1 row in set (0.00 sec) 
  1. You can also insert data into the Department table employees as follows:
#Above insert_ emp. Step 3 of Py file can be replaced with the following code:
insert1 = "INSERT INTO employees (dep_id, dep_name) VALUES(%s, %s)"
data = [(2, 'Operation and maintenance department'), (3, 'Development Department')]
cursor.executemany(insert1, data)

The results of mariadb are as follows:

MariaDB [tedu]>> select * from departments;
+--------+----------+------------+-------------+------------+--------+
| emp_id | emp_name | birth_date |  phone       | email      | dep_id |
+--------+----------+------------+-------------+------------+--------+
|      1  |   Wang Jun    | 2018-09-30 | 15678789090 | wj@163.com |      3 |
|      2  |   Li Lei    | 2018-09-30 | 15678789090 | wj@163.com |      2 |
|      3  |   Zhang Mei    | 2018-09-30 | 15678789090 | zm@163.com |      1 |
+--------+----------+------------+-------------+------------+--------+
3 rows in set (0.00 sec)

Step 6: insert data into the salary table

1) New insert_sal.py file. The code is as follows:

[root@localhost day10]# vim insert_sal.py
import pymysql
1)Connect to database
conn = pymysql.connect(
    host='127.0.0.1',        #Connection ip
    port=3306,            #Port number
    user='root',            #Database user name
    passwd='tedu.cn',        #Database password
    db='tedu',            #Database name
    charset='utf8'        #Set the character set of the database
)
2)Create cursor
cursor = conn.cursor()
3)Report to Department salary Insert data into
insert2 = "INSERT INTO salary(date, emp_id,basic, awards) VALUES(%s, %s, %s, %s)"
data = [('2018-9-30', 2, 1000, 2000), ('2018-9-30', 3, 3000, 6000),('2018-9-30', 1, 8000, 9000)]
cursor.executemany(insert2, data)
4)Commit updates to database
conn.commit()    
5)Close cursor
cursor.close()
6)Close database connection
conn.close()

2) Execute insert_sal.py file:

[root@localhost day10]# python3 insert_sal.py

3) Log in to mariadb to view the results:

MariaDB [tedu]>> select * from salary;
+---------+------------+--------+-------+--------+
| auto_id | date       | emp_id | basic | awards |
+---------+------------+--------+-------+--------+
|       1 | 2018-09-30 |      2  |  1000 |   2000 |
|       2 | 2018-09-30 |      3  |  3000 |   6000 |
|       3 | 2018-09-30 |      1  |  8000 |   9000 |
+---------+------------+--------+-------+--------+
3 rows in set (0.01 sec) 

2. Scan surviving hosts

2.1 problems

Create mtping Py script to realize the following functions:

  1. ping to test whether the host is reachable
  2. If the ping fails, the host is considered unavailable for any reason
  3. Concurrent scanning through multithreading

2.2 scheme

subprocess. The call () method can call the system command, and its return value is the exit code of the system command. That is, if the system command is successfully executed, it returns 0. If it is not successfully executed, it returns a non-zero value.

By calling the Ping object, you can call the ping command of the system and judge whether the host is pinged through the exit code. If executed sequentially, each Ping operation takes several seconds, and all 254 addresses take more than 10 minutes. Using multithreading, you can ping these 254 addresses at the same time. The result of concurrency is to shorten the execution time to about 10 seconds.

2.3 steps

To implement this case, you need to follow the following steps.

Step 1: Script

[root@localhost day09]# vim mtping.py
#!/usr/bin/env python3
import subprocess
import threading
def ping(host):
    rc = subprocess.call(
        'ping -c2 %s &> /dev/null' % host,
        shell=True
    )
    if rc:
        print('%s: down' % host)
    else:
        print('%s: up' % host)
if __name__ == '__main__':
    ips = ['172.40.58.%s' % i for i in range(1, 255)]
    for ip in ips:
        # Create a thread. Ping is the function defined above, and args is the parameter passed to the ping function
        t = threading.Thread(target=ping, args=(ip,))
        t.start()  # Perform ping(ip)

The object-oriented code is written as follows:

Define the Ping class, which can realize the function of allowing Ping to pass through any host:

  1. Use__ init__ Method initializes parameters. When the Ping class instance is called, the method is automatically called

  2. Use__ call__ () method turns the Ping class instance into a callable object call. When t.start() is called, it refers to the subprocess module to execute the shell command Ping all hosts and return the execution result to the rc variable. At this time, if the Ping fails, the return result is 1 and if it can ping, the return result is 0

  3. If the rc variable value is not 0, it means that the ping fails and the output is down

  4. Otherwise, it means you can ping and output up

Use the list derivation to generate the IP address list of the whole network segment [172.40.58.1172.40.58.2...]

Loop through the whole network segment list, directly use Thread class to create Thread object and execute Ping(ip).

[root@localhost day09]# vim mtping2.py
#!/usr/bin/env python3
import threading
import subprocess
class Ping:
    def __init__(self, host):
        self.host = host
    def __call__(self):
        rc = subprocess.call(
            'ping -c2 %s &> /dev/null' % self.host,
            shell=True
        )
        if rc:
            print('%s: down' % self.host)
        else:
            print('%s: up' % self.host)
if __name__ == '__main__':
    ips = ('172.40.58.%s' % i for i in range(1, 255))  # Create generator
    for ip in ips:
        # To create a thread, Ping is the function defined above
        t = threading.Thread(target=Ping(ip))  # Create an instance of Ping
        t.start()   #Execute Ping(ip)

Step 2: test script execution

[root@localhost day09]# python3 udp_time_serv.py 
172.40.58.1: up
172.40.58.69: up
172.40.58.87: up
172.40.58.90: up
172.40.58.102: up
172.40.58.101: up
172.40.58.105: up
172.40.58.106: up
172.40.58.108: up
172.40.58.110: up
172.40.58.109: up
...
...
...
...
172.40.58.241: down
172.40.58.242: down
172.40.58.243: down
172.40.58.245: down
172.40.58.246: down
172.40.58.248: down
172.40.58.247: down
172.40.58.250: down
172.40.58.249: down
172.40.58.251: down
172.40.58.252: down
172.40.58.253: down
172.40.58.254: down

3. Use multithreading to realize ssh concurrent access

3.1 problems

Write a remote_comm.py script to realize the following functions:

  1. Remove all remote host IP addresses from the file
  2. Accept the remote server IP address file, remote server password, and commands executed on the remote host on the shell command line
  3. The concurrent execution of commands on all remote servers is realized through multithreading

3.2 steps

To implement this case, you need to follow the following steps.

Step 1: install paramiko

paramiko follows the SSH2 protocol and supports the connection to the remote server in the way of encryption and authentication. It can upload and download remote files or execute commands remotely through ssh.

[root@localhost ~]# pip3 install paramiko
...
...
Successfully installed bcrypt-3.1.4 paramiko-2.4.1 pyasn1-0.4.4 pynacl-1.2.1
You are using pip version 9.0.1, however version 18.0 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.

Test for successful installation

>>> import paramiko
>>>

Step 2: Script

[root@localhost day11]# vim remote_comm.py
#!/usr/bin/env python3
import sys
import getpass
import paramiko
import threading
import os
#Create a function to realize the functions of remote connection host, server password and commands executed on the remote host
def remote_comm(host, pwd, command):
#Create an instance to connect to the ssh server
    ssh = paramiko.SSHClient()
#Set auto add host key
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
#Connect the ssh server, add the connected host, user name and password, and fill them in
    ssh.connect(hostname=host, username='root', password=pwd)
#Execute the specified command on the ssh server and return three class file objects: input, output and error
    stdin, stdout, stderr = ssh.exec_command(command)
#Read output
    out = stdout.read()
#Read error
    error = stderr.read()
#If there is an output
    if out:
#Print host output
        print('[%s] OUT:\n%s' % (host, out.decode('utf8')))
#If there is an error
    if error:
#Print host error message
        print('[%s] ERROR:\n%s' % (host, error.decode('utf8')))
#Program end
    ssh.close()
if __name__ == '__main__':
#Set sys Argv length, ensure remote_ Number of arguments in comm function
    if len(sys.argv) != 3:
        print('Usage: %s ipaddr_file "command"' % sys.argv[0])
        exit(1)
#Judge the input on the command line. If it is not a file, make sure it is a file  
    if not os.path.isfile(sys.argv[1]):
        print('No such file:', sys.argv[1])
        exit(2)
#fname is the file that stores the ip address of the remote host, using sys Argv method, you can enter the file name when executing the script, which is more flexible
    fname = sys.argv[1]
#Command is the command executed on the remote host, using sys Argv method, you can enter the corresponding command when executing the script, and the command is remote_comm function third argument
    command = sys.argv[2]
#Enter the remote server password through getpass, and pwd is remote_comm function second argument
    pwd = getpass.getpass()
#Open the file containing the remote host ip
    with open(fname) as fobj:
#The traversal file is stored in ips, line Strip() can remove the ip address of each line. \ n
        ips = [line.strip() for line in fobj]
#Loop through the list to obtain the ip address, which is remote_comm function first argument
    for ip in ips:
#Take the read ip address as remote_ The actual parameters of the comm function are passed to the function, and several ip addresses in the ips loop several times
#Create multithreading
        t = threading.Thread(target=remote_comm, args=(ip, pwd, command))
#Enable multithreading
        t.start()

Step 3: test script execution

#The effect of missing parameters is as follows:
[root@localhost day11]# python3 remote_comm.py server_addr.txt
Usage: remote_comm.py ipaddr_file "command"
#The parameters have the following effects:
[root@localhost day11]# python3 remote_comm.py server_addr.txt id zhangsan
Usage: remote_comm.py ipaddr_file "command"
#The normal display is as follows:
[root@localhost day11]# python3 remote_comm.py server_addr.txt "id zhangsan"
Password:
[192.168.4.2] OUT:
uid=1001(zhangsan) gid=1001(zhangsan) group=1001(zhangsan)
[192.168.4.3] OUT:
uid=1001(zhangsan) gid=1001(zhangsan) group=1001(zhangsan)
[root@localhost day11]# python3 remote_comm.py server_addr.txt "echo redhat | passwd –stdin root"
Password:
[192.168.4.3] OUT:
Change user root Password for:
passwd: All authentication tokens have been successfully updated.
[192.168.4.2] OUT:
Change user root Password for:
passwd: All authentication tokens have been successfully updated.
#At this point, the password has become redhat
[root@localhost day11]# python3 remote_comm.py server_addr.txt "id zhangsan"
Password:
[192.168.4.2] OUT:
uid=1001(zhangsan) gid=1001(zhangsan) group=1001(zhangsan)
[192.168.4.3] OUT:
uid=1001(zhangsan) gid=1001(zhangsan) group=1001(zhangsan)

Exercise

1. What are the methods to install pymysql module?

  • Online installation
pip install pymysql
  • visit http://pypi.python.org , download the pymysql module and install it locally through pip
pip3 install PyMySQL-0.8.0.tar.gz
  • visit http://pypi.python.org , download the pymysql module and install it locally
tar xzf PyMySQL-0.8.0.tar.gz
cd PyMySQL
python3 setup.py install

2. How to improve the speed by installing the module online through pip?

  • When pip directly installs the module, it is connected to the foreign official website. If domestic mirror sites are adopted, the efficiency can be improved as follows:
[root@localhost ~]# mkdir ~/.pip/
[root@localhost ~]# vim ~/.pip/pip.conf 
[global]
index-url=http://pypi.douban.com/simple/
[install]
trusted-host=pypi.douban.com

3 what is the general process of using pymysql to operate the database?

  • Connect to the database first
  • Create cursor
  • Perform various operations on the database through the cursor
  • If you are adding, deleting or modifying, you need to confirm
  • Close the connection to the database

4 what is the general process of managing remote servers through paramiko?

  • Create SSHClient instance
  • Set add host key policy
  • Connect to ssh server
  • Execute the specified command
  • In the shell command line, accept the password used to connect to the remote server and the command executed on the remote host

In case of infringement, please contact the author to delete

Topics: Linux Operation & Maintenance CentOS MariaDB cloud computing