Python Interview Questions on Python Database Interface

We have compiled most frequently asked Python Interview Questions which will help you with different expertise levels.

Python Interview Questions on Python Database Interface

Question 1:
What is the generic Python “built-in” database module called?
Answer:
DBM

Question 2:
What are some advantages of using DBM?
Answer:
No back-end database is required. The files are portable and work with the pickle and shelve modules to store Python objects.

Question 3:
What does it mean to pickle and unpickle a Python object?
Answer:
Using the pickle module, allows the writing of Python objects to a file. Unpickling is the reverse, reading a file containing a pickled Python object and re-instantiating it in the running program.

Question 4:
What is the difference between the pickle and cPickle Python modules?
Answer:
cPickle is much faster, but it cannot be subclassed like pickle can.

Question 5:
What is the shelve module, and how is it different from pickle?
Answer:
Shelving an object is a higher-level operation that pickles, in that the shelve module provides its own open method and pickles objects behind the scene. Shelve also allows the storage of more complex Python objects.

Question 6:
Illustrate creating a DBM file.
Answer:
import anydbm
myDB = anydbm.open(“newDBFile.dbm”, ‘n’)

Question 7:
Illustrate writing a list to a DBM file.
Answer:
import anydbm
my List = [“This”, “That”, “Something Else”]
myDB = anydbm.open(“newDBFile.dbm”, ‘w’)
mylndex = 0
for myltem in myList:
myDB[my!tem] = myList[myIndex]
mylndex += 1 myDB.close( )

Question 8:
Illustrate pickling a list.
Answer:
import cPickle .
myList = [“This”, “That”, “Something Else”]
myFile = open(“myPickleFile.dat”, “w”)
myPickler = cPickle.Pickler(myFile)
myPickler.dump(myList)
myFile.close( )

Question 9:
Illustrate shelving a dictionary.
Answer:
import shelve
myDict = {“name”: “John Doe”, “Address” : “1234 Main street”}
my Shelf = shelve.open(” my ShelveFile.dat”, “n”)
my Shelfl” Dictionary”] = myDict
my Shelf. close( )

Question 10:
Illustrate retrieving a dictionary from a shelf file Answer:
import shelve
myShelf= shelve.open(” my ShelveFile.dat”, “r”)
myDict = my Shelfl “Dictionary”]
myShelf.close( )

Question 11:
What Python module is used to work with a MySQL database?
Answer:
MySQLdb.

Question 12:
Illustrate connecting to a MySQL database on the local host, with a username and a password.
Answer:
import MySQLdb
myDB = MySQLdb.connect(host=”127.0.0.1″, user=”username”, passwd=”password”)

Question 13:
What is a MySQLdb cursor?
Answer:
It is a handle that lets you send SQL commands to MySQL and retrieve the result.

Question 14:
Illustrate creating a cursor, then sending a select
command to MySQL
Answer:
import MySQLdb
myDB = MySQLdb.connect(host=” 127.0.0.1″, usem”username”,
passwd= “password “)
my Cursor = myDB.cursorO
myCursor.execute(” select * from tablename”)
myResults = myCursor.fetchall( )

Question 15:
How are databases and tables created in MySQL from Python?
Answer:
Using a database connection and cursor to execute the appropriate CREATE DATABASE and CREATE TABLE SQL commands.

Question 6:
How is the currently selected database name retrieved?
Answer:
Using a database connection and cursor to execute a SELECT DATABASE() SQL command.

Question 17:
What is the .fetchall( ) method of the cursor object?
Answer:
It is used to retrieve all of the results of the executed SQL command. It returns a series of one or more lists depending on the results available.

Question 18:
Illustrate adding a row to a MySQL database
Answer:
import MySQLdb
myDB = MySQLdb.connect(host=”127.0.0.1”, user=”username”, passwd= “password “)
myCursor = myDB.cursor( )
my Cursor. execute(” INSERT INTO tablename VALUES ‘Vail’, ‘Val2’, ‘etc.'”)
myDB. commit( )

Question 19:
What does the .commit method of the database object do?
Answer:
It flushes the pending request buffer and ensures that the transactions are all written to disk.

Question 20:
Illustrate retrieving a list of available tables in a MySQL database.
Answer: „
import MySQLdb
myDB = MySQLdb.connect(host=” 127.0.0.1″, user=”username”, passwd= “password “)
myCursor = myDB.cursor ( )
myCursor.executeC’SHOW TABLES “)
myResults = myCursor.fetchall( )

