使用SQLAlchemy读取SQLite数据库字符串字段时触发TypeError错误的咨询

Great question—let’s break this down clearly since you’re new to SQLAlchemy and database development!

The Root Cause #

This issue boils down to two key points:

1. SQLite’s flexible (but non-standard) type system: SQLite doesn’t have an official string data type. It uses TEXT as the base storage class for all text-based values, and VARCHAR, CHAR, etc., are just aliases for TEXT. The string type you used is a custom label added by SQLiteStudio—not a native SQLite type.
2. SQLAlchemy’s reflection behavior: When you use autoload=True to load table metadata, SQLAlchemy relies on the declared column type name to map to Python types. Since string isn’t a recognized standard type for SQLite, it misinterprets it as a numeric/real type. That’s why it throws an error when trying to read string values into a field it expects to be a number.

Fixes for Your Test Database #

You have two straightforward options to resolve this:

• Option 1: Revert to standard SQLite text types
  Change the column type back to VARCHAR or TEXT—in SQLite, these are functionally identical (both map to the underlying TEXT storage class). Your original code will work perfectly again, since SQLAlchemy correctly maps these types to Python strings.

• Option 2: Custom type mapping (if you must keep the "string" label)
  If you want to stick with the string type for testing, you can tell SQLAlchemy to treat it as TEXT. Here’s how to modify your code:

  db_path = 'test.db'
  import sqlalchemy as db
  from sqlalchemy.types import TypeDecorator, TEXT
  
  class SQLiteString(TypeDecorator):
      impl = TEXT
  
      @classmethod
      def get_dbapi_type(cls, dbapi_conn):
          # Map SQLiteStudio's "string" type to SQLAlchemy's TEXT
          return dbapi_conn.TEXT
  
  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)
      # Fix columns misinterpreted as REAL
      for col in table.columns:
          # Adjust column names to match your affected fields
          if col.type.__class__.__name__ == 'REAL' and col.name in ['name', 'fullname']:
              col.type = SQLiteString()
      # Rest of your code remains the same
      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))
  

  Note: This is a workaround and not ideal for production—it’s tied directly to this specific type misinterpretation.

For Large Production Databases #

You don’t need to worry about this scenario at all! Proper production databases (like PostgreSQL, MySQL, or SQL Server) have standard, well-documented string types (VARCHAR, TEXT, etc.) that SQLAlchemy supports flawlessly. This issue is unique to SQLite’s loose type system combined with SQLiteStudio’s non-standard string label. Stick to your database’s native standard type names, and you’ll avoid these kinds of headaches entirely.

内容的提问来源于stack exchange，提问作者Mark Seagoe