Python Forum

I have the code below for migration from mysql to postgresql.

def get_mysql_connection(database_name: Optional[str] = None) -> MysqlConnection:
    if database_name is None:
        database_name = ""
    return pymysql.connect(host=DB_HOST, port=int(3306), user=MYSQL_DB_USER, passwd=password, db=MYSQL_DB_NAME)


def create_mysql_database(connection: MysqlConnection) -> None:
    # create mysql database
    connection.cursor().execute("DROP DATABASE IF EXISTS {};".format(MYSQL_DB_NAME))
    connection.cursor().execute("CREATE DATABASE {};".format(MYSQL_DB_NAME))
    connection.close()


def get_postgresql_connection(database_name: Optional[str] = None) -> PostgresConnection:
    if database_name is None:
        database_name = "postgres"
    engine = create_engine(f'postgresql://{POSTGRESQL_DB_USER}:{password}@{DB_HOST}/{database_name}', echo=False,
                           isolation_level="AUTOCOMMIT")
    return engine.connect()


def create_postgres_database(connection: PostgresConnection) -> None:
    connection.execute("DROP DATABASE IF EXISTS {};".format(POSTGRESQL_DB_NAME))
    connection.execute("CREATE DATABASE {};".format(POSTGRESQL_DB_NAME))
    connection.close()

def migrate_data_from_mysql_to_postgres(mysql_conn: MysqlConnection, postgres_conn: PostgresConnection) -> None:

    tables_mysql = pd.read_sql_query("""SHOW TABLES""", mysql_conn)

    for table in tables_mysql["""Tables_in_{}""".format(MYSQL_DB_NAME)]:
        query = f"""SELECT * FROM '{table}'"""
        table_chunks = pd.read_sql_query(query, mysql_conn, chunksize=100)

        for chunk in table_chunks:
            table_cols = chunk.columns

            new_col_names = []
            for col_name in table_cols:
                new_col_names.append(to_camel_case(col_name))
            chunk.columns = new_col_names

            print(chunk.columns)
            print('====')

            # load chunk into postgresql database
            chunk.to_sql('{}'.format(table), con=postgres_conn, if_exists='append', index=False)


def main() -> None:
    mysql_conn = get_mysql_connection()

    create_mysql_database(mysql_conn)

    mysql_conn = get_mysql_connection(MYSQL_DB_NAME)

    load_data_dump("ex-str.sql", mysql_conn)

    postgres_conn = get_postgresql_connection()

    create_postgres_database(postgres_conn)

    postgres_conn = get_postgresql_connection(POSTGRESQL_DB_NAME)

    migrate_data_from_mysql_to_postgres(mysql_conn, postgres_conn)

Im running this code on ubuntu 18.04 with python3.8 and mysql 14.
I got an error message like:

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''<style>\\r\\n\\r\\nbody {\\r\\n\tbackground: linear-gradient(-45deg, #ee7752, #e73c7e,' at line 1")

How can i fix this error?

(May-17-2022, 07:43 AM)ilknurg Wrote: [ -> ]How can i fix this error?

Difficult to say. First isolate the offending line in your code. Then add print statements before this offending line to print the variables the offending statement is using.
Then probably it wil be clearer what is happening.

(May-17-2022, 08:05 AM)ibreeden Wrote: [ -> ]

(May-17-2022, 07:43 AM)ilknurg Wrote: [ -> ]How can i fix this error?

Difficult to say. First isolate the offending line in your code. Then add print statements before this offending line to print the variables the offending statement is using.
Then probably it wil be clearer what is happening.

what do you mean by offending line?

Get Python to output the query you are creating as a string, copy it.

Go to phpMyAdmin, try the SQL query you want to pass directly in phpMyAdmin.

Tinker with it until it works.

When it works, change your code.

What is this doing in an SQL query????

Quote:''<style>\\r\\n\\r\\nbody {\\r\\n\tbackground: linear-gradient(-45deg, #ee7752, #e73c7e,' at line 1")

(May-17-2022, 09:35 AM)ilknurg Wrote: [ -> ]what do you mean by offending line?

The line in your code where the error occurs.