Selenium Grid Tutorial: Hub & Node (with Example) | What is Selenium Grid?

Selenium Python – Selenium-Grid

Parallel execution of tests is made possible through Seleniumthrough its Grid component. We come across scenarios like executing tests for cross-browser verification or executing a huge test suite by splitting it into smaller suites in parallel to save time. For all these, the Grid component is useful and effective as it allows parallel test execution.

Structure

  • Selenium-Grid
  • Creating hub
  • Creating nodes
  • Executing tests in parallel

Objective

In this chapter, we learn how to s tup Selenium-Grid in our local system. We understand what is a hub and a node, and how we set them up. In this chapter, we will set up a hub that will act as a central server and receive requests. We will have a Chrome node and a
Firefox node to complete the Grid for execution.

Selenium-Grid

An important component of Selenium is the Selenium-Grid. It allows us to run our tests in parallel, which helps in saving time and cost. To set up the Selenium-Grid, we need to first download the Selenium standalone server from: https://www.seleniumhq.org/download/
After we have downloaded the server JAR file, we will store it in a folder. This JAR file can now be invoked in two different modes to set up the Grid:

  • The hub
  • The node

A hub is a central server that receives the request to execute the test. It will send the test to the node in the Grid which matches the description in the test. While anode is a machine and browser combination where the actual execution of the test takes place.
Let us see a Grid setup, with the help of the following diagram:

 

Selenium Python - Selenium-Grid chapter 12 img 1

In the above diagram, we can see that the hub will receive the test execution request, which will get passed on to the matching node for actual execution. As the test is executed at the node, the result is passed back to the hub. The information, about the browser, and the operating system on which the test is to be executed, is present in the test script which the hub receives. Then the test commands are sent to the matched node for the actual execution.

We will now set up a Grid with one hub and two nodes—one for Chrome and another for Firefox. Let us see the commands for it.

Setting up the hub

To set up the hub, we need to open the Command Prompt window and go to the folder where our standalone JAR file is present. There we need to type the following command:

D:\work\jarfile>java . jan selenium.server-standalone-3.141.59.jan-role hub

In the above command, we are executing the standalone JAR file using the role flag that uses the value hub. So it will execute the server in the hub mode. By default, it will start the hub on port 4444.
If we want to change the port, we can use the – port flag and provide a value to it. For example:

D:\work\jarfile>java . jan selenium-server-standalone-3.141.59.jan-role hub-port 6666

If you are working with a different Selenium standalone server version, the version number will change for you in here.
Once the hub has started, you can verify it using the following steps:

  1. Open a browser.
  2. Type this URL:
    http://localhost:4444/grid/console
  3. If all is working fine, you should be able to see the following:

 

Selenium Python - Selenium-Grid chapter 12 img 4

At the Command Prompt, we will see the following:

D:\work\Jarfiles>java - jar selenium-server-standalone-3.141.59.jar - role hub
17:17:17.540 INFO [GridLaunchV3.parse] - selenium server version: 3.141.59, revision: e82
be7d358
17:17:17.540 INFO [GridLaunchV3.lambda$buildLaunchers$5] - Launching selenium Grid hub on port: 4444
2019-05-27 17:17:18.139: INFO: : main : Logging initialized @1218ms to org.seleniumhq.jetty9.util.log.stdErrLog
17:17:18.576 INFO [Hub.start] - selenium Grid hub is up and running
17:17:18.576 INFO [Hub.start] - Nodes should register to http://192.168.0.109:4444/grid/register/
17:17:18.576 INFO [Hub.start] - Clients should connect to http://192.168.0.109:4444/wb/hub

Setting a Chrome node on a Windows machine

To set a Chrome node on a Windows machine, we will have to download the Selenium standalone server on that machine and execute it in the node mode. To do this, we will have to execute this command:

D:\work\JarFiles>java - Dwebdriver.chrome.driver="D:\work\JarFiles\resource\chromedriver.exe" - jar selenium-server-standalone-3.141.59-Jar-role - hub http://localhost:4444/grid/register - browser "browsername-chrome" - post5556

