Web Programming with Python: Creating a Database Table

Hi there, welcome to the next tutorial on SQLite where you will learn how to create a database and a SQLite table.

Creating a database

1. The first thing that you need to do in order to access SQLite is to import its module using the following statement.

import sqlite3

2. Next you need to specify a connection to an existing database.

conn=sqlite3.connnect(‘tutorial.db’)

Here conn is the connection with the database and all the information within it.

3. We will now define the cursor

c=conn.cursor()

Cursor in sqlite is same as what you see in and MySQL. You can use the cursor to perform whatever actions that you need to do on the database.

4. Now close the connection

conn.close()
So, this is how your code looks like:
[py]
Import sqlite3
conn=sqlite3.connnect(‘tutorial.db’)
c=conn.cursor()
conn.close()

5. Run this program to create a database by the name tutorial.db.

Creating a Table in a Database
We will now make slight changes to the code mentioned above to demonstrate how a table is created. The new statements added have been highlighted with bold text for you to get a better understanding. Here , we will show have the ‘CREATE’ command of SQL can be used in python for creating tables.

1. To create a table you will have to create a function after the cursor is defined.

Import sqlite3
conn=sqlite3.connnect(‘tutorial.db’)
c=conn.cursor()
def create_table():
conn.close()

2. In this function use the cursor to execute the SQL commands. Here, we will execute the SQL command to create a table by the name ‘example’. Coders generally use all caps for SQL specific commands and no caps for the other things. It is just a standard that is followed to keep things convenient. So, you see that “CREATE TABLE” is in caps (because they carry meaning and values in SQL) and the name of the table “example” is non caps. This standard process is followed by most coders so that it is easier to read the code.

Import sqlite3
conn=sqlite3.connnect(‘tutorial.db’)
c=conn.cursor()
def create_table():
       c.execute(“CREATE TABLE example (Language VARCHAR, Version REAL, skill TEXT”)
conn.close()

Language, Version and Skill are names of the columns and VARCHAR, REAL and TEXT define the datatype of the values that can be stored in these columns

3. Now to run the command to create table, call the function as shown below:

create_table()

4. Now run the code to create a table.

If you run the code again then you will face an “Operation Error” saying that the Table already exists in the database. You cannot create the same table again and again.

GET YOUR FREE PYTHON EBOOK!