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)