In this command, we are providing a path to the Chrome driver as per the location in our system, that is, Dwebdriver. chrome. driver=”D: \ WORK\DarFiles\nesource\chromedriver.exe”. Then we set the role flag to be the node. Then we provide the hub flag, where we point to the hub location: http://localhost:4444/grid/register. We set the browser flag to chrome and the port flag to 5556.
At the script level, we will be making some changes in the setup method, which is as follows:

def setup(self):
    self.driver = webdriver.Remote(
         command_executor="http://Localhost:4444/wd/hub",
         desired_capabilities={
              "browserName":"chrome",
     })
   self.base_url = "http://practice.bpbonline.com/catalog/index.php"

Here, we create an instance of the remote WebDriver, pass the details of the hub and in desired capabilities variable, we pass information for the browser, on which we want to execute our test, in this case, Chrome. We will discuss more desired capabilities a little later in the chapter. So when we execute the script, the commands are sent to the hub. It fetches the information to find the node on which the actual test is to be executed and sends the commands to it. In this case, it will send the commands to a node that is registered with a Chrome browser.

Please take a look at the following screenshot where we have entered all the details:

 

Selenium Python - Selenium-Grid chapter 12 img 8

Setting a Firefox node on a Windows machine

To set a Firefox node on a Windows machine, we will have to download the Selenium standalone server on that machine and execute it in the node mode. To do this we will have to execute this command:

D:\work\JarFiles>java - Dwebdriver.chrome.driver="D:\work\JarFiles\resource\getkodriver.exe" - jar selenium-server-standalone-3.141.59-Jar-role - hub http://localhost:4444/grid/register - browser "browsername-firebox" - post5557

In this command, we are providing the path to the gecko driver as per the location in our system: Dwebdriver. gecko. driver=”D: \WORK\ DarFiles\resource\geckodriver. exe”. Then we set the role flag to be a node. Then we provide the hub flag, where we point to the hub location: http: //localhost:4444/grid/register. We set the browser flag to firefox and the port flag to 5557.

At the script level, we will be making some changes in the setup method:

def setup(self):
    self.driver = webdriver.Remote(
         command_executor="http://Localhost:4444/wd/hub",
         desired_capabilities={
               "browserName":"firebox",
})
self.base_url = "http://practice.bpbonline.com/catalog/index.php"

Here, we create an instance of the remote WebDriver, pass the details of the hub and in desired capabilities variable, we pass information for the browser, on which we want to execute our test, in this case, the Firefox browser. We will discuss more desired capabilities a little later in the chapter. So when we execute the script, the commands are sent to the hub. It fetches the information to find the node on which the actual test is to be executed and sends the commands to it. In this case, it will send the commands to a node that is registered with a Firefox browser:

 

Selenium Python - Selenium-Grid chapter 12 img 11

Executing tests in parallel

To execute the tests in parallel, on the Chrome and Firefox browser together, we will initiate them. So the test execution commands will reach the hub, and the hub will direct them to their respective nodes. In this case, the tests are directed to Chrome browser machine, and Firefox browser machine:

 

Selenium Python - Selenium-Grid chapter 12 img 12

Desired capabilities

To set the test environment settings at the browser level, we have some properties associated with each browser that we can set. So at the time of execution, whenever the browser instance will be invoked it will be set with those browser properties. Internet Explorer, Chrome, and Firefox come with their own key-value pair.
In our above test scripts, we have used desired capabilities, to set the browser key-value to Firefox or Chrome, respectively:

desired_capabilities={
“browserName”: “firefox”.,}

desired_capabilities={
“browserName”: “chrome”,}

Conclusion

In this chapter, we understood the usage of an important component of Selenium which allows us to run tests in parallel. We understood how we establish the Selenium server in hub mode and node mode, and instantiate the remote Web’’)river at the script level to run our tests in the Grid.

Related Articles:

Locators in Selenium- How To Locate Elements On Web-page?

Selenium Python – Locators in Selenium

Introduction

When we try to automate a web application, there are two important steps to consider. One is to identify the object uniquely on which we would want to perform the action. The second is to perform the action on the identified object. To identify the object uniquely on the web page, Selenium provides us with some locator strategies. In this chapter, we will discuss and explore them.

Structure

  • What is a locator?
  • Different types of locators
  • Where are locators used?

