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)
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.
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")
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])
my_data={'tasks':e1.get(),'status':False} # dicitionary
r_set=my_conn.execute(text("INSERT INTO my_tasks (tasks, status) VALUES(:tasks,:status)"),my_data)
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
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',)
(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')
try:
my_conn.execute('''DROP table student;''')
print("student table deleted")
except sqlite3.Error as my_error:
print("error: ",my_error)
my_conn.commit()
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
);''')
my_conn = create_engine("sqlite:///D:\\testing\\sqlite\\my_db.db")
r_set=my_conn.execute("CREATE TABLE IF NOT EXISTS category(cat_id integer primary key,\
category text)")
my_conn.commit()
r_set=my_conn.execute("INSERT INTO `category` (`cat_id`, `category`) VALUES \
(1, 'Fruits'),\
(2, 'Colors'),\
(3, 'Games'),\
(4, 'Vehicles');")
my_conn.commit()
r_set=my_conn.execute("CREATE TABLE IF NOT EXISTS subcategory(cat_id integer,\
subcategory text)")
my_conn.commit()
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()
from datetime import date,timedelta
import sqlite3
my_data=[] # List to store data
dt=date.today() # Todays date as base date
for delta in range(-3,3): # change the range values
dt2=dt + timedelta(days=delta) # new date object
## One Tuple with three elements is added to list
my_data.append((delta,dt2.strftime('%Y-%m-%d'),dt2.strftime('%b-%Y')))
print(my_data) # to check the list with Tuples
my_path='E:\\testing\\my_db2.db' # to store SQLite database
my_conn = sqlite3.connect(my_path)
## Delete the table if required
r_set=my_conn.execute('''DROP TABLE table_dt''')
try:
my_conn.execute('''
CREATE TABLE IF NOT EXISTS `table_dt` (
`record_id` INTEGER PRIMARY KEY,
`my_dt` TEXT NOT NULL,
`month_year` TEXT
);''')
my_conn.commit()
print("Table created successfully")
except sqlite3.Error as my_error:
print("error: ",my_error)
r_set=my_conn.executemany("INSERT INTO table_dt VALUES(?, ?, ?)", my_data)
my_conn.commit()
print("No. of Records added : ",r_set.rowcount)
## Check the data by displaying
r_set=my_conn.execute('''SELECT * from table_dt ''');
for row in r_set:
print(row)
21-02-2023 | |
I am new to tkinter and sqlite3. can i post a question regarding my learning project? |