Reading and Writing Data - Including From a Query

Context

pandas reads a table from delimited text, from a JSON list of records, from a spreadsheet and from the result of a SQL query, and writes a table back to each of them. The options on each call decide how the text in a file becomes typed columns, and how a table's rows go into a database table.

Every project starts at a reader and ends at a writer. A listings site exports CSV, a web service answers in JSON, a colleague keeps a workbook, and the largest source is a database you query with Section 2.1's SQL so that only the rows you need reach your script. At the other end the cleaned table goes to a file the next step reads, or into a database table the next pipeline reads from, as this module's capstone does.

A default that does not match the file usually fails without raising. A missing-value marker pandas does not know turns a price column into text, a file separated by semicolons arrives as one column, and a table written with its index comes back with an extra column nobody asked for. On the way into a database the stakes are higher: one option, if_exists="replace", deletes a STRICT table with every row in it and builds a table that accepts anything. Lesson 2.4.1 read housing.csv with one call and every default, and this lesson opens that call up.

Goal

• Read a CSV with sep, usecols, dtype, na_values, index_col and nrows, and tell from a wrong result which option the file needed.
• Install openpyxl and read housing.json and housing.xlsx into the same table as housing.csv.
• Write a table to CSV, JSON and Excel, and read each back without an extra index column.
• Pull a query's result into a DataFrame with pd.read_sql and ? parameters, and predict the dtype each SQLite column arrives with.
• Append a DataFrame to a STRICT table with to_sql, read the refusal a value that cannot convert without loss produces, and state what if_exists="replace" destroys.

Prerequisites

Neither is required, and the lesson works without both. They are here if you want to see the formats side by side first.

• YouTube - "Python Pandas Tutorial (Part 11): Reading/Writing Data to Different Sources - Excel, JSON, SQL, Etc" by Corey Schafer. The CSV, Excel and JSON parts, for the read-and-write pattern repeated across formats.
• Official docs - pandas 3.0 User Guide, "IO tools", the "CSV & text files" section. The common read_csv options, most with their defaults, the reference to keep open while reading a file you did not write.

Table of Contents

• Reading CSV
• Reading JSON and Excel
• Writing Files
• Reading From a SQL Query
• Writing to a STRICT Table

Theory

This lesson's downloads are housing.json and housing.xlsx, the same 209 listings as housing.csv, saved as a JSON list of records and as a workbook with one sheet, Sheet1. Save both into ~/dev/bootcamp. Reading the workbook needs one more library, openpyxl, the library pandas calls to open an .xlsx file. With (bootcamp) active in ~/dev/bootcamp, install it:

uv pip install openpyxl==3.1.5  # the engine read_excel uses

 + et-xmlfile==2.0.0
 + openpyxl==3.1.5

et-xmlfile is a library openpyxl needs in turn. pandas did not install openpyxl itself, because it treats Excel support as optional, so a read_excel run before this install fails with a long traceback, most of it inside pandas, that ends with this line:

ImportError: `Import openpyxl` failed.  Use pip or conda to install the openpyxl package.

The message names pip and conda, and the installer for (bootcamp) is the uv pip line above.

A block with no import continues the script above it in the same subtitle, and a # raises comment marks a line that stops the script, so delete that line once you have seen its error.

Reading CSV

What. pd.read_csv reads delimited text into a DataFrame, and its options say what the text means: sep the character between fields, usecols the columns to keep, dtype the type a column must have, na_values extra markers for a missing value, index_col the column that labels the rows, and nrows how many rows to read. Why. housing.csv matched every default, so Lesson 2.4.1 needed none. A file from somewhere else rarely does, and a wrong default rarely raises: the read succeeds and returns a table whose types or shape are wrong. When. For every file you did not write yourself, and every time a first look shows a numeric column as str or a table with one column.

The two small files this subtitle reads are made by a script first. make_files.py writes new_listings.csv, three new listings in the columns of housing.csv with one price typed as unknown, and export.csv, a table whose fields are separated by semicolons:

