Insert error using SQL Alchemy to transfer data from SQL Server to MariaDB

Question

Using SQL Alchemy, I am trying to move data from a SQL Server table to a MariaDB table. The tables are almost the same, as you can see in this code:

MariaDB table :

CREATE TABLE IF NOT EXISTS customers
(
    id           INT PRIMARY KEY,
    first_name   VARCHAR(50)  NOT NULL,
    last_name    VARCHAR(50)  NOT NULL,
    email        VARCHAR(50)  NOT NULL UNIQUE,
    phone_number VARCHAR(15)  NOT NULL,
    address      VARCHAR(150) NOT NULL,
    city         VARCHAR(50)  NOT NULL,
    state        VARCHAR(50)  NOT NULL,
    zip_code     VARCHAR(10)  NOT NULL,
    create_date  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_date  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

SQL Server table :

CREATE TABLE customers
(
    id           INT PRIMARY KEY IDENTITY (1,1),
    first_name   VARCHAR(50)  NOT NULL,
    last_name    VARCHAR(50)  NOT NULL,
    email        VARCHAR(50)  NOT NULL UNIQUE,
    phone_number VARCHAR(15)  NOT NULL,
    address      VARCHAR(150) NOT NULL,
    city         VARCHAR(50)  NOT NULL,
    state        VARCHAR(50)  NOT NULL,
    zip_code     VARCHAR(10)  NOT NULL,
    create_date  DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_date  DATETIME DEFAULT CURRENT_TIMESTAMP
);

And my code is like this :

def main():
    # Connection strings
    sql_server_conn_str = CONFIG['connectionStrings']['sqlServer']
    maria_conn_str = CONFIG['connectionStrings']['mariaDb']

    # create SQLAlchemy engine for SQL Server and MariaDB
    sql_server_engine = create_engine(sql_server_conn_str)
    maria_engine = create_engine(maria_conn_str)

    # create connection for both db
    sql_server_conn = sql_server_engine.connect()
    maria_conn = maria_engine.connect();

    # create SQLAlchemy MetaData objects for SQL Server and MariaDB
    sql_server_metadata = MetaData()
    maria_metadata = MetaData()

    # reflect the SQL Server database schema into the MetaData object
    sql_server_metadata.reflect(bind=sql_server_engine)

    # create Table objects for each SQL Server table
    customers_sql_server = Table('customers', sql_server_metadata, autoload=True, autoload_with=sql_server_engine)

    # reflect the MariaDB database schema into the MetaData object
    maria_metadata.reflect(bind=maria_engine)

    # create Table objects for each MariaDB table
    customers_maria = Table('customers', maria_metadata, autoload=True, autoload_with=maria_engine)

    # select all rows from the customers table in SQL Server
    select_customers_sql_server = select(customers_sql_server)

    # execute the select query and fetch all rows
    result_proxy = sql_server_conn.execute(select_customers_sql_server)
    customers_data = result_proxy.fetchall()

    # insert the rows into the customers table in MariaDB
    tuples_to_insert = [tuple(row) for row in customers_data]
    maria_conn.execute(customers_maria.insert(), tuples_to_insert)

I have this error I couldn't solve:

Traceback (most recent call last):   ...\main.py", line 48, in <module>
        main()   File "...\main.py", line 35, in main
        maria_conn.execute(customers_maria.insert(), tuples_to_insert)   File "...\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1413, in execute
        return meth(
               ^^^^^   File "...\venv\Lib\site-packages\sqlalchemy\sql\elements.py", line 483, in
    _execute_on_connection
        return connection._execute_clauseelement(
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^   File "...\venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1613, in
    _execute_clauseelement
        keys = sorted(distilled_parameters[0])
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

TypeError: '<' not supported between instances of 'str' and 'int'

Sorry, I am new to Python ^_^

Check the contents of `customers_data` and `tuples_to_insert` before the `insert`. Is it empty? In proper order of columns? Did values convert with `tuple` call? You may not need `tuples_to_insert`. — Parfait, Apr 23 '23 at 15:32
@Parfait, yes, I checked everything. The values I receive are absolutely OK. Also, I tried to insert a hardcoded tuple and I had the same error :( — Oleg Ivsv, Apr 23 '23 at 15:36
Not sure if your MariaDB `customer.id` is autonumber but try leaving out `id` in both tables including hardcoded attempt. Usually you do not insert into autonumber fields. — Parfait, Apr 23 '23 at 15:45
Aside, consider slight revision per this [answer](https://stackoverflow.com/a/27235131/1422451) for efficiency. Though there may be other newer bulk methods in SQLAlchemy, so read more recent dated answers. — Parfait, Apr 23 '23 at 15:45

score 0 · Answer 1 · answered Apr 23 '23 at 16:00

The problem is that the tuple can't contain values of different types. So the solution is to cast everything to string. Like this:

    customer1 = {
        'id': '3333',
        'first_name': 'John',
        'last_name': 'Doe',
        'email': 'johndoe@example.com',
        'phone_number': '555-1234',
        'address': '123 Main St',
        'city': 'Anytown',
        'state': 'CA',
        'zip_code': '12345',
        'create_date': str(datetime(2023, 4, 23)),
        'update_date': str(datetime(2023, 4, 23))
    }

    maria_conn.execute(customers_maria.insert(), [customer1])
    maria_conn.commit()
    maria_conn.close();

The error message was not clear at all :(

Hmmm, mariadb isn't something I know much about, nor the tuple mechanism used (I assumed the tuples are used to *bind* to prepared sql statements). As a quick and dirty one-off, this looks fine. But, if I were doing a lot more of this, I'd be worried about converting to strings as an overall approach. What of, for example, a None/null in a column? Perhaps a `tuple(dict(row).values())` would be safer? (assuming `row` is sqlalchemy result row). — JL Peyret, Apr 23 '23 at 22:25

Insert error using SQL Alchemy to transfer data from SQL Server to MariaDB

1 Answers1