def sa_table():
choices = ['a', 'b', 'c']
meta = sa.MetaData()
post = sa.Table(
'test_post', meta,
sa.Column('id', sa.Integer, nullable=False),
sa.Column('title', sa.String(200), nullable=False),
sa.Column('category', sa.String(200), nullable=True),
sa.Column('body', sa.Text, nullable=False),
sa.Column('views', sa.Integer, nullable=False),
sa.Column('average_note', sa.Float, nullable=False),
# sa.Column('pictures', postgresql.JSON, server_default='{}'),
sa.Column('published_at', sa.DateTime, nullable=False),
# sa.Column('tags', postgresql.ARRAY(sa.Integer), server_default='{}'),
sa.Column('status',
sa.Enum(*choices, name="enum_name", native_enum=False),
server_default="a", nullable=False),
sa.Column('visible', sa.Boolean, nullable=False),
# Indexes #
sa.PrimaryKeyConstraint('id', name='post_id_pkey'))
return post
import aiomysql.sa
import sqlalchemy as sa
from sqlalchemy.dialects import mysql
__all__ = ['question', 'choice']
meta = sa.MetaData()
question = sa.Table('question', meta,
sa.Column('id', sa.Integer, nullable=False),
sa.Column('question_text', sa.String(200), nullable=False),
sa.Column('pub_date', sa.Date, nullable=False))
choice = sa.Table(
'choice', meta, sa.Column('id', sa.Integer, nullable=False),
sa.Column('question_id', sa.Integer, nullable=False),
sa.Column('choice_text', sa.String(200), nullable=False),
sa.Column('votes', sa.Integer, server_default="0", nullable=False))
class RecordNotFound(Exception):
"""Requested record in database was not found"""
pass
async def init_db(app):
conf = app['config']['mysql']
engine = await aiomysql.sa.create_engine(db=conf['database'],
user=conf['user'],
password=conf['password'],
host=conf['host'],
commandline.standard_argparse_options(
ap, default_config='./config/main_config.yaml')
options = ap.parse_args(sys.argv[1:])
config = commandline.config_from_options(options, TRAFARET)
#__all__ = [table_name]
meta = sa.MetaData()
logs_table_name = 'smartsocket'
#{"DevEUI_uplink": {"Time": "2019-08-15T08:31:48.478+00:00","DevEUI": "001BC503500000F5","FPort": 2,"FCntUp": 17449,"ADRbit": 1,"MType": 2,"FCntDn": 1,"payload_hex": "2f0e10","mic_hex": "a962f3cd","Lrcid": "00000241","LrrRSSI": -103.000000,"LrrSNR": 2.000000,"SpFact": 12,"SubBand": "G1","Channel": "LC2","DevLrrCnt": 8,"Lrrid": "00000509","Late": 0,"LrrLAT": 55.752792,"LrrLON": 37.646118,"Lrrs": {"Lrr": [{"Lrrid": "00000509","Chain": 0,"LrrRSSI": -103.000000,"LrrSNR": 2.000000,"LrrESP": -105.124428},{"Lrrid": "000038F1","Chain": 0,"LrrRSSI": -98.000000,"LrrSNR": -2.000000,"LrrESP": -102.124428},{"Lrrid": "0000059C","Chain": 0,"LrrRSSI": -112.000000,"LrrSNR": -4.000000,"LrrESP": -117.455406}]},"CustomerID": "1100000033","CustomerData": {"alr":{"pro":"LORA/Generic","ver":"1"}},"ModelCfg": "0","InstantPER": 0.000000,"MeanPER": 0.000000,"DevAddr": "0440F634","TxPower": 16.000000,"NbTrans": 1}}
table_logs = sa.Table(
logs_table_name,
meta,
sa.Column('id', sa.BigInteger, primary_key=True), # default=1,
sa.Column('deveui', sa.String(20)),
sa.Column('status', sa.Boolean()),
sa.Column('payload_hex', sa.String(100)), # , nullable=False
#sa.Column('raw', sa.String(10000)),
sa.Column('raw', sa.Text),
sa.Column('created_at', sa.DateTime, index=True, default=datetime.utcnow))
async def save_json(cur, data):
rows = {}
rows["raw"] = str(data)
rows["created_at"] = datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')
try:
data = data["DevEUI_uplink"]
rows["deveui"] = d = data["DevEUI"]