SQLAlchemy - insert from a result object?-CodePudding

The below query makes a result set in the variable 'result'

I need to insert that into the iconndest (the new MySQL server). But I have no idea how to insert the query result into the new table? I just want to do Insert into DB.TBL SELECT * FROM RESULT. But I am not sure how?

import mysql.connector
import pandas as pd
from sqlalchemy import create_engine
import multiprocessing as mp
from multiprocessing import cpu_count

try:
    engine_source = create_engine("CONN STRING")

    iconn = engine_source.connect()

    result = iconn.execute('SELECT QUERY')

    print('EXTRACT COMPLETE')

    engine_dest = create_engine("CONN STRING")

    iconndest = engine_dest.connect()

    iconndest.execute('SELECT * from ')

    engine_source.dispose()
    engine_dest.dispose()

except Exception as e:
    print('extract: '   str(e))

CodePudding user response：

What you describe is very simple if we use .mappings() to convert the list of Row objects to a list of RowMapping objects when we retrieve the results. RowMapping objects behave like dict objects when passed as parameter values:

import sqlalchemy as sa

source_engine = sa.create_engine("mssql pyodbc://scott:tiger^5HHH@mssql_199")
destination_engine = sa.create_engine("sqlite://")

with source_engine.begin() as conn:
    results = (
        conn.exec_driver_sql(
            """\
    SELECT 1 AS id, N'foo' AS txt
    UNION ALL
    SELECT 2 AS id, N'bar' AS txt
    """
        )
        .mappings()
        .all()
    )

print(results)
# [{'id': 1, 'txt': 'foo'}, {'id': 2, 'txt': 'bar'}]

destination_engine.echo = True
with destination_engine.begin() as conn:
    conn.exec_driver_sql("CREATE TABLE t (id int, txt varchar(10))")
    conn.execute(
        sa.text("INSERT INTO t (id, txt) VALUES (:id, :txt)"), results
    )
    """SQL emitted:
    INSERT INTO t (id, txt) VALUES (?, ?)
    [generated in 0.00038s] ((1, 'foo'), (2, 'bar'))
    """