Unable to utilize SimpleSQLite 1.1.3 after installation via pip

[bobgott]

I'm running on Windows 10. After installation via pip, I tried running the example script create table from data matrix (4.1.1.) in your read the docs. The program error'd out with unable to find 'pytablewriter'. I loaded that library via pip and your demo worked. Then I tried reading in a more complicated table and it error's out with

ValueError: 'word_master' table not included in the schema

That table was in the schema. I suspect more libraries are missing.

[thombashi]

@bobgott
Thank you for your report.
I'm not sure what happened in your environment.
The example script succeeds in my Windows 10 environment.
Could you show me some code example to reproduce the problem?

[bobgott]

@thombashi
I just pip'd the most current version (1.1.3) and now I get the message

ImportError: cannot import name 'SimpleSQLite' from partially initialized module 'simplesqlite' (most likely due to a circular import)

Simple program

import os
from simplesqlite import SimpleSQLite

db_name = 'mindgames.db'
table_name = 'word_master'
db_path = os.path.join(os.getcwd(), db_name)
con = SimpleSQLite(db_path)
print(con.fetch_attr_names(table_name))
result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
    print(record)
for record in con.select_as_dict(table_name=table_name):
    print(record)

[thombashi]

@bobgott
Thank you for your additional information.
What is the filename of your script? if the name is simplesqlite.py, please rename it.

[bobgott]

@thombashi
Well, getting closer. You were correct .. in haste, I named my script simplesqlite. I renamed the script .. and now getting a strange table error 'table not included in the schema' after verifying table is in schema. The code follows

import os
from simplesqlite import SimpleSQLite, DatabaseError

db_name = 'test_sql.db'
table_name = 'word_master'
db_path = os.path.join(os.getcwd(), db_name)
con = SimpleSQLite(db_path, 'a')
print(con.fetch_table_names())
if not con.has_table(table_name):
    print(f"Cannot find table '{table_name}'")
try:
    con.verify_table_existence(table_name)
except (DatabaseError, ValueError) as e:
    print(f"Error getting table - {str(e)}")
    exit(-1)
else:
    print(f"Table '{table_name}' verified")
try:
    print(con.fetch_attr_names(table_name))
   # Exception thrown here! 
except (DatabaseError, ValueError) as e:
    print(f"Error getting '{table_name}' attributes - {str(e)}")
    # Print: Error getting 'word_master' attributes - 'word_master' table not included in the schema
    exit(-1)
result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
    print(record)
for record in con.select_as_dict(table_name=table_name):
    print(record)

[thombashi]

@bobgott
Please try upgrade sqliteschema package to the latest version (1.0.4) to work around the issue:

pip install -U sqliteschema

I assume the cause of the problem is the bug of sqliteschema library which occurred when passing a database that some of the fields does not have data types.
I can reproduce the issue with a table like the following:

CREATE TABLE word_master(id, word);

and confirmed the problem fixed by the latest sqliteschema package.

[bobgott]

@thombashi, Progress!
So now I've made it down to the very last operation

for record in con.select_as_dict(table_name=table_name):
    print(record)

On the last operation I get the following error .. I've also tried on various tables w/in same DB and get same error (column number changes w/table)

Traceback (most recent call last):
  File "C:\Pythons\Python3.8.2\lib\site-packages\simplesqlite\core.py", line 335, in execute_query
    result = self.connection.execute(str(query))
sqlite3.OperationalError: no such column: 25

[thombashi]

@bobgott
Could you possibly upload your SQLite database file? It will help to resolve the issue.
Full error logs would also helpful.

[bobgott]

@thombashi, I changed the db file and got the same error.

I am attaching the actual py script, a log, requirements.txt, and the db file. At this point you can easily run this by yourself.

simplesqlite_test.zip

[thombashi]

@bobgott
Thank you for your information.
I had fixed the issue at SimpleSQLite 1.1.4, please try that version.

