[go: up one dir, main page]

0% found this document useful (0 votes)
427 views12 pages

Command Line SH-WPS Office

The document describes a command line shell called sqlite3 that allows users to manually enter and execute SQL statements against an SQLite database. It can be started by typing "sqlite3" followed optionally by the name of a database file. If no file is specified, a temporary database is used. SQL statements are entered and terminated with a semicolon. The shell looks for semicolons to know when commands are complete.

Uploaded by

Varun Majesty
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
427 views12 pages

Command Line SH-WPS Office

The document describes a command line shell called sqlite3 that allows users to manually enter and execute SQL statements against an SQLite database. It can be started by typing "sqlite3" followed optionally by the name of a database file. If no file is specified, a temporary database is used. SQL statements are entered and terminated with a semicolon. The shell looks for semicolons to know when commands are complete.

Uploaded by

Varun Majesty
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 12

Command Line Shell for SQLite

(Taken from the documentation available at http://sqlite.org/cli.html)

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:

sqlite> CREATE TABLE tbl2 (

...> f1 varchar(30) primary key, .

...> f2 text,
...> f3 real

...> );

sqlite>

SQLite Data Types

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

Missing or unknown information.

INTEGER

Whole numbers, either positive or negative.


REAL

Real numbers with decimal values that use 8-byte floats.

TEXT

Character data. SQLite supports various character encodings.

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.

Returns a Cursor object which uses this


Connection.
Further reading: https://www.python.org/dev/peps/pep-0249/#connection-methods

Creating a database from Python - Practice

import sqlite3

MySchool=sqlite3.connect('schooltest.db')

curschool=MySchool.cursor()

curschool.execute('''CREATE TABLE student (

StudentID INTEGER PRIMARY KEY AUTOINCREMENT,

name TEXT (20) NOT NULL,

age INTEGER,

marks REAL);''')

MySchool.close()

Inserting a new Record

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.

Example 1: To add the following record:

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()

2. To add a new record to the table, we execute the INSERT query.

import sqlite3

MySchool=sqlite3.connect('schooltest.db')

curschool=MySchool.cursor()

curschool.execute("INSERT INTO student (StudentID, name, house, marks) VALUES (5,'Sherlock',32,50);")

3. We now commit the changes to confirm them.

import sqlite3

MySchool=sqlite3.connect('schooltest.db')

curschool=MySchool.cursor()

curschool.execute("INSERT INTO student (StudentID, name, house, marks) VALUES (5,'Sherlock',32,50);")

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.

Inserting a new Record

Example 2: To accept user input for the values in the table:


1. Instead of adding known values, you can also accept user input for these values. 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()

2. To accept user input, we use variables to store each of the values.

import sqlite3

MySchool=sqlite3.connect('schooltest.db')

curschool=MySchool.cursor()

mysid= int(input("Enter ID: "))

myname=input("Enter name: ")

myhouse=int(input("Enter house: "))

mymarks=float(input("Enter marks: "))

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()

mysid= int(input("Enter ID: "))

myname=input("Enter name: ")

myhouse=int(input("Enter house: "))

mymarks=float(input("Enter marks: "))


curschool.execute("INSERT INTO student (StudentID, name, house, marks) VALUES (?,?,?,?);",
(mysid,myname,myhouse,mymarks))

4. We now commit the changes.

import sqlite3

MySchool=sqlite3.connect('schooltest.db')

curschool=MySchool.cursor()

mysid= int(input("Enter ID: "))

myname=input("Enter name: ")

myhouse=int(input("Enter house: "))

mymarks=float(input("Enter marks: "))

curschool.execute("INSERT INTO student (StudentID, name, house, marks) VALUES (?,?,?,?);",


(mysid,myname,myhouse,mymarks))

MySchool.commit()

Error handling in Python

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

>>> print("Hello World)

SyntaxError: EOL while scanning string literal

>>>

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)

Traceback (most recent call last):

File "<pyshell#0>", line 1, in <module>

10 * (1/0)

ZeroDivisionError: division by zero

>>>

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')

mysid= int(input("Enter ID: "))

myname=input("Enter name: ")

myhouse=int(input("Enter house: "))

mymarks=float(input("Enter marks: "))

#try block to catch exceptions

try:

curschool=MySchool.cursor()
curschool.execute("INSERT INTO student (StudentID, name, house, marks) VALUES (?,?,?,?)", (mysid,
myname, myhouse, mymarks))

MySchool.commit()

print ("One record added successfully.")

#except block to handle exceptions

except:

print ("Error in operation.")

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.

Try and Except

The try statement works as follows.

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.

Further Reading: https://docs.python.org/3/tutorial/errors.html


The SELECT query forms a result set containing all records returned as a response to a query. The
execute() method uses a string representing the SELECT query statement. There are two prominent
methods as per DB-API standard. The below two methods are used:

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')

sql="SELECT * from student;"

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()

for record in result:

print (record)

import sqlite3

MySchool=sqlite3.connect('schooltest.db')

nm=input("enter name: ")

sql="SELECT * from student WHERE name='"+nm+"';"

curschool=MySchool.cursor()

curschool.execute(sql)

record=curschool.fetchone()

print (record)

m=float(input("enter new marks: "))

sql="UPDATE student SET marks='"+str(m)+"' WHERE name='"+nm+"';"

try:

curschool.execute(sql)

MySchool.commit()

print ("record updated successfully")


except:

print ("error in update operation")

MySchool.rollback()

You might also like