Python sqlalchemy.Sequence() Examples

The following are 30 code examples of sqlalchemy.Sequence(). You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may also want to check out all available functions/classes of the module sqlalchemy , or try the search function .
Example #1
Source File: postgres_database.py    From open-raadsinformatie with MIT License 7 votes vote down vote up
def generate_ori_identifier(self, iri):
        """
        Generates a Resource with an ORI identifier and adds the IRI as a Source if it does not already exist.
        """

        session = self.Session()
        new_id = self.engine.execute(Sequence('ori_id_seq'))
        new_identifier = Uri(Ori, new_id)

        try:
            # If the resource already exists, create the source as a child of the resource
            resource = session.query(Source).filter(Source.iri == iri).one().resource
            resource.sources.append(Source(iri=iri))
            session.flush()
        except NoResultFound:
            # If the resource does not exist, create resource and source together
            resource = Resource(ori_id=new_id, iri=new_identifier, sources=[Source(iri=iri)])
            session.add(resource)
            session.commit()
        finally:
            session.close()

        return new_identifier 
Example #2
Source File: test_defaults.py    From sqlalchemy with MIT License 6 votes vote down vote up
def dataset_no_autoinc(self, metadata):
        # plain autoincrement/PK table in the actual schema
        Table("x", metadata, Column("set_id", Integer, primary_key=True))

        # for the INSERT use a table with a Sequence
        # and autoincrement=False.  Using a ForeignKey
        # would have the same effect

        some_seq = Sequence("some_seq")

        dataset_no_autoinc = Table(
            "x",
            MetaData(),
            Column(
                "set_id",
                Integer,
                some_seq,
                primary_key=True,
                autoincrement=False,
            ),
        )
        return dataset_no_autoinc 
Example #3
Source File: test_metadata.py    From sqlalchemy with MIT License 6 votes vote down vote up
def test_pickle_metadata_sequence_restated(self):
        m1 = MetaData()
        Table(
            "a",
            m1,
            Column("id", Integer, primary_key=True),
            Column("x", Integer, Sequence("x_seq")),
        )

        m2 = pickle.loads(pickle.dumps(m1))

        s2 = Sequence("x_seq")
        t2 = Table(
            "a",
            m2,
            Column("id", Integer, primary_key=True),
            Column("x", Integer, s2),
            extend_existing=True,
        )

        assert m2._sequences["x_seq"] is t2.c.x.default
        assert m2._sequences["x_seq"] is s2 
Example #4
Source File: test_compiler.py    From sqlalchemy with MIT License 6 votes vote down vote up
def test_schema_translate_map_sequence(self):
        s1 = schema.Sequence("s1")
        s2 = schema.Sequence("s2", schema="foo")
        s3 = schema.Sequence("s3", schema="bar")

        schema_translate_map = {None: "z", "bar": None, "foo": "bat"}

        self.assert_compile(
            schema.CreateSequence(s1),
            "CREATE SEQUENCE [SCHEMA__none].s1 START WITH 1",
            schema_translate_map=schema_translate_map,
        )

        self.assert_compile(
            schema.CreateSequence(s2),
            "CREATE SEQUENCE [SCHEMA_foo].s2 START WITH 1",
            schema_translate_map=schema_translate_map,
        )

        self.assert_compile(
            schema.CreateSequence(s3),
            "CREATE SEQUENCE [SCHEMA_bar].s3 START WITH 1",
            schema_translate_map=schema_translate_map,
        ) 
Example #5
Source File: sequence.py    From AnyBlok with Mozilla Public License 2.0 6 votes vote down vote up
def create_sequence(cls, values):
        """Create the database sequence for an instance of Sequence Model.

        :return: suitable field values for insertion of the Model instance
        :rtype: dict
        """
        seq_name = values.get('seq_name')
        if seq_name is None:
            seq_id = cls.registry.execute(SQLASequence(cls._cls_seq_name))
            seq_name = '%s_%d' % (cls.__tablename__, seq_id)
            values['seq_name'] = seq_name

        number = values.setdefault('number', 0)
        if number:
            seq = SQLASequence(seq_name, number)
        else:
            seq = SQLASequence(seq_name)
        seq.create(cls.registry.bind)
        return values 
