0

I have an issue while using sqlite3 with PyQt4 while accessing a database. I have a form, from which I read the string:

Name = str(self.PopupWidget.QLineEdit_field.text().toUtf8()).strip()

The string I entered into the QLineEdit_field is "ą", which translates to 0xc4 0x85 in unicode. Now, I create a table:

db.execute("CREATE TABLE table_name(field1 TEXT, field2 REAL,....);".replace('table_name',Name))
db.commit()

This works perfectly fine. Then I insert the data into the table:

db.execute("INSERT INTO '%s' VALUES ('%s','%f',...);" %(Name,data1,data2...))

This also works fine (it is being displayed in my widget as well as in an external database explorer). When I try to extract a single field from the table:

db.execute("select field1 from '%s';" %(Name))
records = [a[0]for a in db.fetchall()]

It also works perfectly. But then I try to access all data from a single row in the table:

db.execute("SELECT * FROM '%s' WHERE field1 = '%s';" %(Name,data1))

And this leads to the error:

UnicodeDecodeError: 'ascii' codec can't decode byte 0xc4 in position 0: ordinal not in range(128)

I tried different approaches, however nothing worked and it always resulted in this error. What could be the cause of this?

::::SOLVED::::

...kinda. When I tried to use the same syntax as when creating the table:

db.execute("SELECT * FROM table1 WHERE field1 = 'table2';".replace('table1',Name).replace('table2',data1))

it worked properly. I have no idea why, and I don't think that is in any way a valid solution, but at least it worked.

Mee
  • 27
  • 1
  • 8
  • For the purposes of debugging, can you hard-code `Name` into your query and follow the process I listed recently [here](http://stackoverflow.com/questions/39974214/fetching-data-from-microsoft-access-database-using-select-query-with-where-claus/39975075#39975075)? You do not follow standards for `SQLite3` in building the queries and so it might be down to string formatting that `data1` is causing the errors. i.e. placeholder for `data1`. – roganjosh Oct 11 '16 at 20:32
  • Hardcoding would be hard, since my code is way bigger and it dynamically acquires data from database and uses it to display other data. The `data1` is surely not causing errors, it is a date in format: yyyy-mm-dd, so there's no error there. – Mee Oct 11 '16 at 20:39
  • Then I'm confused because `("CREATE TABLE table_name(field1 TEXT, field2 REAL,....);")` creates a table called `table_name` (Ok, I get that's a generic name) but then all your other queries somehow read and write to a table presumably called "ą" (`Name`) without any error – roganjosh Oct 11 '16 at 20:42
  • Oh sorry, will correct the code now – Mee Oct 11 '16 at 20:54
  • I have a feeling that this issue resides outside the code that you've listed. You need to go back to debugging with `print` statements in the wider picture. If indeed you're able to do everything you say you can do then I'm at a loss on why the last part fails. Your query strings are not standard practice so it might help to get it in line with my original link and http://stackoverflow.com/questions/39588293/python-sqlite3-database-table-isnt-being-updated/39589436#39589436. I can't be sure that my approach is _the_ best practice but it's closer than your current one – roganjosh Oct 11 '16 at 21:03
  • @Mee. Always post the **full** traceback, so that people can see exactly where the error occurs. – ekhumoro Oct 11 '16 at 21:04

0 Answers0