Python is a powerful programming language that you can use to interact with SQL databases. With the help of Python, you can create, manipulate, and interact with the tables in the SQL database.

In this tutorial, we will be discussing how to create and manipulate tables in a SQL database using Python.

Prerequisites:

To follow along with this tutorial, you will need the following:

  • A working knowledge of Python.
  • A SQL database. For this tutorial, we will be using SQLite.

How to Create a Table in a SQL Database Using Python

To create a table in a SQL database using Python, we first need to establish a connection to the database. For this tutorial, we will be using the SQLite database. SQLite is a lightweight, serverless database that is ideal for small projects.

To connect to the SQLite database, we will be using the built-in SQLite3 module in Python.

Here is the code to create a table in our SQLite database using Python:

import sqlite3

# connect to the database
conn = sqlite3.connect('example.db')

# create a cursor object
c = conn.cursor()

# create a table
c.execute('''CREATE TABLE employees
             (id INT PRIMARY KEY NOT NULL,
              name TEXT NOT NULL,
              age INT NOT NULL)''')

# save the changes
conn.commit()

# close the connection
conn.close()

In the above code, we first established a connection to the SQLite database using the connect method of the sqlite3 module. We then created a cursor object using the cursor method.

Next, we executed a SQL query to create a table named employees with three columns: id, name, and age. The id column is set as the primary key, which means that each record in the table will have a unique id value. The name and age columns are set as NOT NULL, which means that they cannot be empty.

Finally, we saved the changes using the commit method and closed the connection using the close method.

How to Insert Data into a Table

Now that we have created a table, we can insert data into it. Here is the code to insert data into the employees table:

import sqlite3

# connect to the database
conn = sqlite3.connect('example.db')

# create a cursor object
c = conn.cursor()

# insert data into the table
c.execute("INSERT INTO employees (id, name, age) VALUES (1, 'John Doe', 30)")
c.execute("INSERT INTO employees (id, name, age) VALUES (2, 'Jane Doe', 25)")

# save the changes
conn.commit()

# close the connection
conn.close()

In the above code, we inserted two records into the employees table using the execute method. We passed in two SQL queries, one for each record.

How to Select Data from a Table

Now that we have inserted data into the employees table, we can retrieve it using the SELECT statement. Here is the code to select data from the employees table:

import sqlite3

# connect to the database
conn = sqlite3.connect('example.db')

# create a cursor object
c = conn.cursor()

# select data from the table
c.execute("SELECT * FROM employees")

# fetch all the records
records = c.fetchall()

# print the records
for record in records:
    print(record)

# close the connection
conn.close()

In the above code, we selected all the records from the employees table using the SELECT statement. We then fetched all the records using the fetchall method and printed them using a for a loop.

How to Update Data in a Table

To update data in a table, we use the UPDATE statement. Here is the code to update data in the employees table:

import sqlite3

# connect to the database
conn = sqlite3.connect('example.db')

# create a cursor object
c = conn.cursor()

# update data in the table
c.execute("UPDATE employees SET age = 35 WHERE name = 'John Doe'")

# save the changes
conn.commit()

# select data from the table
c.execute("SELECT * FROM employees")

# fetch all the records
records = c.fetchall()

# print the records
for record in records:
    print(record)

# close the connection
conn.close()

In the above code, we updated the age of the record with the name 'John Doe' to 35 using the UPDATE statement. We then saved the changes using the commit method.

How to Delete Data from a Table

To delete data from a table, we use the DELETE statement. Here is the code to delete data from the employees table:

import sqlite3

# connect to the database
conn = sqlite3.connect('example.db')

# create a cursor object
c = conn.cursor()

# delete data from the table
c.execute("DELETE FROM employees WHERE name = 'Jane Doe'")

# save the changes
conn.commit()

# select data from the table
c.execute("SELECT * FROM employees")

# fetch all the records
records = c.fetchall()

# print the records
for record in records:
    print(record)

# close the connection
conn.close()

In the above code, we deleted the record with the name 'Jane Doe' from the employees table using the DELETE statement. We then saved the changes using the commit method.

Conclusion

In this article, we discussed how to create and manipulate tables in a SQL database using Python.

We covered how to create a table, insert data into it, select data from it, update data in it, and delete data from it. We also provided the necessary code to accomplish these tasks.

By following the steps outlined in this tutorial, you can create and manipulate tables in any SQL database using Python.

Let’s connect on Twitter and LinkedIn.