Database image malformed

[naptar]

Question:
Why is the database image malformed on replit? It works in Visual Studio, but not on replit.
Repl link:

import sqlite3
from datetime import datetime

def is_leap_year(year):
    if (year % 4 == 0 and year % 100 != 0) or (year % 400 == 0):
        return True
    else:
        return False

con = sqlite3.connect('naptar.db')
cursor = con.cursor()

table_query = '''
CREATE TABLE IF NOT EXISTS "naptar" (
    "month" TEXT,
    "day" INTEGER,
    "birthday" TEXT,
    "nameday" TEXT,
    "note" TEXT
)
'''
con.execute(table_query)
con.commit()
con.close()

con = sqlite3.connect('naptar.db')
cursor = con.cursor()

months = ["Jan", "Feb", "March", "April", "May", "June", "July", "Aug", "Sep", "Oct", "Nov", "Dec"]

current_year = datetime.now().year

for month_num, month_name in enumerate(months, start=1):
    if month_num in [1, 3, 5, 7, 8, 10, 12]:
        num_days = 31
    elif month_num == 2:
        if is_leap_year(current_year):
            num_days = 29
        else:
            num_days = 28
    else:
        num_days = 30
    
    for day in range(1, num_days + 1):
        insert_query = "INSERT INTO naptar (month, day, birthday, nameday, note) VALUES (?, ?, ?, ?, ?);"
        cursor.execute(insert_query, (month_name, day, "none", "none", "none"))

con.commit()
con.close()

[KAlexK]

Can you describe your problem in more detail?

[naptar]

Sure thing
So, when I download the file, my db database viewer says, it’s malformed. Also, it just shows a bunch of red text in the file, which is all like nulnulnulnulnulnul and this for like a 10 hundred times. Or just random letters.
Now these turns out to be control character nulls.
This is pretty much everything I found out.
Maybe there’s a problem with character decoding?
Or the line ending difference? (Unix vs. Windows)

[KAlexK]

I couldn’t determine exactly why the table is being created corrupted, but I fixed the code, and it began to create a normal table.

Corrected code:

import sqlite3
from datetime import datetime

def is_leap_year(year):
    if (year % 4 == 0 and year % 100 != 0) or (year % 400 == 0):
        return True
    else:
        return False

con = sqlite3.connect('naptar.db')
cursor = con.cursor()

table_query = '''
CREATE TABLE IF NOT EXISTS naptar (
    month TEXT,
    day INTEGER,
    birthday TEXT,
    nameday TEXT,
    note TEXT
)
'''
con.execute(table_query)
con.commit()

months = ["Jan", "Feb", "March", "April", "May", "June", "July", "Aug", "Sep", "Oct", "Nov", "Dec"]

current_year = datetime.now().year

for month_num, month_name in enumerate(months, start=1):
    if month_num in [1, 3, 5, 7, 8, 10, 12]:
        num_days = 31
    elif month_num == 2:
        if is_leap_year(current_year):
            num_days = 29
        else:
            num_days = 28
    else:
        num_days = 30
    
    for day in range(num_days + 1):
        insert_query = "INSERT INTO naptar (month, day, birthday, nameday, note) VALUES (?, ?, ?, ?, ?);"
        cursor.execute(insert_query, (month_name, day, "none", "none", "none"))

con.commit()
con.close()

What has been changed:

Unnecessary quotes

To specify the name of a table or column, quotation marks are not needed (if the name does not have space characters).

Old code:

table_query = '''
CREATE TABLE IF NOT EXISTS "naptar" (
    "month" TEXT,
    "day" INTEGER,
    "birthday" TEXT,
    "nameday" TEXT,
    "note" TEXT
)
'''

New code:

table_query = '''
CREATE TABLE IF NOT EXISTS naptar (
    month TEXT,
    day INTEGER,
    birthday TEXT,
    nameday TEXT,
    note TEXT
)
'''

Unnecessary creation and deletion of a database connection

It is not necessary to delete the database connection and then re-create it.

Old code:

con.commit()
con.close()

con = sqlite3.connect('naptar.db')
cursor = con.cursor()

New code:

con.commit()

[naptar]

The problem is, that these are all optional changes.
I think the problem may be occuring, because I am developing a web document,
So an HTML, CSS, JS.
So I didn’t create a python application, just this website, and this python code should be
the backend, the problem tho, is that when I run it, from the shell, using:
python backend.py
It runs it perfectly, but then I see the database and it’s badly damaged.
These changes, what you did are all formal changes, and therefore do not change anything.
With the same exact code that I provided, it does work, so the Visual Studio handles it perfectly.
This is why I think it has to do with the environment, replit’s python handling inside of a website application.
So I think the code is perfect, maybe I need to change something with the replit settings ?
Do you have any ideas?

[KAlexK]

I tried running your code again, and for some reason it created the correct database. I can’t understand why it didn’t work correctly the first time.
Could there be a problem in the database viewer?

[naptar]

Nah, I don’t think, it can open any other, just not this. But creating this database with the same exact code in VS, it perfectly works. Even with the viewer.
And also, when I open the database, I see a bunch of red control character nulls, like nulnulnulnul
or random letters, or just 45 degrees rotated cubes with a ? in it.
And that’s not how a database file should look like. It is pretty strange

[QwertyQwerty88]

Sooo… Have you tried creating a Python Repl?

[NateDhaliwal]

Hey @naptar !
I am also using sqlite3 in a game and my database looks the same as yours. I believe this is the way it stores the data, and the data is encrypted inside this (there might be a few words occasionally).
The data can be retrieved, but certainly not through scrolling through the db file.

[naptar]

Wow, so you mean the database IS working, just it looks very strange and a normal DB Viewer cannot open it? Interesting, thanks. I’ll try it!

[naptar]

Well if I create a python repl, how is that supposed to be my website’s backend? If I create that in python?

[KAlexK]

The backend of your site can be in a replica with a Python Flask or Python Django template. Using one of these templates, you will be able to place the backend and frontend of your site in the repl, and you will also be able to create and edit databases, as in a regular Python repl.

[Nicolello]

If you’re opening the database file and see a bunch of unsupported characters that seem nonsense - it’s not nonsense, that’s how it’s supposed to work. You can kinda see it as a way sqlite does all of its database stuff as opposed to a simple plaintext file - indexes, table schemas, etc.

[naptar]

Well okay, but in Visual Studio OR VSC OR PyCharm or everywhere else it is 1:
Not all red, not just nonnonnonnonnonnonnon characters and it is actually KINDA understandable. But thanks

[NateDhaliwal]

Hi @naptar !
If your question has been answered, you can mark the post that helped you most as the Solution.

[naptar]

Yes, I will, but I haven’t tried it yet.
But I think that does makes sense (what @NateDhaliwal ) said.