How to Alter an SQLite Table using Python?

Altering an SQLite table is a common task when working with databases and can be easily done using Python. SQLite is a well-known open-source relational database management system that is used to store data in local files. In this article, we'll look at how to alter an SQLite table in Python by using the ALTER TABLE command to add, edit, or delete columns from an existing table.

We will go through the necessary steps, including establishing a connection to the SQLite database, creating a cursor object, executing the ALTER TABLE statement, committing the changes, and closing a database connection.

SQLite

SQLite in Python refers to the Python SQLite module, which provides an interface for accessing SQLite databases from Python programs. SQLite is a widely used open-source relational database management system. It is used to store data in local files and has a small footprint, making it ideal for small to medium-sized applications.

Python’s SQLite module provides a simple way to work with SQLite databases, allowing developers to modify, create and query SQLite databases directly from their Python code. The SQLite module comes pre-installed with Python, making it easy to use and accessible for many developers. SQLite is widely used in web and mobile applications, as well as for small to medium-sized projects, due to its small footprint and easy-to-use interface.

Importing the module

To import the SQLite module in Python, copy the following code in the editor −

import sqlite3

The sqlite3 module is part of the Python Standard Library and comes pre-installed with Python. It provides an interface for accessing SQLite databases from Python code.

The sqlite3 module is now imported, we can use the sqlite3 functions and classes to interact with SQLite databases, including establishing connections to databases, retrieving data, and executing SQL commands.

Steps to alter SQLite table using Python

To alter an SQLite table, we need to use the ALTER TABLE statement. This statement allows us to add, modify or delete columns from an existing table. We can execute this statement using the Python SQLite module.

We have used Sublime Text as the text editor and all the outputs will be there on the terminal window while executing the particular sublime python file.

Here are the steps to alter an SQLite table using Python.

Step 1

Create a new file with .py extension. Import the sqlite module.

import sqlite3

Step 2

The next step is to connect to the SQLite database using the connect() method provided by the sqlite3 module in Python. This method takes the path to the database file as an argument and returns a Connection object.

conn=sqlite3.connect('database_name.db')

Step 3

The next step is to create a cursor object using the cursor() method provided by the Connection object. The cursor object is used to execute SQL statements on the database.

c= conn.cursor()

Step 4

The next step is to create a new table and execute it by using the execute() method of the cursor object. The statement takes the name of the table and define the name of the columns with there datatypes that you want in the table.

c.execute(""CREATE TABLE table_name(column names with there datatypes)"")

Step 5

The next step is to insert the values in the table and execute it by using the execute() method of the cursor object.

c.execute("INSERT INTO table_name VALUES (enter the values that you want to provide to the colummns of the table)")

Step 6

Now, we will fetch all the rows from the table before we alter the table and make some changes to it. We will use the execute() and fetchall() methods of the cursor object that will fetch all the rows from the table.

c.execute("SELECT * FROM table_name")
print(c.fetchall())

Step 7

The ALTER TABLE command will be executed in this step. To alter a table, use the cursor object’s execute() function to run the ALTER TABLE command. As parameters, the statements accept the name of the table and the column to be updated. For example, to add a new column to a table, we can use the following statement −

c.execute("ALTER TABLE table_name ADD COLUMN column_name datatype")

Step 8

Now, we will fetch all the rows from the table after we have altered the table and made some changes to it. We will use the execute() and fetchall() methods of the cursor object that will fetch all the rows from the table.

c.execute("SELECT * FROM  table_name")
print(c.fetchall())

Step 9

After we have executed the ALTER TABLE statement, we need to commit the changes using the commit() method of the Connection object.

conn.commit()

Step 10

Finally, we need to close the database connection using the close() method of the Connection object.

conn.close()

Let us look at one example to understand it in a better way −

Example

import sqlite3
conn=sqlite3.connect('cust.db')

#create a cursor
c= conn.cursor()

#create a table
c.execute("""CREATE TABLE customers_table (first_name text,last_name text,email text)""") 

#insert values in the columns of the table
c.execute("INSERT INTO customers_table VALUES ('Mary','Dakota','mdakota@gmail.com')")
c.execute("INSERT INTO customers_table VALUES ('Amy','Jackson','ajackson@gmail.com')")

#Printing all the values before altering the table
print("Table before using ALTER ..")
c.execute("SELECT * FROM customers_table")
print(c.fetchall())

#Alter the table
c.execute("ALTER TABLE customers_table ADD COLUMN UserName CHAR(25)")

#Print the table after altering
print("Table after using ALTER ..")
c.execute("SELECT * FROM customers_table")
print(c.fetchall())

print("Command executed successfully...")
conn.commit()
#close our connection
conn.close()

Output

Conclusion

In conclusion, altering an SQLite table using Python is a simple process. We can use the ALTER TABLE statement to add, delete, or modify columns from an existing table. By following the above steps, we can easily alter an SQLite table using Python.