with open("new_listings.csv", "w", encoding="utf-8") as f:
    f.write("price,area_sqm,rooms,year_built,neighborhood,condition,listing_date\n")
    f.write("318000,88.5,3,1996,Riverside,good,2024-12-02\n")
    f.write("unknown,64.0,2,1978,Downtown,fair,2024-12-03\n")
    f.write("402000,120.0,4,2011,Eastgate,excellent,2024-12-05\n")

with open("export.csv", "w", encoding="utf-8") as f:
    f.write("price;area_sqm;rooms\n")
    f.write("310000;82.5;3\n")
    f.write("245000;64.0;2\n")

pandas already reads a set of common markers as missing, among them an empty field, NA, N/A, n/a, NULL, null, NaN, nan and None. unknown is not one of them, and neither are -, ?, missing or none in lower case, so a column holding one of those is read as text, every value in it:

import pandas as pd

new = pd.read_csv("new_listings.csv")
print(new.dtypes)
print(new["price"])

price               str
area_sqm        float64
rooms             int64
year_built        int64
neighborhood        str
condition           str
listing_date        str
dtype: object
0     318000
1    unknown
2     402000
Name: price, dtype: str

Nothing failed, and the table looks right in print. It fails later, when something computes on it: new["price"].mean() raises TypeError: Cannot perform reduction 'mean' with string dtype. na_values names the extra markers, and dtype states the type a column must have, so a value that does not fit stops the read at once, unless the type is a narrow one such as Lesson 2.5.2's int8, which holds only -128 to 127 and wraps a larger number without a word. Here the error names the value:

new = pd.read_csv("new_listings.csv", na_values=["unknown"])
print(new["price"].dtype)   # float64
print(new["price"].mean())  # 360000.0

# raises ValueError: could not convert string to float: 'unknown'
pd.read_csv("new_listings.csv", dtype={"price": "float64"})

The separator is the other default that usually fails without a sound. Read with the comma it expects, this semicolon file has no commas to split on, so every line becomes one field and the header becomes one column name:

export = pd.read_csv("export.csv")
print(export.shape)          # (2, 1)
print(list(export.columns))  # ['price;area_sqm;rooms']

export = pd.read_csv("export.csv", sep=";")
print(export.shape)          # (2, 3)

usecols and nrows read less of a large file, and index_col turns a column into the row labels. usecols keeps the columns in the file's order whatever order the list gives, and an index made from a column need not be unique:

first = pd.read_csv("housing.csv", usecols=["neighborhood", "price"], nrows=3)
print(first)

by_area = pd.read_csv("housing.csv", index_col="neighborhood")
print(by_area.shape)
print(by_area.loc["Downtown"].shape)

    price neighborhood
0  437000     Lakeview
1  268000     Downtown
2  268000    Southgate
(209, 6)
(20, 6)

The three prices printed as 437000, not 437000.0 as in Lesson 2.4.1: none of the first three rows lacks a price, so price read from them alone is int64. neighborhood left the columns to become the labels, and .loc["Downtown"] returns all 20 rows carrying that label. The last option, encoding, names how the file's characters were saved, utf-8 by default, and a file saved as latin-1 with a character such as é in it raises UnicodeDecodeError under the default until encoding="latin-1" is passed.

DON'T:

• DON'T trust a numeric column that loads as str. One marker pandas does not know turns every value into text, and the error comes only when something computes on it, far from the read that caused it.
• DON'T read a file with the default separator without looking at the shape. A semicolon file came back as (2, 1), one column named after the whole header line, and nothing raised.
• DON'T judge a file's dtypes from an nrows sample. The first three rows of housing.csv read price as int64, and the whole file reads it as float64, because the gaps come later.

Reading JSON and Excel

What. pd.read_json reads a JSON file, and a file holding a list of objects becomes one row per object and one column per key. pd.read_excel reads one sheet of an .xlsx workbook, the first unless sheet_name names another. Why. Data arrives in the format of whoever produced it: web services answer in JSON, and people keep tables in spreadsheets. Both readers return the same kind of DataFrame, so everything after the read is the same code. When. Whenever the source is a JSON export or a workbook.

The JSON file is Lesson 1.5.3's list of records written out with json.dump, one object per listing, {"price": 437000, "area_sqm": 131.8, ...}, with null where a value is missing:

