Create database + import CSV

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:


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *