Learn to Program 19

Posted by Derek Banas on Aug 20, 2016 in Web Design | 0 comments

In this part of my Learn to Program series I will show how to use SQLite with Python. We’ll cover connect(), cursor(), execute(), commit(), rollback(), fetchall(), fetchone(), Select, Data Types, Create, Insert, Update, Delete, Alter, Backing up the Database and more.

Here is my SQLite Tutorial which covers SQLite in massive detail. All of the code and a transcript follows the video below.

If you value videos like this consider contributing a $1 on Patreon.

[googleplusone]

Code & Transcript

# Here I'll show you how to work with SQLite databases
# in Python

# A database makes it easy for you to organize your
# data for storage and fast searching

# I show how to install SQLite and use it in this
# tutorial https://youtu.be/QjICgmk31js

# You need the SQLite module to use it
import sqlite3
import sys
import csv

# ---------- FUNCTIONS ----------

def printDB():
    # To retrieve data from a table use SELECT followed
    # by the items to retrieve and the table to
    # retrieve from

    try:
        result = theCursor.execute("SELECT id, FName, LName, Age, Address, Salary, HireDate FROM Employees")

        # You receive a list of lists that hold the result
        for row in result:
            print("ID :", row[0])
            print("FName :", row[1])
            print("LName :", row[2])
            print("Age :", row[3])
            print("Address :", row[4])
            print("Salary :", row[5])
            print("HireDate :", row[6])

    except sqlite3.OperationalError:
        print("The Table Doesn't Exist")

    except:
        print("Couldn't Retrieve Data From Database")

# ---------- END OF FUNCTIONS ----------

# connect() will open an SQLite database, or if it
# doesn't exist it will create it
# The file appears in the same directory as this
# Python file
db_conn = sqlite3.connect('test.db')

print("Database Created")

# A cursor is used to traverse the records of a result
theCursor = db_conn.cursor()

# execute() executes a SQL command
# We organize our data in tables by defining their
# name and the data type for the data

# We define the table name
# A primary key is a unique value that differentiates
# each row of data in our table
# The primary key will auto increment each time we
# add a new Employee
# If a piece of data is marked as NOT NULL, that means
# it must have a value to be valid

# NULL is NULL and stands in for no value
# INTEGER is an integer
# TEXT is a string of variable length
# REAL is a float
# BLOB is used to store binary data

# You can delete a table if it exists like this
# db_conn.execute("DROP TABLE IF EXISTS Employees")
# db_conn.commit()

try:
    db_conn.execute("CREATE TABLE Employees(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, FName TEXT NOT NULL, LName TEXT NOT NULL, Age INT NOT NULL, Address TEXT, Salary REAL, HireDate TEXT);")

    db_conn.commit()

    print("Table Created")

except sqlite3.OperationalError:
    print("Table couldn't be Created")


# To insert data into a table we use INSERT INTO
# followed by the table name and the item name
# and the data to assign to those items

db_conn.execute("INSERT INTO Employees (FName, LName, Age, Address, Salary, HireDate)"
                "VALUES ('Derek', 'Banas', 41, '123 Main St', '500,000', date('now'))")

db_conn.commit()

print("Employee Entered")

# Print out all the data in the database
printDB()

# You can update a value in a table by referencing
# something unique like the ID or anything else
# with the UPDATE command

try:
    db_conn.execute("UPDATE Employees SET Address = '121 Main St' WHERE ID=2")
    db_conn.commit()

except sqlite3.OperationalError:
    print("Database couldn't be Updated")

printDB()

# Delete matching data from the database by
# referencing the table name and something unique

try:
    db_conn.execute("DELETE FROM Employees WHERE ID=2")
    db_conn.commit()

except sqlite3.OperationalError:
    print("Data couldn't be Deleted")

printDB()

# Undo the last commit()
db_conn.rollback()

printDB()

# You can add a new column to a table with ALTER

try:
    db_conn.execute("ALTER TABLE Employees ADD COLUMN 'Image' BLOB DEFAULT NULL")

    db_conn.commit()

except sqlite3.OperationalError:
    print("Table couldn't be Altered")

# Retrieve table column names
theCursor.execute("PRAGMA TABLE_INFO(Employees)")

# fetchall() returns all remaining rows of a query result
# as a list
rowNames = [nameTuple[1] for nameTuple in theCursor.fetchall()]
print(rowNames)

# Get the total number of rows
theCursor.execute('SELECT COUNT(*) FROM Employees')

numOfRows = theCursor.fetchall()

print("Total Rows :", numOfRows[0][0])

# Get SQLite version
theCursor.execute('SELECT SQLITE_VERSION()')

# fetchone() returns one result
print("SQLite Version :", theCursor.fetchone())

# Use the dictionary cursor to retrieve data in a dictionary
with db_conn:
    db_conn.row_factory = sqlite3.Row

    theCursor = db_conn.cursor()

    theCursor.execute("SELECT * FROM Employees")

    rows = theCursor.fetchall()

    for row in rows:
        print("{} {}".format(row["FName"], row["LName"]))

