Python devops Development (13)----SQLAlchemy and Paramiko

Content directory:

ORM Architecture SQLAlchemy

Paramiko

SQLAlchemy Operations on a table

Use Orm/schema type/sql Expression language/engine/connectionpooling/dialect All components to manipulate the data. Objects are created from the class, objects are converted to SQL, and SQL is executed.

1. Create a table

# Single-table class Test (Base): __tablename__ = ' Test ' nid = Column (Integer, primary_key=true,autoincrement=true) name = Co Lumn (String (32)) # One-to-many class group (Base): __tablename__ = ' Group ' nid = Column (Integer, primary_key=true,autoincrement =true) Caption = column (String ()) class User (Base): __tablename__ = ' User ' nid = Column (Integer, Primary_key=tru E,autoincrement=true) Username = column (String) group_id = Column (Integer, ForeignKey (' Group.nid ')) group = R Elationship ("Group", backref= ' UUU ') def __repr__ (self): temp = "%s-%s:%s"% (Self.nid, Self.username, Self.gro  UP_ID) return temp# Many-to-many class Host (Base): # metaclass,host.table Object __tablename__ = ' Host ' nid = Column (Integer, PRIMARY_KEY=TRUE,AUTOINCREMENT=TRUE) hostname = column (string ()) port = column (string ()) IP = column (String ( ) # Host_user = relationship (' Hostuser ', Secondary=hosttohostuser, backref= ' h ') Host_user = relationship (' HostUse R ', Secondary=hosttohostuser.__table__, backref= ' h ') class Hostuser (Base): __tablename__ = ' host_user ' nid = Column (Integer, Primary_key=true , autoincrement=true) Username = Column (String (+)) def init_table (): Base.metadata.create_all (Engine) def drop_table () : Base.metadata.drop_all (Engine) init_table () session = Sessionmaker (bind=engine) session = Session ()

2. Operation table

Add action

# Method 1session.add (Group (caption= ' DBA ')) Session.add (Group (caption= ' SA ')) Session.commit () # Method 2session.add_all ([    User (username= ' jabe1 ', group_id = 1),    User (username= ' jabe2 ', group_id = 2)]) Session.commit ()

Delete table Data operations

Session.query (Users). Filter (Users.id > 2). Delete () Session.commit ()

Modify table record Operations

Session.query (Users). Filter (Users.id > 2). Update ({"name": "099"}) session.query (users). Filter (Users.id > 2). Update ({Users.name:Users.name + "099"}, Synchronize_session=false) Session.query (Users). Filter (Users.id > 2). Update ({"num": Users.num + 1}, synchronize_session= "Evaluate") Session.commit ()

Query operations

#条件查询ret = session.query (user). filter (User.username = = ' Jabe1 '). All () print (ret) #单表查询ret = session.query (user). All () obj = Ret[0]print (ret) print (obj) print (obj.nid) print (obj.username) print (obj.group_id) #关联查询sql = Session.query (User) . Join (group,isouter=true) print (SQL) ret = session.query (User). Join (Group,isouter=true). All () print (ret) # sql = Session.query (user.username,group.caption). Join (group,isouter=true) # Print (SQL) # ret = Session.query (user.username , group.caption). Join (Group,isouter=true). All () # print (ret) #原始方式 (query user name and user group) # ret = Session.query (User.username, group.caption). Join (Group,isouter=true). All () # print (ret) #新方式正向查询 # ret = session.query (User). All () #-obj in ret:# # The obj generation refers to the data # #obj of each row of the user table. Group refers to the Group object # Print (Obj.nid,obj.username,obj.group_id,obj.group,obj.group.nid,obj.group. Caption) #原始方式 (query all users belonging to the DBA Group) ret = Session.query (user.username,group.caption). Join (Group,isouter=true). Filter ( Group.caption = = ' DBA '). All () print (ret) #新方式 (reverse query) obj = Session.query (Group). Filter (GrOup.caption = = ' DBA '). First () print (Obj.nid) print (obj.caption) rint (OBJ.UUU)　　 

Additional

