def test_mol_from_ctab(self):
rs = engine.execute(select([ func.is_valid_ctab('xyz') ]))
self.assertFalse(rs.fetchall()[0][0])
ctab = '''chiral1.mol
ChemDraw04200416412D
5 4 0 0 0 0 0 0 0 0999 V2000
-0.0141 0.0553 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0
0.8109 0.0553 0.0000 F 0 0 0 0 0 0 0 0 0 0 0 0
-0.4266 0.7697 0.0000 Br 0 0 0 0 0 0 0 0 0 0 0 0
-0.0141 -0.7697 0.0000 Cl 0 0 0 0 0 0 0 0 0 0 0 0
-0.8109 -0.1583 0.0000 C 0 0 0 0 0 0 0 0 0 0 0 0
1 2 1 0
1 3 1 0
1 4 1 1
1 5 1 0
M END'''
rs = engine.execute(select([ func.is_valid_ctab(ctab) ]))
self.assertTrue(rs.fetchall()[0][0])
rs = engine.execute(select([ func.mol_from_ctab(ctab) ]))
self.assertIsInstance(rs.fetchall()[0][0], Chem.Mol)
def _queryNewsVolumes(aggStartDatetime, aggStopDatetime):
""" Query the database for the counts of security releases+headlines for each
company that were detected during the specified time window.
:param aggStartDatetime: inclusive start of aggregation interval as
UTC datetime
:param aggStopDatetime: non-inclusive upper bound of aggregation interval as
UTC datetime
:returns: a sparse sequence of two-tuples: (symbol, count); companies that
have no detected news in the given aggregation period will be absent from
the result.
"""
headlineSel = sql.select(
[schema.xigniteSecurityHeadline.c.symbol.label("symbol")]
).where(
(schema.xigniteSecurityHeadline.c.discovered_at >= aggStartDatetime) &
(schema.xigniteSecurityHeadline.c.discovered_at < aggStopDatetime))
releaseSel = sql.select(
[schema.xigniteSecurityRelease.c.symbol]
).where(
(schema.xigniteSecurityRelease.c.discovered_at >= aggStartDatetime) &
(schema.xigniteSecurityRelease.c.discovered_at < aggStopDatetime))
allNewsUnion = sql.union_all(headlineSel, releaseSel)
aggSel = sql.select(
["symbol", sql.func.count("symbol").label("sym_count")]
).select_from(allNewsUnion.alias("union_of_tables")
).group_by("symbol")
return collectorsdb.engineFactory().execute(aggSel).fetchall()
def test_with_transaction(self):
conn = self.engine.connect()
trans = conn.begin()
eq_(conn.execute(select([1])).scalar(), 1)
assert not conn.closed
self.engine.test_shutdown()
_assert_invalidated(conn.execute, select([1]))
assert not conn.closed
assert conn.invalidated
assert trans.is_active
assert_raises_message(
tsa.exc.StatementError,
"Can't reconnect until invalid transaction is rolled back",
conn.execute, select([1]))
assert trans.is_active
assert_raises_message(
tsa.exc.InvalidRequestError,
"Can't reconnect until invalid transaction is rolled back",
trans.commit
)
assert trans.is_active
trans.rollback()
assert not trans.is_active
assert conn.invalidated
eq_(conn.execute(select([1])).scalar(), 1)
assert not conn.invalidated
def whereInEquipement(self,fullQueryJoin,criteria):
sensorObj = list(filter(lambda x:'FK_Sensor'==x['Column'], criteria))[0]
sensor = sensorObj['Value']
table = Base.metadata.tables['MonitoredSiteEquipment']
joinTable = outerjoin(table,Sensor, table.c['FK_Sensor'] == Sensor.ID)
if sensorObj['Operator'].lower() in ['is','is not'] and sensorObj['Value'].lower() == 'null':
subSelect = select([table.c['FK_MonitoredSite']]
).select_from(joinTable).where(
and_(MonitoredSite.ID== table.c['FK_MonitoredSite']
,or_(table.c['EndDate'] >= func.now(),table.c['EndDate'] == None)
))
if sensorObj['Operator'].lower() == 'is':
fullQueryJoin = fullQueryJoin.where(~exists(subSelect))
else :
fullQueryJoin = fullQueryJoin.where(exists(subSelect))
else :
subSelect = select([table.c['FK_MonitoredSite']]
).select_from(joinTable).where(
and_(MonitoredSite.ID== table.c['FK_MonitoredSite']
,and_(eval_.eval_binary_expr(Sensor.UnicIdentifier,sensorObj['Operator'],sensor)
,or_(table.c['EndDate'] >= func.now(),table.c['EndDate'] == None))
))
fullQueryJoin = fullQueryJoin.where(exists(subSelect))
return fullQueryJoin
def test_basic(self):
for threadlocal in False, True:
engine = engines.reconnecting_engine(
options={'pool_threadlocal': threadlocal})
conn = engine.contextual_connect()
eq_(conn.execute(select([1])).scalar(), 1)
conn.close()
# set the pool recycle down to 1.
# we aren't doing this inline with the
# engine create since cx_oracle takes way
# too long to create the 1st connection and don't
# want to build a huge delay into this test.
engine.pool._recycle = 1
# kill the DB connection
engine.test_shutdown()
# wait until past the recycle period
time.sleep(2)
# can connect, no exception
conn = engine.contextual_connect()
eq_(conn.execute(select([1])).scalar(), 1)
conn.close()
def test_conn_reusable(self):
conn = self.db.connect()
conn.execute(select([1]))
eq_(
self.dbapi.connect.mock_calls,
[self.mock_connect]
)
self.dbapi.shutdown()
assert_raises(
tsa.exc.DBAPIError,
conn.execute, select([1])
)
assert not conn.closed
assert conn.invalidated
eq_(
[c.close.mock_calls for c in self.dbapi.connections],
[[call()]]
)
# test reconnects
conn.execute(select([1]))
assert not conn.invalidated
eq_(
[c.close.mock_calls for c in self.dbapi.connections],
[[call()], []]
)
def test_reconnect(self):
conn = self.engine.connect()
eq_(conn.execute(select([1])).scalar(), 1)
assert not conn.closed
self.engine.test_shutdown()
_assert_invalidated(conn.execute, select([1]))
assert not conn.closed
assert conn.invalidated
assert conn.invalidated
eq_(conn.execute(select([1])).scalar(), 1)
assert not conn.invalidated
# one more time
self.engine.test_shutdown()
_assert_invalidated(conn.execute, select([1]))
assert conn.invalidated
eq_(conn.execute(select([1])).scalar(), 1)
assert not conn.invalidated
conn.close()
def dat_crime(fpath=None):
# Step Zero: Create dat_crime table
raw_crime(fpath=fpath)
dedupe_crime()
src_crime()
src_crime_table = Table('src_chicago_crimes_all', Base.metadata,
autoload=True, autoload_with=engine, extend_existing=True)
dat_crime_table = crime_table('dat_chicago_crimes_all', Base.metadata)
dat_crime_table.append_column(Column('chicago_crimes_all_row_id', Integer, primary_key=True))
dat_crime_table.append_column(Column('start_date', TIMESTAMP, server_default=text('CURRENT_TIMESTAMP')))
dat_crime_table.append_column(Column('end_date', TIMESTAMP, server_default=text('NULL')))
dat_crime_table.append_column(Column('current_flag', Boolean, server_default=text('TRUE')))
dat_crime_table.append_constraint(UniqueConstraint('id', 'start_date'))
dat_crime_table.create(bind=engine, checkfirst=True)
new_cols = ['start_date', 'end_date', 'current_flag', 'chicago_crimes_all_row_id']
dat_ins = dat_crime_table.insert()\
.from_select(
[c for c in dat_crime_table.columns.keys() if c not in new_cols],
select([c for c in src_crime_table.columns])
)
conn = engine.contextual_connect()
res = conn.execute(dat_ins)
cols = crime_master_cols(dat_crime_table)
master_ins = MasterTable.insert()\
.from_select(
[c for c in MasterTable.columns.keys() if c != 'master_row_id'],
select(cols)\
.select_from(dat_crime_table)
)
conn = engine.contextual_connect()
res = conn.execute(master_ins)
return 'DAT crime created'
def verify_thd(conn):
metadata = sa.MetaData()
metadata.bind = conn
builders = sautils.Table('builders', metadata, autoload=True)
q = sa.select([builders])
num_rows = 0
for row in conn.execute(q):
self.assertEqual(
row, (1, u'bname', u'description', u'dontcare'))
num_rows += 1
self.assertEqual(num_rows, 1)
tags = sautils.Table('tags', metadata, autoload=True)
builders_tags = sautils.Table('builders_tags', metadata,
autoload=True)
q = sa.select([tags.c.id, tags.c.name,
tags.c.name_hash])
self.assertEqual(conn.execute(q).fetchall(), [])
q = sa.select([builders_tags.c.id,
builders_tags.c.builderid,
builders_tags.c.tagid])
self.assertEqual(conn.execute(q).fetchall(), [])
def test_union(self):
t1 = table(
't1', column('col1'), column('col2'),
column('col3'), column('col4'))
t2 = table(
't2', column('col1'), column('col2'),
column('col3'), column('col4'))
s1, s2 = select(
[t1.c.col3.label('col3'), t1.c.col4.label('col4')],
t1.c.col2.in_(['t1col2r1', 't1col2r2'])), \
select([t2.c.col3.label('col3'), t2.c.col4.label('col4')],
t2.c.col2.in_(['t2col2r2', 't2col2r3']))
u = union(s1, s2, order_by=['col3', 'col4'])
self.assert_compile(u,
'SELECT t1.col3 AS col3, t1.col4 AS col4 '
'FROM t1 WHERE t1.col2 IN (:col2_1, '
':col2_2) UNION SELECT t2.col3 AS col3, '
't2.col4 AS col4 FROM t2 WHERE t2.col2 IN '
'(:col2_3, :col2_4) ORDER BY col3, col4')
self.assert_compile(u.alias('bar').select(),
'SELECT bar.col3, bar.col4 FROM (SELECT '
't1.col3 AS col3, t1.col4 AS col4 FROM t1 '
'WHERE t1.col2 IN (:col2_1, :col2_2) UNION '
'SELECT t2.col3 AS col3, t2.col4 AS col4 '
'FROM t2 WHERE t2.col2 IN (:col2_3, '
':col2_4)) AS bar')
def test_limit_offset_with_correlated_order_by(self):
t1 = table('t1', column('x', Integer), column('y', Integer))
t2 = table('t2', column('x', Integer), column('y', Integer))
order_by = select([t2.c.y]).where(t1.c.x == t2.c.x).as_scalar()
s = select([t1]).where(t1.c.x == 5).order_by(order_by) \
.limit(10).offset(20)
self.assert_compile(
s,
"SELECT anon_1.x, anon_1.y "
"FROM (SELECT t1.x AS x, t1.y AS y, "
"ROW_NUMBER() OVER (ORDER BY "
"(SELECT t2.y FROM t2 WHERE t1.x = t2.x)"
") AS mssql_rn "
"FROM t1 "
"WHERE t1.x = :x_1) AS anon_1 "
"WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1",
checkparams={'param_1': 20, 'param_2': 10, 'x_1': 5}
)
c = s.compile(dialect=mssql.MSDialect())
eq_(len(c._result_columns), 2)
assert t1.c.x in set(c._create_result_map()['x'][1])
assert t1.c.y in set(c._create_result_map()['y'][1])
def _propagate_new_wf_comments(cycle_task_ids):
"""Special handler for comment propagation."""
if not cycle_task_ids:
return
acl_table = all_models.AccessControlList.__table__.alias("original_acl")
acr_table = all_models.AccessControlRole.__table__.alias("original_acr")
ct_propagated_roles = {
"Admin Mapped",
"Workflow Member Mapped",
}
propagation_acr_ids = sa.select([acr_table.c.id]).where(
acr_table.c.name.in_(ct_propagated_roles)
)
cycle_task_type = all_models.CycleTaskGroupObjectTask.__name__
cycle_task_acl_ids = sa.select([acl_table.c.id]).where(
sa.and_(
acl_table.c.object_id.in_(cycle_task_ids),
acl_table.c.object_type == cycle_task_type,
acl_table.c.ac_role_id.in_(propagation_acr_ids),
)
)
_propagate_to_cte(cycle_task_acl_ids)
def test_strict_binds(self):
"""test the 'strict' compiler binds."""
from sqlalchemy.dialects.mssql.base import MSSQLStrictCompiler
mxodbc_dialect = mxodbc.dialect()
mxodbc_dialect.statement_compiler = MSSQLStrictCompiler
t = table('sometable', column('foo'))
for expr, compile in [
(
select([literal("x"), literal("y")]),
"SELECT 'x' AS anon_1, 'y' AS anon_2",
),
(
select([t]).where(t.c.foo.in_(['x', 'y', 'z'])),
"SELECT sometable.foo FROM sometable WHERE sometable.foo "
"IN ('x', 'y', 'z')",
),
(
t.c.foo.in_([None]),
"sometable.foo IN (NULL)"
)
]:
self.assert_compile(expr, compile, dialect=mxodbc_dialect)
def verify_thd(self, conn):
"verify the contents of the db - run in a thread"
# note that this will all change if we re-create the tarball!
model = self.db.model
r = conn.execute(
sa.select([model.changes], order_by=model.changes.c.changeid))
ch = r.fetchone()
self.failUnlessEqual(ch.changeid, 1)
self.failUnlessEqual(ch.author, u'the snowman <\N{SNOWMAN}@norpole.net>')
self.failUnlessEqual(ch.comments, u'shooting star or \N{COMET}?')
self.failUnlessEqual(ch.revision, u'\N{BLACK STAR}-devel')
self.failUnlessEqual(ch.branch, u'\N{COMET}')
ch = r.fetchone()
self.failUnlessEqual(ch.changeid, 2)
self.failUnlessEqual(ch.author, u"dustin <*****@*****.**>")
self.failUnlessEqual(ch.comments, u'on-branch change')
self.failUnlessEqual(ch.revision, u'1234')
self.failUnlessEqual(ch.branch, u'') # arguably a bug - should be None?
r = conn.execute(
sa.select([model.change_files]))
# use a set to avoid depending on db collation
filenames = set([ row.filename for row in r ])
expected = set([
u'boring/path',
u'normal/path',
u'\N{BLACK STAR}/funny_chars/in/a/path',
])
self.failUnlessEqual(filenames, expected)
def verify_thd(self, conn):
"partially verify the contents of the db - run in a thread"
model = self.db.model
# this is a big db, so we only spot-check things -- hopefully any errors
# will occur on the import
r = conn.execute(
sa.select([model.changes],
whereclause=model.changes.c.changeid == 70))
ch = r.fetchone()
self.failUnlessEqual(ch.changeid, 70)
self.failUnlessEqual(ch.author, u'Jakub Vysoky <*****@*****.**>')
self.failUnlessEqual(ch.comments, u'some failing tests in check_downgrade and metapackage_version')
self.failUnlessEqual(ch.revision, u'2ce0c33b7e10cce98e8d9c5b734b8c133ee4d320')
self.failUnlessEqual(ch.branch, u'master')
r = conn.execute(
sa.select([model.change_files.c.filename],
whereclause=model.change_files.c.changeid == 70))
self.assertEqual(r.scalar(), 'tests/test_debian.py')
r = conn.execute(
sa.select([model.changes],
whereclause=model.changes.c.changeid == 77))
ch = r.fetchone()
self.failUnlessEqual(ch.changeid, 77)
self.failUnlessEqual(ch.author, u'BuildBot')
self.failUnlessEqual(ch.comments, u'Dependency changed, sending dummy commit')
self.failUnlessEqual(ch.revision, u'HEAD')
self.failUnlessEqual(ch.branch, u'master')
r = conn.execute(
sa.select([model.change_files.c.filename],
whereclause=model.change_files.c.changeid == 77))
self.assertEqual(r.scalar(), 'CHANGELOG')
def merge_data(args):
engine = create_engine(args.postgres)
Base.metadata.bind = engine
Base.metadata.create_all(bind=engine)
DBSession = sessionmaker()
DBSession.bind = engine
s = DBSession()
ctes = []
raw_data = RawData.__table__
for filename in args.filenames:
cte = sa.select([raw_data.c.data]).where(
sa.and_(
raw_data.c.file_name == filename,
raw_data.c.connector == args.connector,
raw_data.c.version == args.connector_version
)
).cte(filename)
ctes.append(cte)
selected_cols = ctes[0].c.data
for cte in ctes[1:]:
selected_cols = selected_cols + cte.c.data
select_from = ctes[0].join(
ctes[1], ctes[0].c.data['Country_Code'] == ctes[1].c.data['Country_Code']
)
merge_stmt = sa.select([selected_cols]).select_from(select_from)
rs = s.execute(merge_stmt)
for rst in rs:
merged_data = MergedData(
data=rst[0], connector=args.connector,
version=args.connector_version
)
s.add(merged_data)
s.commit()
s.close()
def execute(self, metadata, connection, filter_values):
try:
table = metadata.tables[self.table_name]
except KeyError:
raise TableNotFoundException("Unable to query table, table not found: %s" % self.table_name)
asha_table = self.get_asha_table(metadata)
max_date_query = sqlalchemy.select([
sqlalchemy.func.max(asha_table.c.date).label('date'),
asha_table.c.case_id.label('case_id')
])
if self.filters:
for filter in self.filters:
max_date_query.append_whereclause(filter.build_expression(table))
max_date_query.append_group_by(
asha_table.c.case_id
)
max_date_subquery = sqlalchemy.alias(max_date_query, 'max_date')
checklist_query = sqlalchemy.select()
for column in self.columns:
checklist_query.append_column(column.build_column(asha_table))
checklist_query = checklist_query.where(
asha_table.c.case_id == max_date_subquery.c.case_id
).where(
asha_table.c.date == max_date_subquery.c.date
)
return connection.execute(checklist_query, **filter_values).fetchall()
def test_functions_with_cols(self):
users = table(
'users',
column('id'),
column('name'),
column('fullname'))
calculate = select([column('q'), column('z'), column('r')], from_obj=[
func.calculate(
bindparam('x', None), bindparam('y', None)
)])
self.assert_compile(select([users], users.c.id > calculate.c.z),
"SELECT users.id, users.name, users.fullname "
"FROM users, (SELECT q, z, r "
"FROM calculate(:x, :y)) "
"WHERE users.id > z"
)
s = select([users], users.c.id.between(
calculate.alias('c1').unique_params(x=17, y=45).c.z,
calculate.alias('c2').unique_params(x=5, y=12).c.z))
self.assert_compile(
s, "SELECT users.id, users.name, users.fullname "
"FROM users, (SELECT q, z, r "
"FROM calculate(:x_1, :y_1)) AS c1, (SELECT q, z, r "
"FROM calculate(:x_2, :y_2)) AS c2 "
"WHERE users.id BETWEEN c1.z AND c2.z", checkparams={
'y_1': 45, 'x_1': 17, 'y_2': 12, 'x_2': 5})
def test_joins(self):
region = self.con.table("tpch_region")
nation = self.con.table("tpch_nation")
rt = self._to_sqla(region).alias("t0")
nt = self._to_sqla(nation).alias("t1")
ipred = region.r_regionkey == nation.n_regionkey
spred = rt.c.r_regionkey == nt.c.n_regionkey
fully_mat_joins = [
(region.inner_join(nation, ipred), rt.join(nt, spred)),
(region.left_join(nation, ipred), rt.join(nt, spred, isouter=True)),
(region.outer_join(nation, ipred), rt.outerjoin(nt, spred)),
]
for ibis_joined, joined_sqla in fully_mat_joins:
expected = sa.select(["*"]).select_from(joined_sqla)
self._compare_sqla(ibis_joined, expected)
subselect_joins = [
(region.inner_join(nation, ipred).projection(nation), rt.join(nt, spred)),
(region.left_join(nation, ipred).projection(nation), rt.join(nt, spred, isouter=True)),
(region.outer_join(nation, ipred).projection(nation), rt.outerjoin(nt, spred)),
]
for ibis_joined, joined_sqla in subselect_joins:
expected = sa.select([nt]).select_from(joined_sqla)
self._compare_sqla(ibis_joined, expected)
def _index_query(self, obj):
"""
Returns the query needed for fetching the index of this record relative
to version history.
"""
alias = sa.orm.aliased(obj)
subquery = (
sa.select([sa.func.count('1')], from_obj=[alias.__table__])
.where(
getattr(alias, tx_column_name(obj))
<
getattr(obj, tx_column_name(obj))
)
.correlate(alias.__table__)
.label('position')
)
query = (
sa.select([subquery], from_obj=[obj.__table__])
.where(sa.and_(*self._pk_correlation_condition(obj, False)))
.order_by(
getattr(obj.__class__, tx_column_name(obj))
)
)
return query
def thddeleteOldLogs(conn):
model = self.db.model
res = conn.execute(sa.select([sa.func.count(model.logchunks.c.content)]))
count1 = res.fetchone()[0]
res.close()
# update log types older than timestamps
# we do it first to avoid having UI discrepancy
res = conn.execute(
model.logs.update()
.where(model.logs.c.stepid.in_(
sa.select([model.steps.c.id])
.where(model.steps.c.started_at < older_than_timestamp)))
.values(type='d')
)
res.close()
# query all logs with type 'd' and delete their chunks.
q = sa.select([model.logs.c.id])
q = q.select_from(model.logs)
q = q.where(model.logs.c.type == 'd')
# delete their logchunks
res = conn.execute(
model.logchunks.delete()
.where(model.logchunks.c.logid.in_(q))
)
res.close()
res = conn.execute(sa.select([sa.func.count(model.logchunks.c.content)]))
count2 = res.fetchone()[0]
res.close()
return count1 - count2
def test_conn_reusable(self):
conn = db.connect()
conn.execute(select([1]))
assert len(dbapi.connections) == 1
dbapi.shutdown()
# raises error
try:
conn.execute(select([1]))
assert False
except tsa.exc.DBAPIError:
pass
assert not conn.closed
assert conn.invalidated
# ensure all connections closed (pool was recycled)
gc_collect()
assert len(dbapi.connections) == 0
# test reconnects
conn.execute(select([1]))
assert not conn.invalidated
assert len(dbapi.connections) == 1
def verify_thd(conn):
metadata = sa.MetaData()
metadata.bind = conn
buildworkers = sa.Table('buildworkers',
metadata, autoload=True)
configured_buildworkers = sa.Table('configured_buildworkers',
metadata, autoload=True)
connected_buildworkers = sa.Table('connected_buildworkers',
metadata, autoload=True)
q = sa.select([buildworkers])
self.assertEqual(map(dict, conn.execute(q).fetchall()), [
# (the info does not get de-JSON'd due to use of autoload)
{'id': 29, 'name': u'windows', 'info': '{}'}])
# check that the name column was resized
self.assertEqual(buildworkers.c.name.type.length, 50)
q = sa.select([configured_buildworkers.c.buildermasterid,
configured_buildworkers.c.buildworkerid])
self.assertEqual(conn.execute(q).fetchall(), [])
q = sa.select([connected_buildworkers.c.masterid,
connected_buildworkers.c.buildworkerid])
self.assertEqual(conn.execute(q).fetchall(), [])
def test_insert_from_select(self):
table = self.tables.manual_pk
config.db.execute(
table.insert(),
[
dict(id=1, data="data1"),
dict(id=2, data="data2"),
dict(id=3, data="data3"),
]
)
config.db.execute(
table.insert(inline=True).
from_select(
("id", "data",), select([table.c.id + 5, table.c.data]).where(
table.c.data.in_(["data2", "data3"]))
),
)
eq_(
config.db.execute(
select([table.c.data]).order_by(table.c.data)
).fetchall(),
[("data1", ), ("data2", ), ("data2", ),
("data3", ), ("data3", )]
)
def test_insert_from_select_cte_follows_insert_one(self):
dialect = default.DefaultDialect()
dialect.cte_follows_insert = True
table1 = self.tables.mytable
cte = select([table1.c.name]).where(table1.c.name == "bar").cte()
sel = select([table1.c.myid, table1.c.name]).where(
table1.c.name == cte.c.name
)
ins = self.tables.myothertable.insert().from_select(
("otherid", "othername"), sel
)
self.assert_compile(
ins,
"INSERT INTO myothertable (otherid, othername) "
"WITH anon_1 AS "
"(SELECT mytable.name AS name FROM mytable "
"WHERE mytable.name = :name_1) "
"SELECT mytable.myid, mytable.name FROM mytable, anon_1 "
"WHERE mytable.name = anon_1.name",
checkparams={"name_1": "bar"},
dialect=dialect,
)
def ping_connection(self, connection, branch):
if branch:
# "branch" refers to a sub-connection of a connection,
# we don't want to bother pinging on these.
return
try:
# run a SELECT 1. use a core select() so that
# the SELECT of a scalar value without a table is
# appropriately formatted for the backend
connection.scalar(select([1]))
except exc.DBAPIError as err:
# catch SQLAlchemy's DBAPIError, which is a wrapper
# for the DBAPI's exception. It includes a .connection_invalidated
# attribute which specifies if this connection is a "disconnect"
# condition, which is based on inspection of the original exception
# by the dialect in use.
if err.connection_invalidated:
# run the same SELECT again - the connection will re-validate
# itself and establish a new connection. The disconnect detection
# here also causes the whole connection pool to be invalidated
# so that all stale connections are discarded.
connection.scalar(select([1]))
else:
raise
def store_initial_soil_water(engine, fname_HDFstore, args):
"""retrieves the INITIAL_SOIL_WATER table from a CGMS12 database.
if the --crop_no option is used only the records for the given crop_no
will be retrieved.
"""
meta = MetaData(engine)
tbl_isw = Table("initial_soil_water", meta, autoload=True)
# retrieve distinct crop types from DB table
if args.crop_no is not None:
s = sa.select([tbl_isw.c.crop_no]).distinct()
crops = [row[0] for row in s.execute()]
if args.crop_no not in crops:
print("Crop ID specified with --cropno (%s) not found in INITIAL_SOIL_WATER table! Returning..." % args.crop_no)
sys.exit()
# Select distinct years to iterate over
s = sa.select([tbl_isw.c.year]).distinct()
years = s.execute()
dataset_name = "/initial_soil_water"
with pd.io.pytables.HDFStore(fname_HDFstore) as store:
for yr, in sorted(years):
if args.crop_no:
s = tbl_isw.select().where(sa.and_(tbl_isw.c.year == yr,
tbl_isw.c.crop_no == args.crop_no))
print("Storing initial_soil_water for crop %i and year %i" % (args.crop_no, yr))
else:
s = tbl_isw.select().where(tbl_isw.c.year == yr)
print("Storing initial_soil_water for year %i" % yr)
df_isw = pd.read_sql(s, engine)
if dataset_name in store:
store.append(dataset_name, df_isw, data_columns=["grid_no", "stu_no", "crop_no", "year"])
else:
store.put(dataset_name, df_isw, format="table", data_columns=["grid_no", "stu_no", "crop_no", "year"])
def test_insert_from_select(self):
table = self.tables.autoinc_pk
config.db.execute(
table.insert(),
[
dict(data="data1"),
dict(data="data2"),
dict(data="data3"),
]
)
config.db.execute(
table.insert().
from_select(
("data",), select([table.c.data]).where(
table.c.data.in_(["data2", "data3"]))
),
)
eq_(
config.db.execute(
select([table.c.data]).order_by(table.c.data)
).fetchall(),
[("data1", ), ("data2", ), ("data2", ),
("data3", ), ("data3", )]
)
def apply_default_value(self, column):
if column.default:
execute = self.table.migration.conn.execute
val = column.default.arg
table = self.table.migration.metadata.tables[self.table.name]
table.append_column(column)
cname = getattr(table.c, column.name)
if column.default.is_callable:
Table = self.table.migration.metadata.tables['system_model']
Column = self.table.migration.metadata.tables['system_column']
j1 = join(Table, Column, Table.c.name == Column.c.model)
query = select([Column.c.name]).select_from(j1)
query = query.where(Column.c.primary_key.is_(True))
query = query.where(Table.c.table == self.table.name)
columns = [x[0] for x in execute(query).fetchall()]
query = select([func.count()]).select_from(table)
query = query.where(cname.is_(None))
nb_row = self.table.migration.conn.execute(query).fetchone()[0]
for offset in range(nb_row):
query = select(columns).select_from(table)
query = query.where(cname.is_(None)).limit(1)
res = execute(query).fetchone()
where = and_(
*[getattr(table.c, x) == res[x] for x in columns])
query = update(table).where(where).values(
{cname: val(None)})
execute(query)
else:
query = update(table).where(cname.is_(None)).values(
{cname: val})
execute(query)
def upgrade(active_plugins=None, options=None):
bind = op.get_bind()
branches = list(bind.execute(
sa.select(columns=['id', 'name', 'project_id'],
from_obj=sa.Table('branches', sa.MetaData()))))
projects = list(bind.execute(
sa.select(columns=['id'], from_obj=sa.Table('projects',
sa.MetaData()))))
if len(projects) > 0:
branch_dict = {}
for branch in branches:
branch_dict[(branch['project_id'], branch['name'])] = branch['id']
tasks_table = table(
'tasks',
sa.Column('project_id', sa.Integer(), nullable=True),
sa.Column('branch_id', sa.Integer(), nullable=True),
)
bind.execute(tasks_table.update().
where(tasks_table.c.project_id.is_(None)).
values(project_id=projects[0].id))
for project in projects:
bind.execute(
tasks_table.update().
where(tasks_table.c.project_id == project['id']).
values(branch_id=branch_dict[(project['id'], "master")])
)
sqlalchemy_opentracing.init_tracing(tracer)
sqlalchemy_opentracing.register_engine(engine)
span = tracer.start_span('create sample')
# 1. Create a table
metadata = MetaData()
users = Table(
'users',
metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
)
creat = CreateTable(users)
sqlalchemy_opentracing.set_parent_span(creat, span)
conn.execute(creat)
# 2. Insert a single value.
ins = users.insert().values(name='John Doe', id=1)
sqlalchemy_opentracing.set_parent_span(ins, span)
conn.execute(ins)
# 3. Select the new value.
sel = select([users])
sqlalchemy_opentracing.set_parent_span(sel, span)
print conn.execute(sel).fetchone()
span.finish()
tracer.flush()
def get_user_by_uid(uid):
query = select([users.c.id, users.c.uid, users.c.name]) \
.where(users.c.uid == uid)
result = conn.execute(query)
return result.fetchone()
def __init__(self, callback):
super(GeographyMenu, self).__init__()
geography_table = Geography.__table__
geos = select([
geography_table.c.id, geography_table.c.name,
geography_table.c.parent_id
]).execute().fetchall()
geos_hash = {}
# TODO: i think the geo_hash should be calculated in an idle
# function so that starting the editor isn't delayed while the
# hash is being built
for geo_id, name, parent_id in geos:
try:
geos_hash[parent_id].append((geo_id, name))
except KeyError:
geos_hash[parent_id] = [(geo_id, name)]
for kids in geos_hash.values():
kids.sort(key=itemgetter(1)) # sort by name
def get_kids(pid):
try:
return geos_hash[pid]
except KeyError:
return []
def has_kids(pid):
try:
return len(geos_hash[pid]) > 0
except KeyError:
return False
def build_menu(geo_id, name):
item = gtk.MenuItem(name)
if not has_kids(geo_id):
if item.get_submenu() is None:
item.connect('activate', callback, geo_id)
# self.view.connect(item, 'activate',
# self.on_activate_add_menu_item, geo_id)
return item
kids_added = False
submenu = gtk.Menu()
# removes two levels of kids with the same name, there must be a
# better way to do this but i got tired of thinking about it
kids = get_kids(geo_id)
if len(kids) > 0:
kids_added = True
for kid_id, kid_name in kids: # get_kids(geo_id):
submenu.append(build_menu(kid_id, kid_name))
if kids_added:
sel_item = gtk.MenuItem(name)
submenu.insert(sel_item, 0)
submenu.insert(gtk.SeparatorMenuItem(), 1)
item.set_submenu(submenu)
#self.view.connect(sel_item, 'activate',callback, geo_id)
sel_item.connect('activate', callback, geo_id)
else:
item.connect('activate', callback, geo_id)
return item
def populate():
"""
add geography value to the menu, any top level items that don't
have any kids are appended to the bottom of the menu
"""
if not geos_hash:
# we would get here if the Geography menu is populate,
# usually during a unit test
return
no_kids = []
for geo_id, geo_name in geos_hash[None]:
if geo_id not in geos_hash.keys():
no_kids.append((geo_id, geo_name))
else:
self.append(build_menu(geo_id, geo_name))
for geo_id, geo_name in sorted(no_kids):
self.append(build_menu(geo_id, geo_name))
self.show_all()
import gobject
gobject.idle_add(populate)
def get_total_row_count(self, db: Session) -> int:
"""Return the total number of rows."""
return len(db.execute(select(self.model)).all())
async def mutate(cls, root, info, email, props):
async with info.context['dbpool'].acquire() as conn, conn.begin():
username = props.username if props.username else email
data = {
'username': username,
'email': email,
'password': props.password,
'need_password_change': props.need_password_change,
'full_name': props.full_name,
'description': props.description,
'is_active': props.is_active,
'domain_name': props.domain_name,
'role': UserRole(props.role),
}
try:
query = (users.insert().values(data))
result = await conn.execute(query)
if result.rowcount > 0:
# Read the created user data from DB.
checkq = users.select().where(users.c.email == email)
result = await conn.execute(checkq)
o = User.from_row(await result.first())
# Create user's first access_key and secret_key.
from .keypair import generate_keypair, keypairs
ak, sk = generate_keypair()
is_admin = True if data['role'] in [
UserRole.SUPERADMIN, UserRole.ADMIN
] else False
kp_data = {
'user_id': email,
'access_key': ak,
'secret_key': sk,
'is_active': True,
'is_admin': is_admin,
'resource_policy': 'default',
'concurrency_used': 0,
'rate_limit': 1000,
'num_queries': 0,
'user': o.uuid,
}
query = (keypairs.insert().values(kp_data))
await conn.execute(query)
# Add user to groups if group_ids parameter is provided.
from .group import association_groups_users, groups
if props.group_ids:
query = (sa.select([
groups.c.id
]).select_from(groups).where(
groups.c.domain_name == props.domain_name).where(
groups.c.id.in_(props.group_ids)))
result = await conn.execute(query)
grps = await result.fetchall()
if grps:
values = [{
'user_id': o.uuid,
'group_id': grp.id
} for grp in grps]
query = association_groups_users.insert().values(
values)
await conn.execute(query)
return cls(ok=True, msg='success', user=o)
else:
return cls(ok=False,
msg='failed to create user',
user=None)
except (pg.IntegrityError, sa.exc.IntegrityError) as e:
return cls(ok=False, msg=f'integrity error: {e}', user=None)
except (asyncio.CancelledError, asyncio.TimeoutError):
raise
except Exception as e:
return cls(ok=False, msg=f'unexpected error: {e}', user=None)
def one_by_unique_key(self, db: Session, unique_key_name: str, unique_key_value: Any) -> ModelType:
"""Return exactly one row specified by unique_key or raise an exception."""
statement = select(self.model).where(getattr(self.model, unique_key_name) == unique_key_value)
return db.execute(statement).one()[0]
from sqlalchemy import create_engine, Table, MetaData
from sqlalchemy import select, or_, and_, not_, desc
from sqlalchemy import func #to use .sum() and .count() don't import those directly, python conflicts
#connect to DB
engine = create_engine("sqlite:///census.sqlite")
connection = engine.connect()
#set up metadata
metadata = MetaData()
#get the table (there is only one in census.sqlite)
census = Table('census', metadata, autoload=True, autoload_with=engine)
#you can use .distinct() which is an SQL command, to only grab distinct values
stmt = select([census.columns.state.distinct()])
stmt = stmt.order_by(census.columns.state)
results = connection.execute(stmt).fetchall()
print("All States:" + '*' * 77 + "\n", results)
#there are some operations you can do in SQL that are more efficient than doing them in python
#examples: Count, Sum
#these two are called aggregation functions, cause they collapse multiple records into one
#to use it in an SQLAlchemy select query, do this
stmt = select([func.sum(census.columns.pop2008)])
#execute the query and get the results from a ResultProxy
results = connection.execute(stmt).fetchall()
print("Sum of US population from the census 2008:", results, 30 * '*')
def update_area(self, session, areaid):
# Select all cells in this area and derive a bounding box for them
radio, mcc, mnc, lac = decode_cellarea(areaid)
load_fields = ('cellid', 'lat', 'lon', 'radius', 'region', 'last_seen',
'max_lat', 'max_lon', 'min_lat', 'min_lon')
shard = self.cell_model.shard_model(radio)
fields = [getattr(shard.__table__.c, f) for f in load_fields]
cells = session.execute(
select(fields)
.where(shard.__table__.c.radio == radio)
.where(shard.__table__.c.mcc == mcc)
.where(shard.__table__.c.mnc == mnc)
.where(shard.__table__.c.lac == lac)
.where(shard.__table__.c.lat.isnot(None))
.where(shard.__table__.c.lon.isnot(None))
).fetchall()
if len(cells) == 0:
# If there are no more underlying cells, delete the area entry
session.execute(
delete(self.area_table)
.where(self.area_table.c.areaid == areaid)
)
return
# Otherwise update the area entry based on all the cells
area = session.execute(
select([self.area_table.c.areaid,
self.area_table.c.modified,
self.area_table.c.lat,
self.area_table.c.lon,
self.area_table.c.radius,
self.area_table.c.region,
self.area_table.c.avg_cell_radius,
self.area_table.c.num_cells,
self.area_table.c.last_seen,
])
.where(self.area_table.c.areaid == areaid)
).fetchone()
cell_extremes = numpy.array([
(numpy.nan if cell.max_lat is None else cell.max_lat,
numpy.nan if cell.max_lon is None else cell.max_lon)
for cell in cells] + [
(numpy.nan if cell.min_lat is None else cell.min_lat,
numpy.nan if cell.min_lon is None else cell.min_lon)
for cell in cells
], dtype=numpy.double)
max_lat, max_lon = numpy.nanmax(cell_extremes, axis=0)
min_lat, min_lon = numpy.nanmin(cell_extremes, axis=0)
ctr_lat, ctr_lon = numpy.array(
[(c.lat, c.lon) for c in cells],
dtype=numpy.double).mean(axis=0)
ctr_lat = float(ctr_lat)
ctr_lon = float(ctr_lon)
radius = circle_radius(
ctr_lat, ctr_lon, max_lat, max_lon, min_lat, min_lon)
cell_radii = numpy.array([
(numpy.nan if cell.radius is None else cell.radius)
for cell in cells
], dtype=numpy.int32)
avg_cell_radius = int(round(numpy.nanmean(cell_radii)))
num_cells = len(cells)
region = self.region(ctr_lat, ctr_lon, mcc, cells)
last_seen = None
cell_last_seen = set([cell.last_seen for cell in cells
if cell.last_seen is not None])
if cell_last_seen:
last_seen = max(cell_last_seen)
if area is None:
session.execute(
self.area_table.insert(
mysql_on_duplicate='num_cells = num_cells' # no-op
).values(
areaid=areaid,
radio=radio,
mcc=mcc,
mnc=mnc,
lac=lac,
created=self.utcnow,
modified=self.utcnow,
lat=ctr_lat,
lon=ctr_lon,
radius=radius,
region=region,
avg_cell_radius=avg_cell_radius,
num_cells=num_cells,
last_seen=last_seen,
)
)
else:
session.execute(
self.area_table.update()
.where(self.area_table.c.areaid == areaid)
.values(
modified=self.utcnow,
lat=ctr_lat,
lon=ctr_lon,
radius=radius,
region=region,
avg_cell_radius=avg_cell_radius,
num_cells=num_cells,
last_seen=last_seen,
)
)
def all(self, db: Session) -> list[ModelType]:
"""Return all objects queried from database."""
return db.execute(select(self.model)).scalars().all()
def set_taxonomy(cls, taxon_dataframe):
"""
Set the taxonomy. If a taxonomy already exist, delete it and set the
new one.
:param taxon_dataframe: A dataframe containing the taxonomy to set.
The dataframe must contain at least the following columns:
taxon_id -> The id of the taxon (must be the index of the
DataFrame).
parent_id -> The id of the taxon's parent.
rank -> The rank of the taxon (must be a value in:
'REGNUM', 'PHYLUM', 'CLASSIS', 'ORDO', 'FAMILIA',
'GENUS', 'SPECIES', 'INFRASPECIES')
full_name -> The full name of the taxon.
rank_name -> The rank name of the taxon.
The remaining columns will be stored as synonyms, using the column
name.
"""
LOGGER.debug("Starting set_taxonomy...")
required_columns = {'parent_id', 'rank', 'full_name', 'rank_name'}
cols = set(list(taxon_dataframe.columns))
inter = cols.intersection(required_columns)
synonym_cols = cols.difference(required_columns)
if cls.IDENTITY_SYNONYM_KEY in synonym_cols:
synonym_cols = synonym_cols.difference({cls.IDENTITY_SYNONYM_KEY})
m = "The '{}' synonym key is a special key reserved by Niamoto," \
"this column will be ignored."
LOGGER.warning(m.format(cls.IDENTITY_SYNONYM_KEY))
if not inter == required_columns:
m = "The taxon dataframe does not contains the required " \
"columns {}, csv has: {}".format(required_columns, cols)
raise MalformedDataSourceError(m)
if len(synonym_cols) > 0:
LOGGER.debug(
"The following synonym keys had been detected: {}".format(
','.join(synonym_cols)))
synonyms = taxon_dataframe[list(synonym_cols)].apply(
lambda x: x.to_json(), axis=1)
else:
LOGGER.debug("No synonym keys had been detected.")
synonyms = '{}'
taxon_dataframe.drop(synonym_cols, axis=1, inplace=True)
taxon_dataframe['synonyms'] = synonyms
mptt = ['mptt_tree_id', 'mptt_depth', 'mptt_left', 'mptt_right']
for col in mptt:
taxon_dataframe[col] = 0
taxon_dataframe = cls.construct_mptt(taxon_dataframe)
taxon_dataframe['taxon_id'] = taxon_dataframe.index
taxon_dataframe = taxon_dataframe.astype(object).where(
pd.notnull(taxon_dataframe), None)
with Connector.get_connection() as connection:
current_synonym_keys = set(
pd.read_sql(select([meta.synonym_key_registry.c.name]),
connection)['name'])
to_add = synonym_cols.difference(current_synonym_keys)
to_delete = current_synonym_keys.difference(
synonym_cols).difference({cls.IDENTITY_SYNONYM_KEY})
to_keep = current_synonym_keys.intersection(synonym_cols)
if len(to_delete) > 0:
prov = meta.data_provider
syno = meta.synonym_key_registry
providers_to_update = pd.read_sql(
select([prov.c.name]).select_from(
prov.join(syno,
syno.c.id == prov.c.synonym_key_id)).where(
syno.c.name.in_(to_delete)), connection)
msg = "The following synonym keys will be deleted: {}. " \
"The following data providers where depending on those" \
" synonym keys: {} Please consider updating " \
"them, or updating the taxonomy."
LOGGER.warning(
msg.format(to_delete, set(providers_to_update['name'])))
with connection.begin():
connection.execute("SET CONSTRAINTS ALL DEFERRED;")
# Unregister synonym keys
cls.unregister_all_synonym_keys(
bind=connection,
exclude=to_keep,
)
# Delete existing taxonomy
cls.delete_all_taxa(bind=connection)
# Register synonym cols
for synonym_key in to_add:
cls.register_synonym_key(synonym_key, bind=connection)
# Insert the data
LOGGER.debug("Inserting the taxonomy in database...")
if len(taxon_dataframe) > 0:
ins = meta.taxon.insert().values(
id=bindparam('taxon_id'),
full_name=bindparam('full_name'),
rank_name=bindparam('rank_name'),
rank=bindparam('rank'),
parent_id=bindparam('parent_id'),
synonyms=cast(bindparam('synonyms'), JSONB),
mptt_left=bindparam('mptt_left'),
mptt_right=bindparam('mptt_right'),
mptt_tree_id=bindparam('mptt_tree_id'),
mptt_depth=bindparam('mptt_depth'),
)
result = connection.execute(
ins,
taxon_dataframe.to_dict(orient='records')).rowcount
else:
result = 0
m = "The taxonomy had been successfully set ({} taxa " \
"inserted)!"
LOGGER.debug(m.format(result))
return result, synonym_cols
def one(self, db: Session, id_: Any) -> Optional[ModelType]:
"""Return exactly one row or raise an exception."""
statement = select(self.model).where(self.model.id == id_)
return db.execute(statement).one()[0]
def column_reflection_fallback(
selectable: Select, dialect: Dialect, sqlalchemy_engine: Engine
) -> List[Dict[str, str]]:
"""If we can't reflect the table, use a query to at least get column names."""
col_info_dict_list: List[Dict[str, str]]
# noinspection PyUnresolvedReferences
if dialect.name.lower() == "mssql":
# Get column names and types from the database
# Reference: https://dataedo.com/kb/query/sql-server/list-table-columns-in-database
tables_table_clause: TableClause = sa.table(
"tables",
sa.column("object_id"),
sa.column("schema_id"),
sa.column("name"),
schema="sys",
).alias("sys_tables_table_clause")
tables_table_query: Select = (
sa.select(
[
tables_table_clause.c.object_id.label("object_id"),
sa.func.schema_name(tables_table_clause.c.schema_id).label(
"schema_name"
),
tables_table_clause.c.name.label("table_name"),
]
)
.select_from(tables_table_clause)
.alias("sys_tables_table_subquery")
)
columns_table_clause: TableClause = sa.table(
"columns",
sa.column("object_id"),
sa.column("user_type_id"),
sa.column("column_id"),
sa.column("name"),
sa.column("max_length"),
sa.column("precision"),
schema="sys",
).alias("sys_columns_table_clause")
columns_table_query: Select = (
sa.select(
[
columns_table_clause.c.object_id.label("object_id"),
columns_table_clause.c.user_type_id.label("user_type_id"),
columns_table_clause.c.column_id.label("column_id"),
columns_table_clause.c.name.label("column_name"),
columns_table_clause.c.max_length.label("column_max_length"),
columns_table_clause.c.precision.label("column_precision"),
]
)
.select_from(columns_table_clause)
.alias("sys_columns_table_subquery")
)
types_table_clause: TableClause = sa.table(
"types",
sa.column("user_type_id"),
sa.column("name"),
schema="sys",
).alias("sys_types_table_clause")
types_table_query: Select = (
sa.select(
[
types_table_clause.c.user_type_id.label("user_type_id"),
types_table_clause.c.name.label("column_data_type"),
]
)
.select_from(types_table_clause)
.alias("sys_types_table_subquery")
)
inner_join_conditions: BinaryExpression = sa.and_(
*(tables_table_query.c.object_id == columns_table_query.c.object_id,)
)
outer_join_conditions: BinaryExpression = sa.and_(
*(
columns_table_query.columns.user_type_id
== types_table_query.columns.user_type_id,
)
)
col_info_query: Select = (
sa.select(
[
tables_table_query.c.schema_name,
tables_table_query.c.table_name,
columns_table_query.c.column_id,
columns_table_query.c.column_name,
types_table_query.c.column_data_type,
columns_table_query.c.column_max_length,
columns_table_query.c.column_precision,
]
)
.select_from(
tables_table_query.join(
right=columns_table_query,
onclause=inner_join_conditions,
isouter=False,
).join(
right=types_table_query,
onclause=outer_join_conditions,
isouter=True,
)
)
.where(tables_table_query.c.table_name == selectable.name)
.order_by(
tables_table_query.c.schema_name.asc(),
tables_table_query.c.table_name.asc(),
columns_table_query.c.column_id.asc(),
)
)
col_info_tuples_list: List[tuple] = sqlalchemy_engine.execute(
col_info_query
).fetchall()
# type_module = _get_dialect_type_module(dialect=dialect)
col_info_dict_list: List[Dict[str, str]] = [
{
"name": column_name,
# "type": getattr(type_module, column_data_type.upper())(),
"type": column_data_type.upper(),
}
for schema_name, table_name, column_id, column_name, column_data_type, column_max_length, column_precision in col_info_tuples_list
]
elif dialect.name.lower() == "trino":
try:
table_name = selectable.name
except AttributeError:
table_name = selectable
tables_table: sa.Table = sa.Table(
"tables",
sa.MetaData(),
schema="information_schema",
)
tables_table_query: Select = (
sa.select(
[
sa.column("table_schema").label("schema_name"),
sa.column("table_name").label("table_name"),
]
)
.select_from(tables_table)
.alias("information_schema_tables_table")
)
columns_table: sa.Table = sa.Table(
"columns",
sa.MetaData(),
schema="information_schema",
)
columns_table_query: Select = (
sa.select(
[
sa.column("column_name").label("column_name"),
sa.column("table_name").label("table_name"),
sa.column("table_schema").label("schema_name"),
sa.column("data_type").label("column_data_type"),
]
)
.select_from(columns_table)
.alias("information_schema_columns_table")
)
conditions = sa.and_(
*(
tables_table_query.c.table_name == columns_table_query.c.table_name,
tables_table_query.c.schema_name == columns_table_query.c.schema_name,
)
)
col_info_query: Select = (
sa.select(
[
tables_table_query.c.schema_name,
tables_table_query.c.table_name,
columns_table_query.c.column_name,
columns_table_query.c.column_data_type,
]
)
.select_from(
tables_table_query.join(
right=columns_table_query, onclause=conditions, isouter=False
)
)
.where(tables_table_query.c.table_name == table_name)
.order_by(
tables_table_query.c.schema_name.asc(),
tables_table_query.c.table_name.asc(),
columns_table_query.c.column_name.asc(),
)
.alias("column_info")
)
col_info_tuples_list: List[tuple] = sqlalchemy_engine.execute(
col_info_query
).fetchall()
# type_module = _get_dialect_type_module(dialect=dialect)
col_info_dict_list: List[Dict[str, str]] = [
{
"name": column_name,
"type": column_data_type.upper(),
}
for schema_name, table_name, column_name, column_data_type in col_info_tuples_list
]
else:
# if a custom query was passed
if isinstance(selectable, TextClause):
query: TextClause = selectable
else:
query: Select = sa.select([sa.text("*")]).select_from(selectable).limit(1)
result_object = sqlalchemy_engine.execute(query)
# noinspection PyProtectedMember
col_names: List[str] = result_object._metadata.keys
col_info_dict_list = [{"name": col_name} for col_name in col_names]
return col_info_dict_list
from db_settings import parse_results, engine
from pywhois import whois
import pywhois
from sqlalchemy import select
some = list()
conn = engine.connect()
urls = conn.execute(select([parse_results.c.id, parse_results.c.response_domain])
.where(parse_results.c.response_code == 'ClientConnectorError').offset(1000))
urls = list(urls)
for url in urls:
try:
w = whois.whois(url[1])
exp_date = w.registrar
if not exp_date:
some.append(url[1])
print(url[1], exp_date)
except pywhois.whois.parser.PywhoisError:
some.append(url[1])
except Exception as err:
print(url[1], type(err))
with open('result.txt', 'wt', encoding='utf-8') as file:
file.write('\n'.join(str(line) for line in some))
def test_scalar_strings(self, scalar_strings, connection):
fn = func.scalar_strings(5)
result = connection.execute(select(fn.column_valued())).scalars().all()
eq_(result, ["some string"] * 5)
def related_users_query(city_id):
cols = [user.c.id]
vtab = subscription.join(user).join(weather_state).join(city)
cond = city.c.id == city_id
query = select(cols).select_from(vtab).where(cond)
return query
def test_ip_addrs_vip_name_exists(self):
result = db.execute(
sa.select([self.meta.tables['ip_addrs'].c.vip_name]))
self.assertEqual(result.scalar(), "management")
def test_two_strings(self, two_strings, connection):
fn = func.three_pairs().table_valued("string1", "string2")
result = connection.execute(select(fn.c.string1, fn.c.string2)).all()
eq_(result, [("a", "b"), ("c", "d"), ("e", "f")])
def test_for_update_expr(self):
engine = self._fixture(True)
s1 = select([1], for_update=True)
result = engine.execute(s1)
assert result.cursor.name
def test_deployment_graph_tasks_creation_success(self):
deployment_graph_id = self._insert_deployment_graph()
tasks = [{
'task_name':
'task1',
'deployment_graph_id':
deployment_graph_id,
'version':
'2.0.0',
'type':
'puppet',
'condition':
None,
'requires': ['a', 'b'],
'required_for': ['c', 'd'],
'refresh_on': ['r1', 'r2'],
'cross_depends':
jsonutils.dumps([{
'name': 'a'
}, {
'name': 'b'
}]),
'cross_depended_by':
jsonutils.dumps([{
'name': 'c'
}, {
'name': 'd'
}]),
'reexecute_on': ["nailgun_event1", "nailgun_event2"],
'groups': ['group1', 'group2'],
'roles': ['role1', 'role2'],
'tasks': ['t1', 't2'],
'parameters':
jsonutils.dumps({'param1': 'val1'}),
'_custom':
jsonutils.dumps({}),
}, {
'task_name':
'task2',
'deployment_graph_id':
deployment_graph_id,
'version':
'2.0.0',
'type':
'puppet',
'condition':
None,
'requires': ['task1'],
'required_for': ['c', 'd'],
'refresh_on': [],
'cross_depends':
jsonutils.dumps([{
'name': 'task1'
}]),
'cross_depended_by':
jsonutils.dumps([{
'name': 'c'
}, {
'name': 'd'
}]),
'reexecute_on': ["nailgun_event3", "nailgun_event4"],
'groups': ['group3', 'group4'],
'roles': ['role3', 'role4'],
'tasks': [],
'parameters':
jsonutils.dumps({'param2': 'val2'}),
'_custom':
jsonutils.dumps({}),
}]
db.execute(self.meta.tables['deployment_graph_tasks'].insert(), tasks)
db.commit()
result = db.execute(
sa.select([self.meta.tables['deployment_graph_tasks']]).where(
sa.text(
'deployment_graph_tasks.deployment_graph_id = {0}'.format(
deployment_graph_id))))
db_tasks = [dict(r) for r in result]
for d in db_tasks:
d.pop('id', None)
self.assertItemsEqual(tasks, db_tasks)
def test_stmt_option_disabled(self):
engine = self._fixture(True)
s = select([1]).execution_options(stream_results=False)
result = engine.execute(s)
assert not result.cursor.name
def email(self):
return (
select([Email.email])
.where((Email.user_id == self.id) & (Email.primary.is_(True)))
.as_scalar()
)
