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

[BrandonKastning]

Source of Assistance:

I was able to get some assistance from #mysql on Libera.chat (IRC Network)

• - thumbs (Quotes)
  - Xgc (Syntax) & (DB Normalization & Importance)
  

MariaDB took the following STATEMENTS and it worked!

However, it does not work in pymysql for some reason! I can execute a .py with the pymysql and my working Maria DB SQL STATEMENT and upon refresh and view table (shows nothing); then if I execute it from MariaDB CLI and execute it; it works and when I refresh, the ID columns are (the amount of times I executed the python script -- yet the values are hidden and it just continues from a high # such as ID = 190 instead of 1 because it ran with pymysql and python 3.9; just a glitch or something).

The working MariaDB SQL STATEMENT for the following destination columns for the 4 types of Municipalities for New Jersey scraped from Wikipedia are as follows:

id / INT / 11 - AUTO_INCREMENT + PRIMARY KEY
number / BIGINT / 20 
name / TEXT
type / TEXT
county / TEXT
population_2020_census / BIGINT / 20
population_2010_census / BIGINT / 20
change / TEXT
sq_mi / DOUBLE
km2 / DOUBLE
population_density / TEXT
form_of_government / TEXT
incorporated_year / TEXT
american_constitutional_municipality_entry_timestamp / TIMESTAMP / CURRENT_TIMESTAMP

Destination Tables with the above schema & columns:

• NJ_Only_Cities_3 (Worked 100% w/ CLI STATEMENT)
  NJ_Only_Cities_4 (The Glitch w/ PyMYSQL)
  NJ_Only_Towns (Worked 100% w/ CLI STATEMENT)
  NJ_Only_Villages (Worked 100% w/ CLI STATEMENT)
  NJ_Only_Boroughs (Worked 100% w/ CLI STATEMENT)
  

NJ_Only_Cities_3:

MariaDB [Exodus_J3x_Dev_Bronson]> INSERT INTO `NJ_Only_Cities_3` (`number`,`name`,`type`,`county`,`population_2020_census`,`population_2010_census`,`change`,`sq_mi`,`km2`,`population_density`,`form_of_government`,`incorporated_year`) SELECT `number`,`name`,`type`,`county`,`population_2020_census`,`population_2010_census`,`change`,`sq_mi`,`km2`,`population_density`,`form_of_government`,`incorporated_year` FROM `NJ_Cities_CSV` WHERE (`type`) = 'City'; 
Query OK, 52 rows affected (0.093 sec)
Records: 52  Duplicates: 0  Warnings: 0

Screenshot:



Screenshot (From the pymysql payload python script which did not error) (I executed it several times before manually executing the STATEMENT on MariaDB CLI); then I see the glitch:



NJ_Only_Towns:

MariaDB [Exodus_J3x_Dev_Bronson]> INSERT INTO `NJ_Only_Towns` (`number`,`name`,`type`,`county`,`population_2020_census`,`population_2010_census`,`change`,`sq_mi`,`km2`,`population_density`,`form_of_government`,`incorporated_year`) SELECT `number`,`name`,`type`,`county`,`population_2020_census`,`population_2010_census`,`change`,`sq_mi`,`km2`,`population_density`,`form_of_government`,`incorporated_year` FROM `NJ_Cities_CSV` WHERE (`type`) = 'Town' or (`type`) ='Township';"
Query OK, 256 rows affected (0.203 sec)
Records: 256  Duplicates: 0  Warnings: 0

Screenshot:



NJ_Only_Villages:

MariaDB [Exodus_J3x_Dev_Bronson]> INSERT INTO `NJ_Only_Villages` (`number`,`name`,`type`,`county`,`population_2020_census`,`population_2010_census`,`change`,`sq_mi`,`km2`,`population_density`,`form_of_government`,`incorporated_year`) SELECT `number`,`name`,`type`,`county`,`population_2020_census`,`population_2010_census`,`change`,`sq_mi`,`km2`,`population_density`,`form_of_government`,`incorporated_year` FROM `NJ_Cities_CSV` WHERE (`type`) = 'Village';
Query OK, 3 rows affected (0.082 sec)
Records: 3  Duplicates: 0  Warnings: 0

Screenshot:



NJ_Only_Boroughs:

MariaDB [Exodus_J3x_Dev_Bronson]> INSERT INTO `NJ_Only_Boroughs` (`number`,`name`,`type`,`county`,`population_2020_census`,`population_2010_census`,`change`,`sq_mi`,`km2`,`population_density`,`form_of_government`,`incorporated_year`) SELECT `number`,`name`,`type`,`county`,`population_2020_census`,`population_2010_census`,`change`,`sq_mi`,`km2`,`population_density`,`form_of_government`,`incorporated_year` FROM `NJ_Cities_CSV` WHERE (`type`) = 'Borough';
Query OK, 253 rows affected (0.141 sec)
Records: 253  Duplicates: 0  Warnings: 0

Screenshot:



Thank you everyone for this forum and all your help! If anyone knows about this PyMySQL glitch populating databases (yet invisible; somehow the column ID's are generated even though it doesn't properly payload). Very strange!

Best Regards,

Brandon Kastning