SQLite connection and creating table


Youtube Live session on Tkinter

Database Name: my_db.db
Table Name : student,category, subcategory

First create the database.
import sqlite3
my_conn = sqlite3.connect('my_db.db')
print("Connected to database successfully")
Output ( A new database is created or a connection is established to the existing database if available )
Connected to database successfully
Using different path and executing query to show records.
import sqlite3
my_conn = sqlite3.connect('D:\\testing\\my_db.db')
query="SELECT *  FROM student LIMIT 0,5"
my_data=list(my_conn.execute(query)) # 
print(my_data)

Without installing use SQLite database from google Drive by using Colab.

Using SQLAlchemy

In above code we used sqlite3 library to connect and manage SQLite database. We can also use SQLAlchemy to manage SQLite. The script part remain same and only the connection string changes. Here we have created the connection engine ( the variable ) my_conn. We used a common varaible my_conn as output of both connection engines so the script part remain same throughout.
from sqlalchemy import create_engine
#my_conn = create_engine("sqlite:///my_db.db")
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")
In windows system, the absolute path is used in above code.

Advantage of SQLalchemy

If you want to change to other database ( say MySQL ) then only connection string needs to be changed as by using SQLAlchemy can be used to connect different databases. Here the script part mostly remain same. Easy portability is the main advantages here.

There is a small change at Parameterized query where for the placeholders we have to use %s for MySQL and ? for SQLite.

AttributeError: 'Engine' object has no attribute 'execute'
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")")
my_conn=my_conn.connect() # add this line 
sqlalchemy.exc.ObjectNotExecutableError: Not an executable object:
We have to import text before using the same.
from sqlalchemy import create_engine,text
query="SELECT *  FROM student LIMIT 0,5"
my_data=list(my_conn.execute(text(query)))
Using Parameterized query
query=text("SELECT *  FROM student_address  WHERE id=:id")
my_data={'id':2} # dicitionary with key and value pair
my_cursor=my_conn.execute(query,my_data)
data_row=my_cursor.fetchone() # tuple with all column data 
print(data_row[1])

Creating table

Here is out student table, we used try except to handle any error during the table creation process.
Using SQLAlchemy
Using sqlite3
try:
    my_conn.execute('''
        CREATE TABLE IF NOT EXISTS student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      gender text 
                      );''')
    my_conn.commit()
    print("Student Table created successfully")
except sqlite3.Error as my_error:
  print("error: ",my_error) 
Output
Student Table created successfully

Listing tables in the database

r_set=my_conn.execute('''select name from sqlite_master 
		where type = 'table' ''')
for row in r_set:
    print(row)
Output ( as we have already created on table in above script, we will get this output )
('student',)

Adding records to student table ( with create table )

SQLAlchemy

sqlite3

Displaying records of student table

output will be the rows of records .. ..
(1, 'John Deo', 'Four', 75, 'female')
(2, 'Max Ruin', 'Three', 85, 'male')
(3, 'Arnold', 'Three', 55, 'male')
--------------
--------------
(34, 'Gain Toe', 'Seven', 69, 'male')
(35, 'Rows Noump', 'Six', 88, 'female')

Deleting records

Deleting or dropping student table


Query to delete table from SQLite database by using DROP table in try except error handling code


Using SQLAlchemy
Using sqlite3
try:
  my_conn.execute('''DROP table student;''')
  print("student table deleted")
except sqlite3.Error as my_error:
  print("error: ",my_error)
my_conn.commit()

Create Table with Blob column data type

from sqlalchemy import create_engine
#my_conn = create_engine("sqlite:///my_db.db")
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")
my_conn.execute('''
CREATE TABLE IF NOT EXISTS student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      gender text, 
                      photo blob
                      );''')

Delete > Create > Add records to student table

Delete ( manually the file ) the database and then run this code. Note that the path in line 3 needs to be changed to match your system. This code will create the database my_db.db inside the sqlite directory. This will print the success message in your console.
my_conn = create_engine("sqlite:///D:\\testing\\sqlite\\my_db.db")

Create table category

r_set=my_conn.execute("CREATE TABLE IF NOT EXISTS category(cat_id integer primary key,\
                        category text)")
my_conn.commit()

Add data to category

r_set=my_conn.execute("INSERT INTO `category` (`cat_id`, `category`) VALUES \
(1, 'Fruits'),\
(2, 'Colors'),\
(3, 'Games'),\
(4, 'Vehicles');")
my_conn.commit()

Create table subcategory

r_set=my_conn.execute("CREATE TABLE IF NOT EXISTS subcategory(cat_id integer,\
                        subcategory text)")
my_conn.commit()

Add data to subcategory

r_set=my_conn.execute("INSERT INTO `subcategory` (`cat_id`, `subcategory`) VALUES \
(1, 'Mango'),\
(1, 'Banana'),\
(1, 'Orange'),\
(1, 'Apple'),\
(2, 'Red'),\
(2, 'Blue'),\
(2, 'Green'),\
(2, 'Yellow'),\
(3, 'Cricket'),\
(3, 'Football'),\
(3, 'Baseball'),\
(3, 'Tennis'),\
(4, 'Cars'),\
(4, 'Trucks'),\
(4, 'Bikes'),\
(4, 'Train')")
my_conn.commit()

SQL part only to create table and insert records

The query part to create student table ( without any Python code )


Add records to student table


View and Download sqlite-connection ipynb file ( .html format )
Download .zip file with my_db.db, and .ipynb files
Sqlite Sqlite INSERT DELETE UPDATE SELECT ORDER By LIMIT IIF
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    21-02-2023

    I am new to tkinter and sqlite3. can i post a question regarding my learning project?

    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer