Python sqlalchemy.insert方法代码示例

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import insert [as 别名]
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) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import insert [as 别名]
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) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import insert [as 别名]
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) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import insert [as 别名]
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) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import insert [as 别名]
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) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import insert [as 别名]
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) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import insert [as 别名]
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 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import insert [as 别名]
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 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import insert [as 别名]
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) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import insert [as 别名]
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) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import insert [as 别名]
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 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import insert [as 别名]
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(),
        ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import insert [as 别名]
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",
        ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import insert [as 别名]
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(),
        ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import insert [as 别名]
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"},
        )