Sonzaschool
Rudi

Sekondari ya Kawaida · Kidato cha Nne · Amali · Electronics and Computer

Computer Programming

Performing connection to remote databases

takriban dakika 4 kusoma

Majadiliano
Mada za sehemu hiiCollecting and storing dataMada 4

A remote database is a database that is stored on a computer or server that is not physically located on your own computer. Instead, it is accessed over a network, often through the internet. This allows multiple users or applications to access and share the same data from different locations. For example, a school in Dar es Salaam can store student records on a remote server that teachers in Arusha can also access.

Why Use Remote Databases

Remote databases are important because they enable data sharing across different locations. In Tanzania, businesses with multiple branches—such as a shop in Mwanza and another in Mbeya—can use a remote database to keep inventory updated in real time. They also provide central storage, which makes it easier to back up data and maintain consistency.

Connecting to a Remote Database in Python

Python provides libraries that allow you to connect to remote databases. Two common ones are:

  • psycopg2 — used to connect to PostgreSQL databases
  • mysql-connector-python — used to connect to MySQL databases

The general steps to connect are:

  1. Install the required library using pip. For MySQL, you would run: pip install mysql-connector-python. For PostgreSQL, you would run: pip install psycopg2.
  2. Import the library in your Python script.
  3. Establish a connection by providing the remote server address (host), username, password, and database name.
  4. Create a cursor to execute SQL commands.
  5. Execute queries to retrieve, insert, update, or delete data.
  6. Close the connection when finished.

Worked Example: Connecting to a MySQL Database

Imagine a small pharmacy in Dodoma that stores medicine inventory on a remote MySQL database. Below is how the pharmacy's Python program connects to retrieve the list of medicines.

import mysql.connector

# Step 1: Establish connection
connection = mysql.connector.connect(
    host="pharmacy-server.example.com",
    user="store_clerk",
    password="secret123",
    database="pharmacy_db"
)

# Step 2: Create a cursor
cursor = connection.cursor()

# Step 3: Execute a SELECT query
cursor.execute("SELECT medicine_name, price FROM medicines WHERE quantity > 0")

# Step 4: Fetch and display results
results = cursor.fetchall()
print("Available Medicines:")
print("-" * 40)
for row in results:
    print(f"{row[0]} - TZS {row[1]:,.0f}")

# Step 5: Close cursor and connection
cursor.close()
connection.close()

In this example, the program connects to a remote database hosted on pharmacy-server.example.com. It fetches all medicines that are in stock and displays their names and prices in Tanzanian shillings. The connection is closed after the task is complete to free up server resources.

Similar Example with PostgreSQL

If the same pharmacy used PostgreSQL instead, the code would look very similar:

import psycopg2

connection = psycopg2.connect(
    host="pharmacy-server.example.com",
    user="store_clerk",
    password="secret123",
    database="pharmacy_db"
)

cursor = connection.cursor()
cursor.execute("SELECT medicine_name, price FROM medicines WHERE quantity > 0")

for row in cursor.fetchall():
    print(f"{row[0]} - TZS {row[1]:,.0f}")

cursor.close()
connection.close()

Activity: Practice Connecting and Querying

In class, your teacher will guide you through the following steps:

  1. Install either mysql-connector-python or psycopg2 on your computer.
  2. Use the example code above to connect to a practice database provided by your teacher.
  3. Modify the SQL query to display medicines that cost less than TZS 10,000.
  4. Insert a new medicine record into the database and verify it was added.

This practice helps you become comfortable with the connection process and basic SQL queries.

Role-Play: Simulating Client-Server Interaction

In groups, you will simulate how a client program communicates with a remote database server. One student acts as the client (running the Python program), another acts as the database server (receiving requests and sending responses), and a third student records the interaction. The client sends an SQL query, the server responds with data, and the client displays the results. This activity demonstrates how data travels over a network.

Real-life application

In Tanzania, a small retail shop with branches across different regions can use remote database connections to manage inventory in real time. When a shop in Arusha sells an item, the remote database is updated instantly, so the shop in Dar es Salaam knows how many items are left in stock. This helps prevent overstocking or running out of goods, making the business more efficient and saving money.

Jinsi utakavyotathminiwaTathmini ya mchakato · Tathmini ya matokeo · Tathmini ya maarifa

Tathmini ya mchakato

  • Read and proces data from flat files.
  • Handle errors and clean data.
  • Handle credentials securely.

Tathmini ya matokeo

A Python program that retrieves and processes data from local flat files (e.g., CSV, Excel, or JSON).

Tathmini ya maarifa

Detailed knowledge of:

Method used: The student should explain how to:

  • read process data from remote storage.

Principles: The student should explain the principles of

  • storage based on accessibility and security needs.

Theories: The student should explain:

  • Trade-offs between local storage, shared network storage, and cloud solutions.

Circumstantial knowledge:

Detailed knowledge about:

  • Limitations in storage or retrieval due to connectivity issues.

Swali

What is a remote database?

Ingia ili kuwasilisha jibu lako na lihesabiwe katika umahiri wako.

Ingia ili kufanya mazoezi

Mwalimu

Umekwama? Niulize chochote kuhusu mada hii.

Ingia ili kumuuliza Mwalimu wa AI wa Sonza kuhusu swali hili.

Ingia ili kuuliza

Majadiliano

Hakuna maswali bado

Ingia ili kuuliza
Inapakia majadiliano…