2

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 ^_^

4
  • 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. Commented Apr 23, 2023 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 :( Commented Apr 23, 2023 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. Commented Apr 23, 2023 at 15:45
  • Aside, consider slight revision per this answer for efficiency. Though there may be other newer bulk methods in SQLAlchemy, so read more recent dated answers. Commented Apr 23, 2023 at 15:45

1 Answer 1

0

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': '[email protected]',
        '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 :(

Sign up to request clarification or add additional context in comments.

1 Comment

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.