Here is some custom code I wrote that I think might be problematic for this particular use case.

class SQLServerConnection:
    def __init__(self, database):
        ...
        self.connection_string = \
            "DRIVER="   str(self.driver)    ";"   \
            "SERVER="   str(self.server)    ";"   \
            "DATABASE="   str(self.database)    ";"   \
            "Trusted_Connection=yes;"   
        self.engine = sqlalchemy.create_engine(
            sqlalchemy.engine.URL.create(
                "mssql pyodbc", \
                query={'odbc_connect': self.connection_string}
                                        )
                                              )
    # Runs a command and returns in plain text (python list for multiple rows)
    # Can be a select, alter table, anything like that
    def execute(self, command, params=False): 
        # Make a connection object with the server
        with self.engine.connect() as conn:
        
            # Can send some parameters along with a plain text query... 
                # could be single dict or list of dict
                # Doc: https://docs.sqlalchemy.org/en/14/tutorial/dbapi_transactions.html#sending-multiple-parameters
            if params:
                output = conn.execute(sqlalchemy.text(command,params))
            else:
                output = conn.execute(sqlalchemy.text(command))
            
            # Tell SQL server to save your changes (assuming that is applicable, is not with select)
            # Doc: https://docs.sqlalchemy.org/en/14/tutorial/dbapi_transactions.html#committing-changes
            try:
                conn.commit()
            except Exception as e:
                #pass
                warn("Could not commit changes...\n"   str(e))
           
            # Try to consolidate select statement result into single object to return
            try:
                output = output.all()
            except:
                pass
        return output

If I try:

cnxn = SQLServerConnection(database='MyDatabase')
cnxn.execute("SELECT * INTO [dbo].[MyTable_newdata] FROM [dbo].[MyTable] ")

or

cnxn.execute("SELECT TOP 0 * INTO [dbo].[MyTable_newdata] FROM [dbo].[MyTable] ")

Python returns this object without error, <sqlalchemy.engine.cursor.LegacyCursorResult at 0x2b793d71880>, but upon looking in MS SQL Server, the new table was not generated. I am not warned about the commit step failing with the SELECT TOP 0 way; I am warned ('Connection' object has no attribute 'commit') in the above way.

CREATE TABLE, ALTER TABLE, or SELECT (etc) appears to work fine, but SELECT * INTO seems to not be working, and I'm not sure how to troubleshoot further. Copy-pasting the query into SQL Server and running appears to work fine.

CodePudding user response：

Use this recipe instead:

#!python
from sqlalchemy.sql import Select
from sqlalchemy.ext.compiler import compiles

class SelectInto(Select):
    def __init__(self, columns, into, *arg, **kw):
        super(SelectInto, self).__init__(columns, *arg, **kw)
        self.into = into

@compiles(SelectInto)
def s_into(element, compiler, **kw):
    text = compiler.visit_select(element)
    text = text.replace('FROM', 
                'INTO TEMPORARY TABLE %s FROM' % 
                element.into)
    return text


if __name__ == '__main__':
    from sqlalchemy.sql import table, column

    marker = table('marker', 
        column('x1'),
        column('x2'),
        column('x3')
    )

    print SelectInto([marker.c.x1, marker.c.x2], "tmp_markers").\
            where(marker.c.x3==5).\
            where(marker.c.x1.in_([1, 5]))

This needs some tweaking, hence it will replace all subquery selects as select INTOs, but test it for now, if it worked it would be better than raw text statments.

CodePudding user response：

Have you tried this from this answer by @Michael Berkowski:

INSERT INTO assets_copy
  SELECT * FROM assets;

The answer states that MySQL documentation states that SELECT * INTO isn't supported.