[python]VARCHAR requires a length on dialect mysql with sqlalchemy

I remember I did not need to define the varchar length, however I encountered this error:

sqlalchemy.exc.CompileError: (in table 'test_table', column 'name'): VARCHAR requires a length on dialect mysql

So I would need to define the length of varchar with the String object from SqlAlchemy.

meta = MetaData()

    """
    Define the table. The below is the same as
    CREATE TABLE test_table (
        id INTEGER NOT NULL AUTO_INCREMENT,
        name VARCHAR(255),
        PRIMARY KEY (id)
    )
    """
    test_table = Table(
        "test_table", meta,
        Column("id", Integer, primary_key=True),
        Column("name", String(255))  # MySql VARCHAR requires a defined length.
    )

The entire code as follows:

from sqlalchemy import (create_engine,
                        Table, Column, Integer, String,
                        MetaData)
from sqlalchemy_utils import database_exists, create_database
from getpass import getpass

"""
database url is dialect+driver://username:password@db_address/db_name
To connect to mysql/mariadb, pymysql module is required to install.
The purpose of using SqlAlchemy is to abstract sql syntax from the programmer/scripter,
hence there should be no sql syntax used, to use sql syntax use the execute method of the create_engine object.
"""

# Get database address.
db_addr = input("DB ip address: ")
# Get username of the database.
db_user = input(f"Username of {db_addr}: ")
# Get password.
db_pass = getpass(f"Password of {db_user}@{db_addr}: ")
# Get the database name.
db_name = input("Database name to connect: ")

# join the inputs into a complete database url.
url = f"mysql+pymysql://{db_user}:{db_pass}@{db_addr}/{db_name}"

# Create an engine object.
engine = create_engine(url, echo=True)

# Create database if it does not exist.
if not database_exists(engine.url):
    create_database(engine.url)
else:
    # Connect to database if exists, returns connection object.
    e = engine.connect()
    # create a metadata object for table.
    meta = MetaData()

    """
    Define the table. The below is the same as
    CREATE TABLE test_table (
        id INTEGER NOT NULL AUTO_INCREMENT,
        name VARCHAR(255),
        PRIMARY KEY (id)
    )
    """
    test_table = Table(
        "test_table", meta,
        Column("id", Integer, primary_key=True),
        Column("name", String(255))  # MySql VARCHAR requires a defined length.
    )
    # This commits the table to database.
    meta.create_all(e)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s