The ordinary way of Python (13)

First, the Python Paramiko module introduction

Python's Paramiko module, which is used to connect remote servers and perform related operations

SSH client connects to remote server and executes basic commands

The SSH connection code based on the user name and password is as follows:

1 Import Paramiko 2  3 ssh = Paramiko. Sshclient () #创建SSH对象. 4 Ssh.set_missing_host_key_policy (Paramiko. Autoaddpolicy ())   #允许连接不在know_hosts文件中的主机 5 ssh.connect (hostname= "10.249.1.230", port=22,username= "root", Password= "123456")   #连接服务器 6 stdin,stdout,stderr = Ssh.exec_command ("df")        #执行命令 7 result = Stdout.read ()      # Get command result 8  9 ssh.close ()     #关闭连接10 print Result

Execution Result:

C:\Python27\python.exe e:/code/homework/sshclient.py
Filesystem 1k-blocks used Available use% mounted on
/dev/mapper/volgroup00-logvol00
9385620 1195720 7713128 14%/
Tmpfs 2009884 0 2009884 0%/dev/shm
/DEV/VDA1 396672 30048 346144 8%/boot

SSH is based on the user name and password of the connection, the implementation of two:

1 Import Paramiko 2 transport = Paramiko. Transport (("10.249.1.230",)) 3 Transport.connect (username= "root", password= "123456") 4 ssh = Paramiko. Sshclient () 5 Ssh._transport = Transport 6  7 #stdin, Stdout,stderr = Ssh.exec_command ("df") 8 Stdin,stdout,stderr = SSH . Exec_command ("DF") 9 print stdout.read () #print stdout.read () transport.close ()

Second, the implementation of the Fortress machine

2.1, realize the idea:

2.2, the Fortress machine execution process:

1, the administrator for the user to create an account on the server (the public key placed on the server, or use the user name and password authentication);

2, the user login Fortress machine, enter the user name and password, to achieve the current User Management Server list;

3, the user selects the server which needs to log in, and realizes the automatic login;

4, carry out the corresponding operation and the user all the operation record of the audit platform.

2.3, the Fortress machine realization Process

Step One: Implement User login:

1 Import Getpass2 user = Raw_input ("Username:") 3 pwd =getpass.getpass ("password") 4 if user = = "root" and pwd = = "123": 5     Print "Login Successful" 6 else:7     print "Login Failed"

Step two: According to the user to obtain the relevant server list:

1 dic = {2     "root": [3         "10.249.1.230", 4         "10.249.1.231", 5         "www.baidu.com", 6     ], 7     "Eric": [8
     "10.249.1.244", 9     ]10}11 host_list = dic["root"]13 print "Please select:" "The Index,item list,1):     print Index,item17 INP = raw_input ("Your Select (No):") InP = input (INP) hostname = Host_list[inp-1] Port =22

Step three: Log on to the server according to the user name, private key

  1 tran = Paramiko. Transport ((Hostname,port,)) 2 tran.start_client () 3 Default_path = Os.path.join (os.environ["HOME"], ". SSH", "Id_rsa") 4 Key = Paramiko.  Rsakey.from_private_key_file (Default_path) 5 Tran.auth_publickey ("Jason", key) 6 7 Chan = tran.open_session () #打开一个通道 8 Chan.get_pty () #获取一个终端 9 Chan.invoke_shell () #激活器10 11 "" "12 take advantage of Sys.stdin, arbitrary perform operation 13 user in terminal output Into the content and sends the content to the remote server 14 remote server to execute the command and return the result 15 at the user terminal display content "" "Chan.close () tran.close ()  
 
  After establishing the connection, we begin to transmit the data, and see the following implementation method!!!!!  
 
  implementation mode:  

1 while True:2 ""     "3     Monitor user input and server return Data 4     sys.stdin process user Input 5     Chan is a previously created channel for receiving server return information 6     "     "" 7 Readable,writeable,error = Select.select ([chan,sys.stdin,],[],[],1) 8     if Chan in Readable:9         try:10             x = CHAN.RECV (1024x768) One-             if Len (x) ==0:12                 print "\r\n*** eof\r\n",                 break14             sys.stdout.write (x) 15             Sys.stdout.flush ()         except socket.timeout:17             pass18     if Sys.stdin in readable:19         INP = Sys.stdin.readable ()         Chan.sendall (INP)

Iii. Basic operation of the database under the fortress machine

3.1 Creating databases: Create database [name];    For example: Create DATABASE Messi; This creates a database with a name of: Messi. Be sure to remember to add (;) points in the back.

