Python sqlalchemy.insert() Examples

def test_prefix_with(self):
        table1 = self.tables.mytable

        stmt = (
            table1.insert()
            .prefix_with("A", "B", dialect="mysql")
            .prefix_with("C", "D")
        )

        self.assert_compile(
            stmt,
            "INSERT C D INTO mytable (myid, name, description) "
            "VALUES (:myid, :name, :description)",
        )

        self.assert_compile(
            stmt,
            "INSERT A B C D INTO mytable (myid, name, description) "
            "VALUES (%s, %s, %s)",
            dialect=mysql.dialect(),
        ) 

def checkAlbumsWithDifferentFormats():
        c = MusicDatabase.getCursor()
        sql = text('select id, path, format '
                   '  from albums, album_properties '
                   ' where id in (select album_id '
                   '                from (select  album_id, count(*) '
                   '                        from album_properties '
                   '                    group by album_id '
                   '                      having count(*)>1) '
                   '                  as foo) '
                   '   and id = album_id')

        result = c.execute(sql)
        table = [(str(album_id), path, audioFormat)
                 for album_id, path, audioFormat in result.fetchall()]
        if table:
            table.insert(0, ('ALBUMID', 'PATH', 'FORMAT'))
            aligned = alignColumns(table, (False, True, True))
            print('Albums that contain songs with different formats:')
            for line in aligned:
                print(line)
        return bool(table) 

def test_insert_select_return_defaults(self):
        table1 = self.tables.mytable
        sel = select([table1.c.myid, table1.c.name]).where(
            table1.c.name == "foo"
        )
        ins = (
            self.tables.myothertable.insert()
            .from_select(("otherid", "othername"), sel)
            .return_defaults(self.tables.myothertable.c.otherid)
        )
        self.assert_compile(
            ins,
            "INSERT INTO myothertable (otherid, othername) "
            "SELECT mytable.myid, mytable.name FROM mytable "
            "WHERE mytable.name = %(name_1)s",
            checkparams={"name_1": "foo"},
        ) 

def insertMBArtistIDs(song_id, artistIDs):
        if not artistIDs:
            return
        songs_mb_artistids = table('songs_mb_artistids')

        s = select([songs_mb_artistids.c.artistid]) \
            .where(songs_mb_artistids.c.song_id == song_id)

        result = MusicDatabase.execute(s).fetchall()

        if set(artistIDs) == set(x['artistid'] for x in result):
            return

        d = songs_mb_artistids.delete() \
                              .where(songs_mb_artistids.c.song_id == song_id)
        MusicDatabase.execute(d)
        for artistID in artistIDs:
            i = insert(songs_mb_artistids).values(song_id=song_id,
                                                  artistid=artistID)
            MusicDatabase.execute(i) 

def insertMBAlbumArtistIDs(song_id, albumArtistIDs):
        if not albumArtistIDs:
            return
        songs_mb_albumartistids = table('songs_mb_albumartistids')

        s = select([songs_mb_albumartistids.c.albumartistid]) \
            .where(songs_mb_albumartistids.c.song_id == song_id)

        result = MusicDatabase.execute(s).fetchall()

        if set(albumArtistIDs) == set(x['albumartistid'] for x in result):
            return

        d = songs_mb_albumartistids.delete() \
            .where(songs_mb_albumartistids.c.song_id == song_id)
        MusicDatabase.execute(d)
        for artistID in albumArtistIDs:
            i = insert(songs_mb_albumartistids).values(song_id=song_id,
                                                       albumartistid=artistID)
            MusicDatabase.execute(i) 

def insertMBWorkIDs(song_id, workIDs):
        if not workIDs:
            return
        songs_mb_workids = table('songs_mb_workids')

        s = select([songs_mb_workids.c.workid]) \
            .where(songs_mb_workids.c.song_id == song_id)

        result = MusicDatabase.execute(s).fetchall()

        if set(workIDs) == set(x['workid'] for x in result):
            return

        d = songs_mb_workids.delete() \
                            .where(songs_mb_workids.c.song_id == song_id)
        MusicDatabase.execute(d)
        for workID in workIDs:
            i = insert(songs_mb_workids).values(song_id=song_id,
                                                workid=workID)
            MusicDatabase.execute(i) 