#conditionsret = Session.query (Users). Filter_by (name='Alex'). All () RET= Session.query (Users). Filter (Users.id > 1, Users.name = ='Eric'). All () RET= Session.query (Users). Filter (Users.id.between (1, 3), Users.name = ='Eric'). All () RET= Session.query (Users). Filter (Users.id.in_ ([1,3,4]). All () RET= Session.query (Users). Filter (~users.id.in_ ([1,3,4]). All () RET= Session.query (Users). Filter (Users.id.in_ (Session.query (users.id). Filter_by (name='Eric')) . All () fromSQLAlchemyImportAnd_, Or_ret= Session.query (Users). Filter (And_ (Users.id > 3, Users.name = ='Eric') . All () RET= Session.query (Users). Filter (Or_ (Users.id < 2, Users.name = ='Eric') . All () RET=session.query (Users). Filter (Or_ (users.id< 2, And_ (Users.name=='Eric', Users.id > 3), Users.extra!="") . All ()#wildcard charactersret = Session.query (Users). Filter (Users.name.like ('e%') . All () RET= Session.query (Users). Filter (~users.name.like ('e%') . All ()#Limitret = Session.query (Users) [1:2]#SortRET =session.query (Users). Order_by (Users.name.desc ()). All () RET=session.query (Users). Order_by (Users.name.desc (), USERS.ID.ASC ()). All ()#Grouping fromSqlalchemy.sqlImportFuncret=session.query (Users). group_by (Users.extra). All () RET=session.query (Func.max (users.id), Func.sum (users.id), Func.min (Users.id)). Group_by (Users.name). All () RET =session.query (Func.max (users.id), Func.sum (users.id), Func.min (Users.id)). Group_by (Users.name). Having (func. Min (users.id)>2). All ()#even tableret= Session.query (Users, Favor). Filter (Users.id = =Favor.nid). All () RET=session.query (person). Join (Favor). All () RET= Session.query (person). Join (Favor, isouter=True). All ()#CombinationQ1 = session.query (users.name). Filter (Users.id > 2) Q2= Session.query (favor.caption). Filter (Favor.nid < 2) ret=q1.union (Q2). All () Q1= Session.query (Users.name). Filter (Users.id > 2) Q2= Session.query (favor.caption). Filter (Favor.nid < 2) ret= Q1.union_all (Q2). All ()

other

　　

Paramiko

Fortress machine

Bastion Machine Execution Flow:

1. Administrator creates an account for the user on the server (place the public key on the server, or use the username password)
2. User Login Fortress Machine, enter the Fort machine user name password, the actual current user Management Server list
3. The user chooses the server and automatically logs in
4. Perform actions and record user actions at the same time

Note: Configure the. BRASHRC to implement the script automatically after SSH login, such as:/usr/bin/python/tmp/s13/day13/s7.py

Practice Code:

Import paramikoimport sysimport osimport socketimport getpassfrom paramiko.py3compat import u# Windows does not has Termi Os...try:import Termios Import TTY Has_termios = trueexcept Importerror:has_termios = falsedef interactive_s Hell (chan): If Has_termios:posix_shell (chan) Else:windows_shell (chan) def Posix_shell (Chan): Impor T Select oldtty = termios.tcgetattr (Sys.stdin) Try:tty.setraw (Sys.stdin.fileno ()) Tty.setcbreak (SYS.S        Tdin.fileno ()) chan.settimeout (0.0) log = open (' Handle.log ', ' A + ', encoding= ' utf-8 ') flag = False 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 () except Socke T.timeout:pass if sys.stdin in r:x = Sys.stdin.read (1) impor                 T json if Len (x) = = 0:break if x = = ' \ t ': flag = True Else:temp_list.append (x) if x = = ' \ R ': Log.write ('. Join (Temp_list)) Log.flush () temp_list.clear () chan.send (x) finall Y: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.stdout.flu SH () 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_path ') 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 ()

Reference url:http://www.cnblogs.com/wupeiqi/articles/5699254.html

Python devops Development (13)----SQLAlchemy and Paramiko