3.2 Use this database using Messi; That is, using the Messi database that you just created

3.3, create the data table in the database, the operation is as follows: Remember: When you create a data table, the following code is the 3rd line, be sure to enter a few spaces, and make the subsequent content alignment oh. As shown in the following code:

1 mysql> CREATE TABLE Students 2     -(3     -     ID int not NULL auto_increment primary key, 4     ->
    name Char (8) NOT NULL, 5--     sex char (4) NOT NULL, 6--age     tinyint unsigned not NULL, 7     -&G t;     Tel char (+) NULL default "-" 8     ); 9 Query OK, 0 rows affected (0.03 sec) mysql>

Mysql> Show tables;

+-------------------+
| Tables_in_xiaoluo |
+-------------------+
| Students |
+-------------------+
1 row in Set (0.00 sec)

 

3.4, data operation:

Insert Data operation:

1 mysql> INSERT INTO students (Name,sex,age,tel) VALUES ("Oliver", "F", "a", "1310000000") 2,     3 Query OK, 1 row af  Fected (0.00 sec) 4  5 mysql> SELECT * from students; 6 +----+--------+-----+-----+------------+ 7 | id | name   | sex | Age | Tel        | 8 +----+--------+-----+-----+------------+ 9 |  1 | Oliver | F   |  22 | 1310000000 |10 +----+--------+-----+-----+------------+11 1 row in Set (0.00 sec)

To delete a data operation:

Mysql> Delete from students where id = 2;
Query OK, 1 row Affected (0.00 sec)

To update data operations:

Mysql> UPDATE students Set tel = "1320000000" WHERE name = "Jason"; Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0

Querying Data operations:

mysql> select * from students;
+----+--------+-----+-----+------------+
| ID | name | sex | Age | Tel |
+----+--------+-----+-----+------------+
| 1 | Oliver | F | 22 | 1310000000 |
| 5 | jason | M | 18 | 1320000000 |
| 3 | alice | M | 37 | 1330000000 |
| 4 | cindy | F | 27 | 1340000000 |
| 6 | anny | M | 18 | 1350000000 |
| 7 | M | 18 | 1350000000 |
| 8 | bily | M | 27 | 1370000000 |
+----+--------+-----+-----+------------+
7 Rows in Set (0.00 sec)

3.5. Python MySQL API 3.5.1, inserting data

1 Import MYSQLDB2 conn = MySQLdb.connect (host= "127.0.0.1", user= "root", passwd= "123.com", db= "Xiaoluo") 3 cur = Conn.cursor () 4 recount = Cur.execute ("INSERT into students (Name,sex,age,tel) VALUES (%s,%s,%s,%s)", ("Jet", "M", 30, " 139999999 ")) 5 Conn.commit () 6 cur.close () 7 conn.close () 8 9 print recount

The results of the program run as follows:

mysql> SELECT * from students;
+----+--------+-----+-----+------------+
| id | name | sex | age | tel |
+----+--------+-----+-----+------------+
| 1 | oliver | F | 22 | 1310000000 |
| 5 | jason | M | 18 | 1320000000 |
| 3 | alice | M | 37 | 1330000000 |
| 4 | cindy | F | 27 | 1340000000 |
| 6 | anny | M | 18 | 1350000000 |
| 7 | rice | M | 18 | 1350000000 |
| 8 | bily | M | 27 | 1370000000 |
| 9 | alex | M | 23 | 131111111 |
| | jet | M | 30 | 139999999 |
+----+--------+-----+-----+------------+
9 rows in Set (0.00 sec)

Mysql>

3.5.2 Bulk Data insertion:

 1 1 Import MYSQLDB 2 2 conn = MySQLdb.connect (host= "127.0.0.1", user= "root", passwd= "123.com", db= "Xiaoluo") 3 3 cur = Co Nn.cursor () 4 4 #reCount = Cur.execute ("INSERT into students (Name,sex,age,tel) VALUES (%s,%s,%s,%s)", ("Jet", "M", 30, " 139999999 ")) 5 5 li=[6 6 (" AAA "," f ", +," 138111 "), 7 7 (" BBB "," f ", +," 138222 "), 8 8 (" CCC "," M ", 33," 138333 "  ), 9 9]10 recount = Cur.executemany ("INSERT into students (Name,sex,age,tel) VALUES (%s,%s,%s,%s)", Li) 11 11 12 12 13 13 Conn.commit () Cur.close () conn.close () + 20 print reCount19 Insert the results are as follows: Mysql> SELECT * FROM students;22 +----+--------+-----+-----+------------+23 | ID | name | sex | Age | Tel |24 +----+--------+-----+-----+------------+25 | 1 | Oliver | F | 22 | 1310000000 |26 | 5 | Jason | M | 18 | 1320000000 |27 | 3 | Alice | M | 37 | 1330000000 |28 | 4 | Cindy | F | 27 | 1340000000 |29 | 6 | Anny | M | 18 | 1350000000 |30 | 7 | Rice | M | 18 | 1350000000 |31 | 8 | bily | M | 27 | 1370000000 |32| 9 | Alex | M | 23 | 131111111 |33 | 10 | Jet | M | 30 | 139999999 |34 | 11 | AAA | F | 31 | 138111 |35 | 12 | BBB | F | 32 | 138222 |36 | 13 | CCC | M | 33 | 138333 |37 +----+--------+-----+-----+------------+38 rows in Set (0.00 sec)