import pandas as pd

housing = pd.read_csv("housing.csv")
from_json = pd.read_json("housing.json")
from_excel = pd.read_excel("housing.xlsx")
print(from_json.shape, from_excel.shape)  # (209, 7) (209, 7)
print(from_json.equals(housing))          # True
print(from_excel.equals(housing))         # True

equals is True when two DataFrames hold the same labels, values and dtypes, with missing values in the same places. All three files give the identical table, so the format stops mattering the moment it is read. A workbook can hold several sheets, and sheet_name=None returns every one of them, in a dict keyed by sheet name:

sheets = pd.read_excel("housing.xlsx", sheet_name=None)
print(list(sheets))    # ['Sheet1']
listings = pd.read_excel("housing.xlsx", sheet_name="Sheet1")
print(listings.shape)  # (209, 7)

A name the workbook does not have raises, sheet_name="2024" with ValueError: Worksheet named '2024' not found.

DON'T:

• DON'T read a workbook you did not make without listing its sheets. read_excel reads the first sheet unless told otherwise, and the first sheet is not always the data.
• DON'T assume a column comes out of read_json with the type read_csv gives it. read_json turns a column named date into dates, datetime64[us], where read_csv leaves the same text as str, so a table read from two formats can differ in its dtypes.

Writing Files

What. to_csv, to_json and to_excel write a DataFrame to a file, the mirror of the three readers. to_csv and to_excel write the row labels too unless index=False is passed, to_json writes them as keys in its default layout, which ignores index=False, and orient="records" writes objects that hold the columns only. Why. A cleaned table, a feature set or a result has to outlive the script that made it, and the next step reads it back from disk. Whatever the writer adds, the reader returns as data. When. At the end of every step whose output another step reads.

riverside is a filtered table, so its labels are the ones its rows had in housing, and a default to_csv writes them as a first column with an empty header:

import pandas as pd

housing = pd.read_csv("housing.csv")
riverside = housing[housing["neighborhood"] == "Riverside"]

riverside.to_csv("riverside.csv")
back = pd.read_csv("riverside.csv")
print(back.shape)
print(back.iloc[0:3, 0:3])

(28, 8)
   Unnamed: 0     price  area_sqm
0           6  281000.0     105.6
1           8  309000.0      62.7
2          11  222000.0     121.3

The file's header line starts with an empty name, and read_csv called that column Unnamed: 0. It holds the old labels 6, 8 and 11, which mean nothing outside housing, and a model handed this table would treat them as a feature. index=False leaves them out:

riverside.to_csv("riverside.csv", index=False)
print(pd.read_csv("riverside.csv").shape)  # (28, 7)

JSON and Excel take the same idea. orient="records" writes the list of objects housing.json holds, and without it to_json writes one object per column, keyed by row label, {"price":{"6":281000.0,...}}:

riverside.to_json("riverside.json", orient="records")
riverside.to_excel("riverside.xlsx", index=False)
from_json = pd.read_json("riverside.json")
from_excel = pd.read_excel("riverside.xlsx")
print(from_json.shape, from_excel.shape)  # (28, 7) (28, 7)

DON'T:

• DON'T leave to_csv at its default for a table whose labels mean nothing. The labels come back as Unnamed: 0, a column of old row numbers that looks like data.
• DON'T remove Unnamed: 0 after every read. Fix the write with index=False, and the column never exists.

Reading From a SQL Query

What. pd.read_sql(query, con) sends a SQL query through a sqlite3 connection and returns the result as a DataFrame, one column per selected column. params= fills the query's ? placeholders, as con.execute did in Section 2.1. Why. A database can hold far more rows than memory, and filtering, joining and grouping run inside it, which is what Section 2.1 taught SQL for. A query that does that work first hands pandas only the result. When. Whenever the data lives in a database: let the query reduce it, then work on the result in pandas.

The database is built by a script first. build_listings_db.py creates a STRICT table listings in a file of its own, listings.db, leaving Section 2.1's housing.db as it was, and fills it from housing.csv with to_sql, the next subtitle's subject. It drops the table first, so it runs again cleanly whenever you want the 209 listings back:

