Databases and SQL for Data Science
with Python
30/06/2024
1
Date, Time Functions
Most databases contain special datatypes for dates and times
2
Date, Time Functions (continued)
3
Date or Time Arithmetic
4
Date or Time Arithmetic
5
What is a DB-API?
• Python's standard API for accessing relational databases.
• Allows a single program that to work with multiple kinds of relational
databases.
• Learn DB-API functions once, use them with any database
6
Benefits of using DB-API
• Easy to implement and understand
• Encourages similarity between the Python modules used to access
databases
• Achieves consistency
• Portable across databases
• Broad reach of database connectivity from Python
7
8
9
What are Cursor Methods?
10
11
What is a DBatabase Cursor?
12
13
Writing code using DB-API
14
Accessing Databases with SQL Magic
15
16
17
Using Line Magic Statements
18
Using Line Magic Statements
19
Using SQL Magic
20
Using SQL Magic with SQLite Database
21
Connecting to a database using
ibm_db API
22
What is ibm db?
• The ibm_db API provides a variety of useful Python functions for
accessing and manipulating data in an IBM data server Database
• Ibm_db API uses the IBM Data Server Driver for ODBC and CLI APIs to
connect to IBM DB2 and Informix
23
Identify database connection credentials
24
Create a database connection
25
Close the database connection
26
What is a Stored Procedure?
A set of SQL statements stored and executed on the database server
⮚ Write in many different languages
⮚ Accept information in the form of parameters
⮚ Return results to the client
27
Benefits of Stored Procedures
o Reduction in network traffic
o Improvement in performance
o Reuse of code
o Increase in security
28
CREATE PROCEDURE statement
29
How to Use This Procedure
30
Questions?
31
