How does one clean a populated table in MySQL/MariaDB? Copying values across tables?

[BrandonKastning]

I have been working on this and so far I have come up with the following: Not working yet, but I am certainly getting closer! Even working on upgrading my coding style by adding Print Lines. Once it works; it should be a nice script to build with.

Sources of Learning: Blogs/Tutorials:


https://support.awesome-table.com/hc/en-...onditions-
https://python-forum.io/thread-35939.html
https://www.w3schools.com/python/ref_func_print.asp

I have added two more tables; the Source Table: "NJ_Cities_CSV" contained more types of municipalities, such as Villages and Boroughs; I added those. 4 Table INSERT payload from criteria specified SQL Statements (which I haven't figured out 100% just yet) ...


Code So far:

# Learning on Python-Forum.io
# Attempt #1
# Disabled American Constitutional Pre-Law Student: BrandonKastning
# Date: 01/15/2021
# Script: Kajukenbo_divide_by_criteria_mariadb.pymysql.py
# Purpose: Building Block for Python 3.9.9 + MariaDB 10.3.27
# Thread URL with Sources of Learning (Cited on Board)
# Thread URL: https://python-forum.io/thread-36085.html
 
# Kajukenbo
# DB: Exodus_J3x_Dev_Bronson
# Source Table: NJ_Cities_CSV
# Destination Table #1: NJ_Only_Cities
# Destination Table #2: NJ_Only_Towns
# Destination Table #3: NJ_Only_Villages
# Destination Table #4: NJ_Only_Boroughs
# Purpose: Populate column "american_city_name" from Source Table to Destination Table #1
# Purpose: Populate column "american_town_name" from Source Table to Destination Table #2
# Purpose: Populate column "american_village_name" from Source Table to Destination Table #3
# Purpose: Populate column "american_borough_name" from Source Table to Destination Table #4
import pymysql.cursors
 
# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='brandon',
                             password='password',
                             database='Exodus_J3x_Dev_Bronson',
                             cursorclass=pymysql.cursors.DictCursor)
 
with connection:
    with connection.cursor() as cursor:
        sql = "INSERT INTO 'NJ_Only_Cities' (`american_city_name`) SELECT * WHERE type = 'City' FROM `NJ_Cities_CSV`;"
        cursor.execute(sql)
print("American Cities Payload Executed without Error! [Line 33]")

with connection:
    with connection.cursor() as cursor:
        sql = "INSERT INTO 'NJ_Only_Towns' (`american_town_name`) SELECT * WHERE type = 'Town' or type = 'Township' FROM `NJ_Cities_CSV`;"
        cursor.execute(sql)
print("American Towns Payload Executed without Error! [Line 39]")

with connection:
    with connection.cursor() as cursor:
        sql = "INSERT INTO 'NJ_Only_Villages' (`american_village_name`) SELECT * WHERE type = 'Village' FROM `NJ_Cities_CSV`;"
        cursor.execute(sql)
print("American Villages Payload Executed without Error! [Line 45]")

with connection:
    with connection.cursor() as cursor:
        sql = "INSERT INTO 'NJ_Only_Boroughs' (`american_borough_name`) SELECT * WHERE type = 'Borough' FROM `NJ_Cities_CSV`;"
        cursor.execute(sql)
print("American Boroughs Payload Executed without Error! [Line 51]")

My output is as follows:

root@icedragon:/home/brandon/Desktop/Exodus/Python.Scripts/Python.Forum.io/06_Kajukenbo# python3.9 Kajukenbo_divide_by_criteria_mariadb.pymysql.py
Traceback (most recent call last):
  File "/home/brandon/Desktop/Exodus/Python.Scripts/Python.Forum.io/06_Kajukenbo/Kajukenbo_divide_by_criteria_mariadb.pymysql.py", line 33, in <module>
    cursor.execute(sql)
  File "/usr/local/lib/python3.9/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.9/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.9/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.9/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''NJ_Only_Cities' (`american_city_name`) SELECT * WHERE type = 'City' FROM `NJ...' at line 1")

I believe I have to modify my SQL Statement(s) x 4 to match a working Syntax. Not sure where I went wrong.

Thank you everyone!

Best Regards,

Brandon Kastning