<jupyter_start><jupyter_text>Download Files CSV to use - https://github.com/wcota/covid19br - Great Work<jupyter_code>%%!
pip install --trusted-host pypi.org ipython-sql
%%!
pip install wget
%%!
pip install psycopg2
#Just to first time run
lastd=9
lastm=5
lasty=2020
import wget
import os
from datetime import datetime

url = "https://raw.githubusercontent.com/wcota/covid19br/master/cases-brazil-total.csv"
url2 = "https://github.com/wcota/covid19br/raw/master/cases-brazil-cities.csv"
filePath='C:/OSGeo4W64/bin/Andre/Covid-19Brazil/Tables/cases-brazil-total.csv'
filePath2='C:/OSGeo4W64/bin/Andre/Covid-19Brazil/Tables/cases-brazil-cities.csv'

url3 = "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/web-data/data/cases_country.csv"

filePath3='C:/OSGeo4W64/bin/Andre/Covid-19Brazil/Tables/cases_country.csv'

url4="https://data.humdata.org/hxlproxy/api/data-preview.csv?url=https%3A%2F%2Fraw.githubusercontent.com%2FCSSEGISandData%2FCOVID-19%2Fmaster%2Fcsse_covid_19_data%2Fcsse_covid_19_time_series%2Ftime_series_covid19_confirmed_global.csv&filename=time_series_covid19_confirmed_global.csv"
filePath4='C:/OSGeo4W64/bin/Andre/Covid-19Brazil/Tables/time_total_world.csv'

url5="https://data.humdata.org/hxlproxy/api/data-preview.csv?url=https%3A%2F%2Fraw.githubusercontent.com%2FCSSEGISandData%2FCOVID-19%2Fmaster%2Fcsse_covid_19_data%2Fcsse_covid_19_time_series%2Ftime_series_covid19_confirmed_global.csv&filename=time_series_covid19_confirmed_global.csv"
filePath5='C:/OSGeo4W64/bin/Andre/Covid-19Brazil/Tables/time_series_covid19_confirmed_global.csv'

now = datetime.now()
d= int(now.strftime("%d"))
m=int(now.strftime("%m"))
y= int(now.strftime("%Y"))

if lastd<d:
    print(lastd)
    print(d)
    #check if the month or year has changed
    lastd=d
    if lastm<m:
        lastd=1
    if lasty<y:
        lastd=1
        lastm=1
    #---------------------------------
    
    if os.path.exists(filePath):
        os.remove(filePath)
    else:
        print("Can not delete the file as it doesn't exists")

    if os.path.exists(filePath2):
        os.remove(filePath2)
        
    if os.path.exists(filePath3):
        os.remove(filePath3)
    else:
        print("Can not delete the file as it doesn't exists")
    try:
        wget.download(url,filePath )
        wget.download(url2, filePath2)
        wget.download(url3,filePath3 )
        wget.download(url4,filePath4 )
        wget.download(url5,filePath5 )

        print("\nSuccessfully downloaded")
    except:
        print("Download Problems")
else:
    print("Files already recent")
#url = "ftp://geoftp.ibge.gov.br/cartas_e_mapas/bases_cartograficas_continuas/bc250/versao2019/lista_de_nomes_geograficos/bc250_nomesgeograficos_2019-10-29.csv"
#wget.download(url, 'C:/OSGeo4W64/bin/Andre/Covid-19Brazil/Tables/bc250_nomesgeograficos_2019-10-29.csv')

#url = "ftp://geoftp.ibge.gov.br/cartas_e_mapas/bases_cartograficas_continuas/bc250/versao2019/shapefile/bc250_shapefile_06_11_2019.zip"
#wget.download(url, 'C:/OSGeo4W64/bin/Andre/Covid-19Brazil/Tables/bc250_shapefile_06_11_2019.zip')<jupyter_output>9
30
-1 / unknown........................................................................] 22090 / 22090
Successfully downloaded<jupyter_text>Loading CSV files to a PostGreSQL database<jupyter_code>import psycopg2
import csv
conn = psycopg2.connect("host=localhost dbname=Miebiom user=postgres password=<PASSWORD>")
cur = conn.cursor()

