[SOLVED] SQLAlchemy giving TypeError reading strings from SQLite database

SQLAlchemy giving TypeError reading strings from SQLite database

Here is code to read the data out of the tables in a SQLite database:

db_path = 'test.db'

import sqlalchemy as db
engine = db.create_engine('sqlite:///'+db_path)
inspector = db.inspect(engine)
table_names = inspector.get_table_names()
conn = engine.connect()
md = db.MetaData()

for tname in table_names:
    table = db.Table(tname, md, autoload=True, autoload_with=engine)
    print('Table ' + tname + ' columns: ' + str(table.columns.keys()))
    query = db.select([table])
    table_data = conn.execute(query).fetchall()
    for tdata in table_data:
        print('Data row: ' + str(tdata))

Here's the output from the working version:

Table address columns: ['id', 'email_address', 'user_id']
Table user_account columns: ['id', 'name', 'fullname']
Data row: (1, 'sandy', 'Sandy Cheeksworth')
Data row: (2, 'johnny boy', 'John Snow')
Data row: (3, 'fred', 'Fred Flintstone')
Data row: (4, 'barney', 'Barney Rubble')
Data row: (5, 'squidward', 'Squidward Tentacles')
Data row: (6, 'ehkrabs', 'Eugene H. Krabs')
Data row: (7, 'snoop', 'Calvin Cordozar Broadus Jr.')
Data row: (8, 'jay-z', 'Shawn Corey Carter')
>>>

If I use SQLiteStudio to change the 2nd field from type varchar to type string, things get ugly. Here's the "pre-debugged" output:

Table address columns: ['id', 'email_address', 'user_id']
Table user_account columns: ['id', 'name', 'fullname']
Traceback (most recent call last):
  File "<stdin>", line 6, in <module>
  File "D:\Programs\Python\lib\site-packages\sqlalchemy\engine\result.py", line 992, in fetchall
    return self._allrows()
  File "D:\Programs\Python\lib\site-packages\sqlalchemy\engine\result.py", line 402, in _allrows
    made_rows = [make_row(row) for row in rows]
  File "D:\Programs\Python\lib\site-packages\sqlalchemy\engine\result.py", line 402, in <listcomp>
    made_rows = [make_row(row) for row in rows]
TypeError: must be real number, not str
>>>

I've only been learning SQLAlchemy for a couple days and I'm really new to database stuff as well... so I assume I'm doing something wrong... or does SQLAlchemy just not work well with string data in SQLite? Why does it want a "real number" where column specifies a string, but it's fine with a varchar? This is a small demo database, but I have a large database... should I change all the string fields to varchar? TIA!

Solution

Declare it as TEXT. If you declare it as STRING, it has affinity of NUMERIC, not TEXT

Have a look at Datatypes In SQLite, especially the last sentence in the examples.

And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.

This will cause SQLAlchemy to fail. Have a look also at this SO question