# Write data to File

with open('dump.sql', 'w') as f:

    # iterdump() returns an iterator to dump the database
    # in SQL format
    for line in db_conn.iterdump():
        f.write('%s\n' % line)

# Closes the database connection
db_conn.close()

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

# Here I'll show you how to work with SQLite databases

# in Python

# A database makes it easy for you to organize your

# data for storage and fast searching

# I show how to install SQLite and use it in this

# tutorial https://youtu.be/QjICgmk31js

# You need the SQLite module to use it

import sqlite3

import sys

import csv

# ---------- FUNCTIONS ----------

def printDB():

# To retrieve data from a table use SELECT followed

# by the items to retrieve and the table to

# retrieve from

try:

result = theCursor.execute("SELECT id, FName, LName, Age, Address, Salary, HireDate FROM Employees")

# You receive a list of lists that hold the result

for row in result:

print("ID :", row[0])

print("FName :", row[1])

print("LName :", row[2])

print("Age :", row[3])

print("Address :", row[4])

print("Salary :", row[5])

print("HireDate :", row[6])

except sqlite3.OperationalError:

print("The Table Doesn't Exist")

except:

print("Couldn't Retrieve Data From Database")

# ---------- END OF FUNCTIONS ----------

# connect() will open an SQLite database, or if it

# doesn't exist it will create it

# The file appears in the same directory as this

# Python file

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

print("Database Created")

# A cursor is used to traverse the records of a result

theCursor = db_conn.cursor()

# execute() executes a SQL command

# We organize our data in tables by defining their

# name and the data type for the data

# We define the table name

# A primary key is a unique value that differentiates

# each row of data in our table

# The primary key will auto increment each time we

# add a new Employee

# If a piece of data is marked as NOT NULL, that means

# it must have a value to be valid

# NULL is NULL and stands in for no value

# INTEGER is an integer

# TEXT is a string of variable length

# REAL is a float

# BLOB is used to store binary data

# You can delete a table if it exists like this

# db_conn.execute("DROP TABLE IF EXISTS Employees")

# db_conn.commit()

try:

db_conn.execute("CREATE TABLE Employees(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, FName TEXT NOT NULL, LName TEXT NOT NULL, Age INT NOT NULL, Address TEXT, Salary REAL, HireDate TEXT);")

db_conn.commit()

print("Table Created")

except sqlite3.OperationalError:

print("Table couldn't be Created")

# To insert data into a table we use INSERT INTO

# followed by the table name and the item name

# and the data to assign to those items

db_conn.execute("INSERT INTO Employees (FName, LName, Age, Address, Salary, HireDate)"

"VALUES ('Derek', 'Banas', 41, '123 Main St', '500,000', date('now'))")

db_conn.commit()

print("Employee Entered")

# Print out all the data in the database

printDB()

# You can update a value in a table by referencing

# something unique like the ID or anything else

# with the UPDATE command

try:

db_conn.execute("UPDATE Employees SET Address = '121 Main St' WHERE ID=2")

db_conn.commit()

except sqlite3.OperationalError:

print("Database couldn't be Updated")

printDB()

# Delete matching data from the database by

# referencing the table name and something unique

try:

db_conn.execute("DELETE FROM Employees WHERE ID=2")

db_conn.commit()

except sqlite3.OperationalError:

print("Data couldn't be Deleted")

printDB()

# Undo the last commit()

db_conn.rollback()

printDB()

# You can add a new column to a table with ALTER

try:

db_conn.execute("ALTER TABLE Employees ADD COLUMN 'Image' BLOB DEFAULT NULL")

db_conn.commit()

except sqlite3.OperationalError:

print("Table couldn't be Altered")

# Retrieve table column names

theCursor.execute("PRAGMA TABLE_INFO(Employees)")

# fetchall() returns all remaining rows of a query result

# as a list

rowNames = [nameTuple[1] for nameTuple in theCursor.fetchall()]

print(rowNames)

# Get the total number of rows

theCursor.execute('SELECT COUNT(*) FROM Employees')

numOfRows = theCursor.fetchall()

print("Total Rows :", numOfRows[0][0])

# Get SQLite version

theCursor.execute('SELECT SQLITE_VERSION()')

# fetchone() returns one result

print("SQLite Version :", theCursor.fetchone())

# Use the dictionary cursor to retrieve data in a dictionary

with db_conn:

db_conn.row_factory = sqlite3.Row

theCursor = db_conn.cursor()

theCursor.execute("SELECT * FROM Employees")

rows = theCursor.fetchall()

for row in rows:

print("{} {}".format(row["FName"], row["LName"]))

# Write data to File

with open('dump.sql', 'w') as f:

# iterdump() returns an iterator to dump the database

# in SQL format

for line in db_conn.iterdump():

f.write('%s\n' % line)

# Closes the database connection

db_conn.close()

	Facebook
	YouTube
	Twitter
	LinkedIn

Learn to Program 19

Leave a Reply Cancel reply