CODEWITHSUNDEEP
pythonsqlite
iAwardYouNoPoints
iAwardYouNoPoints

Reputation: 263

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

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?

Upvotes: 1

Views: 1282

Answers (1)

Oghli
Oghli

Reputation: 2340

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

Upvotes: 1

Related Questions