Hello, in this video you will learn how to access databases using Python. Databases are powerful tools for data scientists. After completing this module, you'll be able to explain the basic concepts related to using Python to connect to databases. Then you'll create tables, low data and query data using SQL from Jupyter Notebooks, and finally, analyze the data. In the lab assignments, you will learn how to create an instance in the Cloud, connect to a database, query data from the database using SQL, and analyze the data using Python. You will be able to explain the basic concepts related to connecting a Python application to a database. Describe SQL APIs as well as list some of the proprietary APIs used by popular SQL-based DBM systems. Let's quickly review some of the benefits of using Python, a popular scripting language for connecting to databases. The Python ecosystem is very rich and provides easy to use tools for data science. Some of the most popular packages are NumPy, pandas, matplotlib, and SciPy. Python is easy to learn and has a simple syntax. Due to its open source nature, Python has been ported to many platforms. All your python programs can work on any of these platforms without requiring any changes at all. If you are careful and avoid any system dependent features, Python supports relational database systems. Writing Python code to access databases is made easier by the presence of the Python database API. Commonly referred to as the DB API, and detailed documentation related to Python is easily available. Notebooks are also very popular in the field of data science because they run in an environment that allows creation and sharing of documents that contain live code, equations, visualizations, and explanatory texts. A notebook interface is a virtual notebook environment used for programming. Examples of notebook interfaces include the mathematical notebook, maple worksheet, matlab notebook, IPython Jupyter, R Markdown, Apache Zeppelin, Apache Spark notebook, and the Databricks cloud. In this module, we will be using Jupyter notebooks. The Jupyter notebook is an open source web application that allows you to create and share documents that contain live code, equations, visualizations, and narrative texts. Here are some of the advantages of using Jupyter notebooks. Notebook support for over 40 programming languages including Python, R, Julia, and Scala. Notebooks can be shared with others by email, Dropbox, GitHub, and the Jupyter notebook viewer. Your code can produce rich interactive output HTML, images, videos lay tech, and customized types. You can leverage big data tools such as Apache Spark from Python, R, and Scala, and explore that same data with pandas, scikit-learn, ggplot2, and TensorFlow. This is how a typical user accesses databases using Python code written on a Jupyter notebook, a web based editor. There is a mechanism by which the Python program communicates with the DBMS. The Python code connects to the database using API calls. We will explain the basics of SQL APIs and Python DB APIs. An application programming interface is a set of functions that you can call to get access to some type of servers. The SQL API consists of library function calls as an application programming interface, API, for the DBMS. To pass SQL statements to the DBMS, an application program calls functions in the API, and it calls other functions to retrieve query results and status information from the DBMS. The basic operation of a typical SQL API is illustrated in the figure. The application program begins its database access with one or more API calls that connect the program to the DBMS. To send the SQL statement to the DBMS, the program builds the statement as a text string in a buffer and then makes an API call to pass the buffer contents to the DBMS. The application program makes API calls to check the status of its DBMS request and to handle errors. The application program ends its database access with an API call that disconnects it from the database. Now, lets learn basic concepts about some of the proprietary APIs used by popular SQL-based DBMS systems. Each database system has its own library. As you can see, the table shows a list of a few applications and corresponding SQL APIs. MySQL C API provides low level access to the MySQL client server protocol and enable C programs to access database contents. The psycopg2 API connects Python applications in PostgreSQL databases. The IBM_DB API is used to connect Python applications to IBM DB2 databases. The dblib API is used to connect to SQL server databases. ODBC is used for database access for Microsoft Windows OS. OCI is used by Oracle databases. And finally, JDBC is used by Java applications. Thanks for watching this video.