import sqlite3
import pandas as pd

housing = pd.read_csv("housing.csv")
con = sqlite3.connect("listings.db")
con.execute("DROP TABLE IF EXISTS listings")
con.execute("""
    CREATE TABLE listings (
        id INTEGER PRIMARY KEY,
        price INTEGER,
        area_sqm REAL,
        rooms INTEGER NOT NULL,
        year_built INTEGER NOT NULL,
        neighborhood TEXT NOT NULL,
        condition TEXT,
        listing_date TEXT
    ) STRICT
""")
housing.to_sql("listings", con, if_exists="append", index=False)
con.close()

id is filled in by SQLite, 1 to 209, because an INTEGER PRIMARY KEY left out of an insert gets the next number. A query's result arrives with a dtype per column:

import sqlite3
import pandas as pd

con = sqlite3.connect("listings.db")
large = pd.read_sql(
    "SELECT neighborhood, price, rooms FROM listings WHERE rooms >= 5", con
)
print(large.shape)
print(large.dtypes)

(74, 3)
neighborhood        str
price           float64
rooms             int64
dtype: object

price is an INTEGER column and arrived as float64, because some of the rows returned hold a NULL, which arrives as NaN, and a column of whole numbers holding a NaN comes out float64, as price did from read_csv in Lesson 2.4.1. The same column without its NULLs arrives as integers, so the dtype depends on the rows a query returns, not on the column's declared type:

priced = pd.read_sql("SELECT price FROM listings WHERE price IS NOT NULL", con)
print(priced["price"].dtype)  # int64

The database does the grouping before anything reaches pandas, with Lesson 2.1.3's GROUP BY, COUNT and AVG, and the result is seven rows rather than 209:

summary = pd.read_sql("""
    SELECT neighborhood, COUNT(*) AS listings, COUNT(price) AS priced,
           ROUND(AVG(price)) AS avg_price
    FROM listings
    GROUP BY neighborhood
    ORDER BY listings DESC, neighborhood
""", con)
print(summary)

  neighborhood  listings  priced  avg_price
0    Southgate        37      36   213722.0
1     Eastgate        33      30   736167.0
2    Hillcrest        33      29   184379.0
3     Lakeview        30      28   392893.0
4     Northend        28      26   193654.0
5    Riverside        28      22   298773.0
6     Downtown        20      18   520611.0

Eastgate's average is pulled up by the 9.9 million listing the first look found, Downtown's by one at 5.2 million, and AVG skipped the missing prices, so each average is over the priced count, not the listings count. A value from Python goes in through params=, one item per ?, never pasted into the query text:

query = "SELECT price, area_sqm FROM listings WHERE neighborhood = ? AND price < ?"
riverside_cheap = pd.read_sql(query, con, params=("Riverside", 260000))
print(riverside_cheap)
con.close()

    price  area_sqm
0  222000     121.3
1  237000     140.6

DON'T:

• DON'T build a query by pasting a value into it with an f-string. A value holding a quote breaks the query and a crafted one rewrites it, as Lesson 2.1.1 showed, and params= passes values safely.
• DON'T expect an INTEGER column to arrive as int64. One NULL among the numbers a query returns makes it float64, and a column that comes back holding only NULLs arrives as object, every value None.
• DON'T read a whole table with SELECT * to filter it in pandas when a WHERE can filter it in the database. Every row the query returns is read into Python, and the rows your filter then throws away were read for nothing.

Writing to a STRICT Table

What. df.to_sql(name, con, if_exists="append", index=False) inserts a DataFrame's rows into an existing table. Into a STRICT table, a value must have the column's type or convert to it without loss, and a value that does not stops the whole call. Why. A STRICT table is a contract enforced where the data arrives, so a bad value refused at the insert never reaches a query or a model trained on one. When. Whenever a pipeline writes a table into a database other steps read. The table is created with SQL first, so the constraints are yours, and pandas only appends.

write_listings.py appends the three new listings, read without na_values, so their price column is the text column from the first subtitle:

import sqlite3
import pandas as pd