$ python simplesqlite_test.py
['alembic_version', 'post', 'followers', 'areas']
Table 'post' verified
post ['id', 'body', 'timestamp', 'user_id', 'language', 'dummy', 'dummy2', 'PRIMARY']
(1, 'My First Post is here.', '2018-07-09 23:09:47.068756', 3, None, None, None)
(2, 'This is another post. Number 2 I believe.', '2018-07-10 17:33:24.494403', 3, None, None, None)
(3, 'A post from bebops', '2018-07-10 17:51:30.430560', 1, None, None, None)
(4, 'A second post from BeBops', '2018-07-10 17:51:42.529709', 1, None, None, None)
(5, 'testing again', '2018-07-10 20:05:41.696936', 1, None, None, None)
(6, 'Another msg from bobgott', '2018-07-10 20:13:41.960310', 3, None, None, None)
(7, 'Now is the time for all good men to come to the aid of their party.', '2018-07-10 20:13:54.366600', 3, None, None, None)
(8, 'This is post number 4 for bobgott', '2018-07-10 20:21:10.944344', 3, None, None, None)
(9, 'This is a zebar', '2018-07-12 20:22:53.453862', 5, None, None, None)
(10, "I'm saying something", '2018-07-24 22:14:03.839805', 1, 'en', None, None)
(11, 'Hola, como estas', '2018-07-24 22:15:13.653776', 1, '', None, None)
OrderedDict([('id', 1), ('body', 'My First Post is here.'), ('timestamp', '2018-07-09 23:09:47.068756'), ('user_id', 3), ('dummy2', 'None'), ('PRIMARY', 'PRIMARY')])
OrderedDict([('id', 2), ('body', 'This is another post. Number 2 I believe.'), ('timestamp', '2018-07-10 17:33:24.494403'), ('user_id', 3), ('dummy2', 'None'), ('PRIMARY', 'PRIMARY')])
OrderedDict([('id', 3), ('body', 'A post from bebops'), ('timestamp', '2018-07-10 17:51:30.430560'), ('user_id', 1), ('dummy2', 'None'), ('PRIMARY', 'PRIMARY')])
OrderedDict([('id', 4), ('body', 'A second post from BeBops'), ('timestamp', '2018-07-10 17:51:42.529709'), ('user_id', 1), ('dummy2', 'None'), ('PRIMARY', 'PRIMARY')])
OrderedDict([('id', 5), ('body', 'testing again'), ('timestamp', '2018-07-10 20:05:41.696936'), ('user_id', 1), ('dummy2', 'None'), ('PRIMARY', 'PRIMARY')])
OrderedDict([('id', 6), ('body', 'Another msg from bobgott'), ('timestamp', '2018-07-10 20:13:41.960310'), ('user_id', 3), ('dummy2', 'None'), ('PRIMARY', 'PRIMARY')])
OrderedDict([('id', 7), ('body', 'Now is the time for all good men to come to the aid of their party.'), ('timestamp', '2018-07-10 20:13:54.366600'), ('user_id', 3), ('dummy2', 'None'), ('PRIMARY', 'PRIMARY')])
OrderedDict([('id', 8), ('body', 'This is post number 4 for bobgott'), ('timestamp', '2018-07-10 20:21:10.944344'), ('user_id', 3), ('dummy2', 'None'), ('PRIMARY', 'PRIMARY')])
OrderedDict([('id', 9), ('body', 'This is a zebar'), ('timestamp', '2018-07-12 20:22:53.453862'), ('user_id', 5), ('dummy2', 'None'), ('PRIMARY', 'PRIMARY')])
OrderedDict([('id', 10), ('body', "I'm saying something"), ('timestamp', '2018-07-24 22:14:03.839805'), ('user_id', 1), ('language', 'en'), ('dummy2', 'None'), ('PRIMARY', 'PRIMARY')])
OrderedDict([('id', 11), ('body', 'Hola, como estas'), ('timestamp', '2018-07-24 22:15:13.653776'), ('user_id', 1), ('language', ''), ('dummy2', 'None'), ('PRIMARY', 'PRIMARY')])

The cause of the problem is that fetch_attr_names fails when takes a database that has MySQL(or other databases) style schema as inputs.

[bobgott]

@thombash,
Works perfectly! Thank you.

I'll close this ticket.

[bobgott]

Bug fixed in SimpleSQLite 1.1.4