Working with databases

We mentioned that pandas is useful for tabular or structured data. Many organizations use databases to store tabular data. In this recipe, we will work with databases to insert and read data.

Note that this example uses the SQLite database, which is included with Python. However, Python has the ability to connect with most SQL databases and pandas, in turn, can leverage that.

How to do it...

1. Create a SQLite database to store the Beatles information:

   >>> import sqlite3
   >>> con = sqlite3.connect("data/beat.db")
   >>> with con:
   ...     cur = con.cursor()
   ...     cur.execute("""DROP TABLE Band""")
   ...     cur.execute(
   ...         """CREATE TABLE Band(id INTEGER PRIMARY KEY,
   ...         fname TEXT, lname TEXT, birthyear INT)"""
   ...     )
   ...     cur.execute(
   ...         """INSERT INTO Band VALUES(
   ...         0, &apos...