Example #6
Source File: sequence.py    From AnyBlok with Mozilla Public License 2.0 6 votes vote down vote up
def initialize_model(cls):
        """ Create the sequence to determine name """
        super(Sequence, cls).initialize_model()
        seq = SQLASequence(cls._cls_seq_name)
        seq.create(cls.registry.bind)

        to_create = getattr(cls.registry,
                            '_need_sequence_to_create_if_not_exist', ())
        if to_create is None:
            return

        for vals in to_create:
            if cls.query().filter(cls.code == vals['code']).count():
                continue

            formatter = vals.get('formater')
            if formatter is None:
                del vals['formater']

            cls.insert(**vals) 
Example #7
Source File: test_pandas.py    From snowflake-sqlalchemy with Apache License 2.0 6 votes vote down vote up
def _create_users_addresses_tables(engine_testaccount, metadata):
    users = Table('users', metadata,
                  Column('id', Integer, Sequence('user_id_seq'),
                         primary_key=True),
                  Column('name', String),
                  Column('fullname', String),
                  )

    addresses = Table('addresses', metadata,
                      Column('id', Integer, Sequence('address_id_seq'),
                             primary_key=True),
                      Column('user_id', None, ForeignKey('users.id')),
                      Column('email_address', String, nullable=False)
                      )
    metadata.create_all(engine_testaccount)
    return users, addresses 
Example #8
Source File: test_sequences.py    From sqlalchemy with MIT License 6 votes vote down vote up
def test_freestanding_sequence_via_autoinc(self, connection):
        t = Table(
            "some_table",
            self.metadata,
            Column(
                "id",
                Integer,
                autoincrement=True,
                primary_key=True,
                default=Sequence(
                    "my_sequence", metadata=self.metadata
                ).next_value(),
            ),
        )
        self.metadata.create_all(connection)

        result = connection.execute(t.insert())
        eq_(result.inserted_primary_key, (1,)) 
Example #9
Source File: tracker_store.py    From rasa-for-botfront with Apache License 2.0 6 votes vote down vote up
def _create_sequence(table_name: Text) -> "Sequence":
    """Creates a sequence object for a specific table name.

    If using Oracle you will need to create a sequence in your database,
    as described here: https://rasa.com/docs/rasa/api/tracker-stores/#sqltrackerstore
    Args:
        table_name: The name of the table, which gets a Sequence assigned

    Returns: A `Sequence` object
    """

    from sqlalchemy.ext.declarative import declarative_base

    sequence_name = f"{table_name}_seq"
    Base = declarative_base()
    return sa.Sequence(sequence_name, metadata=Base.metadata, optional=True) 
Example #10
Source File: test_execute.py    From sqlalchemy with MIT License 6 votes vote down vote up
def test_sequence_not_duped(self):
        engine, buf = self._engine_fixture()
        metadata = MetaData()
        t = Table(
            "testtable",
            metadata,
            Column(
                "pk", Integer, Sequence("testtable_pk_seq"), primary_key=True,
            ),
        )

        t.create(engine)
        t.drop(engine)

        eq_(re.findall(r"CREATE (\w+)", buf.getvalue()), ["SEQUENCE", "TABLE"])

        eq_(re.findall(r"DROP (\w+)", buf.getvalue()), ["TABLE", "SEQUENCE"]) 
Example #11
Source File: test_firebird.py    From sqlalchemy with MIT License 6 votes vote down vote up
def test_strlen(self):
        metadata = self.metadata

        # On FB the length() function is implemented by an external UDF,
        # strlen().  Various SA tests fail because they pass a parameter
        # to it, and that does not work (it always results the maximum
        # string length the UDF was declared to accept). This test
        # checks that at least it works ok in other cases.

        t = Table(
            "t1",
            metadata,
            Column("id", Integer, Sequence("t1idseq"), primary_key=True),
            Column("name", String(10)),
        )
        metadata.create_all()
        t.insert(values=dict(name="dante")).execute()
        t.insert(values=dict(name="alighieri")).execute()
        select(
            [func.count(t.c.id)], func.length(t.c.name) == 5
        ).execute().first()[0] == 1 
Example #12
Source File: test_compiler.py    From sqlalchemy with MIT License 6 votes vote down vote up
def test_format(self):
        seq = Sequence("my_seq_no_schema")
        dialect = postgresql.dialect()
        assert (
            dialect.identifier_preparer.format_sequence(seq)
            == "my_seq_no_schema"
        )
        seq = Sequence("my_seq", schema="some_schema")
        assert (
            dialect.identifier_preparer.format_sequence(seq)
            == "some_schema.my_seq"
        )
        seq = Sequence("My_Seq", schema="Some_Schema")
        assert (
            dialect.identifier_preparer.format_sequence(seq)
            == '"Some_Schema"."My_Seq"'
        ) 
