Python Forum
Python Obstacles | Krav Maga | Wiki Scraped Content [Column Copy]
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python Obstacles | Krav Maga | Wiki Scraped Content [Column Copy]
#1
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:

[Image: 1-2021-12-31-20-24-35.png]

[Image: 2-2021-12-31-20-26-33.png]

[Image: 3-2021-12-31-20-27-03.png]

[Image: 4-2021-12-31-20-27-28.png]

[Image: 5-2021-12-31-20-27-53.png]
geojson structure

[Image: 6-2021-12-31-20-28-18.png]

[Image: 7-2021-12-31-20-28-41.png]

[Image: 8-2021-12-31-20-29-03.png]


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
“And one of the elders saith unto me, Weep not: behold, the Lion of the tribe of Juda, the Root of David, hath prevailed to open the book,...” - Revelation 5:5 (KJV)

“And oppress not the widow, nor the fatherless, the stranger, nor the poor; and ...” - Zechariah 7:10 (KJV)

#LetHISPeopleGo

Reply
#2
You can use PyMySQL to talk to the database: https://pypi.org/project/PyMySQL/.
BrandonKastning likes this post
Reply
#3
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/.
“And one of the elders saith unto me, Weep not: behold, the Lion of the tribe of Juda, the Root of David, hath prevailed to open the book,...” - Revelation 5:5 (KJV)

“And oppress not the widow, nor the fatherless, the stranger, nor the poor; and ...” - Zechariah 7:10 (KJV)

#LetHISPeopleGo

Reply
#4
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/.
“And one of the elders saith unto me, Weep not: behold, the Lion of the tribe of Juda, the Root of David, hath prevailed to open the book,...” - Revelation 5:5 (KJV)

“And oppress not the widow, nor the fatherless, the stranger, nor the poor; and ...” - Zechariah 7:10 (KJV)

#LetHISPeopleGo

Reply
#5
Python Solution!

Source Table:

[Image: SOURCE-2022-01-02-22-12-32.png]
download avatar instagram online

Current Destination Table:

[Image: kapap-CURRENT-2022-01-02-22-11-53.png]

Solution:

[Image: SOLUTION-2022-01-02-22-34-16.png]
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
“And one of the elders saith unto me, Weep not: behold, the Lion of the tribe of Juda, the Root of David, hath prevailed to open the book,...” - Revelation 5:5 (KJV)

“And oppress not the widow, nor the fatherless, the stranger, nor the poor; and ...” - Zechariah 7:10 (KJV)

#LetHISPeopleGo

Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Weird characters scraped samuelbachorik 3 857 Oct-29-2023, 02:36 PM
Last Post: DeaD_EyE
  Web scraper not populating .txt with scraped data BlackHeart 5 1,457 Apr-03-2023, 05:12 PM
Last Post: snippsat
  Retrieve website content using Python? Vadanane 1 1,197 Jan-16-2023, 09:55 AM
Last Post: Axel_Erfurt
Question Python Obstacles | Jeet-Kune-Do | BS4 (Tags > MariaDB) [URL/Local HTML] BrandonKastning 0 1,400 Feb-08-2022, 08:55 PM
Last Post: BrandonKastning
  Python Obstacles | Kapap | Wiki Scraped Content [Column Nulling] BrandonKastning 2 1,690 Jan-03-2022, 04:26 AM
Last Post: BrandonKastning
Lightbulb Python Obstacles | Kung-Fu | Full File HTML Document Scrape and Store it in MariaDB BrandonKastning 5 2,819 Dec-29-2021, 02:26 AM
Last Post: BrandonKastning
  Python Obstacles | American Kenpo | Wiki Scrape URL/Table and Store it in MariaDB BrandonKastning 6 2,784 Dec-29-2021, 12:38 AM
Last Post: BrandonKastning
  Python Obstacles | Karate | HTML/Scrape Specific Tag and Store it in MariaDB BrandonKastning 8 3,091 Nov-22-2021, 01:38 AM
Last Post: BrandonKastning
  Python Web Scraping can not getting all HTML content yqqwe123 0 1,616 Aug-02-2021, 08:56 AM
Last Post: yqqwe123
  to scrape wiki-page: getting back the results - can i use pandas also apollo 2 2,602 Feb-09-2021, 03:57 PM
Last Post: apollo

Forum Jump:

User Panel Messages

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