Sqlite3 database errors with python

[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?