the program does not read the data that is in the text file

Question

The.txt rain text file contains results of pH analysis of rainwater samples. Each of the lines has (separated by ";") the date of commencement of sampling (in the form YYYY-MM-DD), the duration of sampling (in hours), the identification of the location (a four-character sequence), the sampling point of the site (a number between 1 and 8) and the pH value measured.

I am trying to make a function that inserts the data from the file rain.txt in the table Samples from the database agua.db. The function should read the rain information.txt and for each line read insert this record in the Samples table.

def inserir_dadosBD(nomeBD,nomeFich,nomeTabela):
    bd = sql.connect(nomeBD, isolation_level = None)
    fich = open(nomeFich)
    nomeTabela = 'CREATE TABLE ' + nomeTabela + '(Data TEXT, Hora INTEGER, 
    Local VARCHAR(4), Ponto INTEGER, pH REAL, PRIMARY KEY(Data));'
    bd.execute(nomeTabela)
    for linha in fich.readlines():
        linha = linha.split(';')
        Data = linha[0]
        Hora = int(linha[1])
        Local = linha[2]
        Ponto = int(linha[3])
        pH = float(linha[4])
        com2 = 'INSERT INTO ' + nomeTabela + ' VALUES ('+ Data + ',' + 
        str(Hora) + ',' + Local + ',' + str(Ponto) + ',' + str(pH) + ');'
        print(com2)
        bd.execute(com2)    
    fich.close()
    bd.close()

By calling inserir_dadosBD('agua.db','chuva.txt','Amostras') and

bd = sql.connect('agua.db',isolation_level = None)        
com = 'SELECT * FROM Amostras;'
res = bd.execute(com)
print(res.fetchall())

in the list of commands appears to me [('Data', 'Hora', 'Local', 'Ponto', 'pH')] What do I have to change in the program to read what’s in the file rain.txt?

Answer 1

Considering a text file like this:

2019-05-02; 5; ABCD; 1; 8
2019-05-03; 4; CDFD; 2; 6
2019-05-04; 3; DDFF; 2; 7
2019-05-05; 2; CBFG; 4; 8
2019-05-06; 1; HTYG; 5; 5
2019-05-07; 5; ABDD; 3; 2

I made some modifications to her function, and she was like this:

def inserir_dadosBD(nomeBD,nomeFich,nomeTabela):
    bd = sql.connect(nomeBD, isolation_level = None)
    fich = open(nomeFich)
    criaTabela = 'CREATE TABLE ' + nomeTabela + ' (Data TEXT, Hora INTEGER, Local VARCHAR(4), Ponto INTEGER, pH REAL, PRIMARY KEY(Data));'
    bd.execute(criaTabela)
    for linha in fich.readlines():
        linha = linha.split(';')
        Data = linha[0]
        Hora = int(linha[1])
        Local = linha[2]
        Ponto = int(linha[3])
        pH = float(linha[4])
        com2 = f"INSERT INTO {nomeTabela} VALUES ('{Data}' , {Hora} , '{Local}' , {Ponto} , {pH});"
        print(com2)
        bd.execute(com2)
    fich.close()
    bd.close()

Note:

• the change in the name of the variable that creates the table. You are overriding the parameter.

• the change in formatting of the variable that mounts the Insert. If you are using Python < 3.6, you can use the format:

com2 = "INSERT INTO {nomeTabela} VALUES ('{Data}' , {Hora} , '{Local}' , {Ponto} , {pH});".format(nomeTabela=nomeTabela, Data=Data, Hora=Hora, Local=Local, Ponto=Ponto, pH=pH)