Python Forum

Python Obstacles | Krav Maga | Wiki Scraped Content [Column Copy]

Sources - Blogs/Tutorials:

https://stackoverflow.com/questions/1116...r/11168786

Fresh to Python and MariaDB; I am learning basic MariaDB techniques for Python Data Science objectives.

Since completing my thread I started : https://python-forum.io/thread-35892-post-151252.html

I learned how to Scrape Wikipedia Article Tables into a CSV file; then name the header row 1 in LibreOffice Calc, save the output and then run another Python script to INSERT it to a MariaDB database.

Next I just learned and need to learn how to execute the same solution in Python. I don't know how to do this just yet.

My database: Eggnest

Set 1:

FROM TABLE:

csv_counties_IA

FROM COLUMN: 

american_county_name

Set 2:

TO TABLE:

WTPO_Dirty_Wiki_Counties_IA

TO COLUMN:

american_county_name

Copying the harvested using Python and Pandas; CSV stored and then INSERTED into MariaDB.

New Table Created for Scraped Data from Wiki Article Table

*Cleaning Up Data*

I have never done this and it worked beautifully!

I had to set AUTO_INCREMENT on the id column with primary key (Which is needed to create a main index file for all database records within the table). It's the official ordering of row on top of row. You cannot reset it easily and there are many reasons developers will say one way or another regarding primary keys from what I have read so far.

I think the id field is critical and I have been applying it to all my database designs to date.

TIMESTAMP is set to "CURRENT_TIMESTAMP" which is my last column, named : american_location_county_timestamp

What I learned was that issuing the following command after having my database "Eggnest" prepped with my two tables. Dirty Wiki Article Table Data (with all the other) and copy it to a database that I have designed as the destination for the payload.

I use the database format: utf8mb4_unicode_ci For two reasons: 1) Full-text Search Ability & 2) Allows for Special Characters such as the American Legal Symbol § [section symbol] which I use often with Studying American Constitutional Supreme Law.

I was in MariaDB console as a Super User:

Code once in MariaDB Console as Super User w/ Database Selected:

INSERT INTO `WTPO_Dirty_Wiki_Counties_IA` (`american_county_name`) SELECT `american_county_name` FROM `csv_counties_IA`;

Full Console Record:

MariaDB [Eggnest]> INSERT INTO `WTPO_Dirty_Wiki_Counties_IA` (`american_county_name`) SELECT `american_county_name` FROM `csv_counties_IA`;
Query OK, 99 rows affected (0.083 sec)
Records: 99  Duplicates: 0  Warnings: 0

MariaDB [Eggnest]>

The following are screenshots:










geojson structure








I would like to know how to do this with Python and not even have to login to MariaDB from Command Line for this type of Operation in Big Database Creation(s).

Thank you everyone for this forum! :)

Best Regards,

Brandon Kastning
Disabled American Pre-Law
Constitutional America Forever

You can use PyMySQL to talk to the database: https://pypi.org/project/PyMySQL/.

ndc85430,

Thank you! I am learning SQLAlchemy right now! It seems to be incredibly powerful and versatile.

Happy New Years!

Best Regards,

Brandon

(Jan-01-2022, 07:37 AM)ndc85430 Wrote: [ -> ]You can use PyMySQL to talk to the database: https://pypi.org/project/PyMySQL/.

ndc85430,

You are right! PyMySQL is the best way to pass a MariaDB/MySQL Statement through Python! Thank you! I also gave thanks to you in my other thread: Kapap

(Jan-01-2022, 07:37 AM)ndc85430 Wrote: [ -> ]You can use PyMySQL to talk to the database: https://pypi.org/project/PyMySQL/.

Python Solution!

Source Table:


download avatar instagram online

Current Destination Table:



Solution:


instagram photo full size download

Python Code:

# Finalized on Python-Forum.io
# Disabled American Constitutional Pre-Law Student: BrandonKastning
# Date: 01/02/2021
# Script: kravmaga_WTPO_IA.py
# Purpose: Building Block for Python 3.9.9 + MariaDB 10.4.22
# Thread URL with Sources of Learning (Cited on Board)
# Thread URL: https://python-forum.io/thread-35939-post-151485.html

# Krav Maga
# DB: Eggnest
# Source Table: WTPO_Dirty_Wiki_Counties_IA
# Destination Table: WTPO_Dirty_Wiki_Counties_IA_TWO
# Purpose: Populate column "american_county_name" from Source Table to Destination Table

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='brandon',
                             password='password',
                             database='Eggnest',
                             cursorclass=pymysql.cursors.DictCursor)

with connection:
    with connection.cursor() as cursor:
        sql = "INSERT INTO `WTPO_Dirty_Wiki_Counties_IA_TWO` (`american_county_name`) SELECT `american_county_name` FROM `WTPO_Dirty_Wiki_Counties_IA`;"
        cursor.execute(sql)

It works! However it doesn't start on row 1; it INSERTED new rows on top of all the nulled values within the 99 row range. Then added from 100+

I wish I knew how to "if row 1 = value = NULL then replace" *or something* !

Thank you everyone for this forum!

Best Regards,

Brandon Kastning