con = sqlite3.connect("listings.db")
new = pd.read_csv("new_listings.csv")
try:
    new.to_sql("listings", con, if_exists="append", index=False)
except pd.errors.DatabaseError as err:
    print(err)
    print(f"{type(err.__cause__).__name__}: {err.__cause__}")
print(con.execute("SELECT COUNT(*) FROM listings").fetchone())

Execution failed
IntegrityError: cannot store TEXT value in INTEGER column listings.price
(209,)

SQLite refused 'unknown' in the INTEGER column with sqlite3.IntegrityError, naming the table and the column, and pandas raised its own DatabaseError from it, the raise ... from chaining of Lesson 1.6.2, so err.__cause__ holds SQLite's error. Uncaught, the traceback prints both, SQLite's first and pandas.errors.DatabaseError: Execution failed as its last line, and the useful one is not the last. The count is still 209: not one of the three rows landed, not even the first one, whose text '318000' would have converted to an integer as Lesson 2.1.1 showed. Read with the marker named, the same rows go in:

new = pd.read_csv("new_listings.csv", na_values=["unknown"])
written = new.to_sql("listings", con, if_exists="append", index=False)
count = con.execute("SELECT COUNT(*) FROM listings").fetchone()
print(written, count)  # 3 (212,)

to_sql returns the number of rows it wrote. price is float64 now, 318000.0 converts to the integer 318000 without loss, and NaN goes in as NULL, which the column allows. A value that cannot convert without loss is refused like the text was: a room count of 2.5 stops the call with cannot store REAL value in INTEGER column listings.rooms.

if_exists takes four values: "fail", the default, which raises ValueError: Table 'listings' already exists. when the table is there, "append", "delete_rows", which deletes every row and keeps the table's definition, STRICT included, before it inserts, and "replace", the one to keep away from a table you created. It drops the table, with its rows and its definition, and creates a new one from the DataFrame's dtypes. SQLite keeps every table's CREATE TABLE statement in a table of its own, sqlite_master, so the definition can be read back before and after:

schema = "SELECT sql FROM sqlite_master WHERE name = 'listings'"
bad = pd.read_csv("new_listings.csv")
bad.to_sql("listings", con, if_exists="replace", index=False)

print(con.execute(schema).fetchone()[0])
print(con.execute("SELECT COUNT(*) FROM listings").fetchone())
print(con.execute("SELECT price, typeof(price) FROM listings").fetchall())
con.close()

CREATE TABLE "listings" (
"price" TEXT,
  "area_sqm" REAL,
  "rooms" INTEGER,
  "year_built" INTEGER,
  "neighborhood" TEXT,
  "condition" TEXT,
  "listing_date" TEXT
)
(3,)
[('318000', 'text'), ('unknown', 'text'), ('402000', 'text')]

The same three rows that were refused a moment ago went in without a word. The table pandas built has no STRICT, no id key and no NOT NULL, its price column is TEXT because the DataFrame's was, and the 212 listings that were in it are gone. Run python3 build_listings_db.py to put them back.

DON'T:

• DON'T pass if_exists="replace" to a table you created with SQL. It deletes every row and every constraint, and the refusal you built the table for disappears with them.
• DON'T stop at Execution failed. It is the last line of the traceback and says nothing, and SQLite's error above it names the column.
• DON'T append with the default index=True. pandas then writes the row labels into a column named index, which the table does not have, and the call fails with table listings has no column named index.

Lab Exercises

Lab Exercise 1: An Agent's Export, Read Right

An estate agent sends agent_export.csv. Create it with exactly these four lines: the header listing_ref;price;area_sqm;rooms, then 00417;299000;76.5;3, 00418;315000;-;4 and 01022;188000;54.0;2. The agent marks a missing area with -, and a listing reference is a code whose leading zeros matter.

In agent_lab.py, read the file with no options and print the shape. Read it with only the right separator and print the dtypes and the table, and say in a comment what is still wrong with two of the columns. Then read it in one call that fixes everything, and print the dtypes and the table again.

Finally, read housing.json and housing.xlsx and print whether each equals housing.csv read with read_csv, and print the names of the workbook's sheets.

Lab Exercise 2: From a Query, Into a Table That Refuses

