Page 198 - Python for Everybody

186 CHAPTER 15. USING DATABASES AND SQL
Table
column attribute
row
tuple
2.3
Relation
2.3
Figure 15.1: Relational Databases
15.3 Database Browser for SQLite
While this chapter will focus on using Python to work with data in SQLite database files, many operations can be done more conveniently using software called the Database Browser for SQLite which is freely available from:
http://sqlitebrowser.org/
Using the browser you can easily create tables, insert data, edit data, or run simple SQL queries on the data in the database.
In a sense, the database browser is similar to a text editor when working with text files. When you want to do one or very few operations on a text file, you can just open it in a text editor and make the changes you want. When you have many changes that you need to do to a text file, often you will write a simple Python program. You will find the same pattern when working with databases. You will do simple operations in the database manager and more complex operations will be most conveniently done in Python.
15.4 Creating a database table
1
When we create a database table we must tell the database in advance the names of each of the columns in the table and the type of data which we are planning to store in each column. When the database software knows the type of data in each column, it can choose the most efficient way to store and look up the data based on the type of data.
You can look at the various data types supported by SQLite at the following url:
http://www.sqlite.org/datatypes.html
Defining structure for your data up front may seem inconvenient at the beginning, but the payoff is fast access to your data even when the database contains a large amount of data.
1SQLite actually does allow some flexibility in the type of data stored in a column, but we will keep our data types strict in this chapter so the concepts apply equally to other database systems such as MySQL.
Databases require more defined structure than Python lists or dictionaries .