The way of the Python-sqlalchemy/paramiko/fortress machine

I. SQLAlchemy-linked table operation

1. One-to-many

Class group (Base): # One-to-many tables, which may contain multiple users    __tablename__ = ' group '    nid = Column (Integer, Primary_key=true, Autoincrement=true)    caption = column (String ()) class User (Base):    __tablename__ = ' User '    uid = column ( Integer, Primary_key=true, autoincrement=true)    name = Column (String ())    gid = column (Integer, ForeignKey (' Group.nid '))

Inserting data into the user table and the Group table

# After creating the table, establish the connection session = Sessionmaker (bind=engine) session = Session () New Group table Data Session.add_all ([    Group (caption= ' DBA '),    Group (caption= ' SA '),    ]) session.commit () Session.add (Group (caption= ' QA ')) New user table data Session.add_all ([    User (name= ' Bob ', gid=1),    User (name= ' Boss ', gid=2),    ]) Session.commit ()

These two codes define 2 tables, one for "group" and one for "User table". One-to-many representations: Multiple users may exist in a group.

1.1 Find the group that corresponds to each user in the user table.

The General table query is as follows:

ret = Session.query (User.Name, group.caption). Join (Group). All () print (ret)  # join defaults to left joinout: [(' Bob ', ' DBA ') ), (' Boss ', ' SA ')]

Sqlacademy Query Method:

The steps are as follows:

1. Building Relationships

# using relationship, you must first create a relationship class Group (Base) when creating a table:    __tablename__ = ' Group '    nid = Column (Integer, primary_key= True, autoincrement=true)    caption = Column (String ()) class User (Base):    __tablename__ = ' user '    uid = Column (integer, Primary_key=true, autoincrement=true)    name = Column (String ())    gid = column (integer, ForeignKey (' Group.nid '))    <strong># is only easy to query .</strong>    group = relationship ("group")

2. Forward Query

ret = session.query (user). All () to obj in ret:    # obj refers to each row of data in the user table, which is the user Object    # Obj.group refers to the group object, you can use Obj.group.XXX to get the value of the field in the Group table    print (Obj.uid, Obj.name, Obj.gid, Obj.group, Obj.group.nid , obj.group.caption)

3. Reverse Query

Group = relationship ("group", backref= ' uuu ') obj = Session.query (group). Filter (group.caption== ' DBA '). First () A statement with a filter condition that filters all members of the DBA group print (obj) # obj represents a qualifying group object out: <__main__. The Group object at 0x00000000032eb710><br>print (obj.uuu) # obj.uuu is the user object that meets the filter criteria out: [<__main__. User object at 0x0000000003b15400> <__main__. User object at 0x0000000003b15470>]for I in obj.uuu:# obj.uuu requires a for loop to query results    print (I.uid, i.name, I.gid)

Relationship () function: This function tells Orm, by using the user. The Group,group class should be connected to the user class.
Relationship () uses a foreign key to clarify the relationship between the two tables. Determines that the User.group attribute is many-to-one, that is, multiple users can be in the same group.
The relationship () sub-function Backref () provides the details of the inverse relationship: the collection of relationship () objects is group.uuu referenced. Many-to-one inverse relationships are always one-to-many, that is, a group can contain multiple users

2. Many-to-many

Class Host (Base):    __tablename__ = ' host '    nid = Column (Integer, Primary_key=true, autoincrement=true)    hostname = column (string)    port = column (string ())    IP = column (string ()) class Hostuser (Base):    __ tablename__ = ' Host_user '    nid = Column (Integer, Primary_key=true, autoincrement=true)    username = column (String (32)) # Many-to-many class Hosttohostuser (Base):    __tablename__ = ' host_to_host_user '    nid = Column (Integer, primary_key= True, Autoincrement=true)     # Two associated IDs, other than key forms exist    host_id = Column (Integer, ForeignKey (' Host.nid '))    Host_ user_id = Column (Integer, ForeignKey (' Host_user.nid '))

