1 00:00:00,580 --> 00:00:04,420 Hello and welcome to writing code using DB-APIs. 2 00:00:05,120 --> 00:00:06,200 After completing this video, 3 00:00:06,200 --> 00:00:09,620 you will be able to explain the basic concepts related to 4 00:00:09,620 --> 00:00:13,480 the Python DB-API and database cursors. 5 00:00:13,860 --> 00:00:16,780 And also write code using DB-APIs. 6 00:00:16,940 --> 00:00:19,020 As we saw in the beginning of this module, 7 00:00:19,300 --> 00:00:23,340 the user writes Python programs using a Jupyter notebook. 8 00:00:23,760 --> 00:00:28,620 There is a mechanism by which the Python code communicates with the DBMS. 9 00:00:28,780 --> 00:00:33,780 The Python code connects to the database using DB-API calls. 10 00:00:34,540 --> 00:00:40,140 DB-API is Python's standard API for accessing relational databases. 11 00:00:40,380 --> 00:00:45,260 It is a standard that allows you to write a single program that works with multiple kinds 12 00:00:45,360 --> 00:00:50,460 of relational databases instead of writing a separate program for each one. 13 00:00:50,860 --> 00:00:53,780 So, if you learn the DB-API functions, 14 00:00:54,000 --> 00:00:58,100 then you can apply that knowledge to use any database with Python. 15 00:00:58,940 --> 00:01:02,340 Here are some advantages of using the DB-API. 16 00:01:02,700 --> 00:01:04,960 It's easy to implement and understand. 17 00:01:05,220 --> 00:01:08,980 This API has been defined to encourage similarity between 18 00:01:09,060 --> 00:01:12,620 the Python modules that are used to access databases. 19 00:01:13,100 --> 00:01:17,460 It achieves consistency which leads to more easily understood modules. 20 00:01:18,000 --> 00:01:20,920 The code is generally more portable across databases, 21 00:01:21,280 --> 00:01:25,980 and it has a broader reach of database connectivity from Python. 22 00:01:26,620 --> 00:01:30,440 As we know, each database system has its own library. 23 00:01:30,540 --> 00:01:34,000 As you can see, the table shows a list of a few databases and 24 00:01:34,060 --> 00:01:39,160 corresponding DB-APIs to connect to Python applications. 25 00:01:39,480 --> 00:01:45,520 The IBM_db library is used to connect to an IBM DB2 database. 26 00:01:46,840 --> 00:01:53,500 The MySQL Connector/Python library is used to connect to a Compose for MySQL database. 27 00:01:53,500 --> 00:02:00,160 The psycopg2 library is used to connect to a Compose from PostgreSQL database. 28 00:02:00,560 --> 00:02:07,140 And finally, the PyMongo library is used to connect to a Compose for MongoDB database. 29 00:02:07,520 --> 00:02:14,460 The two main concepts in the Python DB-API are connection objects and query objects. 30 00:02:14,800 --> 00:02:19,980 You use connection objects to connect to a database and manage your transactions. 31 00:02:20,380 --> 00:02:23,500 Cursor objects are used to run queries. 32 00:02:23,800 --> 00:02:26,760 You open a cursor object and then run queries. 33 00:02:27,320 --> 00:02:31,560 The cursor works similar to a cursor in a text processing system where 34 00:02:31,640 --> 00:02:36,340 you scroll down in your result set and get your data into the application. 35 00:02:36,680 --> 00:02:40,480 Cursors are used to scan through the results of a database. 36 00:02:40,640 --> 00:02:46,760 The DB_API includes a connect constructor for creating a connection to the database. 37 00:02:47,180 --> 00:02:52,600 It returns a Connection Object, which is then used by the various connection methods. 38 00:02:52,840 --> 00:02:54,660 These connection methods are: 39 00:02:55,040 --> 00:02:59,400 The cursor() method, which returns a new cursor object using the connection. 40 00:02:59,680 --> 00:03:05,060 The commit() method, which is used to commit any pending transaction to the database. 41 00:03:05,440 --> 00:03:11,880 The rollback() method, which causes the database to roll back to the start of any pending transaction. 42 00:03:12,600 --> 00:03:16,360 The close() method, which is used to close a database connection. 43 00:03:17,160 --> 00:03:23,900 These objects represent a database cursor, which is used to manage the content of a fetch operation. 44 00:03:24,600 --> 00:03:28,480 Cursors created from the same connection are not isolated 45 00:03:28,600 --> 00:03:31,540 that is, any changes done to the database by 46 00:03:31,660 --> 00:03:35,220 a cursor are immediately visible by the other cursors. 47 00:03:35,660 --> 00:03:39,640 Cursors created from different connections can or cannot be 48 00:03:39,720 --> 00:03:43,400 isolated depending on how the transaction support is implemented. 49 00:03:44,660 --> 00:03:47,080 A database cursor is a control structure that 50 00:03:47,140 --> 00:03:50,460 enables traversal over the records in a database. 51 00:03:50,940 --> 00:03:55,940 It behaves like a file name or file handle in a programming language. 52 00:03:56,340 --> 00:04:00,020 Just as a program opens a file to access its contents, 53 00:04:00,180 --> 00:04:03,740 it opens a cursor to gain access to the query results. 54 00:04:04,260 --> 00:04:07,460 Similarly, the program closes a file to end 55 00:04:07,560 --> 00:04:12,740 its access and closes a cursor to end access to the query results. 56 00:04:12,860 --> 00:04:16,720 Another similarity is that just as file handle keeps 57 00:04:16,840 --> 00:04:20,060 track of the program's current position within an open file, 58 00:04:20,060 --> 00:04:26,340 a cursor keeps track of the program's current position within the query results. 59 00:04:26,780 --> 00:04:32,960 Let's walk through a Python application that uses the DB-API to query a database. 60 00:04:33,620 --> 00:04:39,360 First, you import your database module by using the connect API from that module. 61 00:04:39,680 --> 00:04:41,740 To open a connection to the database, 62 00:04:41,860 --> 00:04:46,100 you use the connect constructor and pass in the parameters, that is, 63 00:04:46,220 --> 00:04:49,540 the database name, username, and password. 64 00:04:49,900 --> 00:04:52,460 The connect function returns connection object. 65 00:04:52,900 --> 00:04:57,440 After this, you create a cursor object on the connection object. 66 00:04:57,820 --> 00:05:01,600 The cursor is used to run queries and fetch results. 67 00:05:01,980 --> 00:05:04,480 After running the queries, using the cursor, 68 00:05:04,580 --> 00:05:08,040 we also use the cursor to fetch the results of the query. 69 00:05:08,380 --> 00:05:11,560 Finally, when the system is done running the queries, 70 00:05:11,760 --> 00:05:14,980 it frees all resources by closing the connection. 71 00:05:15,420 --> 00:05:17,920 Remember that it is always important to close 72 00:05:17,920 --> 00:05:22,420 connections to avoid unused connections taking up resources. 73 00:05:23,040 --> 00:05:24,820 Thanks for watching this video.