def insert_dataset(session, data, tbl):
    '''Batch insert data into the database using PostGIS specific functions.

    :param session: SQLAlchemy Session
    :type session: sqlalchemy.orm.session.Session
    :param data: DataFrame containing value, timestamp, longitude and latitude
    :type data: pandas.core.frame.DataFrame
    :param tbl: Base class representing the database table for the data
    :type tbl: sqlalchemy.ext.declarative.api.DeclarativeMeta
    '''
    values = sqlalchemy.select([sqlalchemy.func.unnest(data.value),
                                sqlalchemy.func.unnest(data.timestamp),
                                sqlalchemy.func.ST_MakePoint(
                                    sqlalchemy.func.unnest(data.longitude),
                                    sqlalchemy.func.unnest(data.latitude))])
    query = sqlalchemy.insert(tbl).from_select(tbl.columns, values)
    session.execute(query) 

def test_anticipate_no_pk_non_composite_pk_prefetch(self):
        t = Table(
            "t",
            MetaData(),
            Column("x", Integer, primary_key=True, autoincrement=False),
            Column("q", Integer),
        )
        d = postgresql.dialect()
        d.implicit_returning = False

        with expect_warnings(
            "Column 't.x' is marked as a member.*" "may not store NULL.$"
        ):
            self.assert_compile(
                t.insert(),
                "INSERT INTO t (q) VALUES (%(q)s)",
                params={"q": 5},
                dialect=d,
            ) 

def checkMusicBrainzTags():
        c = MusicDatabase.getCursor()
        sql = text('SELECT id, path FROM songs '
                   'WHERE root = :root '
                   '  AND NOT EXISTS (SELECT song_id '
                   '                   FROM songs_mb '
                   '                  WHERE recordingid is not NULL '
                   '                    AND song_id = id)'
                   '     ORDER BY id')
        table = []
        for root in config['musicbrainzTaggedMusicPaths']:
            result = c.execute(sql, {'root': root})
            table.extend((str(song_id), path)
                         for song_id, path in result.fetchall())
        if table:
            table.insert(0, ('SONGID', 'PATH'))
            aligned = alignColumns(table, (False, True))
            print('Songs which should have musicbrainz tags but don\'t:')
            for line in aligned:
                print(line)
        return bool(table) 

def test_anticipate_no_pk_non_composite_pk_implicit_returning(self):
        t = Table(
            "t",
            MetaData(),
            Column("x", Integer, primary_key=True, autoincrement=False),
            Column("q", Integer),
        )
        d = postgresql.dialect()
        d.implicit_returning = True
        with expect_warnings(
            "Column 't.x' is marked as a member.*" "may not store NULL.$"
        ):
            self.assert_compile(
                t.insert(),
                "INSERT INTO t (q) VALUES (%(q)s)",
                params={"q": 5},
                dialect=d,
            ) 

def read_only_read_sa_transaction(conn, deferrable):
    await conn.execute(sa.insert(users).values(id=1, name='test1'))
    t1 = await conn.begin(
        isolation_level='SERIALIZABLE',
        readonly=True,
        deferrable=deferrable
    )

    where = users.c.id == 1

    try:
        await conn.execute(sa.update(users).values({'name': 't'}).where(where))
    except InternalError as e:
        assert e.pgcode == '25006'

    await t1.commit()

    await conn.execute(sa.delete(users))
    assert len(await (await conn.execute(users.select())).fetchall()) == 0 

def isolation_read_sa_transaction(conn, conn2):
    await conn.execute(sa.insert(users).values(id=1, name='test1'))
    t1 = await conn.begin()

    where = users.c.id == 1
    q_user = users.select().where(where)
    user = await (await conn.execute(q_user)).fetchone()

    assert await (await conn2.execute(q_user)).fetchone() == user

    await conn.execute(sa.update(users).values({'name': 'name2'}).where(where))

    t2 = await conn2.begin()
    assert await (await conn2.execute(q_user)).fetchone() == user

    await t1.commit()

    await conn2.execute(sa.update(users).values(user).where(where))
    await t2.commit()

    assert await (await conn2.execute(q_user)).fetchone() == user

    await conn.execute(sa.delete(users))
    assert len(await (await conn.execute(users.select())).fetchall()) == 0 

def success_nested_transaction(conn):
    await check_count_users(conn, count=0)

    async with conn.begin_nested():
        await conn.execute(sa.insert(users).values(id=1, name='test1'))

        async with conn.begin_nested():
            await conn.execute(sa.insert(users).values(id=2, name='test2'))

    await check_count_users(conn, count=2)

    async with conn.begin():
        await conn.execute(sa.delete(users).where(users.c.id == 1))
        await conn.execute(sa.delete(users).where(users.c.id == 2))

    await check_count_users(conn, count=0) 

