Web Programming with Python: SQLite Intro
Welcome to the first chapter of section 3- Basic Database (SQLite) with Python. Here we are going to learn about SQLite and databases. Whenever there is a need for data collection or analysis it is important to store the data that you are collecting. The data that you collect can be stored in a text or a csv file. However, the issue with storing string data in this manner is that these files can easily get corrupted and navigation through these files can also become little difficult and this is why developers prefer to use data bases. SQL database is actually a collection of tables and most people have vision of a table when they talk of databases. Every table has columns and rows. Columns are generally of different data types and rows are data entries.
It is easier to work with data bases as they help in organizing data, making navigation simpler and also allow locking mechanism. SQLite is considered to be one of the most easiest and light-weight databases. As the name suggests SQLite is a very light version of SQL database that allows you to use some of the major SQL queries such as inserting, selecting, deleting etc. So, you can easily do all the basic stuff with it. SQLite is included in the standard library and so, you don’t have to install anything extra to access the database. For our tutorial we are using SQLite 3.The version does not actually matter because most of the code used in this module is SQL which is all together different programming language in itself and hence variation of versions is not going to make much of a difference.
A database is like a csv file having columns and rows and no other third dimension. Files are made for the purpose of viewing and using information all at once by the user. Database on the other hand is used to produce reports and yield results based on the information stored in a machine. Databases are used when lots of machines are interacting with each other and files are used when the information is to be directly accessed by a human.
Files can be used to store data but it has some serious drawbacks. Most file system fail to provide transaction support which is required mainly when several users try to access the information concurrently. Databases allow fast indexing which cannot be expected from a file. Indexing is trying to retrieve data based on the indexed attribute. This is a very strong feature of databases. Another thing is that files are stored in directories with some filename and that is it. In databases we can draw relation between entries in different table and with the help of small and very easy commands defined by structured query language (SQL) we are able to retrieve, create, delete or edit related data.
A table comprises of rows and columns. All entries are stored in rows. However, the columns need to be defined in advance. Adding or removing rows is very easy but since a table is defined and entries are made then it is difficult to add or remove columns. Therefore, it is important to plan the data types and columns for a table before creating it.
GET YOUR FREE PYTHON EBOOK!