<p dir="auto">Hello,</p>
<p dir="auto">I am currently reflecting a MSSQL table and doing a bulk insert to it, my problem is that I need to check/avoid duplicates and I don't know how to do it in SQLAlchemy syntax.</p>
<p dir="auto">To give you some context, The SQL table is time series for a mortgage lender that contains 57 parameters including things such as "Loan ID", "Observation date","Month on Book", "Origination date", "Interest rate", etc. The clue to identify duplicates is in the "Month on book" since this can only have one row for each month after the "Origination Date" per "Loan ID". Each Loan ID would have different "Origination dates" and "Observation date", but the "Month on Book" encoding would be the same for every Loan ID.</p>
<p dir="auto">For example:</p>
<p dir="auto">Current date: March 2018</p>
<p dir="auto">Origination Date: 30 Jan 2018</p>
<p dir="auto">Observation Date: 30 March 2018</p>
<p dir="auto">Month on Book: 2 (February would be "1" and Jan would be "0")</p>
<p dir="auto">Since the table in MS SQL is already populated I want to insert new rows, but only having one record of month on book (the encoded version of "Observation Date") per LoanID (just in case someone runs the insert code twice, it doesn't duplicate all the data)</p>
<p dir="auto">This is how I am currently doing the whole insert process up to the duplicate exception (see "sqlalchemy_orm_bulk_insert" or "sqlalchemy_core_insert". Also, I know that the class I created to standardise the connection-insertion process with SQl Alchemy could be a bit crap, so if someone has any suggestions, they would be more than welcomed.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="class SQLAlchemy_cashflow():
    def __init__(self,database, user, password, sql_schema, driver, server):
        self.sql_schema = sql_schema
        self.driver = driver
        self.server = server
        self.database = database
        self.user = user
        self.password = password

        params = urllib.parse.quote_plus('Driver={'+str(self.driver)+'};'\
                                    &quot;SERVER=&quot;+str(self.server)+&quot;;&quot;\
                                    &quot;Database=&quot;+str(self.database)+&quot;;&quot;
                                    &quot;UID=&quot;+str(self.user)+&quot;;&quot;\
                                    &quot;PWD=&quot;+str(self.password)+&quot;;&quot;\
                                    &quot;Trusted_Connection=yes&quot;
                                    )
        engine = sa.create_engine(&quot;mssql+pyodbc:///?odbc_connect=%s&quot; % params)
        self.engine = engine

        conn = engine.connect()
        metadata = MetaData(schema=sql_schema)
        Base = declarative_base(metadata= metadata)
        Base.metadata.reflect(engine)
        Session = sessionmaker(bind=engine)
        session = Session()

        self.metadata = metadata
        self.Base = Base
        self.session = session


    def reflection(self, sql_table):
        self.sql_table = sql_table
        class MyClass(self.Base):
            __table__ = self.Base.metadata.tables[self.sql_table]
        table_reflected = Table(sql_table, self.metadata, autoload=True, autoload_with = self.engine)
        return MyClass, table_reflected
    

    def sqlalchemy_orm_bulk_insert(self,class_object, df):
        connection(&quot;dbo&quot;)
        t0 = time.time()
        session.bulk_insert_mappings(class_object,
                                    df.to_dict(orient=&quot;record&quot;))
        session.commit()
        print(&quot;SQLAlchemy ORM bulk_insert_mappings(): Total time for &quot; + str(len(df)) +
            &quot; records &quot; + str(time.time() - t0) + &quot; secs&quot;)


    def sqlalchemy_core(self, class_object, df):
        connection(&quot;dbo&quot;)
        t0 = time.time()
        engine.execute(class_object.__table__.insert(),
                    df.to_dict(orient=&quot;record&quot;))
        print(&quot;SQLAlchemy Core: Total time for &quot; + str(len(df)) + &quot; records &quot; +
            str(time.time() - t0) + &quot; secs&quot;)

"><pre class="notranslate"><code class="notranslate">class SQLAlchemy_cashflow():
    def __init__(self,database, user, password, sql_schema, driver, server):
        self.sql_schema = sql_schema
        self.driver = driver
        self.server = server
        self.database = database
        self.user = user
        self.password = password

        params = urllib.parse.quote_plus('Driver={'+str(self.driver)+'};'\
                                    "SERVER="+str(self.server)+";"\
                                    "Database="+str(self.database)+";"
                                    "UID="+str(self.user)+";"\
                                    "PWD="+str(self.password)+";"\
                                    "Trusted_Connection=yes"
                                    )
        engine = sa.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
        self.engine = engine

        conn = engine.connect()
        metadata = MetaData(schema=sql_schema)
        Base = declarative_base(metadata= metadata)
        Base.metadata.reflect(engine)
        Session = sessionmaker(bind=engine)
        session = Session()

        self.metadata = metadata
        self.Base = Base
        self.session = session


    def reflection(self, sql_table):
        self.sql_table = sql_table
        class MyClass(self.Base):
            __table__ = self.Base.metadata.tables[self.sql_table]
        table_reflected = Table(sql_table, self.metadata, autoload=True, autoload_with = self.engine)
        return MyClass, table_reflected
    

    def sqlalchemy_orm_bulk_insert(self,class_object, df):
        connection("dbo")
        t0 = time.time()
        session.bulk_insert_mappings(class_object,
                                    df.to_dict(orient="record"))
        session.commit()
        print("SQLAlchemy ORM bulk_insert_mappings(): Total time for " + str(len(df)) +
            " records " + str(time.time() - t0) + " secs")


    def sqlalchemy_core(self, class_object, df):
        connection("dbo")
        t0 = time.time()
        engine.execute(class_object.__table__.insert(),
                    df.to_dict(orient="record"))
        print("SQLAlchemy Core: Total time for " + str(len(df)) + " records " +
            str(time.time() - t0) + " secs")


</code></pre></div>
<p dir="auto">Hi, I recently ran into a situation where a query was accidentally returning 100x the number of rows it should have due to a bad join, but it slipped by for a long time because the de-duping logic in orm/loading.py:instances saved us:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="        if filtered:
            rows = util.unique_list(rows, filter_fn)"><pre class="notranslate"><code class="notranslate">        if filtered:
            rows = util.unique_list(rows, filter_fn)
</code></pre></div>
<p dir="auto">I was wondering if it would be a good idea to add an optional warning to catch discrepancies between the raw row count and the unique count. If so, I'd be happy to submit a PR.</p>
<p dir="auto">Thanks!</p>