Command Line SH-WPS Office
Command Line SH-WPS Office
The SQLite project provides a simple command-line utility named sqlite3 (or sqlite3.exe on Windows)
that allows the user to manually enter and execute SQL statements against an SQLite database.
To start the sqlite3 program, simply type "sqlite3" at the command prompt. The "sqlite3" command may
be optionally followed by the name the file that holds the SQLite database. If the file does not exist, a
new database file with the given name will be created automatically. If no database file is specified on
the command-line, a temporary database is created, then deleted when the "sqlite3" program exits.
On start-up, the sqlite3 program will show a brief banner message then prompt you to enter SQL. Type in
SQL statements (terminated by a semicolon), press "Enter" and the SQL will be executed.
You can terminate the sqlite3 program by typing your system End-Of-File character (usually a Control-D).
Use the interrupt character (usually a Control-C) to stop a long-running SQL statement.
Make sure you type a semicolon at the end of each SQL command! The sqlite3 program looks for a
semicolon to know when your SQL command is complete. If you omit the semicolon, sqlite3 will give you
a continuation prompt and wait for you to enter more text to be added to the current SQL command.
This feature allows you to enter SQL commands that span multiple lines. For example:
...> f2 text,
...> f3 real
...> );
sqlite>
Let's talk a little about data types that are used in SQLite databases. SQLite uses what is called, dynamic
type system. That means, the value stored in a column determines its data type and not the column's
data type. Also, you don’t have to define a specific data type for a column when you create a table. Even
if you have a column with the integer data type for example, you can store any kind of data types such as
text and SQLite will not complain about this.
The ANSI Standard of SQL specifies the data types to be used by relational databases. SQLite provides
the following five data types which are referred to as storage classes:
Storage Class
Meaning
NULL
INTEGER
TEXT
BLOB
Binary Large Object that can be used to store any kind of data. The maximum size of BLOBs is unlimited.
A storage class is more general than a datatype. These storage classes are mapped to standard SQL data
types. For example, INTEGER in SQLite has a type affinity with all integer types such as int, smallint,
bigint, tinyint etc. Similarly REAL in SQLite has a type affinity with float and double data type. Standard
SQL data types such as varchar, char, nchar etc. are equivalent to TEXT in SQLite.
import sqlite3
MySchool=sqlite3.connect('schooltest.db')
curschool=MySchool.cursor()
age INTEGER,
marks REAL);''')
MySchool.close()
In the previous section, you learned how to create a database and a table within it. Now, let's see how to
create a new record in the existing table, student.
Name = Sherlock
House = Slytherin
Marks = 65
1. Assuming that the database MySchool is created and contains the table student, we start by creating a
connection:
import sqlite3
MySchool=sqlite3.connect('schooltest.db')
curschool=MySchool.cursor()
import sqlite3
MySchool=sqlite3.connect('schooltest.db')
curschool=MySchool.cursor()
import sqlite3
MySchool=sqlite3.connect('schooltest.db')
curschool=MySchool.cursor()
MySchool.commit()
The new record is added to the table. You can verify this from SQLite Studio.
You can refer to the helper text for the relevant screenshot.
import sqlite3
MySchool=sqlite3.connect('schooltest.db')
curschool=MySchool.cursor()
import sqlite3
MySchool=sqlite3.connect('schooltest.db')
curschool=MySchool.cursor()
3. We now replaces the fixed VALUES in the INSERT query with the variables, mysid, myname, myhouse
and mymarks. To do this, we use the DB-API’s parameter substitution. We put a ? as a placeholder
wherever we want to use a value and then give a tuple of values as the second argument to the cursor’s
execute() method.
import sqlite3
MySchool=sqlite3.connect('schooltest.db')
curschool=MySchool.cursor()
import sqlite3
MySchool=sqlite3.connect('schooltest.db')
curschool=MySchool.cursor()
MySchool.commit()
In Python, there are (at least) two distinguishable kinds of errors: syntax errors and exceptions. Syntax
errors, also known as parsing errors, are errors in the programming syntax. In the following example, the
quotation mark is missing at the end of the string, hello world. This is a syntax error.
Example 1
Example 2
>>>
Error handling in Python is done through the use of exceptions that are caught in try blocks and handled
in except blocks.
Let us look an example of how this is used. The following code not only accepts a user input and adds a
new record but also displays a message if the operation was successful or not.
Even if a statement or expression is syntactically correct, it may cause an error when an attempt is made
to execute it. Errors detected during execution are called exceptions. In the following example, division
by zero is an exception.
>>> 10 * (1/0)
10 * (1/0)
>>>
Error handling in Python is done through the use of exceptions that are caught in try blocks and handled
in except blocks.
Let us look an example of how this is used. The following code not only accepts a user input and adds a
new record but also displays a message if the operation was successful or not.
import sqlite3
MySchool=sqlite3.connect('schooltest.db')
try:
curschool=MySchool.cursor()
curschool.execute("INSERT INTO student (StudentID, name, house, marks) VALUES (?,?,?,?)", (mysid,
myname, myhouse, mymarks))
MySchool.commit()
except:
MySchool.rollback()
MySchool.close()
The connection class defines the commit() and rollback() methods. Changes in database are finalised
only if the execute() method runs successfully by commit() method. Otherwise, any changes are undone
by the rollback() method. You can try this yourself by saving this code as a .py file and executing it.
First, the try clause (the statement(s) between the try and except keywords) is executed.
If no exception occurs, the except clause is skipped and execution of the try statement is finished
If an exception occurs during execution of the try clause, the rest of the clause is skipped. Then the
except clause is executed, and then execution continues after the try statement.
If an exception occurs which does not match the exception named in the except clause, it is passed on to
outer try statements; if no handler is found, it is an unhandled exception and execution stops with a
message.
fetchone()
fetchall()
This method fetches the next available record from the result set. It is a tuple consisting of values of each
column of the fetched record. The Following code snippet retrieves and prints one record at a time till
the result set is exhausted.
import sqlite3
MySchool=sqlite3.connect('schooltest.db')
curschool=MySchool.cursor()
curschool.execute(sql)
while True:
record=curschool.fetchone()
if record==None:
break
print (record)
This method fetches all the remaining records in the form of a list of tuples. Each tuple corresponds to
one record and contains values of each column in the table. The following code snippet fetches all
records and prints them one at a time by using the 'for' statement.
import sqlite3
MySchool=sqlite3.connect('schooltest.db')
sql="SELECT * from student;"
curschool=MySchool.cursor()
curschool.execute(sql)
result=curschool.fetchall()
print (record)
import sqlite3
MySchool=sqlite3.connect('schooltest.db')
curschool=MySchool.cursor()
curschool.execute(sql)
record=curschool.fetchone()
print (record)
try:
curschool.execute(sql)
MySchool.commit()
MySchool.rollback()