”;
Using the SQLite CREATE TABLE statement you can create a table in a database.
Syntax
Following is the syntax to create a table in SQLite database −
CREATE TABLE database_name.table_name( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype, ..... columnN datatype );
Example
Following SQLite query/statement creates a table with name CRICKETERS in SQLite database −
sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite>
Let us create one more table OdiStats describing the One-day cricket statistics of each player in CRICKETERS table.
sqlite> CREATE TABLE ODIStats ( First_Name VARCHAR(255), Matches INT, Runs INT, AVG FLOAT, Centuries INT, HalfCenturies INT ); sqlite>
You can get the list of tables in a database in SQLite database using the .tables command. After creating a table, if you can verify the list of tables you can observe the newly created table in it as −
sqlite> . tables CRICKETERS ODIStats sqlite>
Creating a Table Using Python
The Cursor object contains all the methods to execute quires and fetch data etc. The cursor method of the connection class returns a cursor object.
Therefore, to create a table in SQLite database using python −
-
Establish connection with a database using the connect() method.
-
Create a cursor object by invoking the cursor() method on the above created connection object.
-
Now execute the CREATE TABLE statement using the execute() method of the Cursor class.
Example
Following Python program creates a table named Employee in SQLite3 −
import sqlite3 #Connecting to sqlite conn = sqlite3.connect(''example.db'') #Creating a cursor object using the cursor() method cursor = conn.cursor() #Doping EMPLOYEE table if already exists. cursor.execute("DROP TABLE IF EXISTS EMPLOYEE") #Creating table as per requirement sql =''''''CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )'''''' cursor.execute(sql) print("Table created successfully........") #Commit your changes in the database conn.commit() #Closing the connection conn.close()
Output
Table created successfully........
”;