# After creating the table, establish the connection session = Sessionmaker (bind=engine) session = Session () # Add Table Data Session.add_all ([    Host (hostname= ' C1 '), port= ' ip= ' 1.1.1.1 '),    host (hostname= ' C2 ', port= ' a ', ip= ' 1.1.1.2 '),    host (Hostname= ' C3 ', port= ' ", ip= ' 1.1.1.3 '),    host (hostname= ' C4 ', port= ' a ', ip= ' 1.1.1.4 '),    host (hostname= ' C5 ', port= ' a ', ip= ' 1.1.1.5 '),]) Session.commit () Session.add_all ([    hostuser (username= ' root '),    hostuser (username= ' db '),    Hostuser ( Username= ' NB '),    hostuser (username= ' SB '),]) Session.commit () Session.add_all ([    hosttohostuser (Host_id=1, host_user_id=1),    Hosttohostuser (host_id=1,host_user_id=2),    Hosttohostuser (host_id=1,host_user_id=3),    Hosttohostuser (host_id=2,host_user_id=2),    Hosttohostuser (host_id=2,host_user_id=4),    Hosttohostuser (host_id=2,host_user_id=3),]) Session.commit ()

The above added data, added five servers, respectively, is c1,c2,c3,c4,c5. Corresponds to id:1,2,3,4,5. Added four people, namely root, DB, NB, SB, corresponding id:1,2,3,4

2.1 General Inquiries

# 1. Gets the object that hosts the C1 host_obj = Session.query (host). Filter (Host.hostname = = ' C1 '). First () # 2. Gets the user Idhost_2_host_user = Session.query (hosttohostuser.host_user_id) that owns the host C1 in the Query relational table. Filter (hosttohostuser.host_id = = Host_obj.nid). All () print (Host_2_host_user) # [(1,), (2,), (3,)] R = Zip (*host_2_host_user) print (list (r)) #[(1, 2, 3)] this is User ID List # 3. Based on the User ID list, query user name users = session.query (hostuser.username). Filter (HostUser.nid.in_ (list (R) [0]). All () print (Users) # [( ' Root ',), (' db ',), (' NB ',)]

2.2 Relationship Query

2.2.1 Building Relationships

Class Host (Base):    __tablename__ = ' host '    nid = Column (Integer, Primary_key=true, autoincrement=true)    hostname = column (string)    port = column (string ())    IP = column (string ()) class Hostuser (Base):    __ tablename__ = ' Host_user '    nid = Column (Integer, Primary_key=true, autoincrement=true)    username = column (String (32)) # Multi-to-many class Hosttohostuser (Base):    __tablename__ = ' host_to_host_user '    nid = Column (Integer, Primary_key=true, autoincrement=true)    host_id = Column (Integer, ForeignKey (' Host.nid '))    host_user_id = column (integer, ForeignKey (' Host_user.nid ')    # useful in new notation    host = relationship (' host ', backref= ' h ')    Host_user = Relationship (' Hostuser ', backref= ' u ')

2.2.2 Query Gets the field information in the Hosttohostuser table

Host_obj = Session.query (Host). Filter (Host.hostname = = ' C1 '). First () <br>print (host_obj)  # <__main__. Host object at 0x0000000003c11128>print (host_obj.hostname)  # hostname: C1print (host_obj.h) # Host_ Obj.h represents a list of Hosttohostuser data objects in the Hosttohostuser table that meet the filter criteria [<__main__. Hosttohostuser object at 0x0000000003b3f198> <__main__. Hosttohostuser object at 0x0000000003b3f898> <__main__.  Hosttohostuser object at 0x0000000003b3f908>] # loop to get user information for item in HOST_OBJ.H:    # print (item.host_user) # Data for a row of users, Hostuser a row of data objects for a table    print (item.host_user.username)

II. Paramiko

There are two basic usages of Paramiko: Connection based on user name password, public-key private key connection. There are two major categories: sshclient (for connecting to remote servers and performing basic commands), sftpclient (for connecting to remote servers and performing upload downloads)

1. Sshclient

1.1 Connect based on user name password:

