Python peewee.PostgresqlDatabase() Examples

def create_db_connection():
    """ Creates a database connection with the postgres db"""

    is_test_env = os.getenv('MQTT_PWN_TESTING_ENV')

    if is_test_env:
        db = SqliteDatabase(':memory:')
    else:
        db = PostgresqlDatabase(
            config.DB_NAME,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            host=config.DB_HOST,
            port=config.DB_PORT
        )

    database_proxy.initialize(db)

    return db 

def get_db_conn():
    import peewee
    env = os.environ.get('SENTINEL_ENV', 'production')

    # default values should be used unless you need a different config for development
    db_host = sentinel_cfg.get('db_host', '127.0.0.1')
    db_port = sentinel_cfg.get('db_port', None)
    db_name = sentinel_cfg.get('db_name', 'sentinel')
    db_user = sentinel_cfg.get('db_user', 'sentinel')
    db_password = sentinel_cfg.get('db_password', 'sentinel')
    db_charset = sentinel_cfg.get('db_charset', 'utf8mb4')
    db_driver = sentinel_cfg.get('db_driver', 'sqlite')

    if (env == 'test'):
        if db_driver == 'sqlite':
            db_name = sqlite_test_db_name(db_name)
        else:
            db_name = "%s_test" % db_name

    peewee_drivers = {
        'mysql': peewee.MySQLDatabase,
        'postgres': peewee.PostgresqlDatabase,
        'sqlite': peewee.SqliteDatabase,
    }
    driver = peewee_drivers.get(db_driver)

    dbpfn = 'passwd' if db_driver == 'mysql' else 'password'
    db_conn = {
        'host': db_host,
        'user': db_user,
        dbpfn: db_password,
    }
    if db_port:
        db_conn['port'] = int(db_port)

    if driver == peewee.SqliteDatabase:
        db_conn = {}

    db = driver(db_name, **db_conn)

    return db 

def init_postgres(url):
    global DB

    db_name = url.path.strip("/")

    DB.initialize(PostgresqlDatabase(
        database=db_name,
        user=url.user or None,
        password=url.password or None,
        host=url.host,
        autocommit=bool(url.get('autocommit', True)),
        autorollback=bool(url.get('autorollback', True))
    ))
    log.info("Database initialized as '%s'. Checking migrations...", db_name)
    return DB, PostgresqlMigrator(DB) 

def get_database(self):
        """Returns the correct database driver

        Returns:
            :obj:`pw.Database`
        Raises:
            AttributeError: if `--username` or `--password` not passed
                (if `--dbtype [postgres|mysql]`)

        """
        if self.get('dbtype') == 'sqlite':
            return pw.SqliteDatabase(self.get('dbname'),
                                     pragmas={'journal_mode': 'wal',
                                              'cache_size': -1 * 64000})
        else:
            if self.get('username') is None or self.get('password') is None:
                raise AttributeError('[ERROR] dbtype %s requires username and'
                                     ' password.\n' % str(self.get('dbtype')))
            if self.get('hostname') is None:
                self.set('hostname', 'localhost')
            if self.get('dbtype') == 'mysql':
                if self.get('port') is None or self.get('port') == '':
                    self.set('port', str(3306))
                return pw.MySQLDatabase(
                    self.get('dbname'),
                    user=self.get('username'),
                    password=self.get('password'),
                    host=self.get('hostname'),
                    port=int(self.get('port')))
            elif self.get('dbtype') == 'postgres':
                if self.get('port') is None or self.get('port') == '':
                    self.set('port', str(5432))
                return pw.PostgresqlDatabase(
                    self.get('dbname'),
                    user=self.get('username'),
                    password=self.get('password'),
                    host=self.get('hostname'),
                    port=int(self.get('port'))) 

def _swap_database(self, query):
        """Swap database for query if swappable. Return **new query**
        with swapped database.

        This is experimental feature which allows us to have multiple
        managers configured against different databases for single model
        definition.

        The essential limitation though is that database backend have
        to be **the same type** for model and manager!
        """
        database = _query_db(query)

        if database == self.database:
            return query

        if self._subclassed(peewee.PostgresqlDatabase, database,
                            self.database):
            can_swap = True
        elif self._subclassed(peewee.MySQLDatabase, database,
                              self.database):
            can_swap = True
        else:
            can_swap = False

        if can_swap:
            # **Experimental** database swapping!
            query = query.clone()
            query._database = self.database
            return query

        assert False, (
            "Error, query's database and manager's database are "
            "different. Query: %s Manager: %s" % (database, self.database)
        )

        return None 

