Why does a SQLite database created in Python have the VARCHAR data type?

user_3029042 0
Viewed 172

When I create an SQLite database from a python data model, any column defined as a String in Python is displayed as VARCHAR in SQLite (viewing with DB Browser for SQLite). Here is an example of the data model in Python:

class Users(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    role = db.Column(db.String(10))
    name_first = db.Column(db.String(50), nullable=False)
    name_last = db.Column(db.String(50), nullable=False)

This may not be relevant, but I should clarify that I'm doing this as part of a website hosted with Flask. The database is initially created by dropping to a python prompt and:

from app import db
db.create_all()

I have a basic understanding of MS SQL and SQLite datatypes (NULL,INTEGER,REAL,TEXT,BLOB), but I don't understand why I'm seeing the columns defined as Strings in Python classified as VARCHAR in DB Browser for SQLite. If I attempt to modify the table, I see all of the expected datatypes for SQLite and also VARCHAR as an option. If I create a new database/table, then VARCHAR doesn't exist as an option for datatypes. Why wouldn't these columns be displayed as TEXT datatypes?

sqlite python
1 Answers

Strings in Python classified as VARCHAR in DB Browser for SQLite.

In Flask you are actually using SQLAlchemy ORM which will convert your class models directly into SQLite statements to create the relational database tables corresponding to it.

String data type in Python class model will be as VARCHAR data type in SQLite using Object Relational Mapper.

In SQLite, Text is the parent of VARCHAR and in the default installation is no different, so VARCHAR is actually same as TEXT.

Also If you check 3.1. Determination Of Column Affinity in the documentation you notice in the second point that:

If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.

For more info check : http://www.sqlite.org/datatype3.html

enter image description here

user_51691860
Related