Import paramiko# Create ssh object ssh = Paramiko. Sshclient () # allows connections to host Ssh.set_missing_host_key_policy (Paramiko) that are not in the Know_hosts file. Autoaddpolicy ()) # Connection Server Ssh.connect (hostname= ' 172.25.50.13 ', port=22, username= ' work ', password= ' 123456 ') # Execute command stdin , stdout, stderr = Ssh.exec_command (' ls-l ') # gets the command result = Stdout.read () print (Result.decode ()) # Close Connection Ssh.close () out: Total 8drwxr-xr-x 2 work work 4096 April 19:22 Cn_market_luadrwxrwxr-x 3 work work 4096 Mar 19:09 www implementation command based on username password

1.2 based on a public key connection:

Import paramiko# Create a key file Private_key = Paramiko. Rsakey.from_private_key_file ('/home/auto/.ssh/id_rsa ') # create ssh object ssh = Paramiko. Sshclient () # allows connections to host Ssh.set_missing_host_key_policy (Paramiko) that are not in the Know_hosts file. Autoaddpolicy ()) # Connection Server Ssh.connect (hostname= ' 172.25.50.13 ', port=22, username= ' work ', Key=private_key) # Execute command stdin, stdout, stderr = Ssh.exec_command (' df-h ') # gets the command result = Stdout.read () # Close Connection ssh.close () Out:filesystem      Size  Used Avail use% mounted on/dev/vda1        20G  4.2G   15G  23%/tmpfs           1.9G     0  1.9G   0%/dev/shm/ DEV/VDB1        99G  499M   93G   1%/data0 implement remote execution commands based on public key key

2. Sftpclient

2.1 Connect based on user name password:

Import paramiko# Create Transporttransport = Paramiko. Transport ((' 172.25.50.13 ')) Transport.connect (username= ' work ', password= ' 123456 ') # Create sftpclient and connect based on Transport To bind them both to SFTP = Paramiko. Sftpclient.from_transport (transport) # upload location.py to Server/tmp/test.pysftp.put ('/tmp/location.py ', '/tmp/test.py ') # download Remove_path to local local_pathsftp.get (' Remove_path ', ' Local_path ') # close sessiontransport.close () upload download based on username password

2.2 based on a public key connection:

Import paramiko# Create a key file Private_key = Paramiko. Rsakey.from_private_key_file ('/home/auto/.ssh/id_rsa ') transport = Paramiko. Transport (' 172.25.50.13 ', ()) Transport.connect (username= ' work ', pkey=private_key) sftp = Paramiko. Sftpclient.from_transport (transport) # upload location.py to Server/tmp/test.pysftp.put ('/tmp/location.py ', '/tmp/test.py ') # download Remove_path to local local_pathsftp.get (' Remove_path ', ' Local_path ') transport.close () upload and download based on public key

3. Fortress machine

The fortress machine probably consists of:

• Administrator creates an account for the user on the server (place the public key on the server, or use the username password)
• User Login Fortress Machine, enter the bastion machine user name password, display the current User Management Server list
• The user chooses the server and automatically logs in
• Perform actions and log user actions at the same time

The above functions can be achieved using Paramiko, where the database is omitted first:

Version one: 1) the user enters the content at the terminal and sends the content to the remote server,

2) The remote server executes the command and returns the result

3) User Terminal display content

Import paramikoimport sysimport osimport socketimport selectimport getpassfrom paramiko.py3compat import u  # Comment out this line in py27 tran = Paramiko. Transport (' 172.25.50.13 ', ', ') tran.start_client () Tran.auth_password (' Work ', ' 123456 ') # Open a channel Chan = Tran.open_ Session () # Gets a terminal chan.get_pty () # Activator Chan.invoke_shell () while True:    # Monitor user input and server return data    # Sys.stdin process user Input    # Chan is a previously created channel for receiving server return information    readable, writeable, error = Select.select ([Chan, Sys.stdin,],[],[],1)    if Chan in Readable:        try:            x = u (CHAN.RECV (1024x768)) # Py3 in code            # x = Chan.recv (1024x768)   # Py2 in code            if Len (x) = = 0:                p Rint (' \r\n*** eof\r\n ')                break            sys.stdout.write (x)            Sys.stdout.flush ()        except socket.timeout:            Pass    if Sys.stdin in readable:        INP = Sys.stdin.readline ()        chan.sendall (INP) chan.close () Tran.close ()

