示例#1
0
class Mymysql(object):
    def __init__(self):
        print('连接数据库...')
        self.engine = create_engine(MYSQL_URL,
                                    pool_recycle=2400,
                                    pool_size=20,
                                    max_overflow=10)
        metadata = MetaData(self.engine)
        Session = sessionmaker(bind=self.engine)
        self.session = Session()
        self.Post_table = Table("posts", metadata,
                                autoload=True)  # autoload=True这个是关键

    def insert(self, arr, table):
        print('插入数据库...')
        try:
            self.session.execute(self.__dict__[table].insert(), arr)
            self.session.commit()
            print('数据已插入', table)
        except Exception as e:
            print('数据插入异常,正在回滚', e)
            self.session.rollback()
        finally:
            print('关闭session')
            self.session.close()

    def close(self):
        print('关闭数据库引擎...')
        self.engine.dispose()
示例#2
0
def push_data(data_in_dict):
    if not data_in_dict or len(data_in_dict) == 0:
        raise Exception('Empty_data_from_HA')
    connection_str = f'mysql+pymysql://{SETTINGS["Database"]["username"]}:{SETTINGS["Database"]["password"]}@{SETTINGS["Database"]["ip"]}:{SETTINGS["Database"]["port"]}/{SETTINGS["Database"]["base"]}'
    engine = create_engine(connection_str,
                           echo=SETTINGS['Database']['echo_debug'])
    Base.metadata.create_all(bind=engine)
    Session = sessionmaker(bind=engine, autocommit=False)
    session = Session()
    session.execute(sa_text('''Truncate table HAhosts'''))
    counter_insert = 0
    for h in data_in_dict:
        if h['svname'] in ('BACKEND', 'FRONTEND'):
            continue
        if h['pxname'] not in SETTINGS['HAproxy']['pxname_filter']:
            continue
        host1 = Host()
        host1.pxname = h['pxname']
        host1.svname = h['svname']
        host1.status = h['status']
        host1.scur = h['scur']
        host1.addr, host1.port = h['addr'].split(':')
        host1.algo = h['algo']
        session.add(host1)
        counter_insert += 1
    session.commit()
    logging.info(f'Inserted {counter_insert} lines')
    session.close()
engine = create_engine('sqlite:///%s' % dbpath)
metadata = MetaData(engine)
people = Table(
    'people',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('count', Integer),
)
Session = sessionmaker(bind=engine)
session = Session()
metadata.create_all(engine)

people_insert = people.insert().values(name='test', count=2)
print(str(people_insert))
session.execute(people_insert)
session.commit()

session.execute(people_insert, [{
    'name': 'jill',
    'count': 15
}, {
    'name': 'joe',
    'count': 10
}])
session.commit()
result = session.execute(select([people]))
for row in result:
    print(row)

# c 表示 name 是people資料表中的欄位(column)