![]() |
How does one clean a populated table in MySQL/MariaDB? Copying values across tables? - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: Data Science (https://python-forum.io/forum-44.html) +--- Thread: How does one clean a populated table in MySQL/MariaDB? Copying values across tables? (/thread-36085.html) |
How does one clean a populated table in MySQL/MariaDB? Copying values across tables? - BrandonKastning - Jan-15-2022 How does one clean a populated table in MySQL/MariaDB? Copying values across tables? I was using python, pandas, pymysql, sqlalchemy, openpyxl, odfpy, requests and bs4 to aggregate data from Wikipedia and payload it into MariaDB databases. I am working on getting my countries Cities, Towns and corresponding Counties organized. This is what I currently have in one example (the table has both Cities and Towns; I want to separate them by copying all the Cities to a new Table in the same order they are set in the aggregated database and I want to copy all the Towns to a new Table in the same order as they are set in the aggregated database). *I hope that makes sense*. Current Table: NJ_Cities_CSV Columns:
Tables for Towns (NEW): NJ_Only_Towns Tables for Cities (NEW): NJ_Only_Cities How would I go about achieving this? I was hoping it would be as easy as a few SQL Statements and then write a python script to execute them using PyMySQL? Below is screenshots of the mixed table as aggregated from the following Wikipedia Article: https://en.wikipedia.org/wiki/List_of_municipalities_in_New_Jersey ![]() upload image online ![]() Thank you everyone for this forum! Best Regards, Brandon Kastning RE: How does one clean a populated table in MySQL/MariaDB? Copying values across tables? - BrandonKastning - Jan-15-2022 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-us/articles/115001152945-Use-Queries-to-show-records-based-on-conditions- 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 RE: How does one clean a populated table in MySQL/MariaDB? Copying values across tables? - BrandonKastning - Jan-17-2022 Source of Assistance: I was able to get some assistance from #mysql on Libera.chat (IRC Network)
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_TIMESTAMPDestination Tables with the above schema & columns:
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: 0Screenshot: ![]() 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: 0Screenshot: ![]() 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: 0Screenshot: ![]() 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: 0Screenshot: ![]() 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 |