Problem with reading SQlite data from PyCharm

I'm working on a simple application to create, edit, display and delete customer records.

My environment is: Windows 10, Tkinter, Pycharm  2020.1,  Python3.7

I can add records and display records without throwing any errors but the output is empty fields that look like this:

(",",",",",",",1)

(",",",",",",",2)  etc.  

The only recognizable output is the Record number (oid).

After I create the database then open the file in the editor there is a top level message: " File was loaded in the wrong encoding 'UTF-8' ".
I have tried playing with a number of different configurations like UTF-16E and  windows11252 but nothing seems to work. It does not seem to matter how I set the file encodings.

Either I get the same result or the change generates a 'database disk image is malformed' message and I have to delete and re-create the database.

Is there a way around this like a setting change in Win 10 ? I am totally stumped and can't find any solutions in the forums.

For the sake of completeness I have included the relevant application code below but i don't think there is a problem with it. It seems to be an encoding problem.

Thanks in advance for any help.

 
def query():
conn = sqlite3.connect('lucindatech.db')
c = conn.cursor()
c.execute("SELECT *,oid FROM customers")
records=c.fetchall()
print(records)

print_records=''
for record in records:
print_records += str(record) + "\n"

query_label= Label(root,text=print_records )
query_label.grid(row=11,column=0, columnspan=2)
 

The code that generates seems to work fine. It is:

c.execute("INSERT INTO customers VALUES (:f_name, :s_name, :address_1,:address_2,:city, :province, :postal_code,:tel)",
{
'f_name': f_name.get(),
's_name': s_name.get(),
'address_1': address_1.get(),
'address_2': address_2.get(),
'city':city.get(),
'province':province.get(),
'postal_code':postal_code.get(),
'tel':tel.get()

})
def query():
conn = sqlite3.connect('lucindatech.db')
c = conn.cursor()
c.execute("SELECT *,oid FROM customers")
records=c.fetchall()
print(records)

print_records=''
for record in records:
print_records += str(record) + "\n"

query_label= Label(root,text=print_records )
query_label.grid(row=11,column=0, columnspan=2)


 

5 comments
Comment actions Permalink

>After I create the database then open the file in the editor there is a top level message: " File was loaded in the wrong encoding 'UTF-8' 

Are you trying to open sqlite3 database file in the editor? Are you using PyCharm Professional? 

Please note that you should not edite sqlite3 database directly in the editor, but using database tools which are available in PyCharm Professional.

Please clarify.

0
Comment actions Permalink

Hi Andrey. The  'Add Record' and 'Query Record' functions are initiated from a python app (some of the code displayed above). They don't generate any errors from what I can see.

I wasn't trying to edit the database. I just opened it in PyCharm to see if I could figure out where the problem was.  That is where I first noticed the message "File was loaded n the wrong encoding 'UTF-8' ". 

I'm using the Community version of PyCharm.

0
Comment actions Permalink

>I just opened it in PyCharm to see if I could figure out where the problem was

sqlite3 database is a binary file and opening it in editor will not indicate any meaningful error.

 

>database disk image is malformed

This will certainly happen if you try to modify encoding on the binary file, essentially corrupting it.

 

>I can add records and display records without throwing any errors but the output is empty fields that look like this:

>(",",",",",",",1)

>(",",",",",",",2)  etc.  

 

Please try to run your code from the terminal outside of IDE, if you get the same result, IDE is likely not related.

0
Comment actions Permalink

Hmmm,  I copy /  pasted my application into the python console and ran it from there with the same result !

 

 

0
Comment actions Permalink

Hi Andrey, sorry for wasting your time, I discovered the problem.

I had placed the  code to clear the record entry text boxes in front of instead of after the code that submits the record data in my submit function!

As a newby at least I learned some valuable lessons about troubleshooting.  Thanks again for your time and patience! 

0

Please sign in to leave a comment.