Using Databases

When you develop a web application, you usually desire to store data in a persistent way. Often, web applications are data-centric, built around data. Sessions, cookies and files are good ways to store temporary data or information and medias you don’t need to manipulate frequently or heavily: when your goal is to manage dynamic data, the best way to use and manipulate them is dealing with a database.

PyHP offers a simple and intuitive connection interface to MySQL, one of the most known and used database management system (DBMS) in open source environments. For further information about MySQL, please refer to the MySQL developer manual available from http://www.mysql.org/. For more information about the SQL data manipulation language, please refer to a specific guide, available in every computer science library.

Connecting to the DBMS

Dealing with a DBMS consists in different phases: connection, SQL instructions execution and finally connection termination. So, the first operation to complete is establishing a connection with the database management system, specifying the DBMS type, the DBMS server address, the database name (as DBMSs can manipulate multiple databases contemporarily), the user name with at least access privileges and the related password. To make everything simple without needing to learn all specific DBMS calls, PyHP provides a transparent interface, using the pyhp.db module. This module exposes the connect() method to establish a connection with the DBMS server. Let’s give an example:

connection_handler = pyhp.db.connect('dbms_type:host=my_dbms_address;dbname=my_db_name', 'my_user_name', 'my_password')

where ‘’dbms_type’’ is the DBMS server (MySQL), ‘’my_dbms_address’’ is the DBMS server address, ‘’my_db_name’’ is the database created on the DBMS (it assumes it has been created yet: this could be done by the DBMS administrator), ‘’my_user_name’’ and ‘’my_password’’ are the access credentials for the database, given by the DB administrator. A real example for a database called “TravelAgency” stored on a MySQL server running on the local machine, could be:

connection = pyhp.db.connect('mysql:host=localhost;dbname=travelagency', 'dave', 'exeter')

The connect() returns a connection handler, used for the following operations.

Executing SQL statements and queries

Once connected to the database, you will desire to execute SQL statements, in order to define data schemas, populate the database and enquiry it for data retrieval. The PyHP pyhp.db module offers two methods for this purpose: the execute() method and the query() method.

When you want to apply data definition and insertions to your database, such as CREATE, ALTER, DROP, INSERT, UPDATE and DELETE directives, you should use the pyhp.db.execute() method. Its syntax is:

exec_status = pyhp.db.execute(connection_handler, 'SQL statement')

This is an example dealing with the previous ones:

status = pyhp.db.execute(connection, 'CREATE TABLE agencies (id INT, name TEXT, address TEXT, city TEXT')

The method returns an execution status, used to check if the statement was correctly executed or not. For each SQL statement, it returns:

• CREATE: returns 0 if the specified table was successfully created, None otherwise (e.g. if it already exists)
• ALTER: as for CREATE
• DROP: as for CREATE
• INSERT: returns 1 if insertion was effectively done, other values otherwise
• UPDATE: returns the number of updated rows
• DELETE: returns the number of deleted rows

Other than defining data structures and modifying data content, most of the times you will need to retrieve data stored on your database. To do that with a DBMS, just make a ‘’query’’ with a SQL SELECT statement. PyHP provides a separated specific method to do that, in order to support returning a data structure. The method is intuitively called query() and this is its syntax:

query_resultset = pyhp.db.query(connection_handler, 'SQL SELECT statement')

A mighty example:

my_result = pyhp.db.query(connection, "SELECT name, address FROM agencies WHERE city = 'Turin' ")

Note

since the SQL statements are passed as strings and might contain apexes and inverted commas, be sure to manage them correctly as you would do in standard Python code.

The result of the query is given as a data structure passed by reference through the ‘’query_resultset’’ returned value. This data structure is organized in ‘’rows’‘, each represented ad a Python dictionary and fetched using the PyHP fetch() method. In order to process every selected row by your SELECT statement, you might easily cycle the resultset ‘’while’’ there is some row to be fetched:

# retrieving a row
row = my_result.fetch()

# cycling until there's something to read
while row:
   # use the retrieved data
   print row
   # move to the next row
   row = my_result.fetch()

Closing the connection

When you don’t need accessing your database anymore (or for example when ending your script), you can easily close the database connection using the close() method, specifying the handler of the connection to be closed:

pyhp.db.close(connection_handler)

Really simple, nothing more than this.