Creating and connecting to a database
First and foremost is to create a database for us to work with. Basically, database is a collection of tables, and there is no limit on number of tables we could have in our database.
Launch your python IDLE and create and save a new python file with name of your choice and location of your choice. Extract the script you see below from the course code you have downloaded from the first article and execute it by pressing F5
Below is the screen shot of my working code.
Above is the newly created database in my folder.
- import sqlite3: Imports the module for our use in our current python program.
- conn = sqlite3.connect("test.db") tries to establish connection with the test.db database file, if the database doesn't exist it will create new database.
- if(conn): if the above operation is successful it will return Boolean true and using that we are informing ourselves the operation is successful using print statement.
- else: We tell us that the operation is not successful using a print statement as well.
Create a table in our database
Now we are going to create a table inside our test database that is going to hold the information of students from different universities. Information will be Student name, University name, Country, Course, Age and Gender.
- conn.execute('''CREAT TABLE STUDENTINFO (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, University TEXT NOT NULL, Country TEXT NOT NULL, Course TEXT NOT NULL, Gender TEXT NOT NULL, Age INT NOT NULL);''') We are actually passing SQLite query as the argument to conn.execute()function.
- In the Query we are simply telling the SQLite to create a table with name STUDENTINFOR that has the columns you see above, we have to specify column name, datatype of the column and size of the column if needed.
- We are using try and except blocks to catch the error if any occurred and print it out.
- If you see in the second line of the code in the above picture we have imported the sqlite3 error module which will help us to get the exact error information, which will be helpful for us to debug the error if any occurred.
In the next article we will discuss about how to add data to our table, update the data as well as delete the data in the table