This post explains the process of prompting AI to generate Python code which creates a database and then imports a CSV file into the database. The database can then be queried using sqlite3. This process can be applied to many different applications. https://github.com/jaymasl/create_database

Python script:
import sqlite3
import csv
# Step 1: Create a SQLite database and a table
def create_database(db_name):
conn = sqlite3.connect(db_name)
cursor = conn.cursor()
# Create a table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER NOT NULL
)
''')
conn.commit()
conn.close()
# Step 2: Import data from CSV file into the database
def import_csv_to_db(db_name, csv_file):
conn = sqlite3.connect(db_name)
cursor = conn.cursor()
with open(csv_file, 'r') as file:
csv_reader = csv.reader(file)
next(csv_reader) # Skip the header row
for row in csv_reader:
cursor.execute('''
INSERT INTO users (id, name, age) VALUES (?, ?, ?)
''', (row[0], row[1], row[2]))
conn.commit()
conn.close()
# Step 3: Query the database
def query_database(db_name):
conn = sqlite3.connect(db_name)
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
print(row)
conn.close()
# Main function to run the steps
if __name__ == "__main__":
db_name = 'example.db'
csv_file = 'data.csv'
create_database(db_name)
import_csv_to_db(db_name, csv_file)
print("Data imported successfully. Querying the database:")
query_database(db_name)
data.csv:
id,name,age
1,John Doe,30
2,Jane Smith,25
3,Bob Johnson,40
console output:


Leave a Reply