def test_semantic_version_field(self) -> None:
id_colname = "id"
version_colname = "version"
id_col = Column(id_colname, Integer, primary_key=True)
version_col = Column(version_colname, SemanticVersionColType)
table = Table("testtable", self.meta, id_col, version_col)
table.create()
v1 = Version("1.1.0")
v2 = Version("2.0.1")
v3 = Version("14.0.0")
table.insert().values([
{
id_colname: 1,
version_colname: v1
},
{
id_colname: 2,
version_colname: v2
},
{
id_colname: 3,
version_colname: v3
},
]).execute()
select_fields = [id_col, version_col]
rows = list(
select(select_fields).select_from(table).order_by(
id_col).execute())
self._assert_version_equal(rows[0][version_col], v1)
self._assert_version_equal(rows[1][version_col], v2)
self._assert_version_equal(rows[2][version_col], v3)
def test_iso_duration_field(self) -> None:
id_colname = "id"
duration_colname = "duration_iso"
id_col = Column(id_colname, Integer, primary_key=True)
duration_col = Column(duration_colname,
PendulumDurationAsIsoTextColType)
table = Table("testtable", self.meta, id_col, duration_col)
table.create()
d1 = Duration(years=1, months=3, seconds=3, microseconds=4)
d2 = Duration(seconds=987.654321)
d3 = Duration(days=-5)
table.insert().values([
{
id_colname: 1,
duration_colname: d1
},
{
id_colname: 2,
duration_colname: d2
},
{
id_colname: 3,
duration_colname: d3
},
]).execute()
select_fields = [id_col, duration_col]
rows = list(
select(select_fields).select_from(table).order_by(
id_col).execute())
self._assert_duration_equal(rows[0][duration_col], d1)
self._assert_duration_equal(rows[1][duration_col], d2)
self._assert_duration_equal(rows[2][duration_col], d3)
def insert_sample_email(self, sample_table_name, email):
if not isinstance(email, Email):
print 'ERROR: input must be of type Email'
return
if sample_table_name == self.table_name:
print('Cannot use the same table name')
return
email_table = Table(sample_table_name, self.metadata)
ins_stmt = email_table.insert()
conn = self.engine.connect()
conn.execute(ins_stmt, date=email.date,
mime_type=email.mime_type,
from_addr=email.from_addr,
to_addr=email.to_addr,
subject=email.subject,
raw_body=email.raw_body,
cleaned_body=email.cleaned_body,
one_line=email.one_line,
path=email.path,
label=email.label,
prediction=email.prediction,
probability=email.probability,
)
def save_data_to_database(self, df: pd.DataFrame, engine: Engine):
if not AbstractPreprocessor._check_write_privilege(engine):
AbstractPreprocessor.create_dir(self.output_dir, os.getlogin())
path = Path.joinpath(self.output_dir, os.getlogin(),
datetime.now().isoformat() + '.json')
with path.open("a") as f:
df.to_json(f)
return
for idx, row in df.iterrows():
with engine.connect() as conn:
t = Table('judgment_map', MetaData(), autoload_with=engine)
if row['judgments']: # only insert, when we find judgments
# Delete and reinsert as no upsert command is available
stmt = t.delete().where(delete_stmt_decisions_with_df(df))
conn.execute(stmt)
for k in row['judgments']:
judgment_type_id = Judgment(k).value
stmt = t.insert().values([{
"decision_id":
str(row['decision_id']),
"judgment_id":
judgment_type_id
}])
conn.execute(stmt)
else:
self.logger.warning(
f"No judgments found for {row['html_url']}")
def save_data_to_database(self, df: pd.DataFrame, engine: Engine):
with engine.connect() as conn:
t = Table('lower_court', MetaData(), autoload_with=conn)
# Delete and reinsert as no upsert command is available
stmt = t.delete().where(delete_stmt_decisions_with_df(df))
conn.execute(stmt)
for _, row in df.iterrows():
if not 'lower_court' in row or row['lower_court'] is None:
continue
lower_court = row["lower_court"]
res = {}
if 'court' in lower_court and lower_court['court'] is not None:
res['court_id'] = list(
self.select(
engine, 'court', 'court_id',
f"court_string = '{lower_court['court']}'")
)[0]['court_id'][0]
res['court_id'] = int(
res['court_id']
) if res['court_id'] is not None else None
if 'canton' in lower_court and lower_court[
'canton'] is not None:
res['canton_id'] = list(
self.select(engine, 'canton', 'canton_id',
f"short_code = '{lower_court['canton']}'")
)[0]['canton_id'][0]
res['canton_id'] = int(
res['canton_id']
) if res['canton_id'] is not None else None
if 'chamber' in lower_court and lower_court[
'chamber'] is not None:
res['chamber_id'] = list(
self.select(
engine, 'chamber', 'chamber_id',
f"chamber_string = '{lower_court['chamber']}'")
)[0]['chamber_id'][0]
res['chamber_id'] = int(
res['chamber_id']
) if res['chamber_id'] is not None else None
stmt = t.insert().values([{
"decision_id":
str(row['decision_id']),
"court_id":
res.get('court_id'),
"canton_id":
res.get('canton_id'),
"chamber_id":
res.get('chamber_id'),
"date":
lower_court.get('date'),
"file_number":
lower_court.get('file_number')
}])
conn.execute(stmt)
def insert_address(self, email_address):
if type(email) != EmailAddress:
print 'ERROR: input must be of type EmailAddress'
return
email_address_table = Table('email_address', self.metadata)
ins_stmt = email_address_table.insert()
conn = self.engine.connect()
result = conn.execute(ins_stmt, address=email_address.address,
name=email_address.name)
async def get_or_create(
query_clause: str, query_values: dict, table: Table, insert_values: dict
):
"""取得或建立"""
result = await database.fetch_one(query=query_clause, values=query_values)
if result:
# get
return result
query = table.insert()
return await database.execute(query=query, values=insert_values)
def test_phone_number_field(self) -> None:
id_colname = "id"
phone_number_colname = "phone_number"
id_col = Column(id_colname, Integer, primary_key=True)
phone_number_col = Column(phone_number_colname, PhoneNumberColType)
table = Table("testtable", self.meta, id_col, phone_number_col)
table.create()
# https://en.wikipedia.org/wiki/Fictitious_telephone_number
p1 = phonenumbers.parse("+44 (0)113 496 0123")
p2 = phonenumbers.parse("+33 1 99 00 12 34 56")
p3 = phonenumbers.parse("07700 900123", "GB")
p4 = None
table.insert().values([
{
id_colname: 1,
phone_number_colname: p1
},
{
id_colname: 2,
phone_number_colname: p2
},
{
id_colname: 3,
phone_number_colname: p3
},
{
id_colname: 4,
phone_number_colname: p4
},
]).execute()
select_fields = [id_col, phone_number_col]
rows = list(
select(select_fields).select_from(table).order_by(
id_col).execute())
self.assertEqual(rows[0][phone_number_col], p1)
self.assertEqual(rows[1][phone_number_col], p2)
self.assertEqual(rows[2][phone_number_col], p3)
self.assertIsNone(rows[3][phone_number_col])
def insert_to_table(self, email, table_name):
if not isinstance(email, Email):
print 'ERROR: input must be of type Email'
return
email_table = Table(table_name, self.metadata)
ins_stmt = email_table.insert()
conn = self.engine.connect()
result = conn.execute(ins_stmt, date=email.date,
mime_type=email.mime_type,
from_addr=email.from_addr,
to_addr=email.to_addr,
subject=email.subject,
body=email.body,
path=email.path,
label=email.label)
def test_vector_greater_than():
metadata = MetaData()
table = Table('people', metadata,
Column('id', Integer, primary_key=True),
Column('first_name', String),
Column('middle_name', String),
Column('last_name', String),
Column('blood_type', String))
def random_person(idx):
first = names.get_first_name()
last = names.get_last_name()
middle = random.choice([names.get_first_name, names.get_last_name, lambda: None])()
blood_type = random.choice(['A', 'A', 'B', 'B', 'O', 'O', 'O', 'O', 'AB'])
return {'id': idx,
'first_name': first,
'middle_name': middle,
'last_name': last,
'blood_type': blood_type
}
engine = create_engine('sqlite:///:memory:', echo=False)
metadata.create_all(engine)
def compare_results(compa, cols, vals):
vals = tuple(map(none_to_minus_inf, vals))
res = set([row['id'] for row in engine.execute(select(table.columns).where(compa))])
all_ = [valmap(none_to_minus_inf, row) for row in engine.execute(select(table.columns))]
cor = set()
for row in all_:
if tuple(row[col.name] for col in cols) > vals:
cor.add(row['id'])
assert_equal(res, cor)
for i in range(1000):
engine.execute(table.insert(random_person(i)))
col_tuples = [(table.columns['id'],),
(table.columns['blood_type'], table.columns['id']),
(table.columns['blood_type'], table.columns['middle_name'], table.columns['id']),
(table.columns['blood_type'], table.columns['id'], table.columns['middle_name']),
(table.columns['middle_name'], table.columns['blood_type'], table.columns['id']),]
val_tuples = [(5,),
('AB', 500),
('B', None, 500),
('B', 500, None),
(None, 'B', 500)]
for cols, vals in zip(col_tuples, val_tuples):
compare_results(vector_greater_than(cols, vals), cols, vals)
def dump_orm_object_as_insert_sql(engine: Engine, obj: object,
fileobj: TextIO) -> None:
"""
Takes a SQLAlchemy ORM object, and writes ``INSERT`` SQL to replicate it
to the output file-like object.
Args:
engine: SQLAlchemy :class:`Engine`
obj: SQLAlchemy ORM object to write
fileobj: file-like object to write to
"""
# literal_query = make_literal_query_fn(engine.dialect)
insp = inspect(obj)
# insp: an InstanceState
# http://docs.sqlalchemy.org/en/latest/orm/internals.html#sqlalchemy.orm.state.InstanceState # noqa
# insp.mapper: a Mapper
# http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper # noqa
# Don't do this:
# table = insp.mapper.mapped_table
# Do this instead. The method above gives you fancy data types like list
# and Arrow on the Python side. We want the bog-standard datatypes drawn
# from the database itself.
meta = MetaData(bind=engine)
table_name = insp.mapper.mapped_table.name
# log.debug("table_name: {}", table_name)
table = Table(table_name, meta, autoload=True)
# log.debug("table: {}", table)
# NewRecord = quick_mapper(table)
# columns = table.columns.keys()
query = select(table.columns)
# log.debug("query: {}", query)
for orm_pkcol in insp.mapper.primary_key:
core_pkcol = table.columns.get(orm_pkcol.name)
pkval = getattr(obj, orm_pkcol.name)
query = query.where(core_pkcol == pkval)
# log.debug("query: {}", query)
cursor = engine.execute(query)
row = cursor.fetchone() # should only be one...
row_dict = dict(row)
# log.debug("obj: {}", obj)
# log.debug("row_dict: {}", row_dict)
statement = table.insert(values=row_dict)
# insert_str = literal_query(statement)
insert_str = get_literal_query(statement, bind=engine)
writeline_nl(fileobj, insert_str)
def insert_cleaned_email_full(self, email):
if not isinstance(email, Email):
print 'ERROR: input must be of type Email'
return
email_table = Table("email_full", self.metadata)
ins_stmt = email_table.insert()
conn = self.engine.connect()
conn.execute(ins_stmt, date=email.date,
mime_type=email.mime_type,
from_addr=email.from_addr,
to_addr=email.to_addr,
subject=email.subject,
raw_body=email.raw_body,
body=email.body,
all_lines=email.all_lines,
one_line=email.one_line,
path=email.path)
def save_data_to_database(self, df: pd.DataFrame, engine: Engine):
for idx, row in df.iterrows():
with engine.connect() as conn:
t = Table('citation', MetaData(), autoload_with=engine)
# Delete and reinsert as no upsert command is available
stmt = t.delete().where(delete_stmt_decisions_with_df(df))
engine.execute(stmt)
for k in row['citations'].keys():
citation_type_id = CitationType(k).value
for citation in row['citations'][k]:
stmt = t.insert().values([{
"decision_id":
str(row['decision_id']),
"citation_type_id":
citation_type_id,
"url":
citation.get("url"),
"text":
citation["text"]
}])
engine.execute(stmt)
def save_data_to_database(self, df: pd.DataFrame, engine: Engine):
with engine.connect() as conn:
t_person = Table('person', MetaData(), autoload_with=conn)
t_jud_person = Table('judicial_person',
MetaData(),
autoload_with=conn)
# Delete and reinsert as no upsert command is available
stmt = t_jud_person.delete().where(
delete_stmt_decisions_with_df(df))
conn.execute(stmt)
for _, row in df.iterrows():
if not 'court_composition' in row or row[
'court_composition'] is None:
continue
court_composition: CourtComposition = row['court_composition']
president = court_composition.president
if president:
# create president person
president_dict = {
"name":
president.name.strip(),
"is_natural_person":
True,
"gender":
president.gender.value[0] if president.gender else None
}
stmt = t_person.insert().returning(
text("person_id")).values([president_dict])
person_id = conn.execute(stmt).fetchone()['person_id']
person_dict = {
"decision_id": str(row['decision_id']),
"person_id": person_id,
"judicial_person_type_id": 1,
"is_president": True
}
stmt = t_jud_person.insert().values([person_dict])
conn.execute(stmt)
# create all judges
for judge in court_composition.judges:
if judge == president: # President is already created above
continue
judge_dict = {
"name": judge.name.strip(),
"is_natural_person": True,
"gender":
judge.gender.value[0] if judge.gender else None
}
stmt = t_person.insert().returning(
text("person_id")).values([judge_dict])
person_id = conn.execute(stmt).fetchone()['person_id']
person_dict = {
"decision_id": str(row['decision_id']),
"person_id": person_id,
"judicial_person_type_id": 1,
"is_president": False
}
stmt = t_jud_person.insert().values([person_dict])
conn.execute(stmt)
# create all clerks
for clerk in court_composition.clerks:
clerk_dict = {
"name": clerk.name.strip(),
"is_natural_person": True,
"gender":
clerk.gender.value[0] if clerk.gender else None
}
stmt = t_person.insert().returning(
text("person_id")).values([clerk_dict])
person_id = conn.execute(stmt).fetchone()['person_id']
person_dict = {
"decision_id": str(row['decision_id']),
"person_id": person_id,
"judicial_person_type_id": 2,
"is_president": False
}
stmt = t_jud_person.insert().values([person_dict])
conn.execute(stmt)
where = f"WHERE NOT EXISTS (SELECT 1 FROM judicial_person b WHERE a.person_id = b.person_id) " \
f"AND NOT EXISTS (SELECT 1 FROM party c WHERE a.person_id = c.person_id)"
stmt = t_person.delete().where(text(where))
def save_data_to_database(self, df: pd.DataFrame, engine: Engine):
if not AbstractPreprocessor._check_write_privilege(engine):
AbstractPreprocessor.create_dir(self.output_dir, os.getlogin())
path = Path.joinpath(self.output_dir, os.getlogin(),
datetime.now().isoformat() + '.json')
with path.open("a") as f:
df.to_json(f)
return
df = df.loc[df['language'] != '--']
if df.empty:
return
df = self.run_tokenizer(df)
with self.get_engine(self.db_scrc).connect() as conn:
# Load the different tables
t = Table('section', MetaData(), autoload_with=engine)
t_paragraph = Table('paragraph', MetaData(), autoload_with=engine)
t_num_tokens = Table('num_tokens',
MetaData(),
autoload_with=engine)
# Delete and reinsert as no upsert command is available. This pattern is used multiple times in this method
if not isinstance(df, pd.DataFrame) or df.empty:
# empty dfs are given as dicts, so no need to save
return
stmt = t.delete().where(delete_stmt_decisions_with_df(df))
conn.execute(stmt)
stmt = t_paragraph.delete().where(
delete_stmt_decisions_with_df(df))
conn.execute(stmt)
for idx, row in df.iterrows():
if idx % 50 == 0:
self.logger.info(f'Saving decision {idx + 1} from chunk')
if row['sections'] is None or row['sections'].keys is None:
continue
for k in row['sections'].keys():
decision_id_str = str(row['decision_id'])
if decision_id_str == '':
continue
section_type_id = k.value
# insert section
section_dict = {
"decision_id": decision_id_str,
"section_type_id": section_type_id,
"section_text": row['sections'][k]
}
stmt = t.insert().returning(text("section_id")).values(
[section_dict])
section_id = conn.execute(stmt).fetchone()['section_id']
# Add num tokens
tokens_per_section = {
'section_id': str(section_id),
'num_tokens_spacy': row[k.name + '_spacy'],
'num_tokens_bert': row[k.name + '_bert']
}
stmt = t_num_tokens.insert().values([tokens_per_section])
conn.execute(stmt)
# Add all paragraphs
paragraph_dicts = []
for paragraph in row['sections'][k]:
paragraph = paragraph.strip()
if len(paragraph) == 0: continue
paragraph_dict = {
'section_id': str(section_id),
"decision_id": decision_id_str,
'paragraph_text': paragraph,
'first_level': None,
'second_level': None,
'third_level': None
}
paragraph_dicts.append(paragraph_dict)
if len(paragraph_dicts) > 0:
stmt = t_paragraph.insert().values(paragraph_dicts)
conn.execute(stmt)
def dump_table_as_insert_sql(engine: Engine,
table_name: str,
fileobj: TextIO,
wheredict: Dict[str, Any] = None,
include_ddl: bool = False,
multirow: bool = False) -> None:
"""
Reads a table from the database, and writes SQL to replicate the table's
data to the output ``fileobj``.
Args:
engine: SQLAlchemy :class:`Engine`
table_name: name of the table
fileobj: file-like object to write to
wheredict: optional dictionary of ``{column_name: value}`` to use as
``WHERE`` filters
include_ddl: if ``True``, include the DDL to create the table as well
multirow: write multi-row ``INSERT`` statements
"""
# http://stackoverflow.com/questions/5631078/sqlalchemy-print-the-actual-query # noqa
# http://docs.sqlalchemy.org/en/latest/faq/sqlexpressions.html
# http://www.tylerlesmann.com/2009/apr/27/copying-databases-across-platforms-sqlalchemy/ # noqa
# https://github.com/plq/scripts/blob/master/pg_dump.py
log.info("dump_data_as_insert_sql: table_name={}", table_name)
writelines_nl(fileobj, [
SEP1,
sql_comment(f"Data for table: {table_name}"),
SEP2,
sql_comment(f"Filters: {wheredict}"),
])
dialect = engine.dialect
if not dialect.supports_multivalues_insert:
multirow = False
if multirow:
log.warning("dump_data_as_insert_sql: multirow parameter substitution "
"not working yet")
multirow = False
# literal_query = make_literal_query_fn(dialect)
meta = MetaData(bind=engine)
log.debug("... retrieving schema")
table = Table(table_name, meta, autoload=True)
if include_ddl:
log.debug("... producing DDL")
dump_ddl(table.metadata,
dialect_name=engine.dialect.name,
fileobj=fileobj)
# NewRecord = quick_mapper(table)
# columns = table.columns.keys()
log.debug("... fetching records")
# log.debug("meta: {}", meta) # obscures password
# log.debug("table: {}", table)
# log.debug("table.columns: {!r}", table.columns)
# log.debug("multirow: {}", multirow)
query = select(table.columns)
if wheredict:
for k, v in wheredict.items():
col = table.columns.get(k)
query = query.where(col == v)
# log.debug("query: {}", query)
cursor = engine.execute(query)
if multirow:
row_dict_list = []
for r in cursor:
row_dict_list.append(dict(r))
# log.debug("row_dict_list: {}", row_dict_list)
if row_dict_list:
statement = table.insert().values(row_dict_list)
# log.debug("statement: {!r}", statement)
# insert_str = literal_query(statement)
insert_str = get_literal_query(statement, bind=engine)
# NOT WORKING FOR MULTIROW INSERTS. ONLY SUBSTITUTES FIRST ROW.
writeline_nl(fileobj, insert_str)
else:
writeline_nl(fileobj, sql_comment("No data!"))
else:
found_one = False
for r in cursor:
found_one = True
row_dict = dict(r)
statement = table.insert(values=row_dict)
# insert_str = literal_query(statement)
insert_str = get_literal_query(statement, bind=engine)
# log.debug("row_dict: {}", row_dict)
# log.debug("insert_str: {}", insert_str)
writeline_nl(fileobj, insert_str)
if not found_one:
writeline_nl(fileobj, sql_comment("No data!"))
writeline_nl(fileobj, SEP2)
log.debug("... done")
class SqlDatabaseService(ApplicationSession):
"""An application component for logging telemetry data to sql databases"""
def __init__(self, config):
ApplicationSession.__init__(self, config)
self.count = 0
self.engine = None
metadata = MetaData()
self.telemetry = Table("telemetry", metadata,
Column("id", Integer(), primary_key=True),
Column("MSG_ID", Integer()),
Column("V_FC", Integer()),
Column("V_CAP", Integer()),
Column("A_ENG", Integer()),
Column("A_CAP", Integer()),
Column("T_O2_In", Integer()),
Column("T_O2_Out", Integer()),
Column("T_FC_H2O_Out", Integer()),
Column("Water_In", Integer()),
Column("Water_Out", Integer()),
Column("Master_SW", Integer()),
Column("CAP_Down_SW", Integer()),
Column("Drive_SW", Integer()),
Column("FC_state", Integer()),
Column("Mosfet_state", Integer()),
Column("Safety_state", Integer()),
Column("Air_Pump_load", Numeric()),
Column("Mosfet_load", Integer()),
Column("Water_Pump_load", Integer()),
Column("Fan_load", Integer()),
Column("Acc_X", Integer()),
Column("Acc_Y", Integer()),
Column("Acc_Z", Integer()),
Column("AUX", Numeric()),
Column("GPS_X", Integer()),
Column("GPS_Y", Integer()),
Column("GPS_Z", Integer()),
Column("GPS_Speed", Integer()),
Column("V_Safety", Integer()),
Column("H2_Level", Integer()),
Column("O2_calc", Numeric()),
Column("lat", Numeric()),
Column("lng", Numeric()),
)
# metadata = MetaData()
# self.telemetry = Table("telemetry", metadata,
# Column("id", Integer(), primary_key=True),
# Column("mma_x", Integer()),
# Column("mma_y", Integer()),
# Column("temp", Numeric()),
# Column("lat", Numeric()),
# Column("lng", Numeric()),
# )
#@inlineCallbacks
def onJoin(self, details):
print("Realm joined (WAMP session started).")
# subscribe to telemetry data channel
self.subscribe(self.receive, u'de.elmyra.kotori.telemetry.data')
self.startDatabase()
#self.leave()
#@inlineCallbacks
def startDatabase(self):
self.engine = create_engine(
# sqlite in-memory
#"sqlite://", reactor=reactor, strategy=TWISTED_STRATEGY
# sqlite on filesystem
"sqlite:////tmp/kotori.sqlite", reactor=reactor, strategy=TWISTED_STRATEGY
# mysql... todo
)
# Create the table
yield self.engine.execute(CreateTable(self.telemetry))
#yield self.engine
def onLeave(self, details):
print("Realm left (WAMP session ended).")
ApplicationSession.onLeave(self, details)
def onDisconnect(self):
print("Transport disconnected.")
#reactor.stop()
#@inlineCallbacks
def receive(self, data):
#print "RECEIVE:", data
# decode wire data
payload = data.split(';')
MSG_ID = int(payload[0])
V_FC = int(payload[1])
V_CAP = int(payload[2])
A_ENG = int(payload[3])
A_CAP = int(payload[4])
T_O2_In = int(payload[5])
T_O2_Out = int(payload[6])
T_FC_H2O_Out = int(payload[7])
Water_In = int(payload[8])
Water_Out = int(payload[9])
Master_SW = int(payload[10])
CAP_Down_SW = int(payload[11])
Drive_SW = int(payload[12])
FC_state = int(payload[13])
Mosfet_state = int(payload[14])
Safety_state = int(payload[15])
Air_Pump_load = float(payload[16])
Mosfet_load = int(payload[17])
Water_Pump_load = int(payload[18])
Fan_load = int(payload[19])
Acc_X = int(payload[20])
Acc_Y = int(payload[21])
Acc_Z = int(payload[22])
AUX = float(payload[23])
GPS_X = int(payload[24])
GPS_Y = int(payload[25])
GPS_Z = int(payload[26])
GPS_Speed = int(payload[27])
V_Safety = int(payload[28])
H2_Level = int(payload[29])
O2_calc = float(payload[30])
lat = float(payload[31])
lng = float(payload[32])
# mma_x = int(payload[0])
# mma_y = int(payload[1])
# temp = float(payload[2])
# try:
# lat = float(payload[3])
# lng = float(payload[4])
# except:
# lat = 0
# lng = 0
# store data to database
if self.engine:
yield self.engine.execute(self.telemetry.insert().values(MSG_ID = MSG_ID, V_FC = V_FC, V_CAP = V_CAP, A_ENG = A_ENG, A_CAP = A_CAP, T_O2_In = T_O2_In, T_O2_Out = T_O2_Out, T_FC_H2O_Out = T_FC_H2O_Out, Water_In = Water_In, Water_Out = Water_Out, Master_SW = Master_SW, CAP_Down_SW = CAP_Down_SW, Drive_SW = Drive_SW, FC_state = FC_state, Mosfet_state = Mosfet_state, Safety_state = Safety_state, Air_Pump_load = Air_Pump_load, Mosfet_load = Mosfet_load, Water_Pump_load = Water_Pump_load, Fan_load = Fan_load, Acc_X = Acc_X, Acc_Y = Acc_Y, Acc_Z = Acc_Z, AUX = AUX, GPS_X = GPS_X, GPS_Y = GPS_Y, GPS_Z = GPS_Z, GPS_Speed = GPS_Speed, V_Safety = V_Safety, H2_Level = H2_Level, lat = lat, lng = lng))
def save_data_to_database(self, df: pd.DataFrame, engine: Engine):
"""
'plaintiff': 1,
'defendant': 2,
'representation_plaintiff': 3,
'representation_defendant': 4
"""
for idx, row in df.iterrows():
with engine.connect() as conn:
t_person = Table('person', MetaData(), autoload_with=conn)
t_party = Table('party', MetaData(), autoload_with=conn)
# Delete person
# Delete and reinsert as no upsert command is available
stmt = t_party.delete().where(delete_stmt_decisions_with_df(df))
conn.execute(stmt)
parties = json.loads(row['parties'])
# create all plaintiffs
offset = 0
for plaintiff in parties.get('plaintiffs'):
# Insert person into the person table
plaintiff_dict = {"name": plaintiff['name'].strip(),
"is_natural_person": plaintiff['legal_type'] == 'natural person',
"gender": plaintiff['gender']}
stmt = t_person.insert().returning(column("person_id")).values([plaintiff_dict])
person_id = conn.execute(stmt).fetchone()['person_id']
# Then insert the person into the party table
stmt = t_party.insert().values(
[{"decision_id": str(row['decision_id']), "person_id": person_id, "party_type_id": 1 + offset}])
conn.execute(stmt)
for representant in plaintiff.get('legal_counsel'):
# Insert their representation into the person and party tables
representant_dict = {"name": representant['name'].strip(),
"is_natural_person": representant['legal_type'] == 'natural person',
"gender": representant['gender']}
stmt = t_person.insert().returning(text("person_id")).values([representant_dict])
person_id = conn.execute(stmt).fetchone()['person_id']
stmt = t_party.insert().values([{"decision_id": str(row['decision_id']), "person_id": person_id,
"party_type_id": 3 + offset}])
conn.execute(stmt)
# create all defendants
offset = 1
for defendant in parties.get('defendants'):
# Insert person into the person table
defendant_dict = {"name": plaintiff['name'].strip(),
"is_natural_person": plaintiff['legal_type'] == 'natural person',
"gender": plaintiff['gender']}
stmt = t_person.insert().returning(text("person_id")).values([defendant_dict])
person_id = conn.execute(stmt).fetchone()['person_id']
# Then insert the person into the party table
stmt = t_party.insert().values(
[{"decision_id": str(row['decision_id']), "person_id": person_id, "party_type_id": 1 + offset}])
conn.execute(stmt)
for representant in defendant.get('legal_counsel'):
# Insert their representation into the person and party tables
representant_dict = {"name": representant['name'].strip(),
"is_natural_person": representant['legal_type'] == 'natural person',
"gender": representant['gender']}
stmt = t_person.insert().returning(text("person_id")).values([representant_dict])
person_id = conn.execute(stmt).fetchone()['person_id']
stmt = t_party.insert().values([{"decision_id": str(row['decision_id']), "person_id": person_id,
"party_type_id": 3 + offset}])
conn.execute(stmt)
with engine.connect() as conn:
stmt = t_person.delete().where(text(
f"NOT EXISTS (SELECT FROM judicial_person jp WHERE jp.person_id = person.person_id UNION SELECT FROM party WHERE party.person_id = person.person_id)"))
conn.execute(stmt)
teacher = Table("teacher",meta_data,atuo_load= True)
data = [
{
"name":"teacher_1",
"age":random.randint(20,40),
},
{
"name":"teacher_2",
"age":random.randint(20,40),
"sex":1
}
]
connect.execute(teacher.insert(),data)
## 查询的方式
results = session.query(Student).filter(Student.age == 12).filter(Student.sex == 1).all()
for result in results:
print(result.age,"-------",result.name)
## 更新
student = session.query(Student).filter(Student.id ==3 )
if student is not None :
student.name = "July"
session.commit()
student = session.query(Student).filter(Student.id == 4)
def populate(target: Table, connection: Connection, **kwargs):
connection.execute(target.insert(), *rows)
def test_iso_datetime_field(self) -> None:
id_colname = "id"
dt_local_colname = "dt_local"
dt_utc_colname = "dt_utc"
iso_colname = "iso"
id_col = Column(id_colname, Integer, primary_key=True)
dt_local_col = Column(dt_local_colname, DateTime)
dt_utc_col = Column(dt_utc_colname, DateTime)
iso_col = Column(iso_colname, PendulumDateTimeAsIsoTextColType)
table = Table("testtable", self.meta, id_col, dt_local_col, dt_utc_col,
iso_col)
table.create()
now = Pendulum.now()
now_utc = now.in_tz(pendulum.UTC)
yesterday = now.subtract(days=1)
yesterday_utc = yesterday.in_tz(pendulum.UTC)
table.insert().values([
{
id_colname: 1,
dt_local_colname: now,
dt_utc_colname: now_utc,
iso_colname: now,
},
{
id_colname: 2,
dt_local_colname: yesterday,
dt_utc_colname: yesterday_utc,
iso_colname: yesterday,
},
]).execute()
select_fields = [
id_col,
dt_local_col,
dt_utc_col,
iso_col,
func.length(dt_local_col).label("len_dt_local_col"),
func.length(dt_utc_col).label("len_dt_utc_col"),
func.length(iso_col).label("len_iso_col"),
isotzdatetime_to_utcdatetime(iso_col).label("iso_to_utcdt"),
unknown_field_to_utcdatetime(dt_utc_col).label(
"uk_utcdt_to_utcdt"),
unknown_field_to_utcdatetime(iso_col).label("uk_iso_to_utc_dt"),
]
rows = list(
select(select_fields).select_from(table).order_by(
id_col).execute())
self._assert_dt_equal(rows[0][dt_local_col], now)
self._assert_dt_equal(rows[0][dt_utc_col], now_utc)
self._assert_dt_equal(rows[0][iso_colname], now)
self._assert_dt_equal(rows[0]["iso_to_utcdt"], now_utc)
self._assert_dt_equal(rows[0]["uk_utcdt_to_utcdt"], now_utc)
self._assert_dt_equal(rows[0]["uk_iso_to_utc_dt"], now_utc)
self._assert_dt_equal(rows[1][dt_local_col], yesterday)
self._assert_dt_equal(rows[1][dt_utc_col], yesterday_utc)
self._assert_dt_equal(rows[1][iso_colname], yesterday)
self._assert_dt_equal(rows[1]["iso_to_utcdt"], yesterday_utc)
self._assert_dt_equal(rows[1]["uk_utcdt_to_utcdt"], yesterday_utc)
self._assert_dt_equal(rows[1]["uk_iso_to_utc_dt"], yesterday_utc)
