How to create a temporary table for an existing table with sqlalchemy?

Question

I have a table(in mysql-server) associated with the following class:

class Number(Base):
    __table_name__ = 'number'
    col0 = Column(...) # PRIMARY KEY
    col1 = Column(...)
    col2 = Column(...)
    col3 = Column(...)

I want to create a temporary table for this number table when the program starts, this temporary table looks like:

class tempNumber(Base):
      __table_name__ = 'temp_number'
      col0 = Column(...) # direct from Number.col0
      col1 = Column(...) # from Number.col1, but will be modified, for example tempNumber.col1 = Number.col1.replace(' ','')

I also tried to add the following line to class Number:

__table_args__ = {'prefixes': ['TEMPORARY']}

but my python programm told me that table temp_number doesn't exist.

and one more question is, how to copy the data fast? because there are lots of rows in table number.

score 1 · Answer 1 · edited May 23 '17 at 12:14

Does the temp_number table actually exist in the underlying database?
Generally, copying data from one table to another quickly isn't done through sqlalchemy, which has significant overhead in this type of task. Instead, it will vary depending on your underlying database.

For example, here's how one would do it in MySQL. Here's a way to do it with PostgreSQL. However, this usually doesn't copy things like indexes. That, you'll have to setup manually, via your database driver.

How to create a temporary table for an existing table with sqlalchemy?

1 Answers1