SQL

how to install sqlite3 python

pip install db-sqlite3

python sqlite3

import sqlite3

# Create a database and open the database.
# If the database already exists just opens the database
conn = sqlite3.connect('users.db')
c = conn.cursor()
# Create a users table if the table does not exists
c.execute('''CREATE TABLE IF NOT EXISTS users(name TEXT, age INTEGER)''')
# commit changes and close database connect
conn.commit()
conn.close()

# Insert values with parameter queries to prevent sql injections
conn = sqlite3.connect('users.db')
c = conn.cursor()
name = "John"
number = 20
c.execute("INSERT INTO users VALUES (?, ?)", (name, number))
# commit changes and close database connect
conn.commit()
conn.close()

# Insert many values with parameter queries to prevent sql injections
users = [
   ("James", 10),
   ("Smith", 21),
   ("Jerome", 11)
]
conn = sqlite3.connect('users.db')
c = conn.cursor()
c.executemany("INSERT INTO users VALUES (?, ?)", users)
# commit changes and close database connect
conn.commit()
conn.close()

# Print values from table
conn = sqlite3.connect('users.db')
c = conn.cursor()

for row in c.execute('SELECT * FROM users'):
   print(row)

# list comprehension to store all names and ages in separate lists
names = [row[0] for row in c.execute('SELECT * FROM users')]
ages = [row[1] for row in c.execute('SELECT * FROM users')]
# list comprehension store all names and ages in a list
combined = [f"Name: {row[0]} - Age: {row[1]}" for row in c.execute('SELECT * FROM users')]
# print names and ages list
print(names, ages)
# print name and ages command list
print(combined)
# close connection
conn.close()

# Print all rows in a list of tuples using fetchall
conn = sqlite3.connect('users.db')
c = conn.cursor()
result = c.execute("SELECT * FROM users")
print(result.fetchall())
conn.close()

# output
# ('John', 20)
# ('James', 10)
# ('Smith', 21)
# ('Jerome', 11)
# ['John', 'James', 'Smith', 'Jerome'] [20, 10, 21, 11]
# ['Name: John - Age: 20', 'Name: James - Age: 10', 'Name: Smith - Age: 21', 'Name: Jerome - Age: 11']
# [('John', 20), ('James', 10), ('Smith', 21), ('Jerome', 11)

how to install sqlite3 in python

if you are using python3, sqlite3 is built in into it.

python sqlite

import sqlite3
import os.path

#Connection to the DB
try:
    # Make sure to find the file.db in the script directory
    BASE_DIR = os.path.dirname(os.path.abspath(__file__)) 
    db_path = os.path.join(BASE_DIR, "sqlite.db")
    conn = sqlite3.connect(db_path)

except sqlite3.Error as error:
    print("Failed to read data from sqlite table", error)



# Execute query on the sqlite DB
cur = conn.cursor()
cur.execute("SELECT * FROM tasks")

# Print everything from a table
rows = cur.fetchall()
for row in rows:
        print(row)
    
# Update field 
conn.execute("""UPDATE tasks SET name = 'jhon'
 where id = 1""")

# close the DB connection 
conn.close() 

sqlite3 python

import sqlite3 as lite 
import sys 
 
try: 
    con = lite.connect('products.db') 
    cur = con.cursor()     
    cur.execute("CREATE TABLE drinks(Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Price REAL)") 
    cur.execute("CREATE TABLE fruits(Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Price REAL)") 
    con.commit() 
             
except e: 
    if con: 
        con.rollback() 
     
    print("Unexpected error %s:" % e.args[0]) 
    sys.exit(1) 
finally: 
    if con: 
        con.close()  

how to install sqlite3 in python

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')

print "Opened database successfully";

python sqlite

import sqlite3

con = sqlite3.connect(":memory:")
con.isolation_level = None
cur = con.cursor()

buffer = ""

print("Enter your SQL commands to execute in sqlite3.")
print("Enter a blank line to exit.")

while True:
    line = input()
    if line == "":
        break
    buffer += line
    if sqlite3.complete_statement(buffer):
        try:
            buffer = buffer.strip()
            cur.execute(buffer)

            if buffer.lstrip().upper().startswith("SELECT"):
                print(cur.fetchall())
        except sqlite3.Error as e:
            print("An error occurred:", e.args[0])
        buffer = ""

con.close()

sqlite3 python

import sqlite3
from sqlite3 import Error


def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e)

    return conn


def create_project(conn, project):
    """
    Create a new project into the projects table
    :param conn:
    :param project:
    :return: project id
    """
    sql = ''' INSERT INTO projects(name,begin_date,end_date)
              VALUES(?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, project)
    conn.commit()
    return cur.lastrowid


def create_task(conn, task):
    """
    Create a new task
    :param conn:
    :param task:
    :return:
    """

    sql = ''' INSERT INTO tasks(name,priority,status_id,project_id,begin_date,end_date)
              VALUES(?,?,?,?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, task)
    conn.commit()
    return cur.lastrowid


def main():
    database = r"C:sqlitedbpythonsqlite.db"

    # create a database connection
    conn = create_connection(database)
    with conn:
        # create a new project
        project = ('Cool App with SQLite & Python', '2015-01-01', '2015-01-30');
        project_id = create_project(conn, project)

        # tasks
        task_1 = ('Analyze the requirements of the app', 1, 1, project_id, '2015-01-01', '2015-01-02')
        task_2 = ('Confirm with user about the top requirements', 1, 1, project_id, '2015-01-03', '2015-01-05')

        # create tasks
        create_task(conn, task_1)
        create_task(conn, task_2)


if __name__ == '__main__':
    main()
Code language: Python (python)

python sqlite3

filelist sqllite