Python Forum
Mysql Syntax error in pymysql
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Mysql Syntax error in pymysql
#1
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?
Reply
#2
(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.
Reply
#3
(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?
Reply
#4
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")
buran likes this post
Reply
#5
(May-17-2022, 09:35 AM)ilknurg Wrote: what do you mean by offending line?
The line in your code where the error occurs.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Syntax error for "root = Tk()" dlwaddel 15 1,008 Jan-29-2024, 12:07 AM
Last Post: dlwaddel
Photo SYNTAX ERROR Yannko 3 334 Jan-19-2024, 01:20 PM
Last Post: rob101
  Mysql and mysql.connector error lostintime 2 611 Oct-03-2023, 10:25 PM
Last Post: lostintime
  Syntax error while executing the Python code in Linux DivAsh 8 1,451 Jul-19-2023, 06:27 PM
Last Post: Lahearle
  Code is returning the incorrect values. syntax error 007sonic 6 1,135 Jun-19-2023, 03:35 AM
Last Post: 007sonic
  syntax error question - string mgallotti 5 1,250 Feb-03-2023, 05:10 PM
Last Post: mgallotti
  Syntax error? I don't see it KenHorse 4 1,194 Jan-15-2023, 07:49 PM
Last Post: Gribouillis
  Syntax error tibbj001 2 847 Dec-05-2022, 06:38 PM
Last Post: deanhystad
  Python-for-Android:p4a: syntax error in main.py while compiling apk jttolleson 2 1,777 Sep-17-2022, 04:09 AM
Last Post: jttolleson
  Solving equation equal to zero: How to resolve the syntax error? alexfrol86 3 1,894 Feb-21-2022, 08:58 AM
Last Post: deanhystad

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020