Objective

When working with open source technology like Selenium, it is crucial for us to understand that as end-user what strategy Selenium uses to identify an object on a page. As we are going to write scripts to automate applications at hand, we will have to provide object information using one of the locator strategies which Selenium will use to identify the object on the page so that the required action can be performed on it.

What is a locator?

Locator is a technique to identify the object on a page uniquely by using different identification methods. Once the object is identified, the action can be performed on it. Selenium provides us with the following locator techniques to identify the object on the page:

  • ID
  • NAME
  • XPATH
  • CSS
  • DOM
  • LINKTEXT
  • PARTIALLINKTEXT

To understand the different strategies of the locators, which Selenium uses to identify the object on the page, we will take the example of an HTML code snippet from our web application: http://practice.bpbonline.com/catalog/index.php my account page, which we see,
when we click the My Account link of the home page. The idea is to explore the HTML code associated with the E-mail Address field,
Password field, and the Sign In button. So let us have a look at it:

Selenium Python - Locators in Selenium chapter 3 img 1

If we right-click the HTML page and select View page source. Refer to the following screenshot:

Selenium Python - Locators in Selenium chapter 3 img 2

We can see the HTML content associated with the web page. It is displayed as follows:

 

    <form name"login" action="http://practice.bpbonline.com/catalog/login.php"action=process" method="post"><input type="hidden"
name="formid" value="46fedd827e8b83241e4cebff3c6046ae"/>
   <table border="0" cellspecing="e" cellpadding="2" width"100%">
   <tr>
    <td class="filedkey">E-mail Address:</td>
    <td class="filedvalue"><input type="text" name="email..address"/><td>
 </tr>
 <tr>
   <td class="filedkey"password:</td>
   <td class="filedvalue"><input type="password" name="password" maxlength="40"></td>
</tr>
</table>

<P><a href="http://practice.bpbonline.com/catalog/password_forgotten.php">password forgotten? click here.</a></p>

<p> align="right"><span class="tdblink"><button id="tdb1" type="submit">sign In</button></span><script type="text/javascript $("#tdb1").button({icons:{primary::ui.icon-key"}}).addclass("ui-priority-primary").parent( ).removeClass("tdblink"):
</script></p>

</form>

As we have seen, the above HTML content is associated with three objects – username and password fields, and sign-in button, let us try to understand the different locator strategies Selenium can use to identify them so that an action can be performed on them as our automation script executes. So, the following is explained below:

• ID: The ID locator is fetched from the ID attribute of an HTML element. If the HTML element of interest has an ID attribute, we use it to identify the object uniquely. The example of this is the Sign In button, which has the id = tdblxbutton id=”tdbl” type=”submit”>Sign In</button>

• NAME: This attribute is fetched from the NAME attribute of the HTML element. The data associated with this property of the HTML element is used to identify the object uniquely on the web page. Examples of this property username, and password fields:
<input type=”text” name=”email_address” />
<input type=”password” name=”password” maxlength=”40″ />

• XPATH: The path traversed to reach the node of interest in an XML document is known as XPATH. To create the XPATH locator for an element, we look at an HTML document as if it is an XML document, and then traverse the path to reach it. The XPATH can either be a relative or an absolute one:

  • A relative XPATH will be in relation to a landmark node. A node that has a strong identifier like an ID or NAME. It uses / / in its path creation. Example: // input[@name=”email_addpess”]
  • An absolute XPATH starts from the root node HTML. It uses a single slash /. It is more prone to changes if the document structure undergoes changes during the development of the application, so it is generally avoided.
    Example:/HTML/body/div[2] / f orm[0] /table/ tbody/tr[2]/input

• CSS: It stands for Cascading Style Sheets. We can use this as well to identify the objects uniquely on the web page. The syntax is as follows:

  • If the HTML of the object has an ID attribute then, css=#ID, for example, css=#tdbl
  • Else, css=HTMLtag[prop=value], for example, css=input [namie^ email_address’ ]

• DOM: It stands for Document Object Model. It allows object identification by using the HTML tag name associated with the object.

• LINKTEXT: Generally, whenever we encounter a link in the application we can use it to identify the object on the page. For example, the My Account link can be identified using the same link text as seen on the web page

