Spaces:
Running
Running
File size: 3,456 Bytes
1f38061 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
import sqlite3
import pandas as pd
from typing import Dict, Any, List
DB_PATH = "hackathon_participants.db"
TABLE_NAME = "participants"
def initialize_database():
"""
Initializes the database and creates the participants table if it doesn't exist.
"""
with sqlite3.connect(DB_PATH) as conn:
cursor = conn.cursor()
cursor.execute(f"""
CREATE TABLE IF NOT EXISTS {TABLE_NAME} (
email TEXT PRIMARY KEY,
name TEXT NOT NULL,
linkedin_profile TEXT,
background TEXT,
goals TEXT
)
""")
conn.commit()
def add_participant(participant: Dict[str, Any]):
"""
Adds a new participant to the database.
Args:
participant: A dictionary containing participant data.
Keys should be 'email', 'name', 'linkedin_profile', 'background', 'goals'.
"""
with sqlite3.connect(DB_PATH) as conn:
cursor = conn.cursor()
cursor.execute(f"""
INSERT OR REPLACE INTO {TABLE_NAME} (email, name, linkedin_profile, background, goals)
VALUES (:email, :name, :linkedin_profile, :background, :goals)
""", participant)
conn.commit()
def get_participants_dataframe() -> pd.DataFrame:
"""
Retrieves all participants from the database and returns them as a pandas DataFrame.
Returns:
A pandas DataFrame containing all participant data.
"""
with sqlite3.connect(DB_PATH) as conn:
df = pd.read_sql_query(f"SELECT * FROM {TABLE_NAME}", conn)
return df
if __name__ == '__main__':
# Example usage and basic test
print("Initializing database...")
initialize_database()
print("Database initialized.")
# Sample participants
participants_to_add = [
{
"email": "[email protected]",
"name": "Alice Wonderland",
"linkedin_profile": "linkedin.com/in/alicew",
"background": "5 years of experience in frontend development with React and TypeScript. Interested in UI/UX design.",
"goals": "I want to build a cool project for my portfolio and learn about backend development."
},
{
"email": "[email protected]",
"name": "Bob Builder",
"linkedin_profile": "linkedin.com/in/bobb",
"background": "Backend developer specializing in Python, Django, and PostgreSQL. I'm also a DevOps enthusiast.",
"goals": "Looking to work on a challenging problem, maybe involving infrastructure or data engineering."
},
{
"email": "[email protected]",
"name": "Charlie Chocolate",
"linkedin_profile": "linkedin.com/in/charliec",
"background": "Data scientist with expertise in Python, Pandas, Scikit-learn, and TensorFlow. I love creating predictive models.",
"goals": "I hope to apply my machine learning skills to a real-world problem and collaborate with a diverse team."
}
]
print(f"Adding {len(participants_to_add)} participants...")
for p in participants_to_add:
add_participant(p)
print("Participants added.")
print("\nRetrieving all participants:")
df = get_participants_dataframe()
print(df)
# Verify data
assert len(df) == 3
assert "[email protected]" in df["email"].values
print("\nData verified successfully.") |