Python sqlite3 alter table add column

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 −

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.

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.

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.

Step 10

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

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.

Источник

python sqlite3 introduction

A quick introduction to python sqlite: how to create db, create table, insert values, update table and select from the table.

1. create db, create table and insert values into the table

import sqlite3 # 1. create db in the memory createDB = sqlite3.connect(':memory:') # or create db in the hard drive # createDB = sqlite3.connect('D:\sqlite.db') # 2. define cursor c = createDB.cursor() # 3. create table def createTable(): c.execute('''CREATE TABLE customer (id INTEGER PRIMARY KEY, name TEXT, street TEXT, city TEXT, state TEXT, balance REAL)''') # 4. add data into the table def addCust(name, street, city, state, balance): c.execute('''INSERT INTO customer (name, street, city, state, balance) VALUES (?, ?, ?, ?, ?)''', (name, street, city, state, balance)) def main(): createTable() addCust("H Song", '302 Kentucky St', 'RiverC', 'CA', 188.10) addCust("A Song", '202 Kentucky St', 'RiverC', 'CA', 100.30) addCust("B Song", '102 Kentucky St', 'RiverC', 'CA', 10.83) addCust("C Song", '101 Kentucky St', 'RiverC', 'CA', 100.01) addCust("D Song", '301 Kentucky St', 'RiverC', 'CA', 90.10) addCust("E Song", '303 Kentucky St', 'RiverC', 'CA', 60.10) createDB.commit() c.execute('SELECT * FROM customer') for i in c: print '\n' for j in i: print j main() 

1 H Song 302 Kentucky St RiverC CA 188.1 2 A Song 202 Kentucky St RiverC CA 100.3 3 B Song 102 Kentucky St RiverC CA 10.83 4 C Song 101 Kentucky St RiverC CA 100.01 5 D Song 301 Kentucky St RiverC CA 90.1 6 E Song 303 Kentucky St RiverC CA 60.1

2. alter table, add new column and update the value

import sqlite3 createDB = sqlite3.connect(':memory:') c = createDB.cursor() def createTable(): c.execute('''CREATE TABLE customer (id INTEGER PRIMARY KEY, name TEXT, street TEXT, city TEXT, state TEXT, balance REAL)''') def addCust(name, street, city, state, balance): c.execute('''INSERT INTO customer (name, street, city, state, balance) VALUES (?, ?, ?, ?, ?)''', (name, street, city, state, balance)) def main(): createTable() addCust("H Song", '302 Kentucky St', 'RiverC', 'CA', 188.10) addCust("A Song", '202 Kentucky St', 'RiverC', 'CA', 100.30) addCust("B Song", '102 Kentucky St', 'RiverC', 'CA', 10.83) addCust("C Song", '101 Kentucky St', 'RiverC', 'CA', 100.01) addCust("D Song", '301 Kentucky St', 'RiverC', 'CA', 90.10) addCust("E Song", '303 Kentucky St', 'RiverC', 'CA', 60.10) createDB.commit() c.execute('ALTER TABLE customer ADD COLUMN email TEXT') c.execute('UPDATE customer SET email = "hs@gm.me" where name = "H Song"') c.execute('DELETE FROM customer where >) c.execute('SELECT * FROM customer ORDER BY balance DESC') for i in c: print '\n' for j in i: print j main() 

1 H Song 302 Kentucky St RiverC CA 188.1 hs@gm.me 2 A Song 202 Kentucky St RiverC CA 100.3 None 5 D Song 301 Kentucky St RiverC CA 90.1 None 6 E Song 303 Kentucky St RiverC CA 60.1 None 3 B Song 102 Kentucky St RiverC CA 10.83 None

Posted by Huiming Song Mon 08 August 2016 python python, sql

Recent Posts

Источник