Bulk inserting rows into a dynamically created table.

[Nana Okyere]

In my application, atables is dynamically created on the schema. I need to insert rows onto that table. I tried to use some of the methods suggested here but then I realized that they are passing the mapped class or the table of the class to the bulk_insert_mappings method or the engine.execute(Customer.__table__.insert() method. In both approaches, Customer is a mapped class. For what I'm doing, the table I'm doing the insert on is dynamically created. So I can't do models.TableName. Is there a way to do bulk inserts on such a table in sqlalchey (orm or core) ?

I tried the "insert all .... select * from dual" construct in oracle but that doesn't work with sequence_name.nextval for the id column. So I'm looking for another approach. I read that since oracle 12c I can create a unique id column using GENERATED AS IDENTITY clause. Is that supported in sqlalchemy? Then I could just insert rows without worrying about how the id is generated. Thanks for sqlalchemy and your continued support.

[Mike Bayer]

I would definitely not rely upon new Oracle features like GENERATED AS IDENTITY unless it is absolutely no issue.

The SQL you may emit for using NEXTVAL is along the lines of:

INSERT INTO table (id, col1, col2, ...) VALUES (my_sequence.nextval, 'data1', 'data2', ...)

See the example at http://docs.oracle.com/cd/A84870_01/doc/server.816/a76989/ch26.htm.

SQLAlchemy supports emitting SQL of this form against the Oracle backend, and there are many ways to achieve this result depending on what you are starting with.

What is not clear is what we are inserting.  Is this just a list of dictionaries you'd like to bulk insert into a table?   What kind of Python structure do you have that represents these dynamically created tables?  (choices are:  nothing, a Table, or a mapped class).

On 10/1/15 1:11 AM, Nana Okyere wrote:

In my application, atables is dynamically created on the schema. I need to insert rows onto that table. I tried to use some of the methods suggested here but then I realized that they are passing the mapped class or the table of the class to the bulk_insert_mappings method or the engine.execute(Customer.__table__.insert() method. In both approaches, Customer is a mapped class. For what I'm doing, the table I'm doing the insert on is dynamically created. So I can't do models.TableName. Is there a way to do bulk inserts on such a table in sqlalchey (orm or core) ?

I tried the "insert all .... select * from dual" construct in oracle but that doesn't work with sequence_name.nextval for the id column. So I'm looking for another approach. I read that since oracle 12c I can create a unique id column using GENERATED AS IDENTITY clause. Is that supported in sqlalchemy? Then I could just insert rows without worrying about how the id is generated. Thanks for sqlalchemy and your continued support.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Nana Okyere]

Mike, thanks for your response.

My original goal is to insert excel rows into a dynamically created oracle table. The library I'm using (openpyxl) gives me all the rows of the excel sheet as a generator. So the generator_name.next() returns a tuple which represents a single row. The elements of this tuple represents the cells of the row. So to answer your first question, I'm inserting a generator of tuples.

Answer to second question: The dynamically created table in the schema is represented in python by nothing. I'm looking for the best way to insert all the rows into this table assuming the first tuple from the generator has column names that match the columns of the table (may not be in the same order). I'm a new programmer; any help will be appreciated. Thanks.

[Nana Okyere]

Any take on this? I realize it may not be sqlalchemy specific.

[Mike Bayer]

Have you looked through the general techniques of creating Table objects at http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html?  If you have a list of attribute names, you can programmatically create a Table object from that.

On 10/6/15 10:39 AM, Nana Okyere wrote:

Any take on this? I realize it may not be sqlalchemy specific.

[Nana Okyere]

Mike,

The table creation part is done. My question is about how to insert rows/data into a table that is dynamically created. This dynamically created table is not represented by a class or anything in sqlalchemy. I'm just wondering if you know of a nice way to do bulk / mass inserts into such a table without a loop of insert statements. The source of my data is a generator of tuples (coming from openpyxl). Thanks.

[Mike Bayer]

On 10/6/15 3:48 PM, Nana Okyere wrote:

Mike,

The table creation part is done. My question is about how to insert rows/data into a table that is dynamically created. This dynamically created table is not represented by a class or anything in sqlalchemy.

if the table is there you can just reflect it.

from sqlalchemy import create_engine, Table, MetaData

e = create_engine("sqlite://", echo=True)
e.execute("""
    create table i_am_dynamic(x string, y string)
""")


data = [
    {'x': i, 'y': i + 10} for i in range(100)
]

m = MetaData()
t = Table('i_am_dynamic', m, autoload=True, autoload_with=e)

e.execute(t.insert(), data)

# done

I'm just wondering if you know of a nice way to do bulk / mass inserts into such a table without a loop of insert statements. The source of my data is a generator of tuples (coming from openpyxl). Thanks.

[Nana Okyere]

Nice! I had seen reflection before but I didn't know this is what it meant. 

Anyway, do I have to reflect the table every time the table's structure changes? The application I'm building has the ability to add or remove columns on the dynamically created table.

Can I repeat the reflection code every time I'm about to insert rows into the table? Thanks.