3.5.3 Delete Table: Student's id==1 entry data:

1 Import MYSQLDB 2 conn = MySQLdb.connect (host= "127.0.0.1", user= "root", passwd= "123.com", db= "Xiaoluo") 3 cur = Conn.cursor () 4 values (%s,%s,%s,%s) ", (" Jet "," M "," 139999999 ")) 5 students (Name,sex,age,tel) VALUES (%s,%s,%s,%s)",  LI) 6 recount = Cur.execute ("Delete from students where ID =1") 7  8 Conn.commit () 9 cur.close () Conn.close () 12 13 Print reCount14 ~                                                                                                                     15 ~                 

3.5.4 Modify all the names in student to Alin

1 Import MYSQLDB 2 conn = MySQLdb.connect (host= "127.0.0.1", user= "root", passwd= "123.com", db= "Xiaoluo") 3 cur = Conn.cursor () 4  5  6 recount = Cur.execute ("Update students set name=%s", ("Alin",)) 7  8  9 Conn.commit () Ten Cur.close () conn.close () print recount

Execution Result:

mysql> SELECT * from students;
+----+------+-----+-----+------------+
| id | name | sex | age | tel |
+----+------+-----+-----+------------+
| 5 | alin | M | 18 | 1320000000 |
| 3 | alin | M | 37 | 1330000000 |
| 4 | alin | F | 27 | 1340000000 |
| 6 | alin | M | 18 | 1350000000 |
| 7 | alin | M | 18 | 1350000000 |
| 8 | alin | M | 27 | 1370000000 |
| 9 | alin | M | 23 | 131111111 |
| | alin | M | 30 | 139999999 |
| | alin | F | 31 | 138111 |
| | alin | F | 32 | 138222 |
| | alin | M | 33 | 138333 |
+----+------+-----+-----+------------+
rows in Set (0.00 sec)

Mysql>

3.5.5 Querying data

1 Import MYSQLDB 2 conn = MySQLdb.connect (host= "127.0.0.1", user= "root", passwd= "123.com", db= "Xiaoluo") 3 cur = Conn.cursor () Recount =cur.execute ("SELECT * from students") print Cur.fetchone () print cur.fetchone () cur.scr Oll ( -1,mode= "relative") print Cur.fetchone () print Cur.fetchone () cur.scroll (0,mode= "absolute") print Cur.fetchone () print cur.fetchone ()
Original database table structure:

mysql> SELECT * from students;
+----+------+-----+-----+------------+
| id | name | sex | age | tel |
+----+------+-----+-----+------------+
| 5 | alin | M | 18 | 1320000000 |
| 3 | alin | M | 37 | 1330000000 |
| 4 | alin | F | 27 | 1340000000 |
| 6 | alin | M | 18 | 1350000000 |
| 7 | alin | M | 18 | 1350000000 |
| 8 | alin | M | 27 | 1370000000 |
| 9 | alin | M | 23 | 131111111 |
| | alin | M | 30 | 139999999 |
| | alin | F | 31 | 138111 |
| | alin | F | 32 | 138222 |
| | alin | M | 33 | 138333 |
+----+------+-----+-----+------------+
rows in Set (0.00 sec)

Execution Result:

 [email protected] lab]# python mysql.py
(5L, ' Alin ', ' M ', ' 1320000000 ')
(3L, ' Alin ', ' M ', Notoginseng, ' 1330000000 ')
(3L, ' Alin ', ' M ', Notoginseng, ' 1330000000 ')
(4L, ' Alin ', ' F ', ' 1340000000 ')
(5L, ' Alin ', ' M ', ' 1320000000 ')
(3L, ' Alin ', ' M ', Notoginseng, ' 1330000000 ')

The ordinary way of Python (13)