Write a Python program to load a query result into a pandas DataFrame.
Output
city total
0 Pune 2180
1 Delhi 450
Approach
pd.read_sql_query(sql, conn).
Solution
Python
importsqlite3importpandasaspdconn=sqlite3.connect(":memory:")conn.execute("CREATE TABLE sales (city TEXT, amount INTEGER)")conn.executemany("INSERT INTO sales VALUES (?, ?)",[("Pune",1200),("Delhi",450),("Pune",980)])df=pd.read_sql_query("SELECT city, SUM(amount) AS total FROM sales GROUP BY city ORDER BY total DESC",conn)print(df)
Output
city total
0 Pune 2180
1 Delhi 450
Learn the concept
New to this topic? Read the lesson, then come back.
20 SQLite programs using Python’s built-in sqlite3 module: creating tables, inserting with parameters, querying, updating, deleting, joins, aggregates, transactions and exporting to CSV. Most use an in-memory database so they run anywhere.
Before you start
Read the SQLite lesson first if the topic is new to you.
Write a Python program to export a table to a CSV file.
Output
name,marks
Asha,91
Ravi,74
Approach
Read rows and column names, then use csv.writer.
Solution
Python
importcsvimportsqlite3conn=sqlite3.connect(":memory:")conn.execute("CREATE TABLE students (name TEXT, marks INTEGER)")conn.executemany("INSERT INTO students VALUES (?, ?)",[("Asha",91),("Ravi",74)])cur=conn.execute("SELECT * FROM students")withopen("students.csv","w",newline="")asf:writer=csv.writer(f)writer.writerow([c[0]forcincur.description])writer.writerows(cur)print(open("students.csv").read())
Output
name,marks
Asha,91
Ravi,74
Learn the concept
New to this topic? Read the lesson, then come back.
importsqlite3conn=sqlite3.connect(":memory:")conn.execute("CREATE TABLE users (name TEXT, secret TEXT)")conn.executemany("INSERT INTO users VALUES (?, ?)",[("asha","s1"),("ravi","s2")])user_input="nobody' OR '1'='1"unsafe=conn.execute(f"SELECT name FROM users WHERE name = '{user_input}'").fetchall()safe=conn.execute("SELECT name FROM users WHERE name = ?",(user_input,)).fetchall()print("unsafe query returned:",unsafe)print("safe query returned:",safe)
Write a Python program to find products whose name contains a word.
Output
[('USB cable',), ('USB hub',)]
Approach
LIKE '%' || ? || '%' keeps the value as a parameter.
Solution
Python
importsqlite3conn=sqlite3.connect(":memory:")conn.execute("CREATE TABLE products (name TEXT)")conn.executemany("INSERT INTO products VALUES (?)",[("USB cable",),("Wireless mouse",),("USB hub",),("Keyboard",)])term="usb"print(conn.execute("SELECT name FROM products WHERE name LIKE '%' || ? || '%'",(term,)).fetchall())
Output
[('USB cable',), ('USB hub',)]
Line by line
Line 6
SQLite’s LIKE ignores case for English letters.
Learn the concept
New to this topic? Read the lesson, then come back.
Write a Python program to print the column names of a query result.
Output
['id', 'name', 'email']
Approach
cursor.description.
Solution
Python
importsqlite3conn=sqlite3.connect(":memory:")conn.execute("CREATE TABLE users (id INTEGER, name TEXT, email TEXT)")cur=conn.execute("SELECT * FROM users")print([col[0]forcolincur.description])
Output
['id', 'name', 'email']
Learn the concept
New to this topic? Read the lesson, then come back.
Write a Python program to find customers who have not placed any order.
Output
[('Meena',), ('Ravi',)]
Approach
LEFT JOIN and check for NULL.
Solution
Python
importsqlite3conn=sqlite3.connect(":memory:")conn.executescript(""" CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER); INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meena'); INSERT INTO orders VALUES (10, 1), (11, 1);""")print(conn.execute("""SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL ORDER BY c.name""").fetchall())
Output
[('Meena',), ('Ravi',)]
Learn the concept
New to this topic? Read the lesson, then come back.
with conn: commits on success and rolls back on an exception.
Solution
Python
importsqlite3conn=sqlite3.connect(":memory:")conn.execute("CREATE TABLE acc (name TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))")conn.executemany("INSERT INTO acc VALUES (?, ?)",[("Asha",500),("Ravi",100)])conn.commit()deftransfer(src,dst,amount):try:withconn:conn.execute("UPDATE acc SET balance = balance + ? WHERE name = ?",(amount,dst))conn.execute("UPDATE acc SET balance = balance - ? WHERE name = ?",(amount,src))return"done"exceptsqlite3.IntegrityError:return"failed, rolled back"print(transfer("Asha","Ravi",200),conn.execute("SELECT * FROM acc").fetchall())print(transfer("Ravi","Asha",1000),conn.execute("SELECT * FROM acc").fetchall())
