UNIT-3 Python Interaction with SQLite PDF

UNIT-3 Python interaction with SQLite Unit-3 Python Interaction with SQLite Module: A module is a file containing Python definitions and statements. A module can define functions, classes, and variables. A module can also include runnable code. G...

UNIT-3 Python interaction with SQLite Unit-3 Python Interaction with SQLite Module: A module is a file containing Python definitions and statements. A module can define functions, classes, and variables. A module can also include runnable code. Grouping related code into a module makes the code easier to understand and use. It also makes the code logically organized. How to create module? Step-1: Open a notepad file and save it’s as First.py. Write the following command to open a notepad file from command prompt. Notepad First.py Step-2: Write the following python code into First.py file. def display(): print (“Hello!!!!”) display() #calling statement Step-3: Execute the python code. Python First.py Example: Page 1  UNIT-3 Python interaction with SQLite Output: Import statement: We can use any Python source file as a module by executing an import statement in some other Python source file. When the interpreter encounters an import statement, it imports the module if the module is present in the search path. A search path is a list of directories that the interpreter searches for importing a module. Syntax: import module1[, module2[,... moduleN] For example, To import the module p1.py, we need to put the following command at the top of the script: // import module p1 Import p1 Page 2  UNIT-3 Python interaction with SQLite // you can call defined function that module contains p1.add(4,5) p1.sub(10,6) Output: You can switch to python screen by typing python in command prompt. The From…import Statement: Python's from statement lets you import specific attributes from a module into the current namespace. The from...import has the following syntax – from modname import name1[, name2[,... nameN]] Example: from math import pi print("The value of pi is", pi) It will import only pi from math module Page 3  UNIT-3 Python interaction with SQLite Example: from math import sqrt, factorial print(sqrt(16)) print(factorial(6)) The from...import * Statement It is also possible to import all names from a module into the current namespace by using the following import statement − from modname import * This provides an easy way to import all the items from a module into the current namespace; however, this statement should be used sparingly. Example: From math import * Page 4  UNIT-3 Python interaction with SQLite Print(sqrt(25)) Print(factorial(4)) Print(pi) Locating Module: When you import a module, the Python interpreter searches for the module in the following sequences −  The current directory.  If the module isn't found, Python then searches each directory in the shell variable PYTHONPATH.  If all else fails, Python checks the default path. On UNIX, this default path is normally /usr/local/lib/python/. The module search path is stored in the system module sys as the sys.path variable. The sys.path variable contains the current directory, PYTHONPATH, and the installation-dependent default. The PYTHONPATH Variable The PYTHONPATH is an environment variable, consisting of a list of directories. The syntax of PYTHONPATH is the same as that of the shell variable PATH. Page 5  UNIT-3 Python interaction with SQLite Here is a typical PYTHONPATH from a Windows system – set PYTHONPATH = c:\python20\lib; And here is a typical PYTHONPATH from a UNIX system – set PYTHONPATH = /usr/local/lib/python Namespaces and Scoping Name (also called identifier) is simply a name given to objects. Everything in Python is an object. Name is a way to access the underlying object. Example, When we do the assignment a = 2, 2 is an object stored in memory and a is the name associate it with. We can get the address (in RAM) of some object through the built-in function id(). a =2 print(id(2)) print(id(a)) Here, both refer to the same object 2, so they have the same id(). Example, # Note: You may get different values for the id Page 6  UNIT-3 Python interaction with SQLite a=2 print('id(a) =', id(a)) a = a+1 print('id(a) =', id(a)) print('id(3) =', id(3)) b=2 print('id(b) =', id(b)) print('id(2) =', id(2)) Memory allocation of variable Page 7  UNIT-3 Python interaction with SQLite A namespace containing all the built-in names is created when we start the Python interpreter and exists as long as the interpreter runs. Modules can have various functions and classes. A local namespace is created when a function is called, which has all the names defined in it. Similar is the case with class. Although there are various unique namespaces defined, we may not be able to access all of them from every part of the program. When a reference is made inside a function, the name is searched in the local namespace, then in the global namespace and finally in the built-in namespace. If there is a function inside another function, a new scope is nested inside the local scope. Example: def outer_function(): b = 20 def inner_func(): c = 30 a = 10 Here, the variable a is in the global namespace. Variable b is in the local namespace of outer_function() and c is in the nested local namespace of inner_function(). When we are in inner_function(), c is local to us, b is nonlocal and a is global. We can read as well as assign new values to c but can only read b and a from inner_function(). If we try to assign as a value to b, a new variable b is created in the local namespace which is different than the nonlocal b. The same thing happens when we assign a value to a. Example, Page 8  UNIT-3 Python interaction with SQLite def outer_function(): a = 20 def inner_function(): a = 30 print('a =', a) inner_function() print('a =', a) a = 10 outer_function() print('a =', a) Output: a = 30 a = 20 a = 10 A Python statement can access variables in a local namespace and in the global namespace. If a local and a global variable have the same name, the local variable shadows the global variable. Each function has its own local namespace. Class methods follow the same scoping rule as ordinary functions. Python makes educated guesses on whether variables are local or global. It assumes that any variable assigned a value in a function is local. Therefore, in order to assign a value to a global variable within a function, you must first use the global statement. Page 9  UNIT-3 Python interaction with SQLite The statement global VarName tells Python that VarName is a global variable. Python stops searching the local namespace for the variable. Example: (p2.py) def outer_function(): global a a = 20 def inner_function(): global a a = 30 print('a =', a) inner_function() print('a =', a) a = 10 outer_function() print('a =', a) Output: a = 30 a = 30 a = 30 Here, all references and assignments are to the global a due to the use of keyword global. The dir() function: Page 10  UNIT-3 Python interaction with SQLite The dir() built-in function returns a sorted list of strings containing the names defined by a module. The list contains the names of all the modules, variables and functions that are defined in a module. Following is a simple example − Import math dir(math) Example, Import p2 dir(p2) The locals() and globals() function: The globals() and locals() functions can be used to return the names in the global and local namespaces depending on the location from where they are called. If locals() is called from within a function, it will return all the names that can be accessed locally from that function. Page 11  UNIT-3 Python interaction with SQLite If globals() is called from within a function, it will return all the names that can be accessed globally from that function. Example: module1.py name='s v patel' def sum(x,y): z=x+y print("Local variables list:",locals()) print("Global variable list: ",globals()) return z sum(10,15) The reload() function: When the module is imported into a script, the code in the top-level portion of a module is executed only once. reload() reloads a previously imported module. This is useful if you have edited the module source file using an external editor and want to try out the new version without leaving the Python interpreter. The return value is the module object. Page 12  UNIT-3 Python interaction with SQLite Therefore, if you want to reexecute the top-level code in a module, you can use the reload() function. The reload() function imports a previously imported module again. The syntax of the reload() function is this − reload(module_name) Here, module_name is the name of the module you want to reload and not the string containing the module name. For example, to reload p1 module, do the following – reload(p1) Example: P1.py Output: Now, I have change the p1.py module, but changes of this module does not effect in the output. For that we have to reload that module. Page 13  UNIT-3 Python interaction with SQLite Now, p1.py But result is Same as above. Now, Packages in Python We organize a large number of files in different folders and subfolders based on some criteria, so that we can find and manage them easily. In the same way, a package in Python takes the concept of the modular approach to next logical level. Page 14  UNIT-3 Python interaction with SQLite A module can contain multiple objects, such as classes, functions, etc. A package can contain one or more relevant modules. Physically, a package is actually a folder containing one or more module files. Let's create a package named python_package, using the following steps: Step-1: Create a new folder named C:\MyApp. Step-2: Create an empty init.py file in the ‘python_package’ folder and write the following statements. Step-3: Inside MyApp, create a subfolder with the name 'python_package'. Step-4: With the use of cmd, Create modules greet.py and functions.py with the following code: o greet.py o functions.py o init.py Page 15  UNIT-3 Python interaction with SQLite init.py MyApp Python_package greet.py functions.py Output: Note: ( init.py)  The package folder contains a special file called init.py, which stores the package's content. It serves two purposes: Page 16  UNIT-3 Python interaction with SQLite o The Python interpreter recognizes a folder as the package if it contains init.py file. o init.py exposes specified resources from its modules to be imported. An init.py file makes all functions from the above modules available when this package is imported. Note that init.py is essential for the folder to be recognized by Python as a package. Connecting to database: Use the following steps to connect to SQLite: 1) Import SQLite module: import sqlite3 statement imports the sqlite3 module in the program. Using the classes and methods defined in the sqlite3 module we can communicate with the SQLite database. Import sqlite3 2) Use the connect() method: Use the connect() method of the connector class with the database name. To establish a connection to SQLite, you need to pass the database name you want to connect. If you specify the database file name that already presents on the disk, it will connect to it. But if your specified SQLite database file doesn’t exist, SQLite creates a new database for you. This method returns the SQLite Connection Object if the connection is successful. Con=Sqlite3.connect(“database.db”) Page 17  UNIT-3 Python interaction with SQLite For example, (connect.py) 3) Use the cursor() method Use the cursor() method of a connection class to create a cursor object to execute SQLite command/queries from Python. 4) Use the execute() method The execute() methods run the SQL query and return the result. Con.execute(‘’’ create table/insert into/update/delete statement’’’) When the above program is executed, it will create the developer table in your db1.db and it will display the following messages – Page 18  UNIT-3 Python interaction with SQLite 5) Extract result using fatchall() Use cursor.fetchall() or fetchone() or fetchmany() to read query result. 6) Close the cursor and Connection object use cursor.clsoe() and connection.clsoe() method to close the cursor and SQLite connections after your work completes CREATE Operation: (CONNECT.py) import sqlite3 con=sqlite3.connect('db1.db') print('Open database Sucessfully') con.execute("CREATE TABLE developer(ID INT PRIMARY KEY,NAME TEXT NOT NULL,AGE INT,SALARY REAL)") print("Table created successfully") con.close() INSERT operation: (INSERT.py) import sqlite3 conn=sqlite3.connect("db1.db") Page 19  UNIT-3 Python interaction with SQLite print("Open databse successfully") conn.execute("INSERT INTO DEVELOPER(ID,NAME,AGE,SALARY) VALUES(1,'AMIT',32,25000.00)"); conn.execute("INSERT INTO DEVELOPER(ID,NAME,AGE,SALARY) VALUES(2,'KINJAL',35,15000.00)"); conn.execute("INSERT INTO DEVELOPER(ID,NAME,AGE,SALARY) VALUES(3,'PRIYANK',36,35000.00)"); conn.execute("INSERT INTO DEVELOPER(ID,NAME,AGE,SALARY) VALUES(4,'RAHUL',30,40000.00)"); conn.execute("INSERT INTO DEVELOPER(ID,NAME,AGE,SALARY) VALUES(5,'JAY',33,45000.00)"); conn.commit() print("Records created successfully") conn.close() Output: When the above program is executed, it will create the given records in the developer table and it will display the following two lines − Page 20  UNIT-3 Python interaction with SQLite SELECT Operation: (SELECT.py) import sqlite3 con=sqlite3.connect("db1.db") print("Database open successfully!!!") C=con.execute("SELECT ID,NAME,AGE,SALARY FROM DEVELOPER") X for row in C: print ("ID=",row) print ("NAME=",row) print ("AGE=",row) Page 21  UNIT-3 Python interaction with SQLite print ("SALARY=",row), "\n" print("Operation perfomed successfully") con.close() Output: When the above program is executed, it will produce the following result. UPDATE Operation: (UPDATE.py) Page 22  UNIT-3 Python interaction with SQLite Following Python code shows how to use UPDATE statement to update any record and then fetch and display the updated records from the developer table. import sqlite3 con=sqlite3.connect("db1.db") print("Database open Successfully!!!") con.execute("UPDATE DEVELOPER SET SALARY=60000.00 WHERE ID=1") con.commit() print("Total number of rows updated:,con.total_changes") c=con.execute("SELECT * FROM DEVELOPER") for row in c: print ("ID=", row) print ("NAME=", row) print ("AGE=",row) print ("SALARY=",row, "\n") print "Operation performed successfully" con.close() Output: When the above program is executed, it will produce the following result. Page 23  UNIT-3 Python interaction with SQLite DELETE Operation: (DELETE.py) import sqlite3 con=sqlite3.connect("db1.db") print " Database opened successfully!!!" con.execute("DELETE FROM DEVELOPER WHERE ID=2") con.commit() print "Total number of record deleted:",con.total_changes Page 24  UNIT-3 Python interaction with SQLite c=con.execute("SELECT * FROM DEVELOPER") for row in c: print "ID=",row print "NAME=",row print "AGE=",row print "SALARY=",row,"\n" print"Operation performed!!" con.close() Output: When the above program is executed, it will produce the following result. Page 25  UNIT-3 Python interaction with SQLite Note: Total_changes: Returns the total number of database rows that have been modified, inserted, or deleted since the database connection was opened. Fetch all rows from database table using cursor’s fetchall() & fetchone(). To fetch all rows from a database table, you need to follow these simple steps:-  Create a database Connection from Python. Refer Python SQLite connection. Page 26  UNIT-3 Python interaction with SQLite  Define the SELECT query. Here you need to know the table and its column details.  Execute the SELECT query using the execute() method.  Get all rows from the cursor object using a fetchall().  Iterate over the ResultSet using for loop and get column values of each row.  Close the Python database connection. Output: Page 27 UNIT-3 Python interaction with SQLite Page 28