Python Language Tutorial: Database Handling - Part Eight

Today, I will take on a new part of Python for my tutorial series. In this part you will learn about Database Handling in Python.

I already told you about the following:

Python language support many database servers as:

  • MySQL
  • PostgreSQL
  • Microsoft SQL Server
  • Oracle
  • SQLite

Note

We can work with any database without changing code because mostly the Database works with same method for handling databases that gives flexibly to change database any time without any code change.

Python Also Support various databases .you can see support database to python.

  1. Now today I will tell database handling with SQLite database.

  2. Sqlite database is light weighted and it can easily embed with embedded applications.

  3. Now we are going to implement database using python.

  4. SQlite library are already in-built with python.

  5. Now open Command Prompt and open python terminal for handling database operations.

  6. Now we create database using below code when we write in python terminal.

    import sqlite3
    conn = sqlite3.connect('article.db')
    c=conn.cursor()
    conn.close

    The above code create database whose name is article.db in that directory in which python terminal opens.

    db

    Cursor() method is used for do any operations on database.

  7. Now we create table in article.db database as below code:

    import sqlite3
    conn = sqlite3.connect('article.db')
    c=conn.cursor()
    c.execute("create table example(Software VARCHAR, Version Real,Price Real )")

    In above code we execute create command so you can now understand use of cursor in python.We create example table with three attributes.

    code

  8. Now we inserting the data into example table as below.

    c.execute("Insert into example Values('Python',3.4,'100')")
    c.execute("Insert into example Values('Adobe',10.2,'1000')")
    c.execute("Insert into example Values('Office',16,'1000')")

    code

    In last we commit the connection to make proper connect between pyhon to database.

  9. Now we read the data from databse using code:

    sql = "select * from example"
    for row in c.execute(sql):
    print("Software: "+row[0])
    print("Version: "+str(row[1])
    print("Price: "+str(row[2]))

    Output:Software: Python

    Version: 3.4
    Price: 100.0
    Software: Adobe
    Version: 10.2
    Price: 1000.0
    Software: Office
    Version: 16.0
    Price: 1000.0

    code

  10. Now we Insert data dynamically in database using make method in python:

    def dynamic_data():
    soft = input("Write Software Name : ")
    version = input("Write Versio : ")
    Price= input("Write Price")
    c.execute("insert into example(Software,Version,Price) values(?,?,?)" ,(soft,version,Price))
    conn.commit()
    dynamic_data()

    Output

    • Write Software Name : Google Chrome
    • Write Versio : 46.343
    • Write Price100

    code

  11. Now we take another database handling with update the data in database,

    sql="update example set Version = 3.5 where Software = 'Python'"
    c.execute(sql)
    sql = "select * from example"
    for row in c.execute(sql):
    print(row)

    Output

    ('Python', 3.5, 100.0)
    ('Adobe', 10.2, 1000.0)
    ('Office', 16.0, 1000.0)
    ('Google Chrome', 46.343, 100.0)

    code

  12. Another Example of perform detele operation on database:

    sql="delete from example where Software = 'Python'"
    c.execute(sql)
    sql = "select * from example"
    for row in c.execute(sql):
    print(row)

    Output:

    ('Adobe', 10.2, 1000.0)
    ('Office', 16.0, 1000.0)
    ('Google Chrome', 46.343, 100.0)

    code

    It delete the python row entry.

So in this article you will have learned Python handling with SQlite database.

We used methods that are used in SQLite that also work same as other databases like: MySql , MSSQL.