• PARTIAL LINK TEXT: We can also use a subpart of a complete text of the link to identify it on the web page and then perform actions on it.

It is important to use an appropriate locator to identify the object on the page, which is unique, helps in quick identification of the object, and is robust to application changes during the development process. Generally, if the object HTML has IDor NAME we use it to identify the object on the page. Then we use XPATH, followed by CSS and the last option is DOM. If it is a link, we always use LINKTEXT or PARTIAL LINK TEXT to identify the element on the page. So ideally, this should be the approach we need to take.

Conclusion

In this chapter we discussed the concept of locators; we understood its various types. We also saw where and how they would be needed. These locator strategies are standard in Selenium. They are not going to get modified in any version, and have been consistent with old versions as well. Lastly, we need to keep in mind that the locator strategy we are choosing to identify the object has to be robust and help to locate the object quickly on the page. In the next chapter, we will learn the steps to set up Selenium and Eclipse IDE on Windows OS.

Related Articles:

Data Persistence Python Database DBAPI

Python Data Persistence – Python DB-API

The previous chapter was an overview of SQL fundamentals with the help of the SQLite database. However, as mentioned therein short, there are several equally popular RDBMS in use worldwide. Some of them are open-source and others for enterprise use. Although all of them use SQL underneath them, there are a lot of differences in the implementation of SQL standards by each of them. This also reflected in Python’s interface modules written by individuals for interaction with databases. Since each module defined its own functionality for interaction with the respective database product, its major fallout is a lack of compatibility. If for some reason, a user is required to switch to a different database product, almost the entire code that handles the back-end processing had to be rewritten.

To find a solution for this incompatibility issue, a ‘Special Interest Group’ was formed in 1996. This group (db-sig) recommended a set of specifications by raising ‘Python Enhancement Proposal (PEP 248)’ for a consistent interface to relational databases known as DB-API. The specifications have since been modified by subsequent enhancement proposals (PEP 249). Its recommendations are called DB-API Version 2.0.

As various Python database modules have been made DB-API compliant, most of the functionality required to interact with any database is uniform. Hence, even if the database itself is switched, only a couple of changes in the code should be sufficient.

Oracle, the world’s most popular relational database can be interface with Python with more than one module. cx_OracIe is a Python extension module that enables access to Oracle Database with some features of its owrt in addition to DB-API. It can be used with Oracle 11.2,12.1, and 12.2, and 18.3 client libraries. There is pyodbc module that acts as a Python- ODBC bridge driver that can be used for Python-Oracle interaction as well.

To use Microsoft’s SQL Server database with Python also, there are a couple of alternatives. The pymysql module is there in addition to pyodbc module.

As far as PostgreSQL is concerned, psycopg2 module is the most popular PostgreSQL adapter for the Python programming language.
MySQL is also a very popular relational database, especially in the open-source domain. MySQL Connector/Python is a standardized database driver provided by MySQL itself. There is a mysqldb module for the Python interface but is not still compatible with Python 3.x. You can use pymysql module as a drop-in replacement for mysqldb module while using the Python 3.x version.

As mentioned in the previous chapter, Python’s standard library consists of the sqlite3 module which is a DB-AP1 compliant module for handling the SQLite database through Python program. While other modules mentioned above should be installed in the current Python installation – either by pip utility or by using a customized installer (as in case of MySQL Connector/ Python), the sqlite3 module needs no such installation.

sqlite3 Module

SQLite is often used as a prototyping tool for larger databases. The fact that SQLite is extremely lightweight and doesn’t require any server but still is a fully-featured implementation of SQL, it is a common choice in the developmental stage of an application and is eventually replaced by enterprise RDBMS products such as Oracle. Likewise, you can think of the sqlite3 module as a prototype DB-API module. We shall explore the DB-API specifications with the help of the sqlite3 module. We will soon discover how easy it is to switch to other databases just by modifying a couple of statements.

Let us start by importing the sqlite3 module. Note that, its target SQLite library version may be different from the SQLite binary downloaded by you as shown in the previous chapter.

Example

>>> import sqlite3 
>>> sqlite3.sqlite_version 
'3.21..0'

Connection Object

The all-important connection object is set up by the module level connect ( ) function. The first positional argument to this function is a string representing path (relative or absolute) to an SQLite database file. The function returns a connection object referring to the database file – either existing or new. Assuming that, ‘newdb. sqlite’ doesn’t already exist, the following statement opens it:

