Complete Guide For Python Programming (2015)
PYTHON DATABASES
In python when you use web applications or customer-oriented programs, Databases are very important. Normal files, such as text files, are easy to create and use; Python has the tools built-in and it doesn’t take much to work with files. Databases are used when you work on discrete “structures”, such as customer list that has phone numbers, addresses, past orders, etc. database is used to store a lump of data and it allows the user or developer to pull the necessary information, without regard to how the data is stored. Also, databases can be used to retrieve data randomly, rather than sequentially.
How to Use a Database
A database is a collection of data, which is placed into an arbitrary structured format. Most commonly used database is a relational database. In database tables are used to store the data and relationships can be defined between different tables. SQL (Structured Query Language) is the language which is used to work with most Databases. SQL provides the commands to query a database and retrieve or manipulate the information. SQL is also used to input information into a database.
Working With A Database
Database consists of one or more tables, just like a spreadsheet. The vertical columns comprise of different fields or categories; they are analogous to the fields you fill out in a form. The horizontal rows are individual records; each row is one complete record entry. Here is an example representing a customer's list.
USING SQL TO QUERY A DATABASE
Here, Index field is the one that provides a unique value to every record; it’s often called the primary key field. The primary key is a special object for databases; simply identifying which field is the primary key will automatically increment that field as new entries are made, thereby ensuring a unique data object for easy identification. The other fields are simply created based on the information that you want to include in the database.
Now if you want to make order entry database, and want to link that to the above customer list, so it should be like:
This table is called “Orders_table”. This table shows various orders made by each person in the customer table. Each entry has a unique key and is related to Customers_table by the Customer_ID field, which is the Index value for each customer.
Python and SQLite
Python has a SQLite, a light-weight SQL library. SQLite is basically written in C, so it is very quick and easy to understand. It creates the database in a single file, so implementing a database becomes fairly simple; you don’t need to worry about the issues of having a database spread across a server. SQLite is good for prototyping your application before you throw in a full-blown database. By this you can easily know how your program works and any problems are most likely with the database implementation. It’s also good for small programs that don’t need a complete database package with its associated overhead.
Creating an SQLite database
SQLite is built into Python, so it can easily be imported to any other library. Once you import it, you have to make a connection to it; so as to create the database file. Cursor in SQLite performs most of the functions; you will be doing with the database.
import sqlite3 #SQLite v3 is the version currently included with Python
connection = sqlite3.connect ("Hand_tools.database") #The . database extension is optional
cursor = connection.cursor ()
#Alternative database created only in memory
#mem_conn = sqlite3.connect (":memory:")
#cursor = mem_conn.cursor ()
cursor.execute ("""CREATE TABLE Tools (id INTEGER PRIMARY KEY, name TEXT, size TEXT, price INTEGER)""")
for item in ((None,"Book","Small",15), #The end comma is required to separate tuple items (None,"Purse","Medium",35),(None,"Pen","Large",55),(None," Hat","Small",25),(None,"Handbag","Small",25),(None,"Socks","Small",10),(None,"Comb","Large",60),):cursor.execute ("INSERT INTO Tools VALUES (?,?,?,?)",item)
connection.commit() #Write data to database
cursor.close() #Close database
In this example question marks (?) are used to insert items into the table. They are used to prevent a SQL injection attack, where a SQL command is passed to the database as a legitimate value. The question marks act as a substitution value.
Retrieving data from SQLite
To retrieve the data from a SQLite database, you just use the SQL commands that tell the database what information you want and how you want it formatted.
Example:
cursor.execute ("SELECT name, size, price FROM Tools")
tools Tuple = cursor.fetchall ()
for tuple in tools Tuple:name, size, price = tuple #unpack the tuples
item = ("%s, %s, %d" % (name, size, price))
print item
Output:
Book, Small, 15
Purse, Medium, 35
Pen, Large, 55
Hat, Small, 25
Handbag, Small, 25
Socks, Small, 10
Comb, Large, 60
Book, Small, 15
Purse, Medium, 35
Pen, Large, 55
Hat, Small, 25
Handbag, Small, 25
Socks, Small, 10
Comb, Large, 60
Dealing with existing databases
SQLite will try to recreate the database file every time you run the program. If the database file already exists, you will get an “OperationalError” exception stating that the file already exists. The easiest way to deal with this is to simply catch the exception and ignore it.
cursor.execute ("CREATE TABLE Foo (id INTEGER PRIMARY KEY, name TEXT)")
except sqlite3.Operational Error:pass
This will allow you to run your database program multiple times without having to delete the database file after every run.