Python Experiment No: 9 Implementation of insert, delete, update operation on SQL database.
Aim:- Implementation of insert, delete, update operation on SQL database.
Theory:-
Interfaces to most popular SQL databases are available from third-party modules, and out of the box Python comes with the sqlite3 module (and with the SQLite 3 database), so database programming can be started right away. SQLite is a lightweight SQL database, lacking many of the features of, say, PostgreSQL, but it is very convenient for prototyping,and may prove sufficient in many cases.
To make it as easy as possible to switch between database backends, PEP 249 (Python Database API Specification v2.0) provides an API specification called DB-API 2.0 that database interfaces ought to honor—the sqlite3 module, for example, complies with the specification, but not all the third-party modules do. There are two major objects specified by the API, the connection object and the cursor object, and the APIs they must support are shown in Tables 12.1 and 12.2. In the case of the sqlite3 module, its connection and cursor objects both provide many additional attributes and methods beyond those required by the DB-API 2.0 specification. The SQL version of the DVDs program is dvds-sql.py. The program stores directors separately from the DVD data to avoid duplication and offers one more menu option that lets the user list the directors. The program has slightly fewer than 300 lines, whereas the previous section’s dvds-dbm.py program is slightly fewer than 200 lines, with most of the difference due to the fact that we must use SQL queries rather than perform simple dictionary-like operations, and because we must create the database’s tables the first time the program runs. The main() function is similar to before, only this time we call a custom connect() function to make the connection.
def connect(filename):
create = not os.path.exists(filename)
db = sqlite3.connect(filename)
if create:
cursor = db.cursor()
Theory:-
Interfaces to most popular SQL databases are available from third-party modules, and out of the box Python comes with the sqlite3 module (and with the SQLite 3 database), so database programming can be started right away. SQLite is a lightweight SQL database, lacking many of the features of, say, PostgreSQL, but it is very convenient for prototyping,and may prove sufficient in many cases.
To make it as easy as possible to switch between database backends, PEP 249 (Python Database API Specification v2.0) provides an API specification called DB-API 2.0 that database interfaces ought to honor—the sqlite3 module, for example, complies with the specification, but not all the third-party modules do. There are two major objects specified by the API, the connection object and the cursor object, and the APIs they must support are shown in Tables 12.1 and 12.2. In the case of the sqlite3 module, its connection and cursor objects both provide many additional attributes and methods beyond those required by the DB-API 2.0 specification. The SQL version of the DVDs program is dvds-sql.py. The program stores directors separately from the DVD data to avoid duplication and offers one more menu option that lets the user list the directors. The program has slightly fewer than 300 lines, whereas the previous section’s dvds-dbm.py program is slightly fewer than 200 lines, with most of the difference due to the fact that we must use SQL queries rather than perform simple dictionary-like operations, and because we must create the database’s tables the first time the program runs. The main() function is similar to before, only this time we call a custom connect() function to make the connection.
def connect(filename):
create = not os.path.exists(filename)
db = sqlite3.connect(filename)
if create:
cursor = db.cursor()
cursor.execute("CREATE TABLE directors ("
"id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, "
"name TEXT UNIQUE NOT NULL)")
cursor.execute("CREATE TABLE dvds ("
"id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, "
"title TEXT NOT NULL, "
"year INTEGER NOT NULL, "
"duration INTEGER NOT NULL, "
"director_id INTEGER NOT NULL, "
"FOREIGN KEY (director_id) REFERENCES directors)")
db.commit()
return db
"id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, "
"name TEXT UNIQUE NOT NULL)")
cursor.execute("CREATE TABLE dvds ("
"id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, "
"title TEXT NOT NULL, "
"year INTEGER NOT NULL, "
"duration INTEGER NOT NULL, "
"director_id INTEGER NOT NULL, "
"FOREIGN KEY (director_id) REFERENCES directors)")
db.commit()
return db
The sqlite3.connect() function returns a database object, having opened the database file it is given and created an empty database file if the file did not exist. In view of this, prior to calling sqlite3.connect(), we note whether the database is going to be created from scratch, because if it is,we must create the tables that the program relies on. All queries are executed through a database cursor, available from the database object’s cursor() method.
Notice that both tables are created with an ID field that has an AUTOINCREMENT constraint—this means that SQLite will automatically populate the IDs with unique numbers, so we can leave these fields to SQLite when inserting new records.
Notice that both tables are created with an ID field that has an AUTOINCREMENT constraint—this means that SQLite will automatically populate the IDs with unique numbers, so we can leave these fields to SQLite when inserting new records.
Q1) Explain connectivity of python and SQL.
Q2) Explain different method available with connect object.
Comments
Post a Comment