Run build_listings_db.py first, so listings.db holds the 209 listings.

In query_lab.py, write the Downtown listings to downtown.csv twice, once with the defaults and once with index=False, and print the columns each file reads back with. Then read, with one query and a ? parameter for the room count, the number of listings and the average price per neighborhood among listings with at least 4 rooms, ordered by the number of listings and then by name, and print the result and its dtypes.

Finally, append one listing whose room count is 2.5 to listings. Catch the refusal and print SQLite's message, then print the row count to show whether anything landed.

import pandas as pd

with open("agent_export.csv", "w", encoding="utf-8") as f:
    f.write("listing_ref;price;area_sqm;rooms\n")
    f.write("00417;299000;76.5;3\n")
    f.write("00418;315000;-;4\n")
    f.write("01022;188000;54.0;2\n")

print(pd.read_csv("agent_export.csv").shape)

raw = pd.read_csv("agent_export.csv", sep=";")
print(raw.dtypes)
print(raw)
# listing_ref lost its leading zeros as int64, and area_sqm is text
# because of the "-".

(3, 1)
listing_ref    int64
price          int64
area_sqm         str
rooms          int64
dtype: object
   listing_ref   price area_sqm  rooms
0          417  299000     76.5      3
1          418  315000        -      4
2         1022  188000     54.0      2

export = pd.read_csv(
    "agent_export.csv",
    sep=";",
    dtype={"listing_ref": "str"},
    na_values=["-"],
)
print(export.dtypes)
print(export)

listing_ref        str
price            int64
area_sqm       float64
rooms            int64
dtype: object
  listing_ref   price  area_sqm  rooms
0       00417  299000      76.5      3
1       00418  315000       NaN      4
2       01022  188000      54.0      2

housing = pd.read_csv("housing.csv")
from_json = pd.read_json("housing.json")
from_excel = pd.read_excel("housing.xlsx")
sheets = pd.read_excel("housing.xlsx", sheet_name=None)
print(from_json.equals(housing))   # True
print(from_excel.equals(housing))  # True
print(list(sheets))                # ['Sheet1']

import sqlite3
import pandas as pd

housing = pd.read_csv("housing.csv")
downtown = housing[housing["neighborhood"] == "Downtown"]
downtown.to_csv("downtown.csv")
print(list(pd.read_csv("downtown.csv").columns)[:3])
downtown.to_csv("downtown.csv", index=False)
print(list(pd.read_csv("downtown.csv").columns)[:3])

['Unnamed: 0', 'price', 'area_sqm']
['price', 'area_sqm', 'rooms']

con = sqlite3.connect("listings.db")
query = """
    SELECT neighborhood, COUNT(*) AS listings, ROUND(AVG(price)) AS avg_price
    FROM listings
    WHERE rooms >= ?
    GROUP BY neighborhood
    ORDER BY listings DESC, neighborhood
"""
roomy = pd.read_sql(query, con, params=(4,))
print(roomy)
print(roomy.dtypes)

  neighborhood  listings  avg_price
0    Southgate        21   218571.0
1    Hillcrest        19   181000.0
2    Riverside        16   286250.0
3     Lakeview        14   396692.0
4     Northend        14   197385.0
5     Eastgate        12  1283000.0
6     Downtown         7   250571.0
neighborhood        str
listings          int64
avg_price       float64
dtype: object

row = pd.DataFrame({
    "price": [295000], "area_sqm": [71.0], "rooms": [2.5],
    "year_built": [1999], "neighborhood": ["Hillcrest"],
    "condition": ["good"], "listing_date": ["2024-12-09"],
})
try:
    row.to_sql("listings", con, if_exists="append", index=False)
except pd.errors.DatabaseError as err:
    print(err.__cause__)
print(con.execute("SELECT COUNT(*) FROM listings").fetchone())
con.close()

cannot store REAL value in INTEGER column listings.rooms
(209,)

Theory Assessment

Q1: Three Ways a Read Succeeds and Is Wrong

Name what went wrong and the option that fixes it: a price column that loads as str with unknown in one row, a file that loads with shape (2, 1), and a reference 00417 that loads as 417. Then explain why dtype={"price": "float64"} is worth passing even on a file you expect to be clean.

