http://www.cnblogs.com/alex3714/articles/5248247.html
SqlAlchemy ORM
SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果
pip3 install sqlalchemy
simple
#!/usr/bin/env python
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,func,or_,not_,and_
from sqlalchemy.orm import sessionmaker
Base = declarative_base() # generate a SqlORM base class
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/testdb",echo=False) #echo=True will show sql details
class Host(Base):
__tablename__ = 'host'
id = Column(Integer,primary_key=True,autoincrement=True)
hostname = Column(String(64),unique=False,nullable=False)
ip_addr = Column(String(64),unique=False,nullable=False)
port = Column(Integer,default=22)
Base.metadata.create_all(engine) # create table
#if __name__ = '__main__':
SessionCls = sessionmaker(bind=engine) # create session class which establishes with db, class not object
session = SessionCls()
#------------------------------- add --------------------
#h1 = Host(hostname='localhost',ip_addr='127.0.0.1')
#h2=Host(hostname='ubuntu',ip_addr='10.0.0.4')
#h3=Host(hostname='ubuntu2',ip_addr='10.0.0.5')
#session.add_all([h1,h2])
#session.add(h3)
#h2.hostname = 'ubuntu1'
#session.commit()
#----------------------advanced add ----------------------------------
a=[('localhost','127.0.0.1'),('ubuntu','10.0.0.4'),('ubuntu1','10.0.0.5')]
for i in a:
h = Host(hostname=i[0],ip_addr=i[1])
session.add(h)
session.commit()
#--------------------------- delete --------------------
session.query(Host).filter(Host.ip_addr == '127.0.0.1').delete()
session.commit()
#---------------------------- update --------------------------
session.query(Host).filter(Host.ip_addr == '10.0.0.4').update({'port':10050})
session.commit()
#---------------------------------- query ---------------------------
obj = session.query(Host).filter_by(hostname='ubuntu2').all()
print("\033[31;1m ---------------- > \033[0m",obj,type(obj),obj[0].hostname)
objs = session.query(Host).filter(Host.port == 10050).all()
print("\033[31;1m -----\033[0m",objs,type(objs),objs[1].hostname)
res = session.query(Host).filter(Host.hostname.in_(['ubuntu','ubuntu1'])).first()
print("\033[31;1m-------\033[0m",res,type(res),res.hostname)
ret = session.query(Host).filter(and_(Host.hostname == 'ubuntu1',Host.port==10050)).first()
print(ret,type(ret))
res1 = session.query(Host,func.count(Host.hostname),Host.hostname).all()
print(res1,type(res1),res1[0][1])
res2 = session.query(func.count(Host.hostname),Host.hostname).all()
print(res2,type(res2),res2[0][0])
res3 = session.query(func.count(Host.hostname),Host.hostname).group_by(Host.port).all()
print(res3,type(res3))
#------------------------------------------------------------ another way of update and delete ----------------------------------------------------
objs = session.query(Host).filter(Host.port == 10050).first()
print(objs.port)
objs.port = 223 #udpate
session.commit()
objs1 = session.query(Host).filter(Host.port == 10050).all()
session.delete(objs1[0]) #delete
session.commit()
更多内容详见：
http://www.jianshu.com/p/e6bba189fcbd
http://docs.sqlalchemy.org/en/latest/core/expression_api.html
注：SQLAlchemy无法修改表结构，如果需要可以使用SQLAlchemy开发者开源的另外一个软件Alembic来完成。
one to multi
#!/usr/bin/env python
from sqlalchemy import Table,String,Column,Integer,ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/testdb",echo=False) #echo=True will show sql details
class Host(Base):
__tablename__ = 'hosts'
id = Column(Integer,primary_key=True,autoincrement=True)
hostname = Column(String(64),unique=True,nullable=False)
ip_addr = Column(String(64),unique=True,nullable=False)
port = Column(Integer,default=22)
group_id = Column(Integer,ForeignKey('group.id')) # foreign key, relate to table group column 'id', nothing related to below relationship line. here means one group can have multiple hosts, and one host only belongs one group, because hostname and ip_addr are both unique. this is one to multi: one group to multi hosts.
groups = relationship("Group",backref='hostList') #get relationship with table group
#groups = relationship("Group") #get relationship with table group
class Group(Base):
__tablename__ = 'group'
name = Column(String(64),unique=True,nullable=False)
id = Column(Integer,primary_key=True,autoincrement=True)
hostRe = relationship("Host") # get relationship with table hosts
Base.metadata.create_all(engine)
SessionCls = sessionmaker(bind=engine) # create session class, not object
session = SessionCls()
#------------------------------- add --------------------
g1=Group(name='g1')
g2=Group(name='g2')
#session.add_all([g1,g2])
#g3=Group(name='g3')
#session.add_all([g3,])
h1 = Host(hostname='localhost',ip_addr='127.0.0.1',group_id='1')
h2=Host(hostname='ubuntu',ip_addr='10.0.0.4',group_id='1')
#h3=Host(hostname='ubuntu2',ip_addr='10.0.0.5',group_id=g3.id)
#session.add_all([h1,h2])
#session.add(h3)
h2.hostname = 'ubuntu1'
#session.commit()
#---------------- update ubuntu2's group_id, upper h3 makes its value is NULL-------------
g3=session.query(Group).filter(Group.name=='g3').first()
h3=session.query(Host).filter(Host.hostname=='ubuntu2').update({'group_id':g3.id})
#session.commit()
#------ use table host groups = relationship("Group") check table group info
h= session.query(Host).filter(Host.group_id==1).all()
print(h[0].groups)
print("---- > ",h[0].groups.name)
#-----------------------use table group hostRe=relationship("Host") to get table hosts info ---------------
g=session.query(Group).filter(Group.name=='g3').all()
print(g[0].hostRe)
print('--------->',g[0].hostRe[0].hostname)
#-----------------------use table hosts groups=relationship("Group",backref="hostList") to get table group and hosts info, then we don't need define relationship hostRe = relationship("Host") at table group ---------------
g=session.query(Group).filter(Group.name=='g3').all()
print(g[0].hostList)
print('--------->',g[0].hostList[0].hostname)
#---------- even don't have groups = relationship("Group"), still can check info like below way
obj = session.query(Host,Group).filter(Host.group_id==Group.id).all()
print(obj)
print(obj[0][0].hostname)
objs = session.query(Host,Group).filter(Host.group_id==Group.id).group_by(Host.group_id).all()
print(objs)
# -------- join query by using relationship adn backref -------------------
res = session.query(Host).join(Host.groups).group_by(Group.id).all()
print(res)
res1 = session.query(Group,func.count(Group.name)).join(Group.hostList).group_by(Group.id).all()
print(res1)
relationship
from sqlalchemy.orm import relationship
class Host(Base):
__tablename__ = 'hosts'
id = Column(Integer,primary_key=True,autoincrement=True)
hostname = Column(String(64),unique=True,nullable=False)
ip_addr = Column(String(64),unique=True,nullable=False)
port = Column(Integer,default=22)
group_id = Column(Integer,ForeignKey('group.id')) # foreign key, relate to table group column 'id', nothing related to below relationship line. here means one group can have multiple hosts, and one host only belongs one group, because hostname and ip_addr are both unique. this is one to multi: one group to multi hosts.
groups = relationship("Group",backref='hostList') #get relationship with table group
#groups = relationship("Group") #get relationship with table group
class Group(Base):
__tablename__ = 'group'
name = Column(String(64),unique=True,nullable=False)
id = Column(Integer,primary_key=True,autoincrement=True)
hostRe = relationship("Host") # get relationship with table hosts
#------ use table host groups = relationship("Group") check table group info
h= session.query(Host).filter(Host.group_id==1).all()
print(h[0].groups)
print("---- > ",h[0].groups.name)
#-----------------------use table group hostRe=relationship("Host") to get table hosts info ---------------
g=session.query(Group).filter(Group.name=='g3').all()
print(g[0].hostRe)
print('--------->',g[0].hostRe[0].hostname)
#-----------------------use table hosts groups=relationship("Group",backref="hostList") to get table group and hosts info, then we don't need define relationship hostRe = relationship("Host") at table group and at table hosts groups = relationship("Group") ---------------
g=session.query(Group).filter(Group.name=='g3').all()
print(g[0].hostList)
print('--------->',g[0].hostList[0].hostname)
multi to multi
#!/usr/bin/env python
from sqlalchemy import Table,String,Column,Integer,ForeignKey,func
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
#---------------------------create a third table relate to table hosts and group --------------------------------------
Host2Group = Table('host2group',Base.metadata,
Column('host_id',ForeignKey('hosts.id'),primary_key=True), #relate to table hosts column id
Column('group_id',ForeignKey('group.id'),primary_key=True), #relate to table group column id
)
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/testdb",echo=False) #echo=True will show sql details
class Host(Base):
__tablename__ = 'hosts'
id = Column(Integer,primary_key=True,autoincrement=True)
hostname = Column(String(64),unique=True,nullable=False)
ip_addr = Column(String(64),unique=True,nullable=False)
port = Column(Integer,default=22)
groups = relationship("Group",secondary=Host2Group,backref='hostList') #get relationship with table group and host2group, then table hosts and group don't even need to define foreign key
def __repr__(self): #return the result like below format when using queries
return "====== id=%s; hostname=%s; ip_addr=%s; port=%s =====" %(self.id,self.hostname,self.ip_addr,self.port)
class Group(Base):
__tablename__ = 'group'
name = Column(String(64),unique=True,nullable=False)
id = Column(Integer,primary_key=True,autoincrement=True)
def __repr__(self): #return the result like below format when using queries
return "==== id=%s; name=%s =====" %(self.id,self.name)
Base.metadata.create_all(engine)
SessionCls = sessionmaker(bind=engine) # create session class, not object
session = SessionCls()
#------------------------------- add --------------------
g1=Group(name='g1')
g2=Group(name='g2')
g3=Group(name='g3')
#session.add_all([g1,g2,g3])
h1 = Host(hostname='localhost',ip_addr='127.0.0.1')
h2=Host(hostname='ubuntu',ip_addr='10.0.0.4')
h3=Host(hostname='ubuntu2',ip_addr='10.0.0.5')
#session.add_all([h1,h2])
#session.add(h3)
h2.hostname = 'ubuntu1'
#-------------------- add data into host2group ---------------------------
h=session.query(Host).filter(Host.id==1).first()
g=session.query(Group).all()
'''
mysql> select * from host2group;
Empty set (0.00 sec)
'''
#h.groups=g
#session.commit()
'''
mysql> select * from host2group;
+---------+----------+
| host_id | group_id |
+---------+----------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
+---------+----------+
'''
#h.groups.pop()
#session.commit()
'''
mysql> select * from host2group;
+---------+----------+
| host_id | group_id |
+---------+----------+
| 1 | 1 |
| 1 | 2 |
+---------+----------+
'''
h11=session.query(Host).filter(Host.id==2).first()
g11=session.query(Group).all()
#h11.groups=g11[1:-1]
#session.commit()
#----------------------------query --------------------------
res = h.groups
print(res)
print(h)
print(g11[0].hostList[0].hostname)
print(g11)
# --------------------- upper query result ---------------------------------------------------
[==== id=1; name=g1 =====, ==== id=2; name=g2 =====]
====== id=1; hostname=localhost; ip_addr=127.0.0.1; port=22 =====
localhost
[==== id=1; name=g1 =====, ==== id=2; name=g2 =====, ==== id=3; name=g3 =====]
##################################################################################################
堡垒机
http://www.cnblogs.com/alex3714/articles/5286889.html
download paramiko source code cd demos python demo_simple.py
vi demo_simple.py
#!/usr/bin/env python
# Copyright (C) 2003-2007 Robey Pointer <robeypointer@gmail.com>
#
# This file is part of paramiko.
#
# Paramiko is free software; you can redistribute it and/or modify it under the
# terms of the GNU Lesser General Public License as published by the Free
# Software Foundation; either version 2.1 of the License, or (at your option)
# any later version.
#
# Paramiko is distributed in the hope that it will be useful, but WITHOUT ANY
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
# A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
# details.
#
# You should have received a copy of the GNU Lesser General Public License
# along with Paramiko; if not, write to the Free Software Foundation, Inc.,
# 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
import base64
import getpass
import os
import socket
import sys
import traceback
import paramiko
from modules import interactive
def handle(user,host,osUser,passwd): #modify original paramiko/demos/demo_simple.py
# setup logging
paramiko.util.log_to_file('demo_simple.log')
# get hostname
username = ''
#if len(sys.argv) > 1:
# hostname = sys.argv[1]
# if hostname.find('@') >= 0:
# username, hostname = hostname.split('@')
#else:
#hostname = raw_input('Hostname: ')
hostname = host #get the hostname instead of input manually
if len(hostname) == 0:
print '*** Hostname required.'
sys.exit(1)
port = 22
if hostname.find(':') >= 0:
hostname, portstr = hostname.split(':')
port = int(portstr)
# get username
if username == '':
default_username = getpass.getuser()
#username = raw_input('Username [%s]: ' % default_username)
username = osUser #get the user instead of input manually
if len(username) == 0:
username = default_username
#password = getpass.getpass('Password for %s@%s: ' % (username, hostname))
password = passwd #get the password instead of input manually
# now, connect and use paramiko Client to negotiate SSH2 across the connection
try:
client = paramiko.SSHClient()
client.load_system_host_keys()
client.set_missing_host_key_policy(paramiko.WarningPolicy())
print '*** Connecting...'
client.connect(hostname, port, username, password)
chan = client.invoke_shell()
print repr(client.get_transport())
print '*** Here we go!'
print
interactive.interactive_shell(chan,user,osUser,host)
chan.close()
client.close()
except Exception, e:
print '*** Caught exception: %s: %s' % (e.__class__, e)
traceback.print_exc()
try:
client.close()
except:
pass
sys.exit(1)
vi interactive.py
# Copyright (C) 2003-2007 Robey Pointer <robeypointer@gmail.com>
#
# This file is part of paramiko.
#
# Paramiko is free software; you can redistribute it and/or modify it under the
# terms of the GNU Lesser General Public License as published by the Free
# Software Foundation; either version 2.1 of the License, or (at your option)
# any later version.
#
# Paramiko is distributed in the hope that it will be useful, but WITHOUT ANY
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
# A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
# details.
#
# You should have received a copy of the GNU Lesser General Public License
# along with Paramiko; if not, write to the Free Software Foundation, Inc.,
# 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
import socket
import sys
import time
from modules import log
# windows does not have termios...
try:
import termios
import tty
has_termios = True
except ImportError:
has_termios = False
def interactive_shell(chan,user,osUser,host):
if has_termios:
posix_shell(chan,user,osUser,host)
else:
windows_shell(chan)
def posix_shell(chan,user,osUser,host):
import select
oldtty = termios.tcgetattr(sys.stdin)
try:
tty.setraw(sys.stdin.fileno())
tty.setcbreak(sys.stdin.fileno())
chan.settimeout(0.0)
cmd = '' #modify original paramiko/demos/demo_simple.py and interactive.py
tab_key = False
while True:
r, w, e = select.select([chan, sys.stdin], [], [])
if chan in r:
try:
x = chan.recv(1024)
if tab_key: #means the last cycle -- the below chan.send(x) press the tab in the command input
if x not in ('\x07', '\r\n'): #remove these characters out of the press tab result command
cmd += x #get the press tab result
tab_key = False
if len(x) == 0:
print '\r\n*** EOF\r\n',
break
sys.stdout.write(x)
sys.stdout.flush()
except socket.timeout:
pass
if sys.stdin in r:
x = sys.stdin.read(1)
if '\r' != x:
cmd +=x #before press enter, get the whole command
else: #already press enter, means the command comes up
date = time.strftime("%Y-%m-%d %H:%M:%S")
log.log(user,osUser,host,cmd,date) #record the command into db
cmd = '' #clear cmd, ready for new command input
if '\t' == x: #press tab may be in the command, the next cycle -- the upper cycle stdout will return the result of the press tab
tab_key = True
if len(x) == 0:
break
chan.send(x)
finally:
termios.tcsetattr(sys.stdin, termios.TCSADRAIN, oldtty)
# thanks to Mike Looijmans for this code
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(256)
if not data:
sys.stdout.write('\r\n*** EOF ***\r\n\r\n')
sys.stdout.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 hit ^Z or F6
pass
the db log result is like below:
mysql> select * from baoleidb.log;
+------+-----------+-------------+-----------+---------------------+
| user | osUser | host | cmd | date |
+------+-----------+-------------+-----------+---------------------+
| alex | userb | 127.0.0.1 | ls | 2016-04-17 18:04:29 |
| alex | userb | 127.0.0.1 | who | 2016-04-17 18:04:30 |
| alex | userb | 127.0.0.1 | df | 2016-04-17 18:04:32 |
| alex | userb | 127.0.0.1 | exit | 2016-04-17 18:04:34 |
| alex | userb | 127.0.0.1 | top | 2016-04-17 18:04:49 |
| alex | userb | 127.0.0.1 | qifconfig | 2016-04-17 18:04:57 |
| alex | userb | 127.0.0.1 | exit | 2016-04-17 18:04:58 |
| alex | osuser1 | 10.0.0.1 | ls | 2016-04-17 18:05:22 |
| alex | osuser1 | 10.0.0.1 | exit | 2016-04-17 18:05:27 |
+------+-----------+-------------+-----------+---------------------+