>>> conn=sqlite3.connect('newdb.sqlite') 
>>> type(conn)
<class 'sqlite3.Connection'>

As we’ve seen in the previous chapter, SQLite supports an in-memory database. To open the same programmatically use “:memory: ” as its path.

>>> conn=sqlite3.connect(":memory:")

The connection object has access to various methods in the connection class. One of them is a cursor () method that returns a cursor object, which we shall know in the next section. Transaction control is achieved by commit () and rollback () methods of the connection object. Connection class has important methods to define custom functions and aggregates to be used in SQL queries. Later in this chapter create_ function () and create_aggregate () methods are explained.

cursor Object

After opening the database, the next step is to get a cursor object from it which is essential to perform any operation on the database. A database cursor enables traversal over the records in a database. It facilitates CRUD operations on a table. The database cursor can be considered similar to the concept of an iterator. The cursor ( ) method on the connection object returns the cursor object.

>>> cur=conn.cursor()
>>> type(cur)
<class 'sqlite3.Cursor'>

Once we get hold of the cursor object, we can perform all SQL query operations, primarily with the help of its execute () method. This method needs a strong argument which must be a valid SQL statement. String argument having incorrect SQL statement raises exceptions as defined in the sqlite3 module. Hence, it is recommended that a standard exception handling mechanism is used.

Updating Data

It is fairly straightforward to programmatically perform update operations on a table in the SQLite database. As pointed out in the previous chapter, the update query is normally a conditional operation unless you intend to update all rows of a certain table. Hence a parameterized query is ideal for this purpose.

Following script (1 updateqry.py!) asks the user to input the name of the product and new price and performs update operation accordingly.

Example

