0

In aiohttp, I am inserting data into SQLite where True or False conditions as a 1 or 0, like this as I read in the SQLite documentation that this is the best practice for Boolean:

[Date, post["starttime"], post["endtime"], 0, 1, post["setpoint"]]

For example this is in here which seems to work Ok from a junior dev standpoint:

    elif weekdays:
        info = f'Weekdays Found'
        print(info)
        response_obj = { 'status' : 'success', 'info' : info }

            await db.execute(
                "INSERT INTO posts (Date, starttime, endtime, Weekends, Weekdays, setpoint) VALUES(?, ?, ?, ?, ?, ?)",
                [Date, post["starttime"], post["endtime"], 0, 1, post["setpoint"]],
            )
            await db.commit()
            
            return web.json_response(response_obj)

When I retrieve the Data from SQLite, it does come through as a String True or False even though the data was inserted as a 0 or 1.

For example this is my aiohttp route to retrieve the data from the SQLite where there is most likely a much better way to write how to retrieve last row of data inserted into the db:

@routes.get('/event-payload')
async def get_event_payload(request):
    ret = []
    db = request.config_dict["DB"]
    async with db.execute("SELECT * FROM posts ORDER BY rowid DESC LIMIT 1;") as cursor:
        async for row in cursor:
            ret.append(
           `      {
                    "starttime": row["starttime"],
                    "endtime": row["endtime"],
                    "Weekends": row["Weekends"],
                    "Weekdays": row["Weekdays"],
                    "setpoint": row["setpoint"],
                }
            )

But if I print("ret is", ret) this will show up as True or False strings. ret is [{'starttime': '13:11', 'endtime': '18:19', 'Weekends': 'True', 'Weekdays': 'True', 'setpoint': 444}]

How can I change 'Weekends': 'True', 'Weekdays': 'True' to just Boolean operators and not just strings True or False?

Thanks for any tips not alot of wisdom here.

UPDATE

In aiohttp this is how I created the SQLite db

def try_make_db() -> None:

    my_path = 'form_data.db'

    with sqlite3.connect(my_path) as conn:
        cur = conn.cursor()
        print("Trying to connect to the db!")

        try:
            cur.execute("SELECT 1 FROM posts LIMIT 1;")
            cur.close()
            print("Good enough DB should be Ok")

        except Exception as e:
            print("Table 'posts' does not exist")
            cur.execute(
                """CREATE TABLE posts (
                Date TEXT PRIMARY KEY,
                starttime TEXT,
                endtime TEXT,
                Weekends NUMBER,
                Weekdays NUMBER,
                setpoint NUMBER)
            """
            )
            print("DB TABLE CREATED")
            conn.commit()
bbartling
  • 3,288
  • 9
  • 43
  • 88
  • 1
    How were the tables created? I believe this would only happen if the columns that you are inserting into are string columns. Changing the columns types to boolean may resolve your issues. – Stephen Jul 07 '21 at 15:56
  • Ill make an update to my POST for how I created the table. Cool I think this would help me a lot here where the issue is. Also learning alot too sort of an SQL newb – bbartling Jul 07 '21 at 15:59
  • I think the problem is I dont think I can create `Weekends` or `Weekdays ` as Boolean in SQLIte – bbartling Jul 07 '21 at 16:00
  • https://stackoverflow.com/questions/843780/store-boolean-value-in-sqlite mentions it either needs to be a 1 or 0 as well as in their documentation. https://www.sqlite.org/datatype3.html#:~:text=2.1.%20boolean%20datatype – bbartling Jul 07 '21 at 16:01
  • There is no `NUMBER` data type in SQLite, but it is allowed and does not make any difference. Since you use 1 and 0 in the INSERT statement, I believe the values are stored properly in the table. The problem must be in the code that retrieves the values from the table. – forpas Jul 07 '21 at 16:05
  • Ah yep, Sqlite doesn't support a boolean type. Converting it to an integer should be relatively the same though. Any non-zero value is a boolean `True` and a zero value is a boolean `False`. If you absolutely need those values to be boolean typed, you can create an register a converter to convert to the correct datatype. See https://docs.python.org/3/library/sqlite3.html#sqlite3.register_converter – Stephen Jul 07 '21 at 16:06
  • @Stephen any chance you would know of a tutorial or example of a python register to convert datatypes? – bbartling Jul 07 '21 at 18:17
  • Not off the top of my head unfortunately. The sqlite default converter might do it automatically if you convert the type of that column in the table though. You'll need to edit the file and update the table itself, not your query. – Stephen Jul 07 '21 at 18:22

0 Answers0