def build_query(session, project, **kwargs):
"""Build local query syntax.
Args:
session (SQLAlchemy obj): the db session
project (str): data project
kwargs (dict): query constraints
Returns:
r: (str) SQL query syntax to execute
"""
# for cmip5, cordex separate var from other constraints
if project in ['CMIP5', 'CORDEX'] and 'variable' in kwargs:
var = kwargs.pop('variable')
if project in ['CMIP5', 'CORDEX'] and 'experiment_family' in kwargs.keys():
family = kwargs.pop('experiment_family')
if project == 'CMIP6' and 'activity_id' in kwargs.keys():
activity = kwargs.pop('activity_id')
ctables = {
'CMIP5': [C5Dataset, Path.c5dataset],
'CMIP6': [C6Dataset, Path.c6dataset],
'CORDEX': [CordexDataset, Path.cordexdataset]
}
family_dict = {
'RCP': ['%rcp%'],
'ESM': ['esm%'],
'Atmos-only': ['sst%', 'amip%', 'aqua%'],
'Control': ['sstClim%', '%Control'],
'decadal': ['decadal%', 'noVolc%', 'volcIn%'],
'Idealized': ['%CO2'],
'All': ['%'],
'Paleo': ['lgm', 'midHolocene', 'past1000'],
'Historical': ['historical%', '%Historical']
}
r = (session.query(
Path.path.label('path'), *[
c.label(c.name) for c in ctables[project][0].__table__.columns
if c.name != 'dataset_id'
], *[
c.label(c.name) for c in ExtendedMetadata.__table__.columns
if c.name != 'file_id'
]).join(Path.extended).join(ctables[project][1]).filter_by(**kwargs))
if 'family' in locals() and project == 'CMIP5':
r = r.filter(C5Dataset.experiment.like(any_(family_dict[family])))
if 'family' in locals() and project == 'CORDEX':
r = r.filter(CordexDataset.experiment.like(any_(family_dict[family])))
if 'var' in locals():
r = r.filter(ExtendedMetadata.variable == var)
if 'activity' in locals():
r = r.filter(C6Dataset.activity_id.like("%" + activity + "%"))
return r
def filter_platform(q, platform_id):
# misc function to filter on a platform, depending on the row_type
if self.row_type == ExportRowType.EXTERNAL_OBJECT:
return q.filter(
platform_id == any_(func.array_agg(Platform.id)))
elif self.row_type == ExportRowType.OBJECT_LINK:
return q.filter(platform_id == Platform.id)
def test_any_w_comparator(self, connection):
stuff = self.tables.stuff
stmt = select(stuff.c.id).where(
stuff.c.value > any_(select(stuff.c.value).scalar_subquery())
)
eq_(connection.execute(stmt).fetchall(), [(2,), (3,), (4,), (5,)])
def get_agencies_as_choices():
"""
Get selected category value from the request body and generate a list of sorted agencies from the category.
:return: list of agency choices
"""
if flask_request.args["category"]:
# TODO: is sorted faster than orderby?
choices = sorted(
[(agencies.ein, agencies.name)
for agencies in Agencies.query.filter(
flask_request.args["category"] == any_(Agencies.categories)).
all() if agencies.ein not in HIDDEN_AGENCIES],
key=lambda x: x[1],
)
else:
choices = sorted(
[(agencies.ein, agencies.name)
for agencies in Agencies.query.all()
if agencies.ein not in HIDDEN_AGENCIES],
key=lambda x: x[1],
)
choices.insert(
0, ("", "")
) # Insert blank option at the beginning of choices to prevent auto selection
return jsonify(choices)
def _primary_key_view_statement(self):
with warnings.catch_warnings():
warnings.simplefilter('ignore', category=sa.exc.SAWarning)
pg_index = self.model('pg_index', 'pg_catalog')
pg_attribute = self.model('pg_attribute', 'pg_catalog')
pg_class = self.model('pg_class', 'pg_catalog')
pg_namespace = self.model('pg_namespace', 'pg_catalog')
alias = pg_class.alias('x')
return sa.select([
sa.cast(
pg_index.c.indrelid,
sa.dialects.postgresql.REGCLASS,
).label('table_name'),
sa.func.ARRAY_AGG(
pg_attribute.c.attname).label('primary_keys'),
]).join(
pg_attribute,
pg_attribute.c.attrelid == pg_index.c.indrelid,
).join(
pg_class,
pg_class.c.oid == pg_index.c.indexrelid,
).join(
alias,
alias.c.oid == pg_index.c.indrelid,
).join(
pg_namespace,
pg_namespace.c.oid == pg_class.c.relnamespace,
).where(*[
pg_namespace.c.nspname.notin_(['pg_catalog', 'pg_toast']),
pg_index.c.indisprimary,
pg_attribute.c.attnum == sa.any_(pg_index.c.indkey),
]).group_by(pg_index.c.indrelid)
def test_any_w_comparator(self):
stuff = self.tables.stuff
stmt = select([stuff.c.id]).where(
stuff.c.value > any_(select([stuff.c.value]))
)
eq_(testing.db.execute(stmt).fetchall(), [(2,), (3,), (4,), (5,)])
def _get_nodes(node_id: int, id_col: IA, pid_col: IA, to_root: bool, *other_col):
"""
get all child nodes or all parent nodes for a given node.
node_id: start node's id value;
id_col: the id column of a sqlalchemy class, often is `id`;
pid_col: the parent id column of a sqlalchemy class, often is `parent_id`;
to_root: to root node or to children node;
other_col: other columns you want to select when query the nodes;
"""
class_model = id_col.class_
other_col_names = [col.name for col in other_col]
hierarchy = (
select(
[
id_col.label('id'),
pid_col.label('parent_id'),
*other_col,
literal(0).label('level'),
array((id_col,)).label('path'), # array need tuple
literal(False).label('cycle'),
]
)
.where(id_col == node_id)
.cte(name='hierarchy', recursive=True)
)
next_alias = aliased(class_model, name='next_level')
alias_id_col = getattr(next_alias, id_col.name)
alias_pid_col = getattr(next_alias, pid_col.name)
alias_other_col = [getattr(next_alias, col.name) for col in other_col]
if to_root is True:
'第一层的 parent_id 是下一层的 id'
join_condition = hierarchy.c.parent_id == alias_id_col
else:
'第一层的 id 是下一层的 parent_id'
join_condition = hierarchy.c.id == alias_pid_col
hierarchy = hierarchy.union_all(
select(
[
alias_id_col.label('id'),
alias_pid_col.label('parent_id'),
*alias_other_col,
(hierarchy.c.level + 1).label('level'),
(hierarchy.c.path + array((alias_id_col,))).label('path'),
(alias_id_col == any_(hierarchy.c.path)).label('cycle'),
]
)
.where(hierarchy.c.cycle.is_(False))
.select_from(hierarchy.join(next_alias, join_condition, isouter=False))
)
q = sa.select(
[column('id'), column('parent_id'), *[column(name) for name in other_col_names]]
).select_from(hierarchy)
return session.execute(q)
def search_by_array(cls, q: str):
"""
SELECT * FROM devices WHERE 'X' % ANY(STRING_TO_ARRAY(name, ' '));
"""
query = cls.query.filter(
bindparam('string',
q).op('%%')(any_(func.string_to_array(Device.name,
' '))))
return query
def get_all_data(self, principal_ids: list) -> list:
"""Get all user data from a list of principals."""
users = UserProfile.query().filter(
UserProfile.id == sa.any_(principal_ids)).all()
result = []
for user in users:
data = map_from_object(user_schema, user, default='')
result.append(data)
return result
def extend_conditions(self, _query_parts):
for token, fn in (("block", operator.ne), ("allow", operator.eq)):
if self.kwargs["gene_%slist" % token]:
hgnc_ids = [
hgnc.id for hgnc in Hgnc.objects.filter(
Q(symbol__in=self.kwargs["gene_%slist" % token])
| Q(entrez_id__in=self.kwargs["gene_%slist" % token])
| Q(ensembl_gene_id__in=self.kwargs["gene_%slist" %
token]))
]
if (token == "block" and hgnc_ids) or (token == "allow"):
yield fn(Hgnc.sa.id, any_(hgnc_ids))
def query(
cls,
principal_id: t.Optional[PrincipalIdType]=None,
permission: str='can_view'
) -> Query:
"""Return query object.
:returns: A query object
"""
from briefy.common.db.models.local_role import LocalRole
query = cls.__session__.query(cls)
permission_attr = getattr(cls, permission)
if principal_id:
query = query.join(
LocalRole, LocalRole.item_id == sa.any_(cls.path)
).filter(
sa.and_(
LocalRole.principal_id == principal_id,
LocalRole.role_name == sa.any_(permission_attr),
)
).distinct()
return query
def check_unintelligible_ccc():
"""
:return:
"""
log.info("Start checking unintelligible CCC")
unintelligible_ccc = [
syllable.name for syllable in
Syllable.query.filter(Syllable.type == "UnintelligibleCCC").all()]
ccc_filter = Word.name.like(any_([f"%{ccc}%" for ccc in unintelligible_ccc]))
words = Word.by_event().filter(ccc_filter).all()
_ = [print(word.name) for word in words]
log.info("Finish checking unintelligible CCC")
def on_data(data):
email = data["email"]
sid = request.sid
group_code = (db.session.query(models.Participants.group_code).filter(
models.Participants.email == email).all())
username = (db.session.query(
models.Users.username).filter(models.Users.email == email).all())
password = (db.session.query(
models.Users.password).filter(models.Users.email == email).all())
role = db.session.query(
models.Users.role).filter(models.Users.email == email).all()
profile_img = (db.session.query(
models.Users.profile_img).filter(models.Users.email == email).all())
total_projects = (db.session.query(func.count(
models.Projects.group_code)).filter(
models.Projects.group_code.like(any_(group_code))).all())
total_tasks = (db.session.query(func.count(
models.Tasks.task_owner)).filter(
models.Tasks.group_code.like(any_(group_code))).all())
completed_tasks = (db.session.query(
func.count(models.Tasks.complete_status)).filter(
models.Tasks.group_code.like(any_(group_code))).filter(
models.Tasks.complete_status == "T").all())
socketio.emit(
"data",
{
"profileImg": profile_img,
"role": role,
"password": password,
"username": username,
"totalProjects": total_projects,
"totalTasks": total_tasks,
"completedTasks": completed_tasks,
},
sid,
)
def get_agencies_as_choices():
"""
Get selected category value from the request body and generate a list of sorted agencies from the category.
:return: list of agency choices
"""
if flask_request.args['category']:
# TODO: is sorted faster than orderby?
choices = sorted([(agencies.ein, agencies.name)
for agencies in Agencies.query.filter(
flask_request.args['category'] == any_(
Agencies.categories)).all()],
key=lambda x: x[1])
else:
choices = sorted([(agencies.ein, agencies.name)
for agencies in Agencies.query.all()],
key=lambda x: x[1])
return jsonify(choices)
def search_users(
self,
username: Optional[str],
display_name: Optional[str],
email: Optional[str],
privilege: Optional[JSON],
datacenters: Optional[List[str]],
limit: int,
) -> List[User]:
""" Get a list of all users matching the given criteria """
criteria = []
if username is not None and username != "":
criteria.append(UserEntry.username == username)
if display_name is not None and display_name != "":
criteria.append(UserEntry.display_name == display_name)
if email is not None and email != "":
criteria.append(UserEntry.email == email)
if privilege is not None:
for key in privilege:
if privilege[key] and key != PermissionConstants.DATACENTERS:
criteria.append(UserEntry.privilege[key].astext.cast(
Boolean) == privilege[key])
if datacenters is not None:
for center in datacenters:
print(center)
# criteria.append(UserEntry.datacenters.any([f"{{{center}}}"]))
# criteria.append(UserEntry.datacenters.all(center))
criteria.append(center == any_(UserEntry.datacenters))
filtered_users: List[UserEntry] = UserEntry.query.filter(
and_(*criteria)).limit(limit)
return [
User(
username=user.username,
display_name=user.display_name,
email=user.email,
password=user.password_hash,
privilege=user.privilege,
datacenters=user.datacenters,
) for user in filtered_users
]
def get_agencies_as_choices():
"""
Get selected category value from the request body and generate a list of sorted agencies from the category.
:return: list of agency choices
"""
if flask_request.args['category']:
# TODO: is sorted faster than orderby?
choices = sorted(
[(agencies.ein, agencies.name)
for agencies in Agencies.query.filter(
flask_request.args['category'] == any_(Agencies.categories)
).all() if agencies.ein not in HIDDEN_AGENCIES],
key=lambda x: x[1])
else:
choices = sorted(
[(agencies.ein, agencies.name)
for agencies in Agencies.query.all() if agencies.ein not in HIDDEN_AGENCIES],
key=lambda x: x[1])
choices.insert(0, ('', '')) # Insert blank option at the beginning of choices to prevent auto selection
return jsonify(choices)
def get_message(self, date_start, date_end, foo):
db = DB()
con = DB.get_connect(db)
meta = sqlalchemy.MetaData(bind=con, reflect=True, schema=SCHEMA)
telegram = meta.tables[TABLE_TELEGRAM]
telegram_sql = telegram.select().with_only_columns([
telegram.c.message, telegram.c.id_message, telegram.c.name_chat,
telegram.c.date
]).where((telegram.c.date > date_start) & (telegram.c.date < date_end)
& (sqlalchemy.func.lower(telegram.c.message).like(any_(foo))))
db = con.execute(telegram_sql)
for item in db:
if self.check_message(item._row[1]):
message_dict = dict(message=item._row[0].lower(),
id_message=item._row[1],
name_chat=item._row[2],
date=item._row[3])
self.message.append(message_dict)
db.close()
return self.message
def paginate_search(cls, **kwargs):
page = request.args.get('page', 1, type=int)
perPage = request.args.get('perPage', 10, type=int)
search = request.args.get('search', None, type=str)
if search:
search = json.loads(search)
else:
search = dict()
newSearch = dict()
for key in search:
if search.get(key, None) and search.get(
key, None) is not None and search.get(key, None) != '':
newSearch[key] = search[key]
query = cls().query.filter_by(status=any_([1, 2])).filter_by(
**newSearch)
items = query.offset((page - 1) * perPage).limit(perPage).all()
count = query.count()
db.session.remove()
return Pagination(items, count, page)
def get_select(self, params):
if {"prefix", "continue"} & set(params):
raise NotImplementedError
if "limit" in params and params["limit"] != "max":
raise NotImplementedError
user = self.db.user
groups = self.db.user_groups
ipb = self.db.ipblocks
s = sa.select([user.c.user_id, user.c.user_name])
prop = params["prop"]
if "editcount" in prop:
s.append_column(user.c.user_editcount)
if "registration" in prop:
s.append_column(user.c.user_registration)
# joins
tail = user
if "blockinfo" in prop:
tail = tail.outerjoin(ipb, user.c.user_id == ipb.c.ipb_user)
s.append_column(ipb.c.ipb_by)
s.append_column(ipb.c.ipb_by_text)
s.append_column(ipb.c.ipb_timestamp)
s.append_column(ipb.c.ipb_expiry)
s.append_column(ipb.c.ipb_id)
s.append_column(ipb.c.ipb_reason)
s.append_column(ipb.c.ipb_deleted)
if "groups" in prop or "group" in params or "excludegroup" in params:
tail = tail.outerjoin(groups, user.c.user_id == groups.c.ug_user)
s = s.group_by(*s.columns.values())
user_groups = sa.func.array_agg(groups.c.ug_group).label("user_groups")
s.append_column(user_groups)
s = s.select_from(tail)
# restrictions
s = s.where(user.c.user_id > 0)
if params["dir"] == "ascending":
start = params.get("from")
end = params.get("to")
else:
start = params.get("to")
end = params.get("from")
if start:
s = s.where(user.c.user_name >= start)
if end:
s = s.where(user.c.user_name <= end)
if "group" in params:
s = s.where(params["group"] == sa.any_(user_groups))
if "excludegroup" in params:
s = s.where(sa.not_(params["group"] == sa.any_(user_groups)))
if "witheditsonly" in params:
s = s.where( (user.c.user_editcount != None) & (user.c.user_editcount > 0) )
# TODO
# if "activeusers" in params:
# order by
if params["dir"] == "ascending":
s = s.order_by(user.c.user_name.asc())
else:
s = s.order_by(user.c.user_name.desc())
return s
def _primary_key_view_statement(self, schema, tables, views):
"""
Table name and primary keys association where the table name
is the index.
This is only called once on bootstrap.
It is used within the trigger function to determine what payload
values to send to pg_notify.
Since views cannot be modified, we query the existing view for exiting
rows and union this to the next query.
So if 'specie' was the only row before, and the next query returns
'unit' and 'structure', we want to end up with the result below.
table_name | primary_keys
------------+--------------
specie | {id}
unit | {id}
structure | {id}
"""
with warnings.catch_warnings():
warnings.simplefilter('ignore', category=sa.exc.SAWarning)
pg_class = self.model('pg_class', 'pg_catalog')
pg_index = self.model('pg_index', 'pg_catalog')
pg_attribute = self.model('pg_attribute', 'pg_catalog')
pg_namespace = self.model('pg_namespace', 'pg_catalog')
alias = pg_class.alias('x')
statement = sa.select([
sa.cast(
sa.cast(
pg_index.c.indrelid,
sa.dialects.postgresql.REGCLASS,
),
sa.Text,
).label('table_name'),
sa.func.ARRAY_AGG(pg_attribute.c.attname).label('primary_keys'),
]).join(
pg_attribute,
pg_attribute.c.attrelid == pg_index.c.indrelid,
).join(
pg_class,
pg_class.c.oid == pg_index.c.indexrelid,
).join(
alias,
alias.c.oid == pg_index.c.indrelid,
).join(
pg_namespace,
pg_namespace.c.oid == pg_class.c.relnamespace,
).where(*[
pg_namespace.c.nspname.notin_(['pg_catalog', 'pg_toast']),
pg_index.c.indisprimary,
sa.cast(
sa.cast(
pg_index.c.indrelid,
sa.dialects.postgresql.REGCLASS,
),
sa.Text,
).in_(tables),
pg_attribute.c.attnum == sa.any_(pg_index.c.indkey),
]).group_by(pg_index.c.indrelid)
if PRIMARY_KEY_VIEW in views:
values = self.fetchall(
sa.select([
sa.column('table_name'),
sa.column('primary_keys'),
]).select_from(sa.text(PRIMARY_KEY_VIEW)))
self.__engine.execute(DropView(schema, PRIMARY_KEY_VIEW))
if values:
statement = statement.union(
sa.select(
Values(
sa.column('table_name'),
sa.column('primary_keys'),
).data([(value[0], array(value[1]))
for value in values]).alias('t')))
return statement
def get_tonal_date(args):
print('start_write - ', str(datetime.now()))
negativ = ['not', 'no', 't']
setting = Settings()
url = 'postgresql://{}:{}@{}:{}/{}'
url = url.format(setting.login, setting.password, setting.host,
setting.port, setting.name)
# The return value of create_engine() is our connection object
con = sqlalchemy.create_engine(url, echo=True)
meta = sqlalchemy.MetaData(bind=con, reflect=True, schema='telegram')
users_table = meta.tables['telegram.synonyms']
date_start = args['date_start']
date_end_temp = date_start + timedelta(days=1)
flag = True
w_coin = []
foo = []
nltk_word = list(stopwords.words('english'))
word_coin = get_coin_single(args['symbol'], url)
if word_coin['synonyms'] != '':
temp = word_coin['synonyms'].split(',')
for j in temp:
w_coin.append(j)
foo.append('%' + j + '%')
## w_coin.append(word_coin['symbol'])
## foo.append('%' + j + '%')
## w_coin.append(word_coin['full_name'])
## foo.append('%' + word_coin['full_name'] + '%')
stemmer = SnowballStemmer("english")
word_analitics = get_word_analitics(url)
telegram = meta.tables['telegram.message']
telegram_sql = telegram.select().with_only_columns([
telegram.c.message, telegram.c.id_message, telegram.c.name_chat,
telegram.c.date
]).where((telegram.c.date > args['date_start'])
& (telegram.c.date < args['date_end'])
& (sqlalchemy.func.lower(telegram.c.message).like(any_(foo))))
db = con.execute(telegram_sql)
data_word = []
coin_word = get_coin(url)
for item in db:
data = word_tokenize(item._row[0].lower())
search_temp = [i for i in w_coin if i in data]
if search_temp == None:
continue
for coin in search_temp:
data = [i for i in data if i not in nltk_word]
data = [i for i in data if i not in punctuation]
data = [i for i in data if re.search('\w', i)]
index_coin = data.index(coin)
start_search = index_coin - 3
end_search = index_coin + 3
if start_search < 0:
start_search = 0
if end_search > len(data):
end_search = len(data)
vector_word = data[start_search:end_search]
vector_word = [i for i in vector_word if search_temp[0] != i]
singles = [stemmer.stem(plural) for plural in vector_word]
for j in singles:
data_word.append(j)
print(vector_word)
fd = nltk.FreqDist(data_word)
word_coin = fd.most_common()
fd.plot(150, cumulative=False)
def local_query(session, project='CMIP5', latest=True, **kwargs):
'''Query MAS matching directly the constraints to the file attributes instead of querying first the ESGF
:input: session the db session
:input: project 'CMIP5' by default
:input: latest True by default
:input: kwargs a dictionary with the query constraints
:return: a list of dictionary, each dictionary describe one simulation matching the constraints
'''
# create empty list for results dictionaries
# each dict will represent a file matching the constraints
results = []
project = project.upper()
# for cmip5 separate var from other constraints
if project == 'CMIP5' and 'variable' in kwargs.keys():
var = kwargs.pop('variable')
if project == 'CMIP5' and 'experiment_family' in kwargs.keys():
family = kwargs.pop('experiment_family')
ctables = {
'CMIP5': [C5Dataset, Path.c5dataset],
'CMIP6': [C6Dataset, Path.c6dataset]
}
family_dict = {
'RCP': ['%rcp%'],
'ESM': ['esm%'],
'Atmos-only': ['sst%', 'amip%', 'aqua%'],
'Control': ['sstClim%', '%Control'],
'decadal': ['decadal%', 'noVolc%', 'volcIn%'],
'Idealized': ['%CO2'],
'Paleo': ['lgm', 'midHolocene', 'past1000'],
'historical': ['historical%', '%Historical']
}
r = (session.query(
Path.path.label('path'), *[
c.label(c.name) for c in ctables[project][0].__table__.columns
if c.name != 'dataset_id'
], *[
c.label(c.name) for c in ExtendedMetadata.__table__.columns
if c.name != 'file_id'
]).join(Path.extended).join(ctables[project][1]).filter_by(**kwargs))
if 'family' in locals():
r = r.filter(C5Dataset.experiment.like(any_(family_dict[family])))
if 'var' in locals(): #and 'family' not in locals():
r = r.filter(ExtendedMetadata.variable == var)
# run the sql using pandas read_sql,index data using path, returns a dataframe
df = pandas.read_sql(r.selectable, con=session.connection())
# temporary(?) fix to substitute output1/2 with combined
fix_paths = df['path'].apply(fix_path, latest=latest)
df['pdir'] = fix_paths.map(os.path.dirname)
df['filename'] = df['path'].map(os.path.basename)
res = df.groupby(['pdir'])
results = []
cols = [x for x in list(df) if x not in ['filename', 'path', 'period']]
for g, v in res.groups.items():
gdict = {}
gdict['filenames'] = df['filename'].iloc[list(v)].tolist()
nranges = df['period'].iloc[list(v)].tolist()
for c in cols:
gdict[c] = df[c].iloc[list(v)].unique()[0]
gdict['periods'] = convert_periods(nranges, gdict['frequency'])
gdict['fdate'], gdict['tdate'] = get_range(gdict['periods'])
gdict['time_complete'] = time_axis(gdict['periods'], gdict['fdate'],
gdict['tdate'])
# make sure a version is available even for CMIP6 where is usually None
if gdict['version'] is None:
gdict['version'] = get_version(gdict['pdir'])
results.append(gdict)
return results
def location_for_iata_code(iata_code: str, db_session) -> LocationInfo:
location = db_session.query(LocationInfo).join(AirportInfo)\
.filter(
iata_code == sqla.any_(AirportInfo.iata_codes)
).first()
return location
def test_any_literal(self, connection):
stuff = self.tables.stuff
stmt = select(4 == any_(select(stuff.c.value).scalar_subquery()))
is_(connection.execute(stmt).scalar(), True)
# print( session.query(UserDetails,User) ) #cross join
# print( session.query(UserDetails,User).filter(UserDetails.id==User.id).all() ) #
# print( session.query(UserDetails,User).filter(UserDetails.id==User.id) ) #cross join
# print( session.query(User.username,UserDetails.lost_login).\
# join(UserDetails,UserDetails.id==User.id) ) #inner join
# print( session.query(User.username,UserDetails.lost_login).\
# outerjoin(UserDetails,UserDetails.id==User.id).all() ) #left join
q1 = session.query(User.id)
q2 = session.query(UserDetails.id)
# print(q1.union(q2).all())
from sqlalchemy import all_, any_
sql_0 = session.query(UserDetails.lost_login).subquery()
print(session.query(User).filter(User.creatime > all_(sql_0)).all())
print(session.query(User).filter(User.creatime > any_(sql_0)).all())
#原生sql
sql_1 = '''
select * from `user`
'''
row = session.execute(sql_1)
# print(row,dir(row))
# print(row.fetchone())
# print(row.fetchmany())
# print(row.fetchall())
for i in row:
pass
# print(i)
def sync(
self,
filters: Optional[dict] = None,
txmin: Optional[int] = None,
txmax: Optional[int] = None,
extra: Optional[dict] = None,
ctid: Optional[int] = None,
) -> Generator:
if filters is None:
filters: dict = {}
root: Node = self.tree.build(self.nodes)
self.query_builder.isouter: bool = True
for node in root.traverse_post_order():
self._build_filters(filters, node)
if node.is_root:
if ctid is not None:
subquery = []
for page, rows in ctid.items():
subquery.append(
sa.select([
sa.cast(
sa.literal_column(f"'({page},'").concat(
sa.column("s")).concat(")"),
TupleIdentifierType,
)
]).select_from(
Values(sa.column("s"), ).data([
(row, ) for row in rows
]).alias("s")))
if subquery:
node._filters.append(
sa.or_(*[
node.model.c.ctid == sa.any_(
sa.func.ARRAY(q.scalar_subquery()))
for q in subquery
]))
if txmin:
node._filters.append(
sa.cast(
sa.cast(
node.model.c.xmin,
sa.Text,
),
sa.BigInteger,
) >= txmin)
if txmax:
node._filters.append(
sa.cast(
sa.cast(
node.model.c.xmin,
sa.Text,
),
sa.BigInteger,
) < txmax)
try:
self.query_builder.build_queries(node)
except Exception as e:
logger.exception(f"Exception {e}")
raise
if self.verbose:
compiled_query(node._subquery, "Query")
count: int = self.fetchcount(node._subquery)
with click.progressbar(
length=count,
show_pos=True,
show_percent=True,
show_eta=True,
fill_char="=",
empty_char="-",
width=50,
) as bar:
for i, (keys, row,
primary_keys) in enumerate(self.fetchmany(node._subquery)):
bar.update(1)
row: dict = transform(row, self.nodes)
row[META] = get_private_keys(keys)
if extra:
if extra["table"] not in row[META]:
row[META][extra["table"]] = {}
if extra["column"] not in row[META][extra["table"]]:
row[META][extra["table"]][extra["column"]] = []
row[META][extra["table"]][extra["column"]].append(0)
if self.verbose:
print(f"{(i+1)})")
print(f"Pkeys: {primary_keys}")
pprint.pprint(row)
print("-" * 10)
doc: dict = {
"_id": self.get_doc_id(primary_keys),
"_index": self.index,
"_source": row,
}
if self.routing:
doc["_routing"] = row[self.routing]
if self.es.major_version < 7 and not self.es.is_opensearch:
doc["_type"] = "_doc"
if self._plugins:
doc = next(self._plugins.transform([doc]))
if not doc:
continue
if self.pipeline:
doc["pipeline"] = self.pipeline
yield doc
def _primary_key_view_statement(self, schema, tables, views):
with warnings.catch_warnings():
warnings.simplefilter('ignore', category=sa.exc.SAWarning)
pg_class = self.model('pg_class', 'pg_catalog')
pg_index = self.model('pg_index', 'pg_catalog')
pg_attribute = self.model('pg_attribute', 'pg_catalog')
pg_namespace = self.model('pg_namespace', 'pg_catalog')
alias = pg_class.alias('x')
statement = sa.select([
sa.cast(
sa.cast(
pg_index.c.indrelid,
sa.dialects.postgresql.REGCLASS,
), sa.Text,
).label(
'table_name'
),
sa.func.ARRAY_AGG(pg_attribute.c.attname).label(
'primary_keys'
),
]).join(
pg_attribute,
pg_attribute.c.attrelid == pg_index.c.indrelid,
).join(
pg_class,
pg_class.c.oid == pg_index.c.indexrelid,
).join(
alias,
alias.c.oid == pg_index.c.indrelid,
).join(
pg_namespace,
pg_namespace.c.oid == pg_class.c.relnamespace,
).where(*[
pg_namespace.c.nspname.notin_(
['pg_catalog', 'pg_toast']
),
pg_index.c.indisprimary,
sa.cast(
sa.cast(
pg_index.c.indrelid,
sa.dialects.postgresql.REGCLASS,
), sa.Text,
).in_(tables),
pg_attribute.c.attnum == sa.any_(pg_index.c.indkey),
]).group_by(
pg_index.c.indrelid
)
if PRIMARY_KEY_VIEW in views:
values = self.fetchall(sa.select([
sa.column('table_name'),
sa.column('primary_keys'),
]).select_from(
sa.text(PRIMARY_KEY_VIEW)
))
self.__engine.execute(DropView(schema, PRIMARY_KEY_VIEW))
if values:
statement = statement.union(
sa.select(
Values(
sa.column('table_name'),
sa.column('primary_keys'),
).data(
[(value[0], array(value[1])) for value in values]
).alias(
't'
)
)
)
return statement
def build_slack_user_event_date_filter(start_utc_datetime, end_utc_datetime):
return (SlackUserEvent.event_datetime >= start_utc_datetime) & (SlackUserEvent.event_datetime < end_utc_datetime) \
& (SlackUserEvent.slack_event_type == any_(['message', 'reaction_added', 'reaction_removed']))
def test_any_literal(self):
stuff = self.tables.stuff
stmt = select([4 == any_(select([stuff.c.value]))])
is_(testing.db.execute(stmt).scalar(), True)
def generate(self, value):
if isinstance(value, list):
return self.filter_obj.ilike(any_(value))
return self.filter_obj.ilike(value)
def test_any_literal(self):
stuff = self.tables.stuff
stmt = select([4 == any_(select([stuff.c.value]))])
is_(testing.db.execute(stmt).scalar(), True)
def _primary_keys(self, schema: str,
tables: List[str]) -> sa.sql.selectable.Select:
with warnings.catch_warnings():
warnings.simplefilter("ignore", category=sa.exc.SAWarning)
pg_class = self.model("pg_class", "pg_catalog")
pg_index = self.model("pg_index", "pg_catalog")
pg_attribute = self.model("pg_attribute", "pg_catalog")
pg_namespace = self.model("pg_namespace", "pg_catalog")
alias = pg_class.alias("x")
inclause: list = []
for table in tables:
pairs = table.split(".")
if len(pairs) == 1:
inclause.append(
self.__engine.dialect.identifier_preparer.quote(pairs[0]))
elif len(pairs) == 2:
inclause.append(
f"{pairs[0]}.{self.__engine.dialect.identifier_preparer.quote(pairs[-1])}"
)
else:
raise Exception(
f"cannot determine schema and table from {table}")
return (sa.select([
sa.func.REPLACE(
sa.func.REVERSE(
sa.func.SPLIT_PART(
sa.func.REVERSE(
sa.cast(
sa.cast(
pg_index.c.indrelid,
sa.dialects.postgresql.REGCLASS,
),
sa.Text,
)),
".",
1,
)),
'"',
"",
).label("table_name"),
sa.func.ARRAY_AGG(pg_attribute.c.attname).label("primary_keys"),
]).join(
pg_attribute,
pg_attribute.c.attrelid == pg_index.c.indrelid,
).join(
pg_class,
pg_class.c.oid == pg_index.c.indexrelid,
).join(
alias,
alias.c.oid == pg_index.c.indrelid,
).join(
pg_namespace,
pg_namespace.c.oid == pg_class.c.relnamespace,
).where(*[
pg_namespace.c.nspname.notin_(["pg_catalog", "pg_toast"]),
pg_index.c.indisprimary,
sa.cast(
sa.cast(
pg_index.c.indrelid,
sa.dialects.postgresql.REGCLASS,
),
sa.Text,
).in_(inclause),
pg_attribute.c.attnum == sa.any_(pg_index.c.indkey),
]).group_by(pg_index.c.indrelid))