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()
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)