Firstly, I've read all similar questions and applied the solutions listed here: [1] SQLite with Python "Table has X columns but Y were supplied", [1]"Table has X columns but Y values were supplied" error, 3sqlite3.OperationalError: table card_data has 11 columns but 10 values were supplied, [4]sqlite3.OperationalError: table book has 6 columns but 5 values were supplied, [5]Sqlite3 Table users has 7 columns but 6 values were supplied Yet, none of them worked out.

I am creating 26 tables succesfully insert data using this:

im.execute("""CREATE TABLE IF NOT EXISTS C_Socket7 (Date_Requested, Time_Requested, Time_Passed, Energy_Consumption, Cumulative_Consumption, Hourly_Consumption, Daily_Consumption, Weekly_Consumption, Monthly_Consumption)""")   
string = """INSERT INTO {} VALUES ('{}','{}','{}','{}','{}','{}','{}','{}')""".format('C_' tables[7], tstr, dict4DB['timePassed'], dict4DB['Socket7Consp'],DummyConsumptions['Cumulative'], DummyConsumptions['Hourly'], DummyConsumptions['Daily'], DummyConsumptions['Weekly'], DummyConsumptions['Monthly'])

and this code:

im.execute("""CREATE TABLE IF NOT EXISTS A_Status (Time_Requested, Socket0Status, Socket1Status, Socket2Status, Socket3Status, Socket4Status, Socket5Status, Socket6Status, Socket7Status)""")
string = """INSERT INTO {} VALUES ('{}','{}','{}','{}','{}','{}','{}','{}','{}')""".format('A_' tables[7], tstr, dict4DB['Socket0Stat'], dict4DB['Socket1Stat'],dict4DB['Socket2Stat'], dict4DB['Socket3Stat'], dict4DB['Socket4Stat'], dict4DB['Socket5Stat'], dict4DB['Socket6Stat'], dict4DB['Socket7Stat'])

But when it comes to this table:

im.execute("""CREATE TABLE IF NOT EXISTS A_Environment (Date_Requested, Time_Requested, Voltage, Frequency, In_Temperature, In_Humidity, Ext_Temperature, Ext_Humidity, Door_Switch, Door_Relay)""")
string = """INSERT INTO A_Environment(Date_Requested, Time_Requested, Voltage, Frequency, In_Temperature, In_Humidity, Ext_Temperature, Ext_Humidity, Door_Switch, Door_Relay) VALUES ('{}','{}', '{}','{}','{}','{}','{}','{}','{}','{}')""".format(d_str, h_str, dict4DB['Voltage'], dict4DB['Frequency'],dict4DB['InTemp'], dict4DB['InHumidity'], dict4DB['ExtTemp'], dict4DB['ExtHumidity'], dict4DB['DoorSwitch'], dict4DB['DoorRelay'])

It gives the error:

sqlite3.OperationalError: table A_Environment has 10 columns but 9 values were supplied

When I check the database using "DB Browser for SQLite", I can't see the column names. When I create columns manually using the interface, I get the same error.

Here is a screenshot how it looks from DB Browser: []

It was working fine when the structure was like this:

im.execute("""CREATE TABLE IF NOT EXISTS A_Environment (Time_Requested, Voltage, Frequency, In_Temperature, In_Humidity, Ext_Temperature, Ext_Humidity, Door_Switch, Door_Relay)""")

string = """INSERT INTO {} VALUES ('{}','{}','{}','{}','{}','{}','{}','{}','{}')""".format('A_' tables[6], tstr, dict4DB['Voltage'], dict4DB['Frequency'],dict4DB['InTemp'], dict4DB['InHumidity'], dict4DB['ExtTemp'], dict4DB['ExtHumidity'], dict4DB['DoorSwitch'], dict4DB['DoorRelay'])

I've changed 'tstr' with 'd_str' and 'h_str' which I've seperated date and time into different columns.

I've tried these:

1. Created the columns manually. Got the same result.
2. Dropped the table and let it create itself using "CREATE TABLE IF NOT EXISTS", it created the table but no columns in it too. Put the columns manually. Did not work.
3. Delete the whole database and let it create itself from scratch. Got the same result.
4. I also change the provided values in .format() function as str(d_str) and str(h_str) did not work as well.
5. I am planning to create a minimum working code snippet to see which value is not being able to read, yet after seeing that the columns are not being created. I thought the problem is not about the data to be written.

I've read the documentation but could not find something I can use.

Here is a screenshot of the tables from "DB Browser for SQLite" []

What do you recommend? What should I do?

PS: SQLite is running on Raspberry Pi, Rasbian OS, Python 3.7, SQLite3.

Using

CodePudding user response：

You should never use .format() (or % or f-strings) to generate SQL statements. Your issue is possibly (hard to tell since we don't see your input data) stemming from an unescaped value being wrongly interpreted by SQLite - in effect, you're your own SQL injection vulnerability.

Instead use the placeholder system provided by your database, e.g. like this for Sqlite:

im.execute(
    """
INSERT INTO
  A_Environment(
    Date_Requested,
    Time_Requested,
    Voltage,
    Frequency,
    In_Temperature,
    In_Humidity,
    Ext_Temperature,
    Ext_Humidity,
    Door_Switch,
    Door_Relay
  )
VALUES
  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""",
    (
        d_str,
        h_str,
        dict4DB["Voltage"],
        dict4DB["Frequency"],
        dict4DB["InTemp"],
        dict4DB["InHumidity"],
        dict4DB["ExtTemp"],
        dict4DB["ExtHumidity"],
        dict4DB["DoorSwitch"],
        dict4DB["DoorRelay"],
    ),
)