Hint: Two of the three are about what a value means, and one is about where the fields end. For the last part, ask when you would rather find out about unknown, at the read or three steps later.

Q2: The Column Nobody Wrote

A teammate's cleaned file reads back with a first column called Unnamed: 0 holding 6, 8, 11 and so on. Explain where it came from, why those numbers in particular, and what to change so it never appears. Then say which of the two to_json layouts this lesson showed writes no row labels at all.

Hint: Ask what to_csv writes besides the columns, and what labels a filtered table carries.

Q3: The Same Column, Two Dtypes

SELECT price FROM listings and SELECT price FROM listings WHERE price IS NOT NULL read the same INTEGER column. Say what dtype price arrives with in each case and why. Then say what the database returned with ROUND(AVG(price)) per neighborhood, and why that average is over fewer listings than COUNT(*) reports.

Hint: Ask what a NULL becomes in pandas, and what dtype read_csv gave a column of whole numbers with a gap. For the second part, Lesson 2.1.3 said what AVG does with NULL.

Q4: A Pipeline That Never Complained

listings.db holds the 209 listings in the STRICT table build_listings_db.py created. Read this script without running it, then say what each print shows.

import sqlite3
import pandas as pd

new = pd.read_csv("new_listings.csv")
new.to_csv("staged.csv")
staged = pd.read_csv("staged.csv")

con = sqlite3.connect("listings.db")
staged.to_sql("listings", con, if_exists="replace", index=False)
schema = con.execute("SELECT sql FROM sqlite_master WHERE name = 'listings'")

print(list(staged.columns)[:2])
print(con.execute("SELECT COUNT(*) FROM listings").fetchone())
print(con.execute("SELECT typeof(price) FROM listings").fetchall())
print("STRICT" in schema.fetchone()[0])
con.close()

Nothing raised. List everything that is now wrong with listings.db, and give the three changes to the script that would have made the bad price fail loudly and kept the table intact.

Hint: Follow the price column and the row labels from the read, through the CSV, into the database. Then ask what replace does to a table that existed before the call.

['Unnamed: 0', 'price']
(3,)
[('text',), ('text',), ('text',)]
False

Assignment

Assignment: A Neighborhood Report That Never Holds the Whole Table

Treat listings as a table too large to load at once, and build the per-neighborhood count of listings and of priced listings from it anyway, then store the report in a STRICT table of its own.

Two more options make that possible. pd.read_sql(query, con, chunksize=50) does not return a DataFrame: it returns an iterator, something a for loop walks, that yields the result 50 rows at a time, each piece a DataFrame of its own. pd.read_sql(query, con, index_col="neighborhood") makes a column of the result into the row labels, as index_col does in read_csv.

Run build_listings_db.py first. Then write report.py that walks SELECT neighborhood, price FROM listings in chunks of 50, and for each chunk counts the listings per neighborhood and the listings with a price per neighborhood, adding both into running totals so that a neighborhood missing from one chunk is not lost. Check the totals against a single GROUP BY query read with index_col="neighborhood": prove that every neighborhood has a number on both sides, and print the largest difference, which must be 0. Then create neighborhood_counts with SQL, neighborhood TEXT PRIMARY KEY, listings INTEGER NOT NULL, priced INTEGER NOT NULL, STRICT, dropping it first so the script runs again cleanly, append the report to it, and read both the schema and the table back. The table must still be STRICT after the write.

Hints:

• Start each running total as None. On every chunk, take the chunk's counts if the total is still None, and add them with .add(..., fill_value=0) otherwise, Lesson 2.4.1's stand-in for a label missing on one side.
• The report's row labels are the neighborhoods, so this is the one to_sql call in the lesson where writing the index is right: index=True writes the labels as a column named after the index, and the index here is named neighborhood.

import sqlite3
import pandas as pd

con = sqlite3.connect("listings.db")
query = "SELECT neighborhood, price FROM listings"

