Creating a simple databse using Python Programming Language
- Import necessary modules:
os,shutil, andsqlite3.
1 import os
2 import shutil
3 import sqlite3- It’s time to create a new folder to store the database:
1 os.makedirs('./mydatabase', exist_ok=True)-
Define a function to establish a connection to the database:
1 def create_connection():
2 conn = None
3 try:
4 conn = sqlite3.connect('./mydatabase/mydatabase.db')
5 print(sqlite3.version)
6 except Error as e:
7 print(e)
8
9 return conn-
Define a function to create a table:
1 def create_table(conn):
2 try:
3 sql_create_users_table = """ CREATE TABLE IF NOT EXISTS users (
4 id integer PRIMARY KEY,
5 name text NOT NULL,
6 email text NOT NULL
7 ); """
8
9 if conn is not None:
10 conn.execute(sql_create_users_table)
11 else:
12 print("Error! cannot create the database connection.")
13 except Error as e:
14 print(e)-
Let’s now work with the definition of a function to insert data into the table:
1 def insert_data(conn):
2 sql_insert_data = """ INSERT INTO users (name,email) VALUES
3 ('John','john@gmail.com'),
4 ('Sara','sara@gmail.com')
5 """
6 conn.execute(sql_insert_data)
7 conn.commit()-
It is time to define a function to query data from the table:
1 def query_data(conn):
2 cursor = conn.cursor()
3 cursor.execute("SELECT id, name, email from users")
4 rows = cursor.fetchall()
5
6 for row in rows:
7 print(row)-
At this moment, let’s use these functions to create the database, insert data, and query data:
1 note to do this...# Establish a connection to the database
2 conn = create_connection()
3
4 # Create a table
5create_table(conn)
6
7 # Insert data into the table
8 insert_data(conn)
9
10 # Query data from the table
11 query_data(conn)
12
13 # Close the connection to the database
14 conn.close()- After running this script, you should have a folder named “mydatabase” containing a file named “mydatabase.db”. The “mydatabase.db” file is your database, and it contains a table named “users” with three columns: id, name, and email. The table has two rows of data: John’s and Sara’s email addresses and names.
Remember to replace the paths, file names, and database details with your own details.
About Author
Discover more from SURFCLOUD TECHNOLOGY
Subscribe to get the latest posts sent to your email.
