def init_database(db: sa.engine.Connectable):
"""
Initializes the database to support the models
:param db: SQLAlchemy connectable instance
"""
# setup the Postgres extensions and schema
db.execute("""
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
""")
db.execute(';\n'.join('CREATE SCHEMA IF NOT EXISTS {}'.format(s)
for s in SCHEMAS.values()))
# create the schema from the models
METADATA.create_all(bind=db)
def store_ecocount_in_db(ecoregions: numpy.array, transform: rasterio.Affine,
engine: sqlalchemy.engine.Connectable,
t_eco: sqlalchemy.Table,
t_hex: sqlalchemy.Table) -> None:
for h, eco_count in hex_ecoregions(ecoregions, transform).items():
lat, lon = h3.h3_to_geo(h)
try:
engine.execute(
t_hex.insert({
"hexbin": h,
"longitude": lon,
"latitude": lat
}))
except sqlalchemy.exc.IntegrityError:
pass
for id, freq in eco_count.items():
try:
engine.execute(
t_eco.insert({
"hexbin": h,
"ecoregion": id,
"frequency": freq
}))
except sqlalchemy.exc.IntegrityError:
elsewhere = engine.execute(
sqlalchemy.select([
t_eco.c.frequency
]).where((t_eco.c.hexbin == h)
& (t_eco.c.ecoregion == id))).fetchone()[0]
engine.execute(
t_eco.update().where((t_eco.c.hexbin == h)
& (t_eco.c.ecoregion == id)).values(
{"frequency": elsewhere + freq}))
def find_all(engine: sa.engine.Connectable) -> list[History]:
"""すべてのメッセージを取得する"""
with engine.connect() as connection:
query = sa.sql.select((
history_table.c.id,
history_table.c.title,
history_table.c.link,
history_table.c.board,
))
return [History(**m) for m in connection.execute(query)]
def plot_locations(engine: sqlalchemy.engine.Connectable,
t_hex: sqlalchemy.Table) -> None:
d = sqlalchemy.select(
[t_hex.c.vlongitude, t_hex.c.vlatitude,
t_hex.c.habitable]).where(t_hex.c.vlatitude is not None)
x, y, h = zip(*engine.execute(d))
h = numpy.array(h, dtype=bool)
plt.scatter(x, y, marker='o', alpha=0.5, c=numpy.array(list("rg"))[1 * h])
er = ecoregion_tile_from_geocoordinates(-165, 60).read(1)
plt.imshow(TC[er], extent=(-180, -150, 50, 70))
def get_item_from_id(engine: sqla.engine.Connectable, id: int):
"""idを元に予定を取得する"""
q = sqla.sql.select((
schedules_table.c.id,
schedules_table.c.title,
schedules_table.c.body,
schedules_table.c.begin_at,
schedules_table.c.end_at,
schedules_table.c.created_at.label("createdAt"),
schedules_table.c.updated_at.label("updateAt"),
)).where(schedules_table.c.id == id)
return [Schedule(**m) for m in engine.connect().execute(q)][0]
def plot_sampled(engine: sqlalchemy.engine.Connectable,
t_hex: sqlalchemy.Table) -> None:
d = sqlalchemy.select([
t_hex.c.vlongitude, t_hex.c.vlatitude,
func.min(t_dist.c.distance), t_hex.c.habitable
]).where(t_hex.c.hexbin == t_dist.c.hexbin1).where(
t_dist.c.distance > 0).group_by(t_hex.c.hexbin)
x, y, s, h = zip(*engine.execute(d))
s = numpy.array(s)
h = numpy.array(h)
plt.scatter(x, y, marker='o', s=s / 3600, alpha=0.3, c='r')
er = ecoregion_tile_from_geocoordinates(-165, 60).read(1)
plt.imshow(-TC[er], extent=(-180, -150, 50, 70))
def delete_all(engine: sqla.engine.Connectable) -> None:
"""予定をすべて消す（テスト用）"""
with engine.connect() as connection:
connection.execute(schedules_table.delete())
def add_item(engine: sqla.engine.Connectable, schedule: Schedule):
"""新規アイテムをテーブルにインサートするよ"""
q = schedules_table.insert()
engine.connect().execute(q, schedule.dict())
def delete_all(engine: sa.engine.Connectable) -> None:
"""メッセージをすべて消す（テスト用）"""
with engine.connect() as connection:
connection.execute(history_table.delete())
def add(engine: sa.engine.Connectable, history: History) -> None:
"""メッセージを保存する"""
with engine.connect() as connection:
query = history_table.insert()
connection.execute(query, history.dict())
def iter_sql(
query: typing.Union[str, sqlalchemy.sql.expression.Selectable],
conn: sqlalchemy.engine.Connectable,
target_name: str = None,
) -> base.typing.Stream:
"""Iterates over the results from an SQL query.
By default, SQLAlchemy prefetches results. Therefore, even though you can iterate over the
resulting rows one by one, the results are in fact loaded in batch. You can modify this
behavior by configuring the connection you pass to `iter_sql`. For instance, you can set
the `stream_results` parameter to `True`, as [explained in SQLAlchemy's documentation](https://docs.sqlalchemy.org/en/13/core/connections.html#sqlalchemy.engine.Connection.execution_options). Note, however,
that this isn't available for all database engines.
Parameters
----------
query
SQL query to be executed.
conn
An SQLAlchemy construct which has an `execute` method. In other words you can pass an
engine, a connection, or a session.
target_name
The name of the target field. If this is `None`, then `y` will also be `None`.
Examples
--------
As an example we'll create an in-memory database with SQLAlchemy.
>>> import datetime as dt
>>> import sqlalchemy
>>> engine = sqlalchemy.create_engine('sqlite://')
>>> metadata = sqlalchemy.MetaData()
>>> t_sales = sqlalchemy.Table('sales', metadata,
... sqlalchemy.Column('shop', sqlalchemy.String, primary_key=True),
... sqlalchemy.Column('date', sqlalchemy.Date, primary_key=True),
... sqlalchemy.Column('amount', sqlalchemy.Integer)
... )
>>> metadata.create_all(engine)
>>> sales = [
... {'shop': 'Hema', 'date': dt.date(2016, 8, 2), 'amount': 20},
... {'shop': 'Ikea', 'date': dt.date(2016, 8, 2), 'amount': 18},
... {'shop': 'Hema', 'date': dt.date(2016, 8, 3), 'amount': 22},
... {'shop': 'Ikea', 'date': dt.date(2016, 8, 3), 'amount': 14},
... {'shop': 'Hema', 'date': dt.date(2016, 8, 4), 'amount': 12},
... {'shop': 'Ikea', 'date': dt.date(2016, 8, 4), 'amount': 16}
... ]
>>> with engine.connect() as conn:
... _ = conn.execute(t_sales.insert(), sales)
We can now query the database. We will set `amount` to be the target field.
>>> from river import stream
>>> with engine.connect() as conn:
... query = 'SELECT * FROM sales;'
... dataset = stream.iter_sql(query, conn, target_name='amount')
... for x, y in dataset:
... print(x, y)
{'shop': 'Hema', 'date': '2016-08-02'} 20
{'shop': 'Ikea', 'date': '2016-08-02'} 18
{'shop': 'Hema', 'date': '2016-08-03'} 22
{'shop': 'Ikea', 'date': '2016-08-03'} 14
{'shop': 'Hema', 'date': '2016-08-04'} 12
{'shop': 'Ikea', 'date': '2016-08-04'} 16
"""
result_proxy = conn.execute(query)
if target_name is None:
for row in result_proxy:
yield dict(row.items()), None
return
for row in result_proxy:
x = dict(row.items())
y = x.pop(target_name)
yield x, y
def set_sqlite_pragma(dbapi_connection: sqlalchemy.engine.Connectable,
connection_record: t.Any) -> None:
cursor = dbapi_connection.cursor()
cursor.execute("PRAGMA foreign_keys=ON")
cursor.close()
def plot_distances(engine: sqlalchemy.engine.Connectable,
dist: sqlalchemy.Table) -> None:
distances = sqlalchemy.select(
[dist.c.flat_distance, dist.c.distance, dist.c.source])
x, y, z = zip(*engine.execute(distances))
plt.scatter(x, y, marker='x', c=z, s=40, alpha=0.2)