cur.execute(""" DROP TABLE IF EXISTS brasiltotal; """)
conn.commit()


cur.execute("""
    CREATE TABLE brasiltotal(
    country text,
    state text,
    totalCases text,
    totalCasesMS text,
    notConfirmedByMS text,
    deaths text,
    deathsMS text,
    URL text,
    deaths_per_100k_inhabitants text,
    totalCases_per_100k_inhabitants text,
    deaths_by_totalCases text,
    recovered text,
    suspects text,
    tests text,
    tests_per_100k_inhabitants text,
    date text,
    newCases text, 
    newDeaths text
)
""")




with open('C:/OSGeo4W64/bin/Andre/Covid-19Brazil/Tables/cases-brazil-total.csv', 'r') as f:
    reader = csv.reader(f)
    next(reader) # Skip the header row.
    for row in reader:
        print(row)
        cur.execute(
        "INSERT INTO brasiltotal VALUES ( %s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
        row
    )

    
conn.commit()
import psycopg2
conn = psycopg2.connect("host=localhost dbname=Miebiom user=postgres password=<PASSWORD>")
cur = conn.cursor()
cur.execute(""" DROP TABLE IF EXISTS brasilcasescities; """)
conn.commit()


cur.execute("""
    CREATE TABLE brasilcasescities(
    country text, 
    state text,
    city text,
    ibgeID text,
    deaths text,
    totalCases text,
    deaths_per_100k_inhabitants text,
    totalCases_per_100k_inhabitants text,
    deaths_by_totalCases text,
    _source text,
    date text, 
    newCases text,
    newDeaths text
)
""")

with open('C:/OSGeo4W64/bin/Andre/Covid-19Brazil/Tables/cases-brazil-cities.csv', 'rt', encoding='utf-8') as f:
    reader = csv.reader(f)
    next(reader) # Skip the header row.
    for row in reader:
        cur.execute(
        "INSERT INTO brasilcasescities VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
        row
    )

    
conn.commit()
import psycopg2
import csv

conn = psycopg2.connect("host=localhost dbname=Miebiom user=postgres password=<PASSWORD>")
cur = conn.cursor()
cur.execute(""" DROP TABLE IF EXISTS worldcases; """)
conn.commit()
cur.execute("""
    CREATE TABLE worldcases(
    Country_Region text, 
    Last_Update text,
    Lat text,
    Long_ text,
    Confirmed INT ,
    Deaths INT ,
    Recovered INT ,
    Active INT 
    
    
)
""")


with open('C:/OSGeo4W64/bin/Andre/Covid-19Brazil/Tables/cases_country.csv', 'rt', encoding='utf-8') as f:
    reader = csv.reader(f)
    next(reader) # Skip the header row.
    for row in reader:
       
        #print(row[0], row[1], row[2], row[3], row[4].split(".")[0], row[5], row[6],row[7])

        cur.execute(
        "INSERT INTO worldcases VALUES ( %s, %s, %s, %s, %s, %s, %s, %s)",
        (row[0], row[1], row[2], row[3], row[4].split(".")[0], row[5].split(".")[0], row[6].split(".")[0],row[7].split(".")[0]))
        
     
    
conn.commit()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15344084304646335, 'fraction_numerical': 0.0389589653520677, 'mean_word_length': 3.532561505065123, 'pattern_counts': {'":': 0, '<': 14, '<?xml version=': 0, '>': 11, 'https://': 6, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 18, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '520213', 'license': 'CC0-1.0', 'n_tokens_mistral': 2582, 'n_tokens_neox': 2393, 'n_words': 405, 'path': 'Casos de estudo/Brasil/Covid-19Brazil/getDataBrazil.ipynb', 'repo_name': 'carolina55/covid-pt', 'size': '6245'}