def sequence_next_number(self, connection, name):
cursor = connection.cursor()
version = self.get_version(connection)
if version >= (10, 0):
cursor.execute(
'SELECT increment_by '
'FROM pg_sequences '
'WHERE sequencename=%s',
(name,))
increment, = cursor.fetchone()
cursor.execute(
SQL(
'SELECT CASE WHEN NOT is_called THEN last_value '
'ELSE last_value + %s '
'END '
'FROM {}').format(Identifier(name)),
(increment,))
else:
cursor.execute(
SQL(
'SELECT CASE WHEN NOT is_called THEN last_value '
'ELSE last_value + increment_by '
'END '
'FROM {}').format(sequence=Identifier(name)))
return cursor.fetchone()[0]
def fetchone(self):
if self._prefetch:
res = _cursor.fetchone(self)
if self._query_executed:
self._build_index()
if not self._prefetch:
res = _cursor.fetchone(self)
return res
def fetchone(self):
if self._prefetch:
res = _cursor.fetchone(self)
if self._query_executed:
self._build_index()
if not self._prefetch:
res = _cursor.fetchone(self)
return res
def next(self):
if self._prefetch:
res = _cursor.fetchone(self)
if res is None:
raise StopIteration()
if self._query_executed:
self._build_index()
if not self._prefetch:
res = _cursor.fetchone(self)
if res is None:
raise StopIteration()
return res
def next(self):
if self._prefetch:
res = _cursor.fetchone(self)
if res is None:
raise StopIteration()
if self._query_executed:
self._build_index()
if not self._prefetch:
res = _cursor.fetchone(self)
if res is None:
raise StopIteration()
return res
def get_score_meta(db: connection, chain_id: int,
score_name: str) -> Optional[Dict]:
cursor = db.cursor()
cursor.execute('SELECT batch_id FROM chain_meta WHERE id=%s', [chain_id])
chain_res = cursor.fetchone()
if chain_res is None:
return f'Chain {chain_id} not found.'
cursor.execute('SELECT * FROM scores WHERE name=%s AND batch_id=%s',
[score_name, chain_res.batch_id])
res = cursor.fetchone()
if res is None:
return None
return res._asdict()
def init(self):
from trytond.modules import get_module_info
connection = self.get_connection()
try:
cursor = connection.cursor()
sql_file = os.path.join(os.path.dirname(__file__), 'init.sql')
with open(sql_file) as fp:
for line in fp.read().split(';'):
if (len(line) > 0) and (not line.isspace()):
cursor.execute(line)
for module in ('ir', 'res'):
state = 'not activated'
if module in ('ir', 'res'):
state = 'to activate'
info = get_module_info(module)
cursor.execute('SELECT NEXTVAL(\'ir_module_id_seq\')')
module_id = cursor.fetchone()[0]
cursor.execute(
'INSERT INTO ir_module '
'(id, create_uid, create_date, name, state) '
'VALUES (%s, %s, now(), %s, %s)',
(module_id, 0, module, state))
for dependency in info.get('depends', []):
cursor.execute(
'INSERT INTO ir_module_dependency '
'(create_uid, create_date, module, name) '
'VALUES (%s, now(), %s, %s)',
(0, module_id, dependency))
connection.commit()
finally:
self.put_connection(connection)
def init(self):
from trytond.modules import get_module_info
connection = self.get_connection()
cursor = connection.cursor()
sql_file = os.path.join(os.path.dirname(__file__), 'init.sql')
with open(sql_file) as fp:
for line in fp.read().split(';'):
if (len(line) > 0) and (not line.isspace()):
cursor.execute(line)
for module in ('ir', 'res'):
state = 'uninstalled'
if module in ('ir', 'res'):
state = 'to install'
info = get_module_info(module)
cursor.execute('SELECT NEXTVAL(\'ir_module_id_seq\')')
module_id = cursor.fetchone()[0]
cursor.execute('INSERT INTO ir_module '
'(id, create_uid, create_date, name, state) '
'VALUES (%s, %s, now(), %s, %s)',
(module_id, 0, module, state))
for dependency in info.get('depends', []):
cursor.execute('INSERT INTO ir_module_dependency '
'(create_uid, create_date, module, name) '
'VALUES (%s, now(), %s, %s)',
(0, module_id, dependency))
connection.commit()
self.put_connection(connection)
def fetchone(self):
t = _cursor.fetchone(self)
if t is not None:
nt = self.Record
if nt is None:
nt = self.Record = self._make_nt()
return nt(*t)
def timetable_variant(
variant_id: int, connection: Maybe[connection] = Nothing()
) -> Either[str, TimetableVariant]:
with connection.or_else_lazy(default_connection) as conn:
with conn.cursor() as cursor:
cursor.execute(
"""
select route_name from timetable_variants
where id = %s
""",
[variant_id],
)
variant_row = cursor.fetchone()
if variant_row is None:
return Left(f"Variant {variant_id} not in database")
route_name = variant_row[0]
cursor.execute(
"""
select stop from variant_stops
where variant = %s
order by position asc
""",
[variant_id],
)
stop_ids = (stop for (stop, ) in cursor.fetchall())
stops = tuple(stop_by_id(conn, StopId(s)) for s in stop_ids)
if all(map(lambda s: isinstance(s, Just), stops)):
just_stops = cast(Tuple[Just[Stop], ...], stops)
return Right(
TimetableVariant(route_name,
tuple(s.value for s in just_stops)))
else:
return Left(
"Error in database, stop in variant_stops but not in stops"
)
def timetable(
timetable_id: int, connection: Maybe[connection] = Nothing()
) -> Either[str, Timetable]:
with connection.or_else_lazy(default_connection) as conn:
with conn.cursor() as cursor:
cursor.execute(
"""
select caption from timetables
where id = %s
""",
[timetable_id],
)
result = cursor.fetchone()
if result is None:
return Left(f"Timetable {timetable_id} not in database")
caption = result[0]
cursor.execute(
"""
select id from timetable_variants
where timetable_id = %s
""",
[timetable_id],
)
variants = (timetable_variant(id, Just(conn))
for (id, ) in cursor.fetchall())
return Right(
Timetable(caption,
{v.value
for v in variants if isinstance(v, Right)}))
def fetchone(self):
t = _cursor.fetchone(self)
if t is not None:
nt = self.Record
if nt is None:
nt = self.Record = self._make_nt()
return nt(*t)
def get_snapshot(db: connection, chain_id: int,
step: int) -> Optional[Dict[str, List]]:
cursor = db.cursor()
cursor.execute(queries['snapshot'], {'chain_id': chain_id, 'step': step})
snapshot = cursor.fetchone()
if snapshot is None:
return None
return snapshot._asdict()
def get_version(self, connection):
if self.name not in self._version_cache:
cursor = connection.cursor()
cursor.execute('SELECT version()')
version, = cursor.fetchone()
self._version_cache[self.name] = tuple(map(int,
RE_VERSION.search(version).groups()))
return self._version_cache[self.name]
def get_version(self, connection):
if self.name not in self._version_cache:
cursor = connection.cursor()
cursor.execute('SHOW server_version_num')
version, = cursor.fetchone()
major, rest = divmod(int(version), 10000)
minor, patch = divmod(rest, 100)
self._version_cache[self.name] = (major, minor, patch)
return self._version_cache[self.name]
def get_version(self, connection):
if self.name not in self._version_cache:
cursor = connection.cursor()
cursor.execute('SELECT version()')
version, = cursor.fetchone()
self._version_cache[self.name] = tuple(
map(int,
RE_VERSION.search(version).groups()))
return self._version_cache[self.name]
def current_user(self):
if self._current_user is None:
connection = self.get_connection()
try:
cursor = connection.cursor()
cursor.execute('SELECT current_user')
self._current_user = cursor.fetchone()[0]
finally:
self.put_connection(connection)
return self._current_user
def current_user(self):
if self._current_user is None:
connection = self.get_connection()
try:
cursor = connection.cursor()
cursor.execute('SELECT current_user')
self._current_user = cursor.fetchone()[0]
finally:
self.put_connection(connection)
return self._current_user
def sequence_next_number(self, connection, name):
cursor = connection.cursor()
version = self.get_version(connection)
if version >= (10, 0):
cursor.execute(
'SELECT increment_by '
'FROM pg_sequences '
'WHERE sequencename=%s ' % self.flavor.param, (name, ))
increment, = cursor.fetchone()
cursor.execute(
'SELECT CASE WHEN NOT is_called THEN last_value '
'ELSE last_value + %s '
'END '
'FROM "%s"' % (self.flavor.param, name), (increment, ))
else:
cursor.execute('SELECT CASE WHEN NOT is_called THEN last_value '
'ELSE last_value + increment_by '
'END '
'FROM "%s"' % name)
return cursor.fetchone()[0]
def do_batch_update(
cursor: CursorObj,
table: str,
assignments: List[Composable],
batch_size: int = 10000,
sleep: float = 0.1,
) -> None: # nocoverage
# The string substitution below is complicated by our need to
# support multiple PostgreSQL versions.
stmt = SQL("""
UPDATE {}
SET {}
WHERE id >= %s AND id < %s
""").format(
Identifier(table),
SQL(", ").join(assignments),
)
cursor.execute(
SQL("SELECT MIN(id), MAX(id) FROM {}").format(Identifier(table)))
(min_id, max_id) = cursor.fetchone()
if min_id is None:
return
print(f"\n Range of rows to update: [{min_id}, {max_id}]")
while min_id <= max_id:
lower = min_id
upper = min_id + batch_size
print(f" Updating range [{lower},{upper})")
cursor.execute(stmt, [lower, upper])
min_id = upper
time.sleep(sleep)
# Once we've finished, check if any new rows were inserted to the table
if min_id > max_id:
cursor.execute(
SQL("SELECT MAX(id) FROM {}").format(Identifier(table)))
(max_id, ) = cursor.fetchone()
print(" Finishing...", end="")
def fetchone(self, back_as=None):
t = TupleCursor.fetchone(self)
if t is not None:
back_as = back_as or self.back_as
if back_as:
try:
back_as = self.connection.back_as_registry[back_as]
except KeyError:
raise BadBackAs(back_as, self.connection.back_as_registry)
return back_as(self.description, t)
else:
return t
def store_timetable(timetable: Timetable,
route: RouteId,
conn: Optional[connection] = None) -> None:
if conn is None:
conn = default_connection()
with conn:
with conn.cursor() as cursor:
cursor.execute(
"""
insert into timetables (caption) values (%s) returning id
""",
[timetable.caption],
)
row = cast(Tuple[Any, ...], cursor.fetchone())
timetable_id = row[0]
cursor.execute(
"""
insert into route_timetables (route, timetable)
values (%s, %s)
""",
[route.raw, timetable_id],
)
for variant in timetable.variants:
cursor.execute(
"""
insert into timetable_variants (route_name, timetable_id)
values (%s, %s) returning id
""",
[variant.route.strip(), timetable_id],
)
variant_id = cast(Tuple[Any, ...], cursor.fetchone())[0]
for position, stop in enumerate(variant.stops):
cursor.execute(
"""
insert into variant_stops (position, variant, stop)
values (%s, %s, %s)
""",
[position, variant_id, stop.id.raw],
)
def execute_query(self, query, all=True):
"""
Execute a simple query without arguments for the given connection
:raises psycopg2.ProgrammingError: if there was no result set when executing the query
"""
cursor = self._conn.cursor()
cursor.execute(query)
if all:
query_results = cursor.fetchall()
else:
query_results = cursor.fetchone()
cursor.close()
return query_results
def get_chain_meta(db: connection, chain_id: int) -> Optional[Dict]:
cursor = db.cursor()
cursor.execute('SELECT * FROM chain_meta WHERE id = %s', [chain_id])
chain_record = cursor.fetchone()
if chain_record is None:
return None
cursor.execute('SELECT * FROM scores WHERE batch_id = %s',
[chain_record.batch_id])
scores = [{
k: v
for k, v in row._asdict().items() if k != 'batch_id' and v is not None
} for row in cursor.fetchall()]
return {**chain_record._asdict(), 'scores': scores}
def search_path(self):
if self._search_path is None:
connection = self.get_connection()
try:
cursor = connection.cursor()
cursor.execute('SHOW search_path')
path, = cursor.fetchone()
special_values = {
'user': self.current_user,
}
self._search_path = [
unescape_quote(replace_special_values(
p.strip(), **special_values))
for p in path.split(',')]
finally:
self.put_connection(connection)
return self._search_path
def search_path(self):
if self._search_path is None:
connection = self.get_connection()
try:
cursor = connection.cursor()
cursor.execute('SHOW search_path')
path, = cursor.fetchone()
special_values = {
'user': self.current_user,
}
self._search_path = [
unescape_quote(replace_special_values(
p.strip(), **special_values))
for p in path.split(',')]
finally:
self.put_connection(connection)
return self._search_path
def _search_full_text_language(self, language):
languages = self._search_full_text_languages[self.name]
if language not in languages:
lang = Table('ir_lang')
connection = self.get_connection()
try:
cursor = connection.cursor()
cursor.execute(*lang.select(
Coalesce(lang.pg_text_search, 'simple'),
where=lang.code == language,
limit=1))
config_name, = cursor.fetchone()
finally:
self.put_connection(connection)
languages[language] = config_name
else:
config_name = languages[language]
return config_name
def currid(self, connection, table):
cursor = connection.cursor()
cursor.execute('SELECT last_value FROM "' + table + '_id_seq"')
return cursor.fetchone()[0]
def nextid(self, connection, table):
cursor = connection.cursor()
cursor.execute("SELECT NEXTVAL('" + table + "_id_seq')")
return cursor.fetchone()[0]
def currid(self, connection, table):
cursor = connection.cursor()
cursor.execute('SELECT last_value FROM "' + table + '_id_seq"')
return cursor.fetchone()[0]
def dictfetchone(self):
row = _2cursor.fetchone(self)
if row:
return self.__build_dict(row)
else:
return row
def fetchone(self):
if self._query_executed:
self._build_index()
return _cursor.fetchone(self)
def fetchone(self):
t = _cursor.fetchone(self)
if t is not None:
return MutableRow(self.description, t)
def one(self, sql, parameters=None, default=None, back_as=None, max_age=None, **kw):
"""Execute a query and return a single result or a default value.
:param str sql: the SQL statement to execute
:param parameters: the `bind parameters`_ for the SQL statement
:type parameters: dict or tuple
:param default: the value to return or raise if no results are found
:param back_as: the type of record to return
:type back_as: type or string
:param float max_age: how long to keep the result in the cache (in seconds)
:param kw: alternative to passing a :class:`dict` as `parameters`
:returns: a single record or value, or :attr:`default` (if
:attr:`default` is not an :class:`Exception`)
:raises: :exc:`~postgres.TooFew` or :exc:`~postgres.TooMany`,
or :attr:`default` (if :attr:`default` is an
:class:`Exception`)
.. _bind parameters: #bind-parameters
Use this for the common case where there should only be one record, but
it may not exist yet.
>>> db.one("SELECT * FROM foo WHERE bar='buz'")
Record(bar='buz', baz=42)
If the record doesn't exist, we return :class:`None`:
>>> record = db.one("SELECT * FROM foo WHERE bar='blam'")
>>> if record is None:
... print("No blam yet.")
...
No blam yet.
If you pass :attr:`default` we'll return that instead of :class:`None`:
>>> db.one("SELECT * FROM foo WHERE bar='blam'", default=False)
False
If you pass an :class:`Exception` instance or subclass for
:attr:`default`, we will raise that for you:
>>> db.one("SELECT * FROM foo WHERE bar='blam'", default=Exception)
Traceback (most recent call last):
...
Exception
We specifically stop short of supporting lambdas or other callables for
the :attr:`default` parameter. That gets complicated quickly, and
it's easy to just check the return value in the caller and do your
extra logic there.
You can use :attr:`back_as` to override the type associated with the
default :attr:`cursor_factory` for your
:class:`~postgres.Postgres` instance:
>>> db.default_cursor_factory
<class 'postgres.cursors.SimpleNamedTupleCursor'>
>>> db.one( "SELECT * FROM foo WHERE bar='buz'"
... , back_as=dict
... )
{'bar': 'buz', 'baz': 42}
That's a convenience so you don't have to go to the trouble of
remembering where :class:`~postgres.cursors.SimpleDictCursor` lives
and importing it in order to get dictionaries back.
If the query result has only one column, then we dereference that for
you.
>>> db.one("SELECT baz FROM foo WHERE bar='buz'")
42
And if the dereferenced value is :class:`None`, we return the value
of :attr:`default`:
>>> db.one("SELECT sum(baz) FROM foo WHERE bar='nope'", default=0)
0
Dereferencing isn't performed if a :attr:`back_as` argument is provided:
>>> db.one("SELECT null as foo", back_as=dict)
{'foo': None}
"""
query = self.mogrify(sql, parameters, **kw)
if max_age:
entry = self._cached_fetchall(query, max_age)
columns = entry.columns
rowcount = len(entry.rows)
if rowcount == 1:
row_tuple = entry.rows[0]
else:
self.run(query)
columns = self.description
rowcount = self.rowcount
if rowcount == 1:
row_tuple = TupleCursor.fetchone(self)
if rowcount == 1:
pass
elif rowcount == 0:
if isexception(default):
raise default
return default
elif rowcount < 0:
raise TooFew(rowcount, 0, 1)
else:
raise TooMany(rowcount, 0, 1)
if len(row_tuple) == 1 and back_as is None:
# dereference
out = row_tuple[0]
if out is None:
if isexception(default):
raise default
return default
else:
# transform
back_as = back_as or self.back_as
if back_as:
try:
back_as = self.connection.back_as_registry[back_as]
except KeyError:
raise BadBackAs(back_as, self.connection.back_as_registry)
out = back_as(columns, row_tuple)
else:
out = row_tuple
return out
def currid(self, connection, table):
cursor = connection.cursor()
cursor.execute(SQL("SELECT last_value FROM {}").format(
Identifier(table + '_id_seq')))
return cursor.fetchone()[0]
def nextid(self, connection, table):
cursor = connection.cursor()
cursor.execute("SELECT NEXTVAL(%s)", (table + '_id_seq',))
return cursor.fetchone()[0]
def nextid(self, connection, table):
cursor = connection.cursor()
cursor.execute("SELECT NEXTVAL('" + table + "_id_seq')")
return cursor.fetchone()[0]
def dictfetchone(self):
row = _2cursor.fetchone(self)
if row:
return self.__build_dict(row)
else:
return row
def query_one(query: str, query_args: Dict) -> Optional[Dict]:
cursor = get_cursor()
cursor.execute(query, query_args)
result = cursor.fetchone()
return result
def fetchone(self):
if self._query_executed:
self._build_index()
return _cursor.fetchone(self)