1

Our group is tasked to create and modify SQL tables (we chose to do it in Python) including the ALTER command but when we try to alter dataype of a column Job_id from CHAR (10) to VARCHAR (10), we always get this syntax.

Here's the code:

import sqlite3

# Connect to the database
conn = sqlite3.connect('employee.db')
cursor = conn.cursor()

cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# Create table
create_table_query = '''
CREATE TABLE EMPLOYEE (
    Emp_no INTEGER PRIMARY KEY,
    E_name TEXT,
    E_address TEXT,
    E_ph_no TEXT,
    Dept_no INTEGER,
    Dept_name TEXT,
    Job_id CHAR(10),
    Salary REAL
);
'''
cursor.execute(create_table_query)

# Display the table schema
cursor.execute("PRAGMA table_info(EMPLOYEE)")
table_schema = cursor.fetchall()
print("Table Schema:")
for column in table_schema:
    print(column)

#2.) Alter table - Change the datatype of JOB_ID from char to varchar
cursor.execute('ALTER TABLE EMPLOYEE ALTER COLUMN Job_id VARCHAR (10);')

# Display the updated table schema
cursor.execute("PRAGMA table_info(EMPLOYEE)")
table_schema = cursor.fetchall()
print("\nUpdated Table Schema: Change the datatype of JOB_ID from char to varchar")
for column in table_schema:
    print(column)

conn.close()

The error occurs in Line 31 cursor.execute('ALTER TABLE EMPLOYEE ALTER COLUMN Job_id VARCHAR (10);')

I've tried different ways but still couldn't work:

cursor.execute('ALTER TABLE EMPLOYEE MODIFY COLUMN Job_id VARCHAR (10);')
cursor.execute('ALTER TABLE EMPLOYEE ALTER COLUMN Job_id TYPE VARCHAR (10);')
cursor.execute('ALTER TABLE EMPLOYEE ALTER COLUMN JOB_ID VARCHAR (10);')

We're expecting this output:

Updated Table Schema: Change the datatype of JOB_ID from char to varchar
(0, 'Emp_no', 'INTEGER', 0, None, 1)
(1, 'E_name', 'TEXT', 0, None, 0)
(2, 'E_address', 'TEXT', 0, None, 0)
(3, 'E_ph_no', 'TEXT', 0, None, 0)
(4, 'Dept_no', 'INTEGER', 0, None, 0)
(5, 'Dept_name', 'TEXT', 0, None, 0)
(6, 'Job_id', 'VARCHAR(10)', 0, None, 0)
(7, 'Salary', 'REAL', 0, None, 0)
0

2 Answers 2

2

Sqlite has limited ALTER TABLE capabilities. You can ADD or REMOVE a column, but not alter it (source).

Sign up to request clarification or add additional context in comments.

Comments

0

Does it help to add the type keyword to the statement?

cursor.execute('ALTER TABLE EMPLOYEE ALTER COLUMN Job_id TYPE VARCHAR (10);')

See How to alter a column's data type in a PostgreSQL table?

1 Comment

How would this help? The question is about SQLite, not Postgresql.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.