Sqlite - Python insert data automatically

Question

Hi, I’m writing an algorithm that collects information from dividend companies. The collection is performed and mounts a table and up to this point everything excellent. but I would like to save in a database and am using Python Sqlite 3. The table is perfect, the problem is when I try to insert in the bank. I believe the error is in the "FOR" loop, but I still can’t unravel.

connection = sqlite3.connect('dividendos.db')
con = connection.cursor()


def create_table():
    con.execute('CREATE TABLE IF NOT EXISTS dados (Empresa text, Tipo text, Data_Ex text, Data_Pag text, Valor text)')

create_table()

def data_entry():
    
        for empresa in tabela['Empresa']:
            empresa = str(empresa)

        for tipo in tabela['Tipo']:
            tipo = str(tipo)
    
            con.execute("INSERT INTO dados VALUES('"+empresa+"','"+tipo+"','DataEx','DataPag','Valor')")
        connection.commit()
data_entry()

No errors returned, just not saving as it should. I can do only one "FOR" to read the whole table, go assigning and only then enter the values in the database?

Answer 1

If using the pandas to store the database values, you can do as follows:

Importing the necessary packages

import pandas as pd
import sqlite3

Simulating incoming data and creating a data frame

empresa = ['EMPRESA_A','EMPRESA_B','EMPRESA_C']
tipo = ['TIPO_A','TIPO_B','TIPO_C']
dataex = ['2019','2019','2020']
datapag = ['01-01-2019','02-01-2019','03-01-2020']
valor = [10, 20, 30]

tabela = pd.DataFrame({'empresa': empresa, 'tipo': tipo, 'Data_Ex': dataex, 'Data_Pag': datapag, 'Valor': valor})

Maintaining the structure of your code to create a db and table

connection = sqlite3.connect('dividendos.db')
con = connection.cursor()

def create_table():
    con.execute('CREATE TABLE IF NOT EXISTS dados (Empresa text, Tipo text, Data_Ex text, Data_Pag text, Valor text)')

create_table()

With this line you add the content of the data frame to the database

tabela.to_sql(name = 'dados', con = connection, if_exists = 'append', index = False)

With this line you can recover what is on the table

back = pd.read_sql('select * from dados', connection)

---

Code:

import pandas as pd
import sqlite3

# simulando dados recebidos e criando um data frame
empresa = ['EMPRESA_A','EMPRESA_B','EMPRESA_C']
tipo = ['TIPO_A','TIPO_B','TIPO_C']
dataex = ['2019','2019','2020']
datapag = ['01-01-2019','02-01-2019','03-01-2020']
valor = [10, 20, 30]

tabela = pd.DataFrame({'empresa': empresa, 'tipo': tipo, 'Data_Ex': dataex, 'Data_Pag': datapag, 'Valor': valor})

connection = sqlite3.connect('dividendos.db')
con = connection.cursor()

def create_table():
    con.execute('CREATE TABLE IF NOT EXISTS dados (Empresa text, Tipo text, Data_Ex text, Data_Pag text, Valor text)')

create_table()

tabela.to_sql(name = 'dados', con = connection, if_exists = 'append', index = False)

back = pd.read_sql('select * from dados', connection)

back

Exit:

    empresa      tipo   Data_Ex Data_Pag    Valor
0   EMPRESA_A   TIPO_A  2019    01-01-2019  10
1   EMPRESA_B   TIPO_B  2019    02-01-2019  20
2   EMPRESA_C   TIPO_C  2020    03-01-2020  30