PYTHON MADE SIMPLE (2015)

Chapter 12: Managing Databases

The use of databases back to before the 1970’s. SQL is one of the few databases that have a wide area of application and more especially with respect to python programming. SQL has a well-structured language with query which is based on creating relationships as a model for sharing in large data banks.

In fact, SQL is often pronounced as sequel when read directly in English. Currently, it is now one of the best standards in the American national standards Institutes (ANSI) after 1987. This is because most of the people prefer using mSQL, postgress SQL, MySQL and other forms of SQL.

What is a database?

A database is defined as a collection of organized data. The organization of the data allows processing of the data either by way of deleting files, rearranging, adding new information, replacing content and many other functions.

A database is also the data itself that has been stored for databank for special reasons. It can also be associated with the database management system. The database management system is the software that can be used in the processing of the data by the user.

The user of the database may not be required to be human but programs and applications too.

Python as introduced at the beginning of this book was expressed as an interactive language. Python has the ability to interact with a database such as SQL. It therefore has the potential of interacting as a user for the database.

It is possible to use SQLite and other SQL forms from Python as a program. It is a standard that the database interface for Python is DB-API. This is used by Python interfaces when interacting with Python. While it is a common interface, it can also be used in relational databases using codes developed in Python for communication with databases. The language used is the same and regardless of the type of the database that will be used and the module that has to be applied.

Already, I have addressed a few examples of databases; SQL and MySQL. Here, we shall look at each one of them individually.

SQLite

This operates a simple relational database system. It has the ability to save the data in the simple database systems in the simple regular data files in simple locations of a computer such as the computer memory in the RAM. It is very compartible with simple applications such as Mozillar-Firefox, Symbian OS, android and many other OS programs.

It is also very fast since it uses simple files and has the capability to operate for large databases.

The operation of the SQLite relies on importing of the module sqLite3. Then, a connection is required with the object. The connection object will represent the database. The argument of the connection is the name of the database such as “companys.db”. It functions both as a name of the file, where the data will be stored and as the name of the database itself. Presence of the file name will have to be established and if the name exists, it will be opened.

Note: the database has to be an SQLite, if the name exists, then it has to be opened.

Let us create a database using the following code. This will be able to open a database company. The file in this case may not need to exist in this case.

Using this code, you will be able to create a database that will have the name ‘Company”. This is a command that initiate and creates a database company in an SQL server.

When you try to use the command:

"sqlite3.connect ('company.db')" again

This will open a previous database with a similar name that had been created.

What you developed is an empty database and what has to be done next is to create tables in the format needed so as to be able to insert and retrieve information in the way that is needed.

An SQL code for developing table "staff" in the database "company" looks like this:

This is how it can be done in an SQL in the command shell. This can be done in Python directly. A command can also be sent to an SQL or SQLite. This is only possible with the use of the SQL cursor. The relevance of a cursor is to enable one to access, read, write and use information in a database. It can also be used I fetching for information from a database with a precise output.

In most of the Python programs, it is used for performing most of the commands.

This can be done by calling the cursor method as a way of establishing the connections. Any number of cursors can be created. They can also be used to go over the records and information which are the results from a database.

Therefore, a complete Python code for developing a power company would look like this: Remember the staff table will also be created in this database:

In the syntax above, it is clear that the AUTOINCREMENT part has been removed. Note that in SQL, The “INTEGER PRIMARY KEY” also automatically auto increase the values in the field required in the table in SQLite3.

This can also be expressed in the sense that when a column of a table is declared to be an INTEGER PRIMARY KEY. Every time whenever a NULL is used as an input for the column, The NULL value will be automatically get converted to an integer with a value larger/higher than the previous value by one in the column. When the table is empty, it will automatically give a value one for the existing number. For instance, when a largest existing value for a column is 9234576898344, any new value that will be added will be one value higher than it, hence the value in SQLite will be an unused value selected randomly.

To this far, we have been able to create a database and a table inside it with several columns. But currently, no data has been created on it.

To create the data on the table, we will have to be able to populate the table using a simple Python command “INSERT” command to SQLite. Similarly, we will need to execute the code as a method of populating the table.

Use the below working example to know how making a database can be easy and interesting using SQLite and Python as the best interactive language.

Try the program below:

To run the program you will either have to remove the file company.db or uncomment the "DROP TABLE" line in the SQL command:

To be able to insert the data on a database in python, you will not be able to literary insert the data into a table. But will be required to have a many data in the forms of data types discussed in the beginning of this book. It can be in the form of dictionaries or a list which can be used as the input of the insert statement.

For Example, using a list of persons, it is possible to insert the names in an existing database company.db and a table staff. This can be achieved using the INSERT statement in Python.

It is possible query the staff table in Python. Follow the following code to know how this can be achieved very easily in Python. This enables you to be able to retrieve any information from the database and as well confirm and verify the information stored in the database.

First, save this program as “query_SQL-company.py” and then run this program. This will give you the following products we get the following result, depending on the actual data:

MySQL

In order to use MySQLdb, it the module has to be installed on a computer on which you will be working on. However, on other programs such as Ubuntu and Debian, it is very easy.

Just type the following code in Python and wait for it to be installed:

Note that besides the import and the connects functions method and everything else as applied in SQLite. The working of MySQLdb operates like this:

· First, you have to import MySQLdb modul in Python

· To create a connection, open a connection to the SQL server

· Use both the sending and receiving commands

· Always close the connection in SQL

The import and connection functions in MySQLdb will look like the codes shown:

Practice Exercise

Create a database using SQLite3 and name it “students.db”. In the database, create a table in the database called std8_students and provide the insert and commit functions. Write a code that will be able to retrieve information from the same database and provide a list of the names of students, gender, age and code for the students.

Conclusion

I most sincerely thank you for downloading this book!

It is my belief that this book was able to help you learn how to interact with python and teach you how to use numbers and texts in various ways in Python.

The next step is for you to ensure that you practice how to use this book and information given to you on a daily basis to perfect on how to use numbers and texts in python. Note that python itself is very interactive will require very close attention.

Finally, if you believe that this book has helped you learn a Python. Please take time to share your thoughts on how this can be improved to make the book even better for others

Also, I encourage you to share your reviews through Amazon. This will be greatly appreciated!

Thank you again for reading this book.

I hope that you enjoy interacting with Python.