"'Connection' object has no attribute 'commit'" error using SQLAlchemy-CodePudding

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] ")

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.

CodePudding user response：

As noted in the introduction to the 1.4 tutorial here:

A Note on the Future

This tutorial describes a new API that’s released in SQLAlchemy 1.4 known as 2.0 style. The purpose of the 2.0-style API is to provide forwards compatibility with SQLAlchemy 2.0, which is planned as the next generation of SQLAlchemy.

In order to provide the full 2.0 API, a new flag called future will be used, which will be seen as the tutorial describes the Engine and Session objects. These flags fully enable 2.0-compatibility mode and allow the code in the tutorial to proceed fully. When using the future flag with the create_engine() function, the object returned is a subclass of sqlalchemy.engine.Engine described as sqlalchemy.future.Engine. This tutorial will be referring to sqlalchemy.future.Engine.

That is, it is assumed that the engine is created with

engine = create_engine(connection_url, future=True)

You are getting the "'Connection' object has no attribute 'commit'" error because you are creating an old-style Engine object.

You can avoid the error by adding future=True to your create_engine() call:

        self.engine = sqlalchemy.create_engine(
            sqlalchemy.engine.URL.create(
                "mssql pyodbc",
                query={'odbc_connect': self.connection_string}
            ),
            future=True
        )