import sqlite3
conn=sqlite3.connect(1mydb.sqlite')
nm=input(1 enter name of product:')
p=int(input('new price:'))
qry='update Products set price=? where name=?'
cur=conn.cursor()
try;
cur.execute(qry, (p,nm))
print ('record updated')
conn.commit()
except:
print ('error in update operation')
conn.rollback()
conn.close( )

Run the above script from a command prompt:

E:\python37>python updateqry.py 
enter the name of the product: TV 
new price:32000 
record updated

The SQLite console can confirm the above action.

sqlite> select * from products where name='TV';
ProductID    Name          Price
------------  --------      --------
2                  TV            32000

Python Data Persistence – Creating Table

Python Data Persistence – Creating Table

We shall now add a table in our newly created ‘mydb.sqlite’ database. In the following script, the first two steps are as illustrated above – setting up connection and cursor objects. Next, we call execute () method of cursor object, giving it a string with CREATE TABLE statement inside. We shall use the same ‘Products’ table that we created in the previous chapter. Save the following script as ‘createqry.py’ and execute it.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite')
cur=conn.cursor()
qry=' ' '
CREATE TABLE Products (
ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
Name     TEXT (20) ,
Price    INTEGER
) ;
' ' '
try:
        cur.execute(qry)
        print ('Table created successfully')
except:
        print ('error in creating table')
conn.close ( )

Products table will be created in our database. We can verify by listing out tables in this database in the SQLite console, as we did in the previous chapter.

sqlite> .open mydb.sqlite 
sqlite> .tables 
Products

Let us also create ‘Customers’ and ‘Invoices’ tables with the same structure as used in the previous chapter. Here, we use a convenience method to execute the script ( ) that is defined in the cursor class. With its help, it is possible to execute multiple execute statements at once.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite')
cur=conn.cursor( )
qry= ' ' '
CREATE TABLE Customers (
          CustID INTEGER PRIMARY KEY AUTOINCREMENT,
          Name           TEXT (20),
          GSTIN           TEXT (15)
   ) ;
CREATE TABLE Invoices (
         InvID             INTEGER             PRIMARY KEY
AUTOINCREMENT,
       CustID               TEXT                REFERENCES Customers
(CustID),
      ProductID        INTEGER            REFERENCES Products
(ProductID),
     Quantity         INTEGER (5)
) ;
' ' '
try:
      cur.executescript(qry)
      print ('Table created successfully')
except:
     print ('error in creating table')
conn.close( )

 

You can go back to the SQLite console and refresh the table list to confirm that all three tables are created in mydb.sqlite database.

Python Data Persistence – Inserting Rows

Python Data Persistence – Inserting Rows

The next step is to insert rows in the tables we have just created. We know the syntax of the INSERT statement and we have used it in console mode of sQLite in the previous chapter. To do it programmatically, declare an INSERT query string and use it as an argument to execute () method.

As noted in the previous chapter, the SQLite database engine is in auto-commit mode by default. To have better transaction control, we should commit the query operation only if it doesn’t encounter any exceptions.
Following code inserts a record in the Products table.

Example

import sqlite3
conn=sqlite3 . connect (' mydb. sqlite ' )
cur=conn.cursor( )
qry="insert into Products values (1,'Laptop', 25000);"
try: cur.execute(qry)
conn.commit( )
print ('Record inserted successfully')
except print ( 'error in insert conn.rollback ( ) operation')
conn.close ( )

In many cases, you may want to accept user input for field values to be inserted. You can form a query string by substituting the user inputs in the string with the help of string formatting technique, as shown below:

Example

>>> id=input('enter ProductID:1) enter ProductID:2
>>> nm=input('enter product name:') enter product name:Hard Disk
>>> p=int(input('enter price:')) enter price:5000
>>> qry="insert into products values ({ }, { }, { });"• format(id, nm, p)
> > > qry
'insert into products values (2, Hard Disk, 5000);'

You can very well use this query string as an argument of execute () method. However, query operations using Python’s string formatting is insecure as it makes the program vulnerable to SQL injection attacks. Hence., DB-API recommends the use of parameter substitution technique.

The execute () method of the sqlite3 module supports the use of question mark symbols (‘?’) as placeholders as well as named placeholders in which case the dictionary object of name and value pairs is given as the second argument to execute( ) method.

>>> #using '?' place holder
>>> cur.execute("insert into products values (?,?,?)",(id,nra,p))
>>> #using named place holder
>>> cur.execute("insert into products values
( :id, :nm, :p) ", {"id":id, "nm":nm, "p":p})

There is another useful variant of execute () method in the sqlite3 module. The first argument to execute the many( ) method is a query string with placeholders, and the second argument is a list of parameter sequences. The query gets executed for each sequence (itself may be a list or tuple) in the list. Following script (‘insertqry.py ) uses execute many () method to insert records in the Products table, as displayed in the previous chapter.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite') cur=conn.cursor()
qry="insert into Products values (?,?,?)" pricelist=[(1, 'Laptop1 ,25000), (2, 'TV 1,40000) ,
(3,'Router',2000),(4,'Scanner',5000),
(5,'Printer',9000), (6,'Mobile',15000)]
try:
cur.executemany(qry, pricelist) conn.commit( )
print ('Records inserted successfully')
except:
print ('error in insert operation') conn.rollback( ) conn.close( )

You can check successful insertion on the SQLite console.

sqlite> select * from products;
ProductID          Name            Price
———-          ———        ——-

1                  Laptop        25000
2                    TV            40000
3                  Router        2000
4                 Scanner       5000
5                  Printer        9000
6                   Mobile       15000

Python Data Persistence – Deleting Rows

Python Data Persistence – Deleting Rows

Similarly, we can programmatically delete a certain record from a table. This operation is also more often than not conditional. Hence, the ‘WHERE’ clause appears in the parameterized DELETE query. Following script (deleteqry.py) deletes row belonging to the user-specified product.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite')
nm=input('enter product to delete:1)
qry='delete from Products where name=?'
cur=conn.cursor( )
try:
cur.execute(qry, (nm,))
print ('record deleted')
conn.commit ( )
except:
print ('error in delete operation')
conn.rollback( )
conn.close( )

To delete the user input product, run the above script from the command prompt.

E:\python37>python deleteqry.py 
enter product to delete: Printer 
record deleted

Execute select query in SQLite console to verify that deleted product doesn’t appear in the list.

sqlite> select * from products;
ProductID      Name         Price
---------        ---------      --------
    1                Laptop        25000
    2                   TV           32000
    3              Router           2000
   4               Scanner        5000
   6               Mobile          15000

The next section explains how to programmatically retrieve records from a table.

Python Data Persistence – ResultSet Object

Python Data Persistence – ResultSet Object

We need to call execute ( ) method on cursor object with a SELECT query string as its argument and a query result set is built which is similar to an iterator of rows returned in response to the query. The module provides the following methods to traverse the result set:

fenchone (): Next row in the query result set is retrieved and returned in the form of a tuple. The method returns None if there are no more rows to be fetched,

fetchall (): This method returns a list of all available rows in the result set. It contains a sequence corresponding to each row. You can employ a regular for loop to traverse the rows, as follows:

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite')
cur=conn.cursor()
qry="select * from Products;"
cur.execute(qry)
rows=cur.fetchall()
for row in rows:
        print (row) 
conn.close()

Run the above code (‘ selectqry. py’) from the command prompt.

E:\python37>python selectqry.py
(1,  'Laptop', 25000)
(2, ’TV', 40000)
(3, 'Router', 2000)
(4, 'Scanner' , 5000)
(5, 'Printer' , 9000)
(6, 'Mobile', 15000)

The fact that execute () method runs a parameterized query can be used to good effect to search for a certain condition in the table. The following code snippet accepts the product’s name as input and displays its price.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite') nm=input ('Enter name of product:') cur=conn.cursor( )
qry="select * from Products where name=?";
cur.execute(qry, (nm,))
row=cur.fetchone( )
print (row)
conn.close( )

When the above script is run from the command prompt, then it above script shows the following output:

E:\python37>python selecttqry.py 
Enter name of product:TV 
(2, 'TV', 40000)

Individual items in the ‘row’ tuple can be accessed by index. The row can also be unpacked in separate variables as under:

Example

row=cur fetchone( ) 
print ( ' ID: ', row [ 0] , 'Name: 1, row[1], 'price:', row [2 ] ) 
id, nm, p=row 
print ( ID:', id, 'Name:', nm, 1 price: ' , p)

Python Data Persistence – User Defined Functions

Python Data Persistence – User Defined Functions

The SQLite database engine by itself is equipped with several built-in functions for finding string length, changing case to upper/lower case, rounding a number, etc. However, it doesn’t have the provision to define a new function with customized functionality. The SQLite module, however, has the provision to do so’ with the help of the create_function () method available to the connection object.

In the following example, we try to represent the price of the product rounded to thousands and attach a ‘k‘ alphabet to it. In other words, 40000 is represented by 40k. First, we define a regular Python function (myfunction) that accepts a number, divides it by 1000, and appends ’k’ to its string conversion. The create_f unction () method has the following prototype:

Example

create_function(SQLFunction, parameters,PythonFunction)

In other words, it assigns a name to the Python function(a function in our case) that can be used as a function in the SQL query.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite') def myfunction(num):
return str(round(num/1000))+"k" conn.create_function('priceinK' , 1,myfunction) cur=conn.cursor()
qry="select name, priceinK(price) from products;"
cur.execute(qry)
rows=cur.fetchall( )
print (rows)
conn.close ( )

Output of above code snippet is:

Example

[('Laptop', '25k'), ('TV', '40k'), ('Router', '2k'),
('Scanner', '5k'), ('Printer', '9k'), ('Mobile',
'15k') ]

SQLite also has several built-in aggregate functions such as SUM, AVG, COUNT, etc. to be applied to one or more columns in a table. For example, the query selects SUM (price) from Products’returns sum of values in the price column of all rows. Using the create_aggregate() method defined to be used with the cursor object, it is possible to define a customized aggregate function.

In the following script, a regular Python class named my class s is defined and it contains a step( ) method which is mandatory for the user-defined aggregate function. The step() method increments the count for each product name ending with ‘r\ The create_aggregate () method attaches a name that can be used in the SQL query. When this aggregate function is called, the value returned by finalize () method of the class is in fact the result of the SELECT statement.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite')
class myclass:
def__init__(self):
self.count=0
def step(self, string):
if string.endswith('r'):
self.count=self.count+1
def finalize (self) :
return self.count
conn.create_aggregate(1MyF',1,myclass)
cur=conn.cursor()
qry="select MyF(name) from products;"
cur.execute(qry)
row=cur.fetchone()
print ('number of products with name ending with ' r1 : ' , (row) [0] )
conn.close()

The output of the above script is:

Example

number of products with the name ending with : 3