Ultimate Version: Fortress machine with user logs

# Log user log import Paramikoimport sysimport osimport socketimport getpassfrom paramiko.py3compat import u# windows does not ha ve termios...try:import termios import tty Has_termios = trueexcept Importerror:has_termios = falsedef Inter    Active_shell (chan): If Has_termios:posix_shell (chan) Else:windows_shell (chan) def Posix_shell (Chan): Import Select Oldtty = Termios.tcgetattr (Sys.stdin) Try:tty.setraw (Sys.stdin.fileno ()) Tty.setcbre AK (Sys.stdin.fileno ()) chan.settimeout (0.0) log = open (' Handle.log ', ' A + ', encoding= ' utf-8 ') flag = F Alse temp_list = [] While True:r, W, E = Select.select ([Chan, Sys.stdin], [], []) if                        Chan in r:try:x = U (CHAN.RECV (1024x768)) If Len (x) = = 0:                        Sys.stdout.write (' \r\n*** eof\r\n ') break if flag: If X.startswith (' \ r \ n'): Pass Else:temp_list.append (x) Flag = False sys.stdout.write (x) Sys.stdout.flush () exce                PT Socket.timeout:pass If Sys.stdin in r:x = Sys.stdin.read (1) Import JSON if Len (x) = = 0:break if x = = ' \ t ': FLA g = True else:temp_list.append (x) if x = = ' \ r ': LOG.W    Rite (". Join (Temp_list)) Log.flush () temp_list.clear () chan.send (x) Finally:termios.tcsetattr (Sys.stdin, Termios. Tcsadrain, Oldtty) def Windows_shell (Chan): Import threading Sys.stdout.write ("Line-buffered terminal emulation. Press F6 or ^z to send eof.\r\n\r\n ") def writeall (sock): while True:data = Sock.recv If not data:sys.stdout.write (' \r\n*** EOF ***\r\n\r\n ') sys.st Dout.flush () break sys.stdout.write (data) Sys.stdout.flush () writer = Threading.            Thread (Target=writeall, Args= (chan)) Writer.start () try:while true:d = Sys.stdin.read (1) If not d:break chan.send (d) except Eoferror: # user hits ^z or F6 passdef Run (): Default_username = Getpass.getuser () Username = input (' username [%s]: '% default_username ') If Len (Usernam e) = = 0:username = default_username hostname = input (' hostname: ') If LEN (hostname) = = 0:print (' * * *        Hostname required. ') Sys.exit (1) tran = Paramiko. Transport ((hostname,,)) tran.start_client () Default_auth = "P" auth = input (' Auth by (p) Assword or (r) SA key[% S] '% Default_auth) if Len (auth) = = 0:auth = Default_auth if auth = = ' R ':       Default_path = Os.path.join (os.environ[' HOME '], '. SSH ', ' id_rsa ') path = input (' RSA key [%s]: '% DEFAULT_PA TH) If len (path) = = 0:path = Default_path Try:key = Paramiko. Rsakey.from_private_key_file (path) except Paramiko. Passwordrequiredexception:password = Getpass.getpass (' RSA key password: ') key = Paramiko. Rsakey.from_private_key_file (path, password) Tran.auth_publickey (username, key) ELSE:PW = Getpass.getpas    S (' Password for%[email protected]%s: '% (username, hostname)) Tran.auth_password (username, PW) # Open a channel Chan = Tran.open_session () # Gets a terminal chan.get_pty () # activator Chan.invoke_shell () Interactive_shell (Chan) c Han.close () tran.close () if __name__ = = ' __main__ ': Run ()

The way of the Python-sqlalchemy/paramiko/fortress machine