1. Add data to the table
1.1 problems
- Insert data into the employees table
- Insert data into the salary table
- 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
- 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)
- 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)
- 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:
- ping to test whether the host is reachable
- If the ping fails, the host is considered unavailable for any reason
- 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:
-
Use__ init__ Method initializes parameters. When the Ping class instance is called, the method is automatically called
-
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
-
If the rc variable value is not 0, it means that the ping fails and the output is down
-
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:
- Remove all remote host IP addresses from the file
- Accept the remote server IP address file, remote server password, and commands executed on the remote host on the shell command line
- 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