Connect SQLite with Python

Created with Sketch.
Python Database Tutorials

Connect SQLite with Python

First you have to install Python and SQLite on your syatem.

Install Python

Use the following code:

  1. sudo apt-get update
  2. sudo apt-get upgrade python

SQLite Connect sqlite with python 1

Press y and installation will be completed within seconds.

Install SQLite

Installation steps

type in the following command:

  1. sudo apt-get install sqlite3 libsqlite3-dev

After installation check installation, sqlite terminal will give you a prompt and version info ?

  1. sqlite3

Go to desired folder and create database:

sqlite3 database.db

It’ll create database.db in the folder you’ve given the command.

To check if your database is created, use the following command in sqlite3 terminal:

  1. .databases

Note: To connect SQLite with Python, you do not need to install the connection module separately because its being shipped by default along with Python version 2.5.x onwards.

SQLite with Python

Create a python file “connect.py”, having the following code:

  1. #!/usr/bin/python
  3. import sqlite3
  5. conn = sqlite3.connect(‘javatpoint.db’)
  7. print “Opened database successfully”;

Execute the following statement on command prompt:

  1. python connect.py

SQLite Connect sqlite with python 2

Now connection is created with the javatpoint database. Now you can create a table.

Create a table

Create a table “Employees” within the database “javatpoint”.

Create a python file “createtable.py”, having the following code:

  1. #!/usr/bin/python
  3. import sqlite3
  5. conn = sqlite3.connect(‘javatpoint.db’)
  6. print “Opened database successfully”;
  8. conn.execute(””’CREATE TABLE Employees
  9.        (ID INT PRIMARY KEY     NOT NULL,
  10.        NAME           TEXT    NOT NULL,
  11.        AGE            INT     NOT NULL,
  12.        ADDRESS        CHAR(50),
  13.        SALARY         REAL);”’)
  14. print “Table created successfully”;
  16. conn.close()

Execute the following statement on command prompt:

  1. python createtable.py

SQLite Connect sqlite with python 3

A table “Employees” is created in the “javatpoint” database.

Insert Records

Insert some records in “Employees” table.

Create a python file “connection.py”, having the following code:

  1. #!/usr/bin/python
  3. import sqlite3
  5. conn = sqlite3.connect(‘javatpoint.db’)
  6. print “Opened database successfully”;
  8. conn.execute(“INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \
  9.       VALUES (1, ‘Ajeet’, 27, ‘Delhi’, 20000.00 )”);
  11. conn.execute(“INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \
  12.       VALUES (2, ‘Allen’, 22, ‘London’, 25000.00 )”);
  14. conn.execute(“INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \
  15.       VALUES (3, ‘Mark’, 29, ‘CA’, 200000.00 )”);
  17. conn.execute(“INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \
  18.       VALUES (4, ‘Kanchan’, 22, ‘Ghaziabad ‘, 65000.00 )”);
  20. conn.commit()
  21. print “Records inserted successfully”;
  22. conn.close()

Execute the following statement on command prompt:

  1. python connection.py

SQLite Connect sqlite with python 4

Records are inserted successfully.

Select Records

Now you can fetch and display your records from the table “Employees” by using SELECT statement.

Create a python file “select.py”, having the following code:

  1. #!/usr/bin/python
  3. import sqlite3
  5. conn = sqlite3.connect(‘javatpoint.db’)
  7. data = conn.execute(“select * from Employees”);
  9. for row in data:
  10.    print “ID = “, row[0]
  11.    print “NAME = “, row[1]
  12.    print “ADDRESS = “, row[2]
  13.    print “SALARY = “, row[3], “\n”
  15. conn.close();

Execute the following statement on command prompt:

  1. python select.py

SQLite Connect sqlite with python 5

See all the records you have inserted before.

By same procedures, you can update and delete the table in SQLite database usnig Python.

0

Leave a Reply

Your email address will not be published. Required fields are marked *

©  2024 python tutorials.  python-tutorials.in