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 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 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 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_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 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 query_one(query: str, query_args: Dict) -> Optional[Dict]: cursor = get_cursor() cursor.execute(query, query_args) result = cursor.fetchone() return result