Integrating Python with various databases using codes

SQLite:

• Library: sqlite3
• Example Code:

import sqlite3

# Connect to SQLite database

conn = sqlite3.connect('example.db')

# Create a cursor object

cursor = conn.cursor()

# Execute SQL query

cursor.execute('SELECT * FROM table_name')

# Fetch results

results = cursor.fetchall()

# Close the cursor and connection

cursor.close()

conn.close()

MySQL:

• Library: mysql-connector-python
• Example Code:

import mysql.connector

# Connect to MySQL database

conn = mysql.connector.connect(

  host='localhost',

  user='username',

  password='password',

  database='database_name'

)

# Create a cursor object

cursor = conn.cursor()

# Execute SQL query

cursor.execute('SELECT * FROM table_name')

# Fetch results

results = cursor.fetchall()

# Close the cursor and connection

cursor.close()

conn.close()

PostgreSQL:

• Library: psycopg2
• Example Code:

import psycopg2

# Connect to PostgreSQL database

conn = psycopg2.connect(

  host='localhost',

  user='username',

  password='password',

  database='database_name'

)

# Create a cursor object

cursor = conn.cursor()

# Execute SQL query

cursor.execute('SELECT * FROM table_name')

# Fetch results

results = cursor.fetchall()

# Close the cursor and connection

cursor.close()

conn.close()

MongoDB:

• Library: pymongo
• Example Code:

from pymongo import MongoClient

# Connect to MongoDB

client = MongoClient('mongodb://localhost:27017/')

# Access a database

db = client['database_name']

# Access a collection

collection = db['collection_name']

# Find documents

results = collection.find({})

# Process results

for document in results:

  print(document)

# Close the client connection

client.close()

Tableau:

• Tableau provides the Tableau Server Client (TSC) library for Python, which allows you to interact with Tableau Server or Tableau Online.
• Example Code:

from tableau_server_client import Server

# Connect to Tableau Server

server = Server('https://tableau-server-url', username='username', password='password')

server.auth.sign_in()

# Publish a workbook

workbook_path = 'path_to_workbook.twbx'

project_id = 'project_id'

server.workbooks.publish(workbook_path, project_id)

# Sign out from Tableau Server

server.auth.sign_out()

AWS (Amazon Web Services):

• For AWS integration, you can use the AWS SDK for Python (Boto3), which provides a Python interface to interact with various AWS services.
• Example Code (S3 Bucket Operations):

import boto3

# Create an S3 client

s3 = boto3.client('s3')

# List all S3 buckets

response = s3.list_buckets()

for bucket in response['Buckets']:

  print(bucket['Name'])

# Upload a file to S3 bucket

bucket_name = 'your_bucket_name'

file_path = 'path_to_file.txt'

s3.upload_file(file_path, bucket_name, 'destination_key.txt')

GCP (Google Cloud Platform):

• For GCP integration, you can use the Google Cloud Client Libraries, which provide Python APIs for various GCP services.
• Example Code (Google Cloud Storage Operations):

from google.cloud import storage

# Create a GCS client

storage_client = storage.Client()

# List all GCS buckets

buckets = storage_client.list_buckets()

for bucket in buckets:

  print(bucket.name)

# Upload a file to GCS bucket

bucket_name = 'your_bucket_name'

file_path = 'path_to_file.txt'

bucket = storage_client.get_bucket(bucket_name)

blob = bucket.blob('destination_key.txt')

blob.upload_from_filename(file_path)

Azure:

• For Azure integration, you can use the Azure SDK for Python, which provides Python libraries for working with various Azure services.
• Example Code (Azure Blob Storage Operations):

from azure.storage.blob import BlobServiceClient

# Create a Blob service client

connection_string = 'your_connection_string'

blob_service_client = BlobServiceClient.from_connection_string(connection_string)

# List all containers in Blob storage

containers = blob_service_client.list_containers()

for container in containers:

  print(container.name)

# Upload a file to Blob storage

container_name = 'your_container_name'

file_path = 'path_to_file.txt'

container_client = blob_service_client.get_container_client(container_name)

blob_client = container_client.get_blob_client('destination_key.txt')

with open(file_path, 'rb') as data:

  blob_client.upload_blob(data)