How do i create a sqlite table in python?

View Discussion

Improve Article

Save Article

  • Read
  • Discuss
  • View Discussion

    Improve Article

    Save Article

    In this article, we will discuss how can we create tables in the SQLite database from the Python program using the sqlite3 module. 

    In SQLite database we use the following syntax to create a table:

    CREATE TABLE database_name.table_name(

                                           column1 datatype PRIMARY KEY(one or more columns),

                                           column2 datatype,

                                           column3 datatype,

                                           …..

                                           columnN datatype

    );

    Now we will create a table using Python:

    Approach:

    Import the required module

    • Establish the connection or create a connection object with the database using the connect() function of the sqlite3 module.
    • Create a Cursor object by calling the cursor() method of the Connection object.
    • Form table using the CREATE TABLE statement with the execute() method of the Cursor class.

    Implementation:

    Python3

    import sqlite3

    connection_obj = sqlite3.connect('geek.db')

    cursor_obj = connection_obj.cursor()

    cursor_obj.execute("DROP TABLE IF EXISTS GEEK")

    table =

    cursor_obj.execute(table)

    print("Table is Ready")

    connection_obj.close()

    Output:

    How do i create a sqlite table in python?


    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........
    

    How do you create a new table in Python?

    The easiest way to create tables in Python is to use tablulate() function from the tabulate library..
    To use this function, we must first install the library using pip: pip install tabulate..
    We can then load the library: from tabulate import tabulate..

    How do I create a database in sqlite3 Python?

    Steps to Create a Database in Python using sqlite3.
    Step 1: Create the Database and Tables. In this step, you'll see how to create: ... .
    Step 2: Insert values into the tables. For this step, let's insert the following data into the 'products' table: ... .
    Step 3: Display the results..

    How do I create a database and table in SQLite?

    SQLite Create Table.
    First, specify the name of the table that you want to create after the CREATE TABLE keywords. ... .
    Second, use IF NOT EXISTS option to create a new table if it does not exist. ... .
    Third, optionally specify the schema_name to which the new table belongs. ... .
    Fourth, specify the column list of the table..

    How do I create a SQLite database from a csv file in Python?

    Pandas makes it easy to load this CSV data into a sqlite table:.
    import pandas as pd..
    # load the data into a Pandas DataFrame..
    users = pd. read_csv('users.csv').
    # write the data to a sqlite table..
    users. to_sql('users', conn, if_exists='append', index = False).