day13

sqlalchemy创建表结构：一对多





#!/usr/bin/env python
#-*- coding:utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
#创建数据连接引擎
engine = create_engine("mysql+pymysql://root:123.com@192.168.11.233:3306/zeng", max_overflow=5)
Base = declarative_base()
class user(Base):
    __tablename__ = 'user'
    nid = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(32))
    group_id = Column(Integer,ForeignKey('group.nid'))

class group(Base):
    __tablename__ = 'group'
    nid =  Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32))
#创建表
# Base.metadata.create_all(engine)
if __name__ == '__main__':
    Session = sessionmaker(bind=engine)
    session = Session()
    # session.add(group(name='DBA'))
    # session.add(group(name='SA'))
    # session.commit()

    session.add_all([
        user(username='alex1',group_id=1),
        user(username='alex2',group_id=2)
    ])
    session.commit()







 一对多






from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
engine = create_engine("mysql+pymysql://root:123.com@192.168.11.233:3306/zeng", max_overflow=5)

Base = declarative_base()
# 一对多
# class group(Base):
#     __tablename__ = 'group'
#     nid = Column(Integer, primary_key=True,autoincrement=True)
#     caption = Column(String(32))
# class user(Base):
#     __tablename__ = 'user'
#     nid = Column(Integer, primary_key=True,autoincrement=True)
#     username = Column(String(32))
#     group_id = Column(Integer, ForeignKey('group.nid'))
#     group = relationship("group", backref='uuu')
#     def __repr__(self):
#         temp = "%s - %s: %s" %(self.nid, self.username, self.group_id)
#         return temp

class user(Base):
    __tablename__ = 'user'
    nid = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(32))
    group_id = Column(Integer,ForeignKey('group.nid'))


#相当于 把group实例嵌套到user实例中的group字段
    group = relationship("group", backref='uuu')
    def __repr__(self):
        temp = "%s - %s: %s" %(self.nid, self.username, self.group_id)
        return temp

class group(Base):
    __tablename__ = 'group'
    nid =  Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32))



#创建表，和删除表
# Base.metadata.create_all(engine)
# Base.metadata.drop_all(engine)

#创建连接
Session = sessionmaker(bind=engine)
session = Session()


#在表中添加信息
# session.add(Group(caption='dba'))
# session.add(Group(caption='ddd'))
#提交
# session.commit()


#在表中添加多条信息
# session.add_all([
#     User(username='alex1',group_id=1),
#     User(username='alex2',group_id=2)
# ])
# session.commit()

# 只是获取用户
# ret = session.query(User).filter(User.username == 'alex1').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)

#映射
# ret = session.query(User.username).all()
# print(ret)
# sql = session.query(User,Group).join(Group, isouter=True)
# print(sql)
# ret = session.query(User,Group).join(Group, isouter=True).all()
# print(ret)
# select * from user left join group on user.group_id = group.nid



#原始方法,连表查询
r = session.query(user.username,group.name).join(group,isouter=True).all()
print(r)
#新方式，正向查询
ret = session.query(user).all()
for obj in ret:
    print(obj.nid,obj.username,obj.group_id,obj.group.nid,obj.group.name)

#原始方法
q = session.query(user.username,group.name).join(group,isouter=True).filter(group.name=='DBA').all()
print(q)
#反向查询
obj2 = session.query(group).filter(group.name == 'DBA').first()
print(obj2.nid)
print(obj2.name)
print(obj2.uuu)







多对多表结构






#!/usr/bin/env python
#-*- coding:utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine("mysql+pymysql://root:123.com@192.168.11.233:3306/zeng", max_overflow=5)
Base = declarative_base()

####****************定义表************************
class Host(Base):
    __tablename__ = 'host'
    nid = Column(Integer, primary_key=True,autoincrement=True)
    hostname = Column(String(32))
    port = Column(String(32))
    ip = Column(String(32))

class HostUser(Base):
    __tablename__ = 'host_user'
    nid = Column(Integer, primary_key=True,autoincrement=True)
    username = Column(String(32))

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'))
#******************与另外两张表建立关系，可以通过h，u查询************************
    host = relationship("Host",backref='h')
    host_user = relationship("HostUser",backref = 'u')


# Base.metadata.create_all(engine)
if __name__ == '__main__':
    Session = sessionmaker(bind=engine)
    session=Session()



    host_obj = session.query(Host).filter(Host.hostname=='c1').first()
    print(host_obj.h)
    for i in host_obj.h:
        print(i.host_user.username)
    # session.commit()
#获取1中所有用户
#     host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
#     print(host_obj)
# #获取id为host_obj.nid的所有数据
#     host_2_host_user = session.query(HostToHostUser.host_user_id).filter(HostToHostUser.host_id == host_obj.nid).all()
#     print(host_2_host_user)
#     r = zip(*host_2_host_user)
#     users = session.query(HostUser.username).filter((HostUser.nid.in_list(r)[0])).all()
#     print(users)
#原始代码
# session.query(HostUser.name).filter(HostUser.nid.in_(session.query(HostToHostUser.host_user_id).filter(HostToHostUser.host_id == session.query(Host.nid).filter(Host.hostname == 'c1'))))







 


堡垒机前戏




开发堡垒机之前，先来学习Python的paramiko模块，该模块机遇SSH用于连接远程服务器并执行相关操作




SSHClient




用于连接远程服务器并执行基本命令




基于用户名密码连接：











 SSHClient 封装 Transport




基于公钥密钥连接：











 SSHClient 封装 Transport




SFTPClient




用于连接远程服务器并执行上传下载




基于用户名密码上传下载











基于公钥密钥上传下载











 Demo




堡垒机的实现 




