SQLite & XML

You build an INSERT query by interpolating user input directly: ``` name = user_input sql = ("INSERT INTO suppliers " "VALUES ('%s')" % name) cur.execute(sql) ``` Why is this dangerous, and what's the fix?

You INSERT a row, then your program exits cleanly. Next run, the row is gone. ``` con = sqlite3.connect("w.db") cur = con.cursor() cur.execute("INSERT INTO t VALUES (1)") con.close() ``` What did you forget?

What's the difference between passing ":memory:" and a filename to sqlite3.connect()?

What does cursor.fetchall() return when a SELECT matches zero rows?

What does cursor.fetchone() return after the last row has already been read?

When should you reach for fetchmany/fetchone/iterating the cursor instead of fetchall()?

You want to insert many rows. Is this the best tool? ``` for row in rows: cur.execute( "INSERT INTO t VALUES (?,?)", row) ```

Which Python value maps to SQLite NULL, and which Python type maps to a SQLite BLOB?

What does `ElementTree.parse(file)` return, and how do you get to the top tag? ``` import xml.etree.ElementTree as ET tree = ET.parse("dict.xml") root = tree.??? ```

You have an element e for <A> containing <B><D/></B><C><E/></C>. What does e.find("E") return?

What's the difference between .find(), .findall(), and .iter() on an Element?

Given <key type="str">Hello</key> as element e, what are e.tag, e.attrib, and e.text?

You called e.get("missing") on an element that has no such attribute. What happens?

How do you build an XML tree element-by-element and write it to a file with ElementTree?

Your service parses XML uploaded by users with ET.parse(uploaded_file). What's the security risk?

How does SAX (xml.sax) parsing fundamentally differ from ElementTree, and what's the cost?

Writing a SAX ContentHandler, you store text from the characters(content) callback. Why is self.value = content a bug?

How do you read and write a gzip-compressed file in Python?

Does using the connection as a context manager close it? ``` with con: cur = con.cursor() cur.execute("INSERT INTO t VALUES (1)") # ... use con again here? ```

An exception is raised inside with con:. What happens to the changes, and does the exception propagate?

A SELECT returns rows as plain tuples, so you index by position. What's a less brittle way to read columns by name?

Every TEXT column comes back as a str. How do you make sqlite3 hand you transformed text (say, uppercased) on every read?

You want SELECT rows returned as dicts keyed by column name instead of tuples. You set con.row_factory to a function — where does that function get the column names?

You want to store custom Circle objects in a TEXT column and get Circle instances back automatically on SELECT. What registrations and one connection setting make the round-trip work?

You build an XML tree and call ElementTree.tostring(root), then try "<?xml?>" + ElementTree.tostring(root). Why does it raise TypeError?

Parsing <p>Hi <b>bold</b> friend</p>, you grab the <b> element e. What are e.text and e.tail?

Given an Element named entry with child tags, what do for e in entry: and entry[0] do?

You need the text inside the first matching <key> child, not the element object. Which ElementTree search method skips the extra .text step?

You already parsed a tree and now want to add and drop child elements at runtime. Which Element methods do that?

You subclassed ContentHandler for SAX parsing. What three calls actually run the parse over a file?

Why can code written against Python's sqlite3 be ported to a PostgreSQL or MySQL driver with only minor changes?

SQLite needs no server and keeps the whole database in one file. What concurrency price do you pay for that simplicity?

Besides ? placeholders, sqlite3 supports named parameters. What's the syntax and what do you pass as the second argument to execute?