How to create table in sqlite database using python

0

You want to store data permanently and want to store that data in table form. Among all choices, the SQLite database is a good option for you. You can create an SQLite database with python. Python provides sqlite3 module as an interface for SQLite databases.

crete table in sqlite db

In this tutorial I will show you how you can create table in sqlite database and store data in it using the Python Programming language.


What is SQLite Database?

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language.

What are the benefits of using SQLite database in Python?

    If you are working on a small project and you think that do not need any indexing and all the heavy stuff, then SQLite database is the best option to work with. Python also provides Different Python Libraries for File Manipulation. You can also store data in a file but it is the classic way of storing data.

    Following are some of the upsides of using the SQLite database.
    • It is a lightweight database
    • It does not require any separate server
    • It can be used for internal data storage
    • It is can be used as a prototype

    Create SQLite database in Python

    Before creating the table and querying it. We need to first create our database and then create a connection to that database using the python sqlite3 module.

    To create a sqlite database :
    • import sqlite3 module
    • use the sqlite3.connect('dbname') function
    • It will create a database file with the name specified as a parameter
    Use the following Python code to create an SQLite database
    import sqlite3
    # it will create a databse with name sqlite.db
    connection= sqlite3.connect('sqlite.db')   
     

    A file with the name 'sqlite.db' is created after running the above code


    Create a Table in SQLite database using python

    Once we create the connection. We need a cursor that will help us to execute SQL commands using python. We can create a cursor using the connection.cursor() function. The cursor() function is called on the connection object.

    Use the following steps to create a table in SQLite database using Python

    • import the sqlite3 module using import sqlite3
    • call the connection=sqlite3.connect(dbname) method
    • create the cursor using cursor =connection.cursor() method
    • execute the SQL query to create the table using cursor.execute(query)
    • commit the changes using commit() method
    • close the SQLite database connection using the close() function
    Python code to create a table in SQLite database
    import sqlite3
    
    # it will create a databse with name sqlite.db
    connection= sqlite3.connect('sqlite.db')
    
    cursor = connection.cursor()
    
    # table name = Website 
    # Table fields are 
    # Post: Text type
    # Autor: Text type
    # Views: Real type
    
    cursor.execute('''CREATE TABLE Website
                   (Post text, Autor text, Views real)''')
    
    # Save (commit) the changes
    connection.commit()
    
    # close connection
        
       
     

    Insert Data into SQLite Database using Python

    Once the database table is created you can insert data into a table using the insert command in SQL. Create the insert command and then execute it using the execute() function.

    Inserting data into the SQLite database using Python
    
    import sqlite3
    connection= sqlite3.connect('sqlite.db')
    cursor = connection.cursor()
    cursor.execute('''CREATE TABLE if not Exists Website
                   (Post text, Autor text, Views real)''')
    
    post_text = 'this is a raw  post'
    post_author = 'alixapordev'
    post_views = 6900
    
    INSERT_QUERY = f"INSERT INTO Website VALUES ('{post_text}','{post_author}','{post_views}')"
    cursor.execute(INSERT_QUERY)
    # Save (commit) the changes
    connection.commit()
    # close connection
    connection.close() 
       
     

    The above python program will insert data into the 'Website' Table prsernt in the database.


    Check the inserted data in the database table using Python

    The SQL 'SELECT' statement can help us find the inserted data in the SQLite database. The following code will return the data present in the 'Website' Table in the SQLite database.

    import sqlite3
    connection= sqlite3.connect('sqlite.db')
    cursor = connection.cursor()
    cursor.execute('''CREATE TABLE if not Exists Website
                   (Post text, Autor text, Views real)''')
    
    post_text = 'this is a raw  post'
    post_author = 'alixapordev'
    post_views = 6900
    
    INSERT_QUERY = f"INSERT INTO Website VALUES ('{post_text}','{post_author}','{post_views}')"
    cursor.execute(INSERT_QUERY)
    
    # check the data in website using select statement
    for row in cursor.execute('SELECT * FROM Website'):
        print(row)
        
    # Save (commit) the changes
    connection.commit()
    # close connection
    connection.close()
      
     

Post a Comment

0Comments
* Please Don't Spam Here. All the Comments are Reviewed by Admin.
Post a Comment (0)

#buttons=(Accept !) #days=(20)

Our website uses cookies to enhance your experience. Learn More
Accept !