listings = None
priced = None
for chunk in pd.read_sql(query, con, chunksize=50):
    counts = chunk["neighborhood"].value_counts()
    with_price = chunk.loc[chunk["price"].notna(), "neighborhood"]
    priced_counts = with_price.value_counts()
    if listings is None:
        listings, priced = counts, priced_counts
    else:
        listings = listings.add(counts, fill_value=0)
        priced = priced.add(priced_counts, fill_value=0)

report = pd.DataFrame({"listings": listings, "priced": priced})
print(report)

              listings  priced
neighborhood                  
Downtown          20.0    18.0
Eastgate          33.0    30.0
Hillcrest         33.0    29.0
Lakeview          30.0    28.0
Northend          28.0    26.0
Riverside         28.0    22.0
Southgate         37.0    36.0

check = pd.read_sql(
    """
    SELECT neighborhood, COUNT(*) AS listings, COUNT(price) AS priced
    FROM listings
    GROUP BY neighborhood
    """,
    con,
    index_col="neighborhood",
)
difference = (check - report).abs()
print(difference.notna().all().all())  # True
print(difference.max().max())          # 0.0

con.execute("DROP TABLE IF EXISTS neighborhood_counts")
con.execute("""
    CREATE TABLE neighborhood_counts (
        neighborhood TEXT PRIMARY KEY,
        listings INTEGER NOT NULL,
        priced INTEGER NOT NULL
    ) STRICT
""")
report.to_sql("neighborhood_counts", con, if_exists="append", index=True)

schema = "SELECT sql FROM sqlite_master WHERE name = 'neighborhood_counts'"
print("STRICT" in con.execute(schema).fetchone()[0])
print(pd.read_sql("SELECT * FROM neighborhood_counts", con))
con.close()

True
  neighborhood  listings  priced
0     Downtown        20      18
1     Eastgate        33      30
2    Hillcrest        33      29
3     Lakeview        30      28
4     Northend        28      26
5    Riverside        28      22
6    Southgate        37      36

Additional Resources

Optional, and nothing later needs either. Worth opening if you want the rules behind this lesson's database half.

• Official docs - SQLite, "Datatypes In SQLite", section "Type Affinity". What a table without STRICT does with a value of the wrong type: the declared type is only a preference, and a value that cannot be converted is stored as it came.
• Official docs - pandas 3.0 API reference, pandas.read_sql. Every parameter of the query reader, chunksize, index_col and params among them, and the one that parses dates on the way in.

Summary

Reading a file looks like the least interesting step of a project and is where the most damage happens quietly. Every default is a guess about the file, the separator, the markers, the types, and a wrong guess rarely raises. It returns a table that looks fine in print and fails three steps later, or never, which is worse. The options are how you replace guesses with statements, and dtype is the strongest of them, because a type wide enough for its column turns a surprise in the data into an error at the line that read it.

Writing is the same contract seen from the other side. Whatever a writer adds, the next reader returns as data, which is how a column of old row numbers ends up as a feature. The formats themselves matter less than they seem: three files held the same table, and after the read the code was the same for all of them.

The database half of the lesson is about where work belongs. A query that filters and groups hands pandas a result rather than a table, and a STRICT table refuses a bad value at the door rather than letting it reach a model. Both only work if you let them: params= instead of pasted values, append into a table you defined instead of replace, and a count checked against the database instead of trusted. The rest of the module cleans tables that arrive through these readers, and its capstone writes its results back through these writers.

Cheat Sheet

Part A - Reference Table

Part B - Compact Code Block

import sqlite3
import pandas as pd

new = pd.read_csv(
    "new_listings.csv",
    sep=",",                           # the default, stated
    na_values=["unknown"],             # extra missing markers
    dtype={"price": "float64"},        # fail loudly at the read
)
listings = pd.read_json("housing.json")
sheet = pd.read_excel("housing.xlsx", sheet_name="Sheet1")
new.to_csv("staged.csv", index=False)  # no Unnamed: 0

con = sqlite3.connect("listings.db")
q = "SELECT price, rooms FROM listings WHERE neighborhood = ?"
riverside = pd.read_sql(q, con, params=("Riverside",))
new.to_sql("listings", con, if_exists="append", index=False)
# never on a table you created: if_exists="replace"
con.close()