Download our e-book of Introduction To Python
Related Blog
Discuss Microsoft Cognitive ToolkitMatplotlib - Subplot2grid() FunctionMicrosoft Cognitive Toolkit (CNTK) - CPU and GPUMatplotlib - PyLab moduleMatplotlib - Twin AxesMatplotlib - MultiplotsMatplotlib - Working with ImagesMatplotlib - Working With TextPython Forensics - Memory and ForensicsMatplotlib - Setting Ticks and Tick Labels View More
Top Discussion
How can I write Python code to change a date string from "mm/dd/yy hh: mm" format to "YYYY-MM-DD HH: mm" format? Exception Type: JSONDecodeError at /update/ Exception Value: Expecting value: line 1 column 1 (char 0) Someone guide please. How to store the name of IV in a list which show non linear behavior ?.......How to understand data is linear or non linear ? 'collections.OrderedDict' object has no attribute 'status_code' What is the difference between a module and a package in Python? How can i use pyspark to parallelize a file's payload How to leave/exit/deactivate a Python virtualenvironment Join Discussion
Top Courses
Webinars
Generative AI Journey in Data Science
Dec 6th (8:00 PM) 2018 Registered
Python SQLite - Create Table
Neha Kumawat
2 years ago
Table of Content
- Syntax
- Example
- Creating a Table Using Python
- Example
- Output
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.
Establish connection with a database using the connect() method.
- Create a cursor object by invoking the cursor() method on the above created connection object.
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.
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........
Like the Blog, then Share it with your friends and colleagues to make this AI community stronger.
To learn more about nuances of Artificial Intelligence, Python Programming, Deep Learning, Data Science and Machine Learning, visit our insideAIML blog page.
Keep Learning. Keep Growing