May-17-2022, 07:43 AM
I have the code below for migration from mysql to postgresql.
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?
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?