Ended at Sun, 26 Jan, 2025, 11:59 pm IST Score: 0 Check all Save
TDS 2025 Jan GA1 -
Development Tools
Instructions
1. Learn what you need. Reading material is provided, but feel free to skip it if you can
answer the question. (Or learn it, just for pleasure.)
2. Check answers regularly by pressing Check . It shows which answers are right or wrong.
You can check multiple times.
3. Save regularly by pressing Save . You can save multiple times. Your last saved submission
will be evaluated.
4. Reloading is OK. Your answers are saved in your browser (not server). Questions won't
change except for randomized parameters.
5. Browser may struggle. If you face loading issues, turn off security restrictions or try a
different browser.
6. Use anything. You can use any resources you want. The Internet, ChatGPT, friends,
whatever. Use any libraries or frameworks you want.
7. It's hackable. It's possible to get the answer to some questions by hacking the code for
this quiz. That's allowed.
Should you take TDS this term?
If this assignment takes you under 2 hours to complete, you will likely do OK in TDS.
If you score above 8 / 10, you might get an S or A grade, with effort and luck.
Have questions? Join the discussion on Discourse
Logout
EndedRecent saves
at Sun, 26 Jan, (most
2025,recent
11:59is pm
yourIST
official score)
Loaded from 13/01/2025, 09:17:14. Score: 9.5
Reload from 13/01/2025, 09:17:13. Score: 9.5
Reload from 13/01/2025, 09:15:49. Score: 9.25
Questions
1. VS Code Version (0.25 marks)
2. Make HTTP requests with uv (1 mark)
3. Run command with npx (0.5 marks)
4. Use Google Sheets (0.25 marks)
5. Use Excel (0.25 marks)
6. Use DevTools (0.5 marks)
7. Count Wednesdays (0.5 marks)
8. Extract CSV from a ZIP (0.25 marks)
9. Use JSON (0.75 marks)
10. Multi-cursor edits to convert to JSON (0.5 marks)
11. CSS selectors (0.5 marks)
12. Process files with different encodings (1 mark)
13. Use GitHub (0.5 marks)
14. Replace across files (0.75 marks)
15. List files and attributes (0.75 marks)
16. Move and rename files (0.5 marks)
17. Compare files (0.5 marks)
18. SQL: Ticket Sales (0.75 marks)
1 VS Code Version (0.25 marks)
Editor: VS Code
Your editor is the most important tool in your arsenal. That's where you'll spend most of
your time. Make sure you're comfortable with it.
Visual Studio Code is, by far, the most popular code editor today. According to the 2024
StackOverflow Survey almost 75% of developers use it. We recommend you learn it well.
Even if you use another editor, you'll be working with others who use it, and it's a good idea
to have some exposure.
Watch these introductory videos (35 min) from the Visual Studio Docs to get started:
Getting Started: Set up and learn the basics of Visual Studio Code. (7 min)
Code Editing: Learn how to edit and run code in VS Code. (3 min)
Ended at Sun, 26 Jan,Tips:2025,
Productivity 11:59a VSpmCode
Become ISTpower user with these productivity tips. (4 min)
Personalize: Personalize VS Code to make it yours with themes. (2 min)
Extensions: Add features, themes, and more to VS Code with extensions! (4 min)
Debugging: Get started with debugging in VS Code. (6 min)
Version Control: Learn how to use Git version control in VS Code. (3 min)
Customize: Learn how to customize your settings and keyboard shortcuts in VS Code.
(6 min)
AI Editors: Copilot, Cursor
Note: AI Editors like Cursor, Cody, and GitHub Copilot use LLMs to help you write code
faster.
These are built on top of VS Code. These are now a standard tool in every developer's
toolkit. Please use them.
Install and run Visual Studio Code. In your Terminal (or Command Prompt), type code -s
and press Enter. Copy and paste the entire output below.
What is the output of code -s?
Version: Code 1.96.2 (fabdb6a30b49f79a7aba0f2ad9df9b399473380f, 2024-
12-19T10:22:47.216Z)
OS Version: Darwin x64 22.6.0
Check
2 Make HTTP requests with uv (1 mark)
Python tools: uv
Install uv.
uv is a fast Python package and project manager that's becoming the standard for running
Python scripts. It replaces tools like pip, conda, pipx, poetry, pyenv, twine, and virtualenv
into one, enabling:
Python Version Management: uv installs and manages multiple Python versions,
allowing developers to specify and switch between versions seamlessly.
Virtual Environment Handling: It automates the creation and management of virtual
environments, ensuring isolated and consistent development spaces for different
projects.
Dependency Management: With support for the pyproject.toml format, uv enables
Ended at Sun,
precise26specification
Jan, 2025,of11:59
projectpm IST It maintains a universal lockfile, uv.lock,
dependencies.
to ensure reproducible installations across different systems.
Project Execution: The uv run command allows for the execution of scripts and
applications within the managed environment, streamlining development workflows.
Here are some commonly used commands:
# Replace python with uv. This automatically installs Python and dependencies
uv run script.py
# Run a Python script directly from the Internet
uv run https://example.com/script.py
# Run a Python script without installing
uvx ruff
# Use a specific Python version
uv run --python 3.11 script.py
# Add dependencies to your script
uv add httpx --script script.py
# Create a virtual environment at .venv
uv venv
# Install packages to your virtual environment
uv pip install httpx
uv uses inline script metadata for dependencies. The eliminates the need for
requirements.txt or virtual environments. For example:
# /// script
# requires-python = ">=3.11"
# dependencies = [
# "httpx",
# "pandas",
# ]
# ///
Ended at Sun, 26 Jan, 2025, 11:59 pm IST
Running uv run --with httpie -- https [URL] installs the Python package httpie and
sends a HTTPS request to the URL.
Send a HTTPS request to https://httpbin.org/get with the URL encoded parameter
email set to [email protected]
What is the JSON output of the command? (Paste only the JSON body, not the headers)
{ "args": { "email": "[email protected]" }, "headers": { "Ac
Check
3 Run command with npx (0.5 marks)
JavaScript tools: npx
npx is a command-line tool that comes with npm (Node Package Manager) and allows you
to execute npm package binaries and run one-off commands without installing them
globally. It's essential for modern JavaScript development and data science workflows.
For data scientists, npx is useful when:
Running JavaScript-based data visualization tools
Converting notebooks and documents
Ended at Sun,
Testing26andJan, 2025, 11:59
formatting code pm IST
Running development servers
Here are common npx commands:
# Run a package without installing
npx http-server . # Start a local web server
npx prettier --write . # Format code or docs
npx eslint . # Lint JavaScript
npx typescript-node script.ts # Run TypeScript directly
npx esbuild app.js # Bundle JavaScript
npx jsdoc . # Generate JavaScript docs
# Run specific versions
npx
[email protected] --write . # Use prettier 3.2
# Execute remote scripts (use with caution!)
npx github:user/repo # Run from GitHub
Watch this introduction to npx (6 min):
Let's make sure you know how to use npx and prettier.
Download README.md . In the directory where you downloaded it, make sure it is called
README.md, and run npx -y [email protected] README.md | sha256sum.
EndedWhat is the output of the command?
at Sun, 26 Jan, 2025, 11:59 pm IST
0da7cd49a86229d6fb2876f1e94660962b29e7e954417cd1a2691559de02e1d9
Check
4 Use Google Sheets (0.25 marks)
Spreadsheet: Excel, Google Sheets
You'll use spreadsheets for data cleaning and exploration. The most popular spreadsheet
program is Microsoft Excel followed by Google Sheets.
You may be already familiar with these. If not, make sure to learn the basics of both.
Go through the Microsoft Excel video training and make sure you cover:
Intro to Excel
Rows & columns
Cells
Formatting
Formulas & Functions
Tables
PivotTables
Watch this video for an introduction to Google Sheets (49 min):
Ended at Sun, 26 Jan, 2025, 11:59 pm IST
Let's make sure you can write formulas in Google Sheets. Type this formula into Google
Sheets. (It won't work in Excel)
=SUM(ARRAY_CONSTRAIN(SEQUENCE(100, 100, 8, 12), 1, 10))
What is the result?
620
Check
5 Use Excel (0.25 marks)
Let's make sure you can write formulas in Excel. Type this formula into Excel.
Note: This will ONLY work in Office 365.
=SUM(TAKE(SORTBY({9,13,13,12,8,12,1,2,7,6,0,4,6,11,3,1}, {10,9,13,2,11,8,16,1
What is the result?
Ended at101Sun, 26 Jan, 2025, 11:59 pm IST
Note: If you get #NAME? you have the wrong version of Excel. Find a friend for whom this
works.
Check
6 Use DevTools (0.5 marks)
Browser: DevTools
Chrome DevTools is the de facto standard for web development and data analysis in the
browser. You'll use this a lot when debugging and inspecting web pages.
Here are the key features you'll use most:
1. Elements Panel
Inspect and modify HTML/CSS in real-time
Copy CSS selectors for web scraping
Debug layout issues with the Box Model
// Copy selector in Console
copy($0); // Copies selector of selected element
2. Console Panel
JavaScript REPL environment
Log and debug data
Common console methods:
console.table(data); // Display data in table format
console.group("Name"); // Group related logs
console.time("Label"); // Measure execution time
3. Network Panel
Monitor API requests and responses
Simulate slow connections
Right-click on a request and select "Copy as fetch" to get the request.
4. Essential Keyboard Shortcuts
Ctrl+Shift+I (Windows) / Cmd+Opt+I (Mac): Open DevTools
Ctrl+Shift+C: Select element to inspect
Ctrl+L: Clear console
$0: Reference currently selected element
$$('selector'): Query selector all (returns array)
EndedVideos
at Sun,from26Chrome Developers (37 min total):
Jan, 2025, 11:59 pm IST
Fun & powerful: Intro to Chrome DevTools (5 min)
Different ways to open Chrome DevTools (5 min)
Faster DevTools navigation with shortcuts and settings (3 min)
How to log messages in the Console (6 min)
How to speed up your workflow with Console shortcuts (6 min)
HTML vs DOM? Let’s debug them (5 min)
Caching demystified: Inspect, clear, and disable caches (7 min)
Console message logging (6 min)
Console workflow shortcuts (6 min)
HTML vs DOM debugging (5 min)
Cache inspection and management (7 min)
Just above this paragraph, there's a hidden input with a secret value.
What is the value in the hidden input?
n9plxow7wv
Check
7 Count Wednesdays (0.5 marks)
How many Wednesdays are there in the date range 1989-04-15 to 2014-02-28?
1298
The dates are in the year-month-day format. Include both the start and end date in your
count. You can do this using any tool (e.g. Excel, Python, JavaScript, manually).
Check
8 Extract CSV from a ZIP (0.25 marks)
Download and unzip file q-extract-csv-zip.zip which has a single extract.csv file inside.
What is the value in the "answer" column of the CSV file?
Ended atd4b51
Sun, 26 Jan, 2025, 11:59 pm IST
Check
9 Use JSON (0.75 marks)
JSON
JSON (JavaScript Object Notation) is the de facto standard format for data exchange on
the web and APIs. Its human-readable format and widespread support make it essential for
data scientists working with web services, APIs, and configuration files.
For data scientists, JSON is essential when:
Working with REST APIs and web services
Storing configuration files and metadata
Parsing semi-structured data from databases like MongoDB
Creating data visualization specifications (e.g., Vega-Lite)
Watch this comprehensive introduction to JSON (15 min):
Key concepts to understand in JSON:
JSON only supports 6 data types: strings, numbers, booleans, null, arrays, and objects
You can nest data. Arrays and objects can contain other data types, including other
Ended at Sun,
arrays26andJan, 2025, 11:59 pm IST
objects
Always validate. Ensure JSON is well-formed. Comm errors: Trailing commas, missing
quotes, and escape characters
JSON Lines is a format that allows you to store multiple JSON objects in a single line. It's
useful for logging and streaming data.
Tools you could use with JSON:
JSONLint: Validate and format JSON
JSON Editor Online: Visual JSON editor and formatter
JSON Schema: Define the structure of your JSON data
jq: Command-line JSON processor
Common Python operations with JSON:
import json
# Parse JSON string
json_str = '{"name": "Alice", "age": 30}'
data = json.loads(json_str)
# Convert to JSON string
json_str = json.dumps(data, indent=2)
# Read JSON from file
with open('data.json') as f:
data = json.load(f)
# Write JSON to file
with open('output.json', 'w') as f:
json.dump(data, f, indent=2)
# Read JSON data a Pandas DataFrame. JSON data is typically stored as an arra
import pandas as pd
df = pd.read_json('data.json')
# Read JSON lines from file into a DataFrame. JSON lines are typically one li
df = pd.read_json('data.jsonl', lines=True)
Practice JSON skills with these resources:
JSON Generator: Create sample JSON data
JSON Path Finder: Learn to navigate complex JSON structures
JSON Schema Validator: Validate JSON against schemas
Let's make sure you know how to use JSON. Sort this JSON array of objects by the value of
the age field. In case of a tie, sort by the name field. Paste the resulting JSON below without
Endedanyat Sun,
spaces or newlines.
26 Jan, 2025, 11:59 pm IST
[{"name":"Alice","age":91},{"name":"Bob","age":68},
{"name":"Charlie","age":76},{"name":"David","age":87},
{"name":"Emma","age":4},{"name":"Frank","age":80},
{"name":"Grace","age":92},{"name":"Henry","age":12},
{"name":"Ivy","age":64},{"name":"Jack","age":46},{"name":"Karen","age":17},
{"name":"Liam","age":84},{"name":"Mary","age":54},{"name":"Nora","age":60},
{"name":"Oscar","age":90},{"name":"Paul","age":54}]
Sorted JSON:
[{"name":"Emma","age":4},{"name":"Henry","age":12},{"name":"Karen","age":17},{"name
Check
10 Multi-cursor edits to convert to JSON (0.5 marks)
Download q-multi-cursor-json.txt and use multi-cursors and convert it into a single JSON
object, where key=value pairs are converted into {key: value, key: value, ...}.
What's the result when you paste the JSON at tools-in-data-science.pages.dev/jsonhash
and click the Hash button?
316909dd28f31aee6253b5f65e4896f167bdd688eec678a3c7c48b227cd0a9cc
Check
11 CSS selectors (0.5 marks)
CSS Selectors
CSS selectors are patterns used to select and style HTML elements on a web page. They
are fundamental to web development and data scraping, allowing you to precisely target
elements for styling or extraction.
For data scientists, understanding CSS selectors is crucial when:
Web scraping with tools like Beautiful Soup or Scrapy
Selecting elements for browser automation with Selenium
Styling data visualizations and web applications
Debugging website issues using browser DevTools
EndedWatch
at Sun,this26comprehensive
Jan, 2025, introduction
11:59 pm ISTto CSS selectors (20 min):
The Mozilla Developer Network (MDN) provides detailed documentation on the three main
types of selectors:
Basic CSS selectors: Learn about element (div), class (.container), ID (#header), and
universal (*) selectors
Attribute selectors: Target elements based on their attributes or attribute values
([type="text"])
Combinators: Use relationships between elements (div > p, div + p, div ~ p)
Practice your CSS selector skills with this interactive tool:
CSS Diner: A fun game that teaches CSS selectors through increasingly challenging
levels
Let's make sure you know how to select elements using CSS selectors. Find all <div>s
having a foo class in the hidden element below. What's the sum of their data-value
attributes?
Sum of data-value attributes:
77
Ended atCheck
Sun, 26 Jan, 2025, 11:59 pm IST
12 Process files with different encodings (1 mark)
Unicode
Ever noticed when you copy-paste some text and get garbage symbols? Or see garbage
when you load a CSV file? This video explains why. It covers how computers store text
(called character encoding) and why it sometimes goes wonky.
Learn about ASCII (the original 7-bit encoding system that could only handle 128
characters), why that wasn't enough for global languages, and how modern solutions like
Unicode save the day by letting us use any character from any language.
Some programs try to guess encodings (sometimes badly!). A signature called BOM (Byte
Order Mark)helps computers know exactly how to read text files correctly.
Learn how Unicode, UTF-8 and character encoding works. This is a common gotcha when
building apps that handle international text - something bootcamps often skip but
developers and data scientists regularly face in the real world.
Unicode is fundamental for data scientists working with international data. Here are key
concepts you need to understand:
Character Encodings: Different ways to represent text in computers
ASCII (7-bit): Limited to 128 characters, English-only
UTF-8: Variable-width encoding, backwards compatible with ASCII
UTF-16: Fixed-width encoding, used in Windows and Java
UTF-32: Fixed-width encoding, memory inefficient but simple
Common encoding issues you'll encounter:
# Reading files with explicit encoding
with open('file.txt', encoding='utf-8') as f:
text = f.read()
# Handling encoding errors
import pandas as pd
df = pd.read_csv('data.csv', encoding='utf-8', errors='replace')
# Detecting file encoding
import chardet
with open('unknown.txt', 'rb') as f:
result = chardet.detect(f.read())
print(result['encoding'])
Ended at Sun, 26 Jan, 2025, 11:59 pm IST
Download and process the files in q-unicode-data.zip which contains three files with
different encodings:
data1.csv: CSV file encoded in CP-1252
data2.csv: CSV file encoded in UTF-8
data3.txt: Tab-separated file encoded in UTF-16
Each file has 2 columns: symbol and value. Sum up all the values where the symbol
matches ‚ OR Œ across all three files.
What is the sum of all values associated with these symbols?
30621
Check
13 Use GitHub (0.5 marks)
Version Control: Git, GitHub
Git is the de facto standard for version control of software (and sometimes, data as well).
It's a system that keeps track of changes you make to files and folders. It allows you to
Endedrevert to a previous state, compare changes, etc. It's a central tool in any developer's
workflow. 26 Jan, 2025, 11:59 pm IST
at Sun,
GitHub is the most popular hosting service for Git repositories. It's a website that shows
your code, allows you to collaborate with others, and provides many useful tools for
developers.
Watch these introductory videos to learn the basics of Git and GitHub (98 min):
Ended at Sun, 26 Jan, 2025, 11:59 pm IST
Essential Git Commands:
# Repository Setup
git init # Create new repo
git clone url # Clone existing repo
git remote add origin url # Connect to remote
# Basic Workflow
git status # Check status
git add . # Stage all changes
git commit -m "message" # Commit changes
git push origin main # Push to remote
# Branching
git branch # List branches
git checkout -b feature # Create/switch branch
git merge feature # Merge branch
git rebase main # Rebase on main
# History
git log --oneline # View history
git diff commit1 commit2 # Compare commits
git blame file # Show who changed what
Best Practices:
1. Commit Messages
Ended at Sun, 26 Jan, 2025, 11:59 pm IST
# Good commit message format
type(scope): summary
Detailed description of changes.
# Examples
feat(api): add user authentication
fix(db): handle null values in query
2. Branching Strategy
main: Production code
develop: Integration branch
feature/*: New features
hotfix/*: Emergency fixes
3. Code Review
Keep PRs small (<400 lines)
Use draft PRs for WIP
Review your own code first
Respond to all comments
Essential Tools
GitHub Desktop: GUI client
GitLens: VS Code extension
gh: GitHub CLI
pre-commit: Git hooks
Let's make sure you know how to use GitHub. Create a GitHub account if you don't have
one. Create a new public repository. Commit a single JSON file called email.json with the
value {"email": "[email protected]"} and push it.
Enter the raw Github URL of email.json so we can verify it. (It might look like
https://raw.githubusercontent.com/[GITHUB ID]/[REPO NAME]/main/email.json.)
https://raw.githubusercontent.com/achuthan-m-iitm/email-repo/refs/heads/main/email.js
Check
14 Replace across files (0.75 marks)
Terminal: Bash
EndedUNIX
at shells are the de facto standard in the data science world and Bash is the most
Sun, 26 isJan,
popular. This 2025,by11:59
available defaultpm IST and Linux.
on Mac
On Windows, install Git Bash or WSL to get a UNIX shell.
Watch this video to understand the basics of Bash and UNIX shell commands (75 min).
Essential Commands:
# File Operations
ls -la # List all files with details
cd path/to/dir # Change directory
pwd # Print working directory
cp source dest # Copy files
mv source dest # Move/rename files
rm -rf dir # Remove directory recursively
# Text Processing
grep "pattern" file # Search for pattern
sed 's/old/new/' f # Replace text
awk '{print $1}' f # Process text by columns
cat file | wc -l # Count lines
# Process Management
ps aux # List processes
kill -9 PID # Force kill process
top # Monitor processes
htop # Interactive process viewer
Ended at Sun, 26 Jan, 2025, 11:59 pm IST
# Network
curl url # HTTP requests
wget url # Download files
nc -zv host port # Test connectivity
ssh user@host # Remote login
# Count unique values in CSV column
cut -d',' -f1 data.csv | sort | uniq -c
# Quick data analysis
awk -F',' '{sum+=$2} END {print sum/NR}' data.csv # Average
sort -t',' -k2 -n data.csv | head # Top 10
# Monitor log in real-time
tail -f log.txt | grep --color 'ERROR'
Bash Scripting Essentials:
#!/bin/bash
# Variables
NAME="value"
echo $NAME
# Loops
for i in {1..5}; do
echo $i
done
# Conditionals
if [ -f "file.txt" ]; then
echo "File exists"
fi
# Functions
process_data() {
local input=$1
echo "Processing $input"
}
Productivity Tips:
1. Command History
history # Show command history
Ctrl+R # Search history
!! # Repeat last command
!$ # Last argument
2. Directory Navigation
Ended at Sun, 26 Jan, 2025, 11:59 pm IST
pushd dir
popd
# Push directory to stack
# Pop directory from stack
cd - # Go to previous directory
3. Job Control
command & # Run in background
Ctrl+Z # Suspend process
bg # Resume in background
fg # Resume in foreground
4. Useful Aliases - typically added to ~/.bashrc
alias ll='ls -la'
alias gs='git status'
alias jupyter='jupyter notebook'
alias activate='source venv/bin/activate'
Download q-replace-across-files.zip and unzip it into a new folder, then replace all "IITM"
(in upper, lower, or mixed case) with "IIT Madras" in all files. Leave everything as-is - don't
change the line endings.
What does running cat * | sha256sum in that folder show in bash?
3b1e1e62826a007a4d6f2e51d808bda3e5908cfe807650cf0a5f8fcf549237d4
Check
15 List files and attributes (0.75 marks)
Download q-list-files-attributes.zip and extract it. Use ls with options to list all files in the
folder along with their date and file size.
What's the total size of all files at least 913 bytes large and modified on or after Thu, 30
Dec, 1999, 2:35 am IST?
395798
Don't copy from inside the ZIP file or use Windows Explorer to unzip. That destroys the
timestamps. Extract using unzip, 7-Zip or similar utilities and check the timestamps.
Check
Move26andJan,
Ended at16 Sun, rename
2025,files11:59
(0.5 marks)
pm IST
Download q-move-rename-files.zip and extract it. Use mv to move all files under folders into
an empty folder. Then rename all files replacing each digit with the next. 1 becomes 2, 9
becomes 0, a1b9c.txt becomes a2b0c.txt.
What does running grep . * | LC_ALL=C sort | sha256sum in bash on that folder show?
7cc38af46a15ec0814b75a01335a8403c9edfc53ff28bee784dcc74825b54213
Check
17 Compare files (0.5 marks)
Download q-compare-files.zip and extract it. It has 2 nearly identical files, a.txt and
b.txt, with the same number of lines.
How many lines are different between a.txt and b.txt?
14
Check
18 SQL: Ticket Sales (0.75 marks)
Database: SQLite
Relational databases are used to store data in a structured way. You'll often access
databases created by others for analysis.
PostgreSQL, MySQL, MS SQL, Oracle, etc. are popular databases. But the most installed
database is SQLite. It's embedded into many devices and apps (e.g. your phone, browser,
etc.). It's lightweight but very scalable and powerful.
Watch these introductory videos to understand SQLite and how it's used in Python (34
min):
Ended at Sun, 26 Jan, 2025, 11:59 pm IST
There are many non-relational databases (NoSQL) like ElasticSearch, MongoDB, Redis, etc.
that you should know about and we may cover later.
Core Concepts:
Ended at Sun, 26 Jan, 2025, 11:59 pm IST
-- Create a table
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Insert data
INSERT INTO users (name, email) VALUES
('Alice', '[email protected]'),
('Bob', '[email protected]');
-- Query data
SELECT name, COUNT(*) as count
FROM users
GROUP BY name
HAVING count > 1;
-- Join tables
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'pending';
Python Integration:
import sqlite3
from pathlib import Path
import pandas as pd
async def query_database(db_path: Path, query: str) -> pd.DataFrame:
"""Execute SQL query and return results as DataFrame.
Args:
db_path: Path to SQLite database
query: SQL query to execute
Returns:
DataFrame with query results
"""
try:
conn = sqlite3.connect(db_path)
return pd.read_sql_query(query, conn)
finally:
conn.close()
# Example usage
db = Path('data.db')
df = await query_database(db, '''
SELECT date, COUNT(*) as count
Ended at Sun, 26 Jan, 2025, 11:59 pm IST
FROM events
GROUP BY date
''')
Common Operations:
1. Database Management
-- Backup database
.backup 'backup.db'
-- Import CSV
.mode csv
.import data.csv table_name
-- Export results
.headers on
.mode csv
.output results.csv
SELECT * FROM table;
2. Performance Optimization
-- Create index
CREATE INDEX idx_user_email ON users(email);
-- Analyze query
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email LIKE '%@example.com';
-- Show indexes
SELECT * FROM sqlite_master WHERE type='index';
3. Data Analysis
-- Time series aggregation
SELECT
date(timestamp),
COUNT(*) as events,
AVG(duration) as avg_duration
FROM events
GROUP BY date(timestamp);
-- Window functions
SELECT *,
AVG(amount) OVER (
PARTITION BY user_id
ORDER BY date
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
Ended at Sun, 26 Jan, 2025, 11:59 pm IST
) as moving_avg
FROM transactions;
Tools to work with SQLite:
SQLiteStudio: Lightweight GUI
DBeaver: Full-featured GUI
sqlite-utils: CLI tool
Datasette: Web interface
There is a tickets table in a SQLite database that has columns type, units, and price.
Each row is a customer bid for a concert ticket.
type units price
gold 459 1.49
GOLD 869 1.46
bronze 469 1.18
GOLD 570 0.96
GOLD 319 1.54
...
What is the total sales of all the items in the "Gold" ticket type? Write SQL to calculate it.
SELECT SUM(units * price) AS total_gold_sales
FROM tickets
WHERE TRIM(LOWER(type)) = 'gold';
Get all rows where the Type is "Gold". Ignore spaces and treat mis-spellings like GOLD,
gold, etc. as "Gold". Calculate the sales as Units * Price, and sum them up.
Check
Check all Save
Save regularly. Your last saved submission will be evaluated.
Ended at Sun, 26 Jan, 2025, 11:59 pm IST
Best of luck!