Example #13
Source File: test_insert_exec.py    From sqlalchemy with MIT License 6 votes vote down vote up
def test_lastrow_accessor_four(self):
        metadata = MetaData()
        self._test_lastrow_accessor(
            Table(
                "t4",
                metadata,
                Column(
                    "id",
                    Integer,
                    Sequence("t4_id_seq", optional=True),
                    primary_key=True,
                ),
                Column("foo", String(30), primary_key=True),
                Column("bar", String(30), server_default="hi"),
            ),
            {"foo": "hi", "id": 1},
            {"id": 1, "foo": "hi", "bar": "hi"},
        ) 
Example #14
Source File: test_insert.py    From sqlalchemy with MIT License 6 votes vote down vote up
def test_insert_from_select_seq(self):
        m = MetaData()

        t1 = Table(
            "t",
            m,
            Column("id", Integer, Sequence("id_seq"), primary_key=True),
            Column("data", String),
        )

        stmt = t1.insert().from_select(("data",), select([t1.c.data]))

        self.assert_compile(
            stmt,
            "INSERT INTO t (data, id) SELECT t.data, "
            "nextval('id_seq') AS next_value_1 FROM t",
            dialect=postgresql.dialect(),
        ) 
Example #15
Source File: test_defaults.py    From sqlalchemy with MIT License 6 votes vote down vote up
def test_col_w_optional_sequence_non_autoinc_no_firing(
        self, dataset_no_autoinc, connection
    ):
        """this is testing that a Table which includes a Sequence, when
        run against a DB that does not support sequences, the Sequence
        does not get in the way.

        """
        dataset_no_autoinc.c.set_id.default.optional = True

        connection.execute(dataset_no_autoinc.insert())
        eq_(
            connection.scalar(
                select([func.count("*")]).select_from(dataset_no_autoinc)
            ),
            1,
        ) 
Example #16
Source File: test_sequences.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_func_embedded_select(self):
        """test can use next_value() in select column expr"""

        s = Sequence("my_sequence")
        self._assert_seq_result(testing.db.scalar(select([s.next_value()]))) 
Example #17
Source File: test_sequences.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_func_implicit_connectionless_scalar(self):
        """test func.next_value().execute()/.scalar() works. """

        s = Sequence("my_sequence", metadata=MetaData(testing.db))
        self._assert_seq_result(s.next_value().scalar()) 
Example #18
Source File: test_sequences.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_inserted_pk_implicit_returning(self):
        """test inserted_primary_key contains the result when
        pk_col=next_value(), when implicit returning is used."""

        metadata = self.metadata
        s = Sequence("my_sequence")
        t1 = Table("t", metadata, Column("x", Integer, primary_key=True,),)
        t1.create(testing.db)

        e = engines.testing_engine(options={"implicit_returning": True})
        with e.connect() as conn:
            r = conn.execute(t1.insert().values(x=s.next_value()))
            self._assert_seq_result(r.inserted_primary_key[0]) 
Example #19
Source File: test_sequences.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_func_explicit(self):
        s = Sequence("my_sequence")
        self._assert_seq_result(testing.db.scalar(s.next_value())) 
Example #20
Source File: test_sequences.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_func_implicit_connectionless_execute(self):
        """test func.next_value().execute()/.scalar() works
        with connectionless execution. """

        s = Sequence("my_sequence", metadata=MetaData(testing.db))
        self._assert_seq_result(s.next_value().execute().scalar()) 
Example #21
Source File: test_sequences.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_explicit_optional(self):
        """test dialect executes a Sequence, returns nextval, whether
        or not "optional" is set """

        s = Sequence("my_sequence", optional=True)
        self._assert_seq_result(s.execute(testing.db)) 
Example #22
Source File: test_sequences.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_implicit_connectionless(self):
        s = Sequence("my_sequence", metadata=MetaData(testing.db))
        self._assert_seq_result(s.execute()) 
Example #23
Source File: test_sequences.py    From sqlalchemy with MIT License 5 votes vote down vote up
def setup_class(cls):
        cls.seq = Sequence("my_sequence")
        cls.seq.create(testing.db) 
Example #24
Source File: test_insert_exec.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_explicit_sequence(self):
        t = self._fixture()
        self._test(
            t.insert().values(
                id=func.next_value(Sequence("t_id_seq")), data="data", x=5
            ),
            (testing.db.dialect.default_sequence_base, "data", 5),
        ) 
Example #25
Source File: test_metadata.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_sequence_restated_replaced(self):
        """Test restatement of Sequence replaces."""

        m1 = MetaData()
        s1 = Sequence("x_seq")
        t = Table("a", m1, Column("x", Integer, s1))
        assert m1._sequences["x_seq"] is s1

        s2 = Sequence("x_seq")
        Table("a", m1, Column("x", Integer, s2), extend_existing=True)
        assert t.c.x.default is s2
        assert m1._sequences["x_seq"] is s2 
Example #26
Source File: test_insert_exec.py    From sqlalchemy with MIT License 5 votes vote down vote up
def define_tables(cls, metadata):
        Table(
            "foo",
            metadata,
            Column(
                "id",
                testing.db.dialect.sequence_default_column_type,
                Sequence("t_id_seq"),
                primary_key=True,
            ),
            Column("data", String(50)),
            Column("x", Integer),
        ) 
Example #27
Source File: test_metadata.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_sequence_attach_to_existing_table(self):
        m1 = MetaData()
        s1 = Sequence("s")
        t = Table("a", m1, Column("x", Integer))
        t.c.x._init_items(s1)
        assert s1.metadata is m1 
Example #28
Source File: test_defaults.py    From sqlalchemy with MIT License 5 votes vote down vote up
def _run_test(self, *arg, **kw):
        metadata = self.metadata
        implicit_returning = kw.pop("implicit_returning", True)
        kw["primary_key"] = True
        if kw.get("autoincrement", True):
            kw["test_needs_autoincrement"] = True
        t = Table(
            "x",
            metadata,
            Column("y", self.MyInteger, *arg, **kw),
            Column("data", Integer),
            implicit_returning=implicit_returning,
        )

        with testing.db.connect() as conn:
            t.create(conn)
            r = conn.execute(t.insert().values(data=5))

            expected_result = "INT_" + str(
                testing.db.dialect.default_sequence_base
                if (arg and isinstance(arg[0], Sequence))
                else 1
            )

            # we don't pre-fetch 'server_default'.
            if "server_default" in kw and (
                not testing.db.dialect.implicit_returning
                or not implicit_returning
            ):
                eq_(r.inserted_primary_key, (None,))
            else:
                eq_(
                    r.inserted_primary_key, (expected_result,),
                )

            eq_(
                conn.execute(t.select()).first(), (expected_result, 5),
            ) 
Example #29
Source File: test_metadata.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_pickle_metadata_sequence_implicit(self):
        m1 = MetaData()
        Table(
            "a",
            m1,
            Column("id", Integer, primary_key=True),
            Column("x", Integer, Sequence("x_seq")),
        )

        m2 = pickle.loads(pickle.dumps(m1))

        t2 = Table("a", m2, extend_existing=True)

        eq_(m2._sequences, {"x_seq": t2.c.x.default}) 
Example #30
Source File: test_metadata.py    From sqlalchemy with MIT License 5 votes vote down vote up
def test_uninitialized_column_copy(self):
        for col in [
            Column("foo", String(), nullable=False),
            Column("baz", String(), unique=True),
            Column(Integer(), primary_key=True),
            Column(
                "bar",
                Integer(),
                Sequence("foo_seq"),
                primary_key=True,
                key="bar",
            ),
            Column(Integer(), ForeignKey("bat.blah"), doc="this is a col"),
            Column(
                "bar",
                Integer(),
                ForeignKey("bat.blah"),
                primary_key=True,
                key="bar",
            ),
            Column("bar", Integer(), info={"foo": "bar"}),
        ]:
            c2 = col.copy()
            for attr in (
                "name",
                "type",
                "nullable",
                "primary_key",
                "key",
                "unique",
                "info",
                "doc",
            ):
                eq_(getattr(col, attr), getattr(c2, attr))
            eq_(len(col.foreign_keys), len(c2.foreign_keys))
            if col.default:
                eq_(c2.default.name, "foo_seq")
            for a1, a2 in zip(col.foreign_keys, c2.foreign_keys):
                assert a1 is not a2
                eq_(a2._colspec, "bat.blah")