def is_postgres(db):
  return isinstance(db, pw.PostgresqlDatabase) 

def __get_migrator(self):
		if isinstance(self.db.engine, peewee.SqliteDatabase) or isinstance(self.db.engine, SqliteExtDatabase):
			return SqliteMigrator(self.db.engine)
		elif isinstance(self.db.engine, peewee.MySQLDatabase):
			return MySQLMigrator(self.db.engine)
		elif isinstance(self.db.engine, peewee.PostgresqlDatabase):
			return PostgresqlMigrator(self.db.engine)
		raise ImproperlyConfigured('Database engine doesn\'t support Migrations!') 

def update(self, records: Iterable[DataRecord], values: SQLValuesToWrite, returning=False) -> Union[int, Iterable[DataRecord]]:
        new_vals = {}
        model = self.vcls.model
        db = self.vcls.model._meta.database
        fields = self.vcls._peewee_fields
        cond = self._build_write_condition(records)

        for k, v in values.items():
            if k in fields:
                field = fields[k]
                is_array_field = isinstance(field, ArrayField)

                if is_array_field:
                    if k in values.set_add_fields:
                        # 这里需要加 [v] 的原因是，params需要数组，举例来说为，[v1,v2,v3]
                        # v = SQL('%s || %%s' % field.column_name, [v])
                        v = SQL('(select ARRAY((select unnest(%s)) union (select unnest(%%s))))' % field.column_name, [v])

                    if k in values.set_remove_fields:
                        v = SQL('(select ARRAY((select unnest(%s)) except (select unnest(%%s))))' % field.column_name, [v])

                    # 尚未启用
                    # if k in values.array_append:
                    #     v = SQL('array_append(%s, %%s)' % field.column_name, [v])

                    # if k in values.array_remove:
                    #     v = SQL('array_remove(%s, %%s)' % field.column_name, [v])

                else:
                    if k in values.incr_fields:
                        v = field + v
                    if k in values.decr_fields:
                        v = field - v

                new_vals[k] = v

        with db.atomic(), PeeweeContext(db):
            if isinstance(db, peewee.PostgresqlDatabase):
                q = model.update(**new_vals).where(cond)
                if returning:
                    # cond: peewee.Expression
                    ret = q.returning(*model._meta.fields.values()).execute()
                    to_record = lambda x: PeeweeDataRecord(None, x, view=self.vcls)
                    items = map(to_record, ret)
                    return list(items)
                else:
                    count = q.execute()
                    return count
            else:
                count = model.update(**new_vals).where(cond).execute()
                if not returning: return count

                to_record = lambda x: PeeweeDataRecord(None, x, view=self.vcls)
                return list(map(to_record, model.select().where(cond).execute())) 

def insert(self, values_lst: Iterable[SQLValuesToWrite], returning=False, ignore_exists=False) -> Union[int, List[DataRecord]]:
        # 基本上，单条插入时，不忽略重复，多条时忽略
        model = self.vcls.model
        db = model._meta.database

        with db.atomic(), PeeweeContext(db):
            if isinstance(db, peewee.PostgresqlDatabase):
                # 对 postgres 可以直接使用 returning，另外防止一种default的bug
                # https://github.com/coleifer/peewee/issues/1555
                q = model.insert_many(values_lst)
                if ignore_exists:
                    q = q.on_conflict_ignore()
                if returning:
                    ret = q.returning(*model._meta.fields.values()).execute()
                    if get_peewee_ver() >= (3, 8, 2):
                        # incompatible change of peewee: https://github.com/coleifer/peewee/releases/tag/3.8.2
                        to_record = lambda x: PeeweeDataRecord(None, x, view=self.vcls)
                    else:
                        to_record = lambda x: PeeweeDataRecord(None, ret.model(**ret._row_to_dict(x)), view=self.vcls)
                    items = map(to_record, ret)
                    return list(items)
                else:
                    count = q.execute()
                    return count
            else:
                if returning:
                    items = []
                    for values in values_lst:
                        try:
                            item = model.create(**values)
                            items.append(PeeweeDataRecord(None, item, view=self.vcls))
                        except peewee.IntegrityError as e:
                            db.rollback()
                            if not ignore_exists:
                                raise e
                    return items
                else:
                    q = model.insert_many(values_lst)
                    if ignore_exists:
                        q = q.on_conflict_ignore()
                    count = q.execute()
                    return count