实现思路：








堡垒机执行流程：




    

  1. 管理员为用户在服务器上创建账号（将公钥放置服务器，或者使用用户名密码）
    2. 

  2. 用户登陆堡垒机，输入堡垒机用户名密码，现实当前用户管理的服务器列表
    3. 

  3. 用户选择服务器，并自动登陆
    4. 

  4. 执行操作并同时将用户操作记录
    5. 





注：配置.brashrc实现ssh登陆后自动执行脚本，如：/usr/bin/python /home/wupeiqi/menu.py




实现过程




步骤一，实现用户登陆
















1


2


3


4


5


6


7


8






import getpass


 


user = raw_input('username:')


pwd = getpass.getpass('password')


if user == 'alex' and pwd == '123':


    print '登陆成功'


else:


    print '登陆失败'


















步骤二，根据用户获取相关服务器列表
















1


2


3


4


5


6


7


8


9


10


11


12


13


14


15


16


17


18


19


20


21






dic = {


    'alex': [


        '172.16.103.189',


        'c10.puppet.com',


        'c11.puppet.com',


    ],


    'eric': [


        'c100.puppet.com',


    ]


}


 


host_list = dic['alex']


 


print 'please select:'


for index, item in enumerate(host_list, 1):


    print index, item


 


inp = raw_input('your select (No):')


inp = int(inp)


hostname = host_list[inp-1]


port = 22


















步骤三，根据用户名、私钥登陆服务器
















1


2


3


4


5


6


7


8


9


10


11


12


13


14


15


16


17


18


19


20


21


22






tran = paramiko.Transport((hostname, port,))


tran.start_client()


default_path = os.path.join(os.environ['HOME'], '.ssh', 'id_rsa')


key = paramiko.RSAKey.from_private_key_file(default_path)


tran.auth_publickey('wupeiqi', key)


 


# 打开一个通道


chan = tran.open_session()


# 获取一个终端


chan.get_pty()


# 激活器


chan.invoke_shell()


 


#########


# 利用sys.stdin,肆意妄为执行操作


# 用户在终端输入内容，并将内容发送至远程服务器


# 远程服务器执行命令，并将结果返回


# 用户终端显示内容


#########


 


chan.close()


tran.close()


















 肆意妄为方式一




 肆意妄为方式二




 肆意妄为方式三




注：密码验证 t.auth_password(username, pw)




详见：paramiko源码demo




数据库操作




Python 操作 Mysql 模块的安装
















1


2


3


4


5






linux：


    yum install MySQL-python


 


window：


    http://files.cnblogs.com/files/wupeiqi/py-mysql-win.zip


















SQL基本使用




1、数据库操作
















1


2


3






show databases; 


use [databasename];


create database  [name];


















2、数据表操作
















1


2


3


4


5


6


7


8


9


10






show tables;


 


create table students


    (


        id int  not null auto_increment primary key,


        name char(8) not null,


        sex char(4) not null,


        age tinyint unsigned not null,


        tel char(13) null default "-"


    );


















 View Code




3、数据操作
















1


2


3


4


5


6


7






insert into students(name,sex,age,tel) values('alex','man',18,'151515151')


 


delete from students where id =2;


 


update students set name = 'sb' where id =1;


 


select * from students


















4、其他
















1


2


3






主键


外键


左右连接


















Python MySQL API




一、插入数据
















1


2


3


4


5


6


7


8


9


10


11


12


13


14


15






import MySQLdb


  


conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')


  


cur = conn.cursor()


  


reCount = cur.execute('insert into UserInfo(Name,Address) values(%s,%s)',('alex','usa'))


# reCount = cur.execute('insert into UserInfo(Name,Address) values(%(id)s, %(name)s)',{'id':12345,'name':'wupeiqi'})


  


conn.commit()


  


cur.close()


conn.close()


  


print reCount


















 批量插入数据




注意：cur.lastrowid




二、删除数据
















1


2


3


4


5


6


7


8


9


10


11


12


13


14






import MySQLdb


 


conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')


 


cur = conn.cursor()


 


reCount = cur.execute('delete from UserInfo')


 


conn.commit()


 


cur.close()


conn.close()


 


print reCount


















三、修改数据
















1


2


3


4


5


6


7


8


9


10


11


12


13






import MySQLdb


 


conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')


 


cur = conn.cursor()


 


reCount = cur.execute('update UserInfo set Name = %s',('alin',))


 


conn.commit()


cur.close()


conn.close()


 


print reCount


















四、查数据










1


2


3


4


5


6


7


8


9


10


11


12


13


14


15


16


17


18


19


20


21


22


23


24


25


26


27


28


29


30


31


32


33


34


35


36


37


38


39


40


41


42


43


44






# ############################## fetchone/fetchmany(num)  ##############################


 


import MySQLdb


 


conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')


cur = conn.cursor()


 


reCount = cur.execute('select * from UserInfo')


 


print cur.fetchone()


print cur.fetchone()


cur.scroll(-1,mode='relative')


print cur.fetchone()


print cur.fetchone()


cur.scroll(0,mode='absolute')


print cur.fetchone()


print cur.fetchone()


 


cur.close()


conn.close()


 


print reCount


 


 


 


# ############################## fetchall  ##############################


 


import MySQLdb


 


conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')


#cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)


cur = conn.cursor()


 


reCount = cur.execute('select Name,Address from UserInfo')


 


nRet = cur.fetchall()


 


cur.close()


conn.close()


 


print reCount


print nRet


for i in nRet:


    print i[0],i[1]












 






            

            
posted @ 
2016-08-06 03:09 
(KeeP) 
阅读(164) 
评论(0) 
编辑 
收藏 
举报