def test_anticipate_no_pk_composite_pk_implicit_returning(self):
        t = Table(
            "t",
            MetaData(),
            Column("x", Integer, primary_key=True),
            Column("y", Integer, primary_key=True),
        )
        d = postgresql.dialect()
        d.implicit_returning = True

        with expect_warnings(
            "Column 't.y' is marked as a member.*"
            "Note that as of SQLAlchemy 1.1,"
        ):
            self.assert_compile(
                t.insert(),
                "INSERT INTO t (x) VALUES (%(x)s)",
                params={"x": 5},
                dialect=d,
            ) 

def fail_first_nested_transaction(conn):
    trans = await conn.begin_nested()

    try:
        await conn.execute(sa.insert(users).values(id=1, name='test1'))

        async with conn.begin_nested():
            await conn.execute(sa.insert(users).values(id=2, name='test2'))

        async with conn.begin_nested():
            await conn.execute(sa.insert(users).values(id=3, name='test3'))

        raise RuntimeError()

    except RuntimeError:
        await trans.rollback()
    else:
        await trans.commit()

    await check_count_users(conn, count=0) 

def test_insert_from_select_col_values(self):
        table1 = self.tables.mytable
        table2 = self.tables.myothertable
        sel = select([table1.c.myid, table1.c.name]).where(
            table1.c.name == "foo"
        )
        ins = table2.insert().from_select(
            (table2.c.otherid, table2.c.othername), sel
        )
        self.assert_compile(
            ins,
            "INSERT INTO myothertable (otherid, othername) "
            "SELECT mytable.myid, mytable.name FROM mytable "
            "WHERE mytable.name = :name_1",
            checkparams={"name_1": "foo"},
        ) 

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 

def test_insert_from_select_dont_mutate_raw_columns(self):
        # test [ticket:3603]
        from sqlalchemy import table

        table_ = table(
            "mytable",
            Column("foo", String),
            Column("bar", String, default="baz"),
        )

        stmt = select([table_.c.foo])
        insert = table_.insert().from_select(["foo"], stmt)

        self.assert_compile(stmt, "SELECT mytable.foo FROM mytable")
        self.assert_compile(
            insert,
            "INSERT INTO mytable (foo, bar) "
            "SELECT mytable.foo, :bar AS anon_1 FROM mytable",
        )
        self.assert_compile(stmt, "SELECT mytable.foo FROM mytable")
        self.assert_compile(
            insert,
            "INSERT INTO mytable (foo, bar) "
            "SELECT mytable.foo, :bar AS anon_1 FROM mytable",
        ) 

def test_insert_from_select_fn_defaults(self):
        metadata = MetaData()

        def foo(ctx):
            return 12

        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("foo", Integer, default=foo),
        )
        table1 = self.tables.mytable
        sel = select([table1.c.myid]).where(table1.c.name == "foo")
        ins = table.insert().from_select(["id"], sel)
        self.assert_compile(
            ins,
            "INSERT INTO sometable (id, foo) SELECT "
            "mytable.myid, :foo AS anon_1 "
            "FROM mytable WHERE mytable.name = :name_1",
            # value filled in at execution time
            checkparams={"name_1": "foo", "foo": None},
        ) 

def test_insert_from_select_with_python_defaults(self):
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("foo", Integer, default=12),
        )
        table1 = self.tables.mytable
        sel = select([table1.c.myid]).where(table1.c.name == "foo")
        ins = table.insert().from_select(["id"], sel)
        self.assert_compile(
            ins,
            "INSERT INTO sometable (id, foo) SELECT "
            "mytable.myid, :foo AS anon_1 "
            "FROM mytable WHERE mytable.name = :name_1",
            # value filled in at execution time
            checkparams={"name_1": "foo", "foo": None},
        ) 

def test_insert_from_select_with_sql_defaults(self):
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("foo", Integer, default=func.foobar()),
        )
        table1 = self.tables.mytable
        sel = select([table1.c.myid]).where(table1.c.name == "foo")
        ins = table.insert().from_select(["id"], sel)
        self.assert_compile(
            ins,
            "INSERT INTO sometable (id, foo) SELECT "
            "mytable.myid, foobar() AS foobar_1 "
            "FROM mytable WHERE mytable.name = :name_1",
            checkparams={"name_1": "foo"},
        ) 

def test_insert_from_select_no_defaults(self):
        metadata = MetaData()
        table = Table(
            "sometable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("foo", Integer, default=func.foobar()),
        )
        table1 = self.tables.mytable
        sel = select([table1.c.myid]).where(table1.c.name == "foo")
        ins = table.insert().from_select(["id"], sel, include_defaults=False)
        self.assert_compile(
            ins,
            "INSERT INTO sometable (id) SELECT mytable.myid "
            "FROM mytable WHERE mytable.name = :name_1",
            checkparams={"name_1": "foo"},
        ) 

def test_insert_from_select_cte_follows_insert_two(self):
        dialect = default.DefaultDialect()
        dialect.cte_follows_insert = True
        table1 = self.tables.mytable

        cte = table1.select().cte("c")
        stmt = cte.select()
        ins = table1.insert().from_select(table1.c, stmt)

        self.assert_compile(
            ins,
            "INSERT INTO mytable (myid, name, description) "
            "WITH c AS (SELECT mytable.myid AS myid, mytable.name AS name, "
            "mytable.description AS description FROM mytable) "
            "SELECT c.myid, c.name, c.description FROM c",
            dialect=dialect,
        ) 

def test_insert_from_select_cte_one(self):
        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,
            "WITH anon_1 AS "
            "(SELECT mytable.name AS name FROM mytable "
            "WHERE mytable.name = :name_1) "
            "INSERT INTO myothertable (otherid, othername) "
            "SELECT mytable.myid, mytable.name FROM mytable, anon_1 "
            "WHERE mytable.name = anon_1.name",
            checkparams={"name_1": "bar"},
        ) 

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(),
        ) 

def test_insert_from_select_returning(self):
        table1 = self.tables.mytable
        sel = select([table1.c.myid, table1.c.name]).where(
            table1.c.name == "foo"
        )
        ins = (
            self.tables.myothertable.insert()
            .from_select(("otherid", "othername"), sel)
            .returning(self.tables.myothertable.c.otherid)
        )
        self.assert_compile(
            ins,
            "INSERT INTO myothertable (otherid, othername) "
            "SELECT mytable.myid, mytable.name FROM mytable "
            "WHERE mytable.name = %(name_1)s RETURNING myothertable.otherid",
            checkparams={"name_1": "foo"},
            dialect="postgresql",
        ) 

def test_anticipate_no_pk_composite_pk_prefetch(self):
        t = Table(
            "t",
            MetaData(),
            Column("x", Integer, primary_key=True),
            Column("y", Integer, primary_key=True),
        )
        d = postgresql.dialect()
        d.implicit_returning = False
        with expect_warnings(
            "Column 't.y' is marked as a member.*"
            "Note that as of SQLAlchemy 1.1,"
        ):
            self.assert_compile(
                t.insert(),
                "INSERT INTO t (x) VALUES (%(x)s)",
                params={"x": 5},
                dialect=d,
            ) 

def test_empty_insert_default(self):
        table1 = self.tables.mytable

        stmt = table1.insert().values({})  # hide from 2to3
        self.assert_compile(stmt, "INSERT INTO mytable () VALUES ()") 

def test_insert_from_select_select(self):
        table1 = self.tables.mytable
        sel = select([table1.c.myid, table1.c.name]).where(
            table1.c.name == "foo"
        )
        ins = self.tables.myothertable.insert().from_select(
            ("otherid", "othername"), sel
        )
        self.assert_compile(
            ins,
            "INSERT INTO myothertable (otherid, othername) "
            "SELECT mytable.myid, mytable.name FROM mytable "
            "WHERE mytable.name = :name_1",
            checkparams={"name_1": "foo"},
        ) 

def test_insert_from_select_select_alt_ordering(self):
        table1 = self.tables.mytable
        sel = select([table1.c.name, table1.c.myid]).where(
            table1.c.name == "foo"
        )
        ins = self.tables.myothertable.insert().from_select(
            ("othername", "otherid"), sel
        )
        self.assert_compile(
            ins,
            "INSERT INTO myothertable (othername, otherid) "
            "SELECT mytable.name, mytable.myid FROM mytable "
            "WHERE mytable.name = :name_1",
            checkparams={"name_1": "foo"},
        )