def __init__(self, bot):
self.bot = bot
# View追加 TODO: テーブル、View作成は別ファイルに分離したい。
if not engine.dialect.has_table(engine, 'reaction_count'):
view = Table('reaction_count', MetaData())
definition = text("SELECT emoji_id, count(emoji_id) as count FROM message_reaction GROUP BY emoji_id")
create_view = CreateView(view, definition)
print(str(create_view.compile()).strip())
engine.execute(create_view)
def createview():
view = db.Table('v_stockown', db.MetaData(db.engine), schema='public')
definition = text(
"SELECT * FROM (SELECT trx.uid, sum(trx.quantity * trx.transtypeid) AS quantityheld, stocks.symbol, stocks.name FROM transactions trx LEFT JOIN stocks stocks ON trx.stockid = stocks.stockid GROUP BY trx.uid, trx.stockid, stocks.symbol, stocks.name) AS prev WHERE prev.quantityheld>0"
)
create_view = CreateView(view, definition, or_replace=True)
db.engine.execute(create_view)
def test_view_with_options():
expected_result = "CREATE VIEW myview WITH (check_option=local) AS SELECT t1.col1, t1.col2 FROM t1"
t1 = Table('t1', sa.MetaData(),
sa.Column('col1', sa.Integer(), primary_key=True),
sa.Column('col2', sa.Integer()))
selectable = sa.sql.select([t1])
view = Table('myview', sa.MetaData())
create_view = CreateView(view,
selectable,
options=dict(check_option='local'))
actual = compile_query(create_view)
assert clean(expected_result) == clean(actual)
def main(argv):
engine = create_engine(db_url)
engine.echo = True
Base.metadata.bind = engine
Base.metadata.create_all()
from_csv = FromCSV(Base, DBSession)
from_csv.restore(realpath('kategori.csv'), Kategori)
from_csv.restore(realpath('jenis.csv'), Jenis)
from_csv.restore(realpath('conf.csv'), Conf)
from_csv.restore(realpath('iso_method.csv'), Method)
from_csv.restore(realpath('bank.csv'), Bank)
for v, view_name in definition_views:
view = Table(view_name, Base.metadata)
definition = text(v)
create_view = CreateView(view, definition, or_replace=True)
engine.execute(create_view)
def _do_views(self, target_metadata, views):
for v in views:
trg_view = self._to_target_table(target_metadata, v)
if not trg_view.exists():
view_definition = inspect(self.src_engine) \
.get_view_definition(v.name)
select_index = view_definition.lower().index('select')
view_definition = view_definition[select_index:]
stmt = CreateView(trg_view, text(view_definition))
try:
self._run_target_transaction(stmt,)
except Exception as e:
self.log.exception(e, scheme=self.scheme, db=self.trg_db)
else:
self.log.view_created(
v.name, scheme=self.scheme, db=self.trg_db)
def create_view(view: str, definition_string: str, engine) -> CreateView:
"""
creates a bew view for us, based on the given definition
:param view:
:param definition_string:
:return:
"""
with Session(engine) as session:
session.begin()
definition = text(definition_string)
creation = CreateView(Table(view, MetaData()), definition, or_replace=True)
session.execute(creation)
session.commit()
return Table(view, MetaData(), autoload=True, autoload_with=engine)
def create_or_drop_view(self, view: Table, view_query: Any) -> DDLElement:
if self.action == VaultAction.CREATE:
return CreateView(view, view_query)
elif self.action == VaultAction.DROP:
return DropView(view)
from sqlalchemy.schema import ForeignKey
from sqlalchemy.sql import text
from sqlalchemy_views import CreateView
from app import base, db
metadata = MetaData()
search_trip_view = Table('search_trip', metadata)
definition = text(
'''SELECT t.trip_id, r.origin, r.destination, r.distance, t.price, a.name aircraft FROM routes r, trips t, aircrafts a
WHERE t.trip_id IN
(SELECT DISTINCT ON (t.route) t.trip_id FROM trips t ORDER BY t.route, t.price)
AND t.route = r.route_id
AND t.aircraft = a.aircraft_id''')
search_trip_view = CreateView(search_trip_view, definition, or_replace=True)
db.execute(search_trip_view)
class SearchTripView(base):
__table__ = Table('search_trip',
base.metadata,
Column('trip_id', Integer, primary_key=True),
Column('origin', String(3), ForeignKey("airports.iata")),
Column('destination', String(3),
ForeignKey("airports.iata")),
Column('distance', DOUBLE_PRECISION),
Column('price', DECIMAL(5, 2)),
Column('aircraft', String(15)),
autoload=True,
autoload_with=db)
app.config['MAIL_SERVER'] = 'smtp.googlemail.com'
app.config['MAIL_PORT'] = 587
app.config['MAIL_USE_TLS'] = True
app.config['MAIL_USERNAME'] = os.environ.get('EMAIL_USER')
app.config['MAIL_PASSWORD'] = os.environ.get('EMAIL_PASS')
#app.config['MAIL_USERNAME'] = ""
#app.config['MAIL_PASSWORD'] = ""
mail = Mail(app)
app.config['RECAPTCHA_PUBLIC_KEY'] = ''
app.config['RECAPTCHA_PRIVATE_KEY'] = ''
app.config['RECAPTCHA_DATA_ATTRS'] = {'theme': 'light'}
app.config['JSON_AS_ASCII'] = False
app.config[
'MAX_CONTENT_LENGTH'] = 800 * 1024 * 1024 # 800MB max file upload limit.
# this import should be at the bottom to avoid circular import
from avr import routes
if not database_exists("sqlite:///" + os.path.join("avr", "site.db")):
try:
db.create_all()
# create students table view
studentsView = Table('students_view', MetaData())
view_definition = text(
"SELECT student.id, student.profilePic, project.year, project.semester, student.studentId, student.firstNameHeb, student.lastNameHeb, project.title as lastProjectTitle, project.status as lastProjectStatus, project.id as lastProjectId FROM student LEFT JOIN project ON project.id = (SELECT project.id FROM project LEFT JOIN student_project ON student_project.projectId=project.id WHERE student_project.studentId=student.id ORDER BY project.year DESC, project.semester ASC LIMIT 1)"
)
create_view = CreateView(studentsView, view_definition)
db.session.execute(create_view)
except Exception as e:
app.logger.error('{}\n{}'.format(e, traceback.format_exc()))
import sqlalchemy as db
from sqlalchemy_views import CreateView, DropView
from sqlalchemy.sql import text
engine = db.create_engine('sqlite:////tmp/test2.db', echo=True)
conn = engine.connect()
metadata = db.MetaData()
field = db.Table('field', metadata, autoload=True, autoload_with=engine)
view = db.Table('my_view', metadata)
create_view = CreateView(view, text("SELECT * FROM field"))
print(create_view.compile())