Working on moving the wiki tables from csv I have completed broken code. I am generating errors regarding package installation. I am not quite sure how to fix this.
Source: Blogs/Tutorials:
https://stackoverflow.com/questions/10154633/load-csv-data-into-mysql-in-python
This is what I have come up with so far:
Created a database called: "Eggnest" and a Table called "csv_wiki_counties_States_WA":
This is the CSV open in LibreOffice Calc trimming row 1 by opening starting with row 2:
import pandas as pd
url = "https://en.wikipedia.org/wiki/List_of_counties_in_Washington"
tables = pd.read_html(url)
tables[1]
print(tables[1])
df = tables[1]
df.to_csv('wiki_pandas_df_output_csv_to_sql2.csv', sep='\t', encoding='utf-8', index=False)
import csv
import MySQLdb
mydb = MySQLdb.connect(host='localhost',
user="brandon",
passwd="password",
db="Eggnest"
)
mycursor = mydb.cursor()
dataframe = csv.reader(open('wiki_pandas_df_output_csv_to_sql2.csv'))
print(dataframe)
for row in dataframe:
mycursor.execute('INSERT INTO csv_wiki_counties_States_WA (american_county_name, american_county_fips_code, american_county_est, american_county_formed_from, american_county_etymology, american_county_population_census_2020, american_county_land_area") VALUES("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")', row)
mydb.commit()
cursor.close()
I am getting the following output with zero successful INSERT's...
brandon@FireDragon:~/Python/03_American-Kenpo$ python3 wiki_pandas_df_csv_2_sql2.py
County FIPS code County seat[11] ... Population (2020) Land area[11] Map
0 Adams County 1 Ritzville ... 20613 1,925 sq mi(4,986 km2) NaN
1 Asotin County 3 Asotin ... 22285 636 sq mi(1,647 km2) NaN
2 Benton County 5 Prosser ... 206873 1,700 sq mi(4,403 km2) NaN
3 Chelan County 7 Wenatchee ... 79074 2,920 sq mi(7,563 km2) NaN
4 Clallam County 9 Port Angeles ... 77155 1,738 sq mi(4,501 km2) NaN
5 Clark County 11 Vancouver ... 503311 629 sq mi(1,629 km2) NaN
6 Columbia County 13 Dayton ... 3952 869 sq mi(2,251 km2) NaN
7 Cowlitz County 15 Kelso ... 110730 1,139 sq mi(2,950 km2) NaN
8 Douglas County 17 Waterville ... 42938 1,819 sq mi(4,711 km2) NaN
9 Ferry County 19 Republic ... 7178 2,204 sq mi(5,708 km2) NaN
10 Franklin County 21 Pasco ... 96749 1,242 sq mi(3,217 km2) NaN
11 Garfield County 23 Pomeroy ... 2286 710 sq mi(1,839 km2) NaN
12 Grant County 25 Ephrata ... 99123 2,680 sq mi(6,941 km2) NaN
13 Grays Harbor County 27 Montesano ... 75636 1,902 sq mi(4,926 km2) NaN
14 Island County 29 Coupeville ... 86857 209 sq mi(541 km2) NaN
15 Jefferson County 31 Port Townsend ... 32977 1,804 sq mi(4,672 km2) NaN
16 King County 33 Seattle ... 2269675 2,115 sq mi(5,478 km2) NaN
17 Kitsap County 35 Port Orchard ... 275611 395 sq mi(1,023 km2) NaN
18 Kittitas County 37 Ellensburg ... 44337 2,297 sq mi(5,949 km2) NaN
19 Klickitat County 39 Goldendale ... 22735 1,872 sq mi(4,848 km2) NaN
20 Lewis County 41 Chehalis ... 82149 2,403 sq mi(6,224 km2) NaN
21 Lincoln County 43 Davenport ... 10876 2,311 sq mi(5,985 km2) NaN
22 Mason County 45 Shelton ... 65726 959 sq mi(2,484 km2) NaN
23 Okanogan County 47 Okanogan ... 42104 5,268 sq mi(13,644 km2) NaN
24 Pacific County 49 South Bend ... 23365 933 sq mi(2,416 km2) NaN
25 Pend Oreille County 51 Newport ... 13401 1,400 sq mi(3,626 km2) NaN
26 Pierce County 53 Tacoma ... 921130 1,670 sq mi(4,325 km2) NaN
27 San Juan County 55 Friday Harbor ... 17788 174 sq mi(451 km2) NaN
28 Skagit County 57 Mount Vernon ... 129523 1,731 sq mi(4,483 km2) NaN
29 Skamania County 59 Stevenson ... 12036 1,656 sq mi(4,289 km2) NaN
30 Snohomish County 61 Everett ... 827957 2,087 sq mi(5,405 km2) NaN
31 Spokane County 63 Spokane ... 539339 1,764 sq mi(4,569 km2) NaN
32 Stevens County 65 Colville ... 46445 2,478 sq mi(6,418 km2) NaN
33 Thurston County 67 Olympia ... 294793 722 sq mi(1,870 km2) NaN
34 Wahkiakum County 69 Cathlamet ... 4422 264 sq mi(684 km2) NaN
35 Walla Walla County 71 Walla Walla ... 62584 1,270 sq mi(3,289 km2) NaN
36 Whatcom County 73 Bellingham ... 226847 2,107 sq mi(5,457 km2) NaN
37 Whitman County 75 Colfax ... 47973 2,159 sq mi(5,592 km2) NaN
38 Yakima County 77 Yakima ... 256728 4,296 sq mi(11,127 km2) NaN
39 Washington (state) 53 Olympia (state capital) ... 7705281 66,544 sq mi (172,350 km2) NaN
[40 rows x 9 columns]
<_csv.reader object at 0x7fd3f8c396d8>
Traceback (most recent call last):
File "wiki_pandas_df_csv_2_sql2.py", line 25, in <module>
mycursor.execute('INSERT INTO csv_wiki_counties_States_WA (american_county_name, american_county_fips_code, american_county_est, american_county_formed_from, american_county_etymology, american_county_population_census_2020, american_county_land_area") VALUES("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")', row)
File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 210, in execute
query = query % args
TypeError: not enough arguments for format string
brandon@FireDragon:~/Python/03_American-Kenpo$
If anyone has an idea why this doesn't work! I would really like to know why! Thank you! :)