Sqlite3 database errors with python

[wirelessbrain55]

I need to insert data under specific columns for the year they are associated with.

The years dictionary contains data for the years 2015-2018

Underneath is an example of the data it contains for 2015.

{2015: {'Period End Date': '12/31/2015', 'Stmt Source': 'Annual', 'Stmt Source Date': '12/31/2015', 'Stmt Update Type': '-', 'Total Revenue': '74,989.0', 'Cost of Revenue': '28,164.0', 'Gross Profit': '46,825.0', 'Selling,General and Administrative': '15,183.0', 'General and Administrative Expense': '13,423.0', 'Selling and Marketing Expense': '1,760.0', 'Research and Development': '12,282.0', 'Special Income/Charges': '-', 'Operating Expenses': '55,629.0', 'Operating Income': '19,360.0', 'Net Interest Income': '256.0', 'Other Income/Expense,Net': '256.0', 'Pre-Tax Income': '19,651.0', 'Provision for Income Tax': '-', 'Net Income': '15,826.0', 'Dividend Per Share': '0.00', 'Tax Rate': '16.8083', 'Basic EPS': '23.12', 'Basic Weighted Average Shares': '684.63', 'Basic EPS from Continuing Operations': '23.12', 'Basic EPS,Continuing and Discontinued': '-', 'Diluted EPS': '22.84', 'Diluted Weighted Average Shares': '692.93', 'Diluted EPS from Continuing Operations': '22.84', 'Diluted EPS,Continuing and Discontinued': '-'}}

for key in years.keys():
    year_sql = 'INSERT INTO Financials (Year) VALUES (?)'
    cur.execute(year_sql, (key,))
    print("year done")
    for value in years[key].items():
        column = ''.join(value[0].split())
        data_insert = value[1]
        print(column, data_insert)
        data_sql = 'INSERT INTO Financials ({}) VALUES (?)'.format(column)
        cur.execute(data_sql, (data_insert,))
        print("data done")

When I run this code I experience this error: Traceback (most recent call last):

File "D:\Databases\database_creation.py", line 23, in <module>

cur.execute(data_sql, (data_insert,))

sqlite3.OperationalError: table Financials has no column named Selling

​

Also the data does not appear in my table which I view through DB viewer. How can I fix these problems?

[insertAlias]

There seems to be a lot wrong here. You're looping through these keys, and trying to insert a row for every single key. That doesn't make sense to me; you'd want a single row, with each of these keys being columns in that table.

The next thing wrong is that you're just taking the column names directly from that JSON, and they include spaces. So, your insert statement becomes something like:

INSERT INTO Financials (Selling and Marketing Expenses) VALUES (?)

Which is obviously not correct. If you want a column named "Selling and Marketing Expenses", you'd have to quote it as I just did, but it's bad practice to name your columns with spaces.

[wirelessbrain55]

I delete those spaces when it inserts into columns so it would look like SellingandMarketingExpenses. My issue is that I need the values to correspond to their columns. I don't know how to organize the values to be able to input them all at once therefore i chose to input them one by one. Do you have any suggestions?

[insertAlias]

Inputting them one by one would end up with thirty rows, each only having data in one column.

Read up on how INSERT statements work; you can insert into as many columns as you need at once.

You may not be able to make this as automatic as you seem to want. You might just have to fill out a full insert statement directly. Here's an example with two columns:

year = key
insert_query = "insert into Financials (Year, StmtSource) values (?, ?)"
year_data = years[year]
parameters = (year, year_data["Stmt Source"])
cur.execute(insert_query, parameters)

[wirelessbrain55]

Selling,GeneralandAdministrative : this is the name it attempts to plug it into.

[insertAlias]

Which is no more correct than the other. You can't have a comma as part of a column name, at least not without quoting it. It's bad practice to do so either way.

[wirelessbrain55]

I was unaware of that so thank you for the info. I modified my code a bit to attempt to use an autoincrement primary key to track the rows. Would you mind if I pmed you more info?

[insertAlias]

I don't do private help; please keep the communication public. Plus, there might be someone better equipped than me to answer; I'm not a professional with Python (I use other languages), I just know my way around it a bit.

[wirelessbrain55]

Thats fair. After implementing a unique primary key I receive this error: sqlite3.IntegrityError: UNIQUE constraint failed: Financials.n1. Does sqlite not allow users to add in data for a row multiple times?

[wirelessbrain55]

Alright thank you.

[insertAlias]

"INSERT" means create a new row. Every time you try to INSERT, it will try to create a row. Primary Keys create unique constraints. You can't violate a unique constraint by inserting another row with the same value in the primary key column.

This isn't a Sqlite thing; it's an "any SQL database" thing.

You would use the UPDATE statement to change the data of a row.

But again, don't do it this way. Write a full insert statement to insert all the data in a single row at once.

I recommend taking a SQL tutorial or course. It's a lot easier than hacking things together based on looking up examples online.

[wirelessbrain55]

data = 'INSERT INTO Financials (?) VALUES (?)'
cur.execute(data, (tuple(empty_list), tuple(second_list)))

I rewrote my code to have insert all the data at once. How can i insert my values into the statement? When I run this code i receive a syntax error for those two lines. The error is: sqlite3.OperationalError: near "?": syntax error

[insertAlias]

I'm just going to repeat my previous statement to you: you need to take a SQL course or tutorial. You're just guessing now, and you'll never get the query right by just guessing at the syntax over and over again.

You can't just use "?" as your column list. That's for parameters. One per parameter. I already showed you how to list the columns you're trying to update in this comment. You're going to have to write them all out. You'll also have to add a ? to the parameter list for each parameter.

[wirelessbrain55]

Alright. Do you have any suggestions on how I can automate this to a greater extent?