[Python] Database connection

# Database connection

 

Python으로 각 DB별 연결 내용 정리

# python3 설치
yum install python3 -y

 

1. PostgreSQL

# PostgreSQL 드라이버 설치
pip install psycopg2
python3 -m pip install psycopg2

 

import psycopg2
 
try:
    conn = psycopg2.connect(
        host="192.168.123.50",
        port="5432",
        database="postgres",
        user="user1",
        password="password")
 
    cur = conn.cursor()
 
    cur.execute("select version()")
 
    print(cur.fetchone())
 
except Exception as e:
    print("Error while fetching Schema")
    print(e)
 
finally:
    conn.close()

 

2. MySQL/MariaDB

# MySQL/MariaDB 드라이버 설치
pip install pymysql
python3 -m pip install pymysql

 

import pymysql
 
try:
    conn = pymysql.connect(
        host='192.168.123.50',
        port=3306 ,
        user='user1',
        password='password',
        db='mysql',
        charset='utf8'
    )
 
    cur = conn.cursor()
    cur.execute("select version()")
 
    rows = cur.fetchall()
    cur.close()
    conn.close()
    print(rows)
 
except Exception as e:
    print("Error while fetching Schema")
    print(e)

 

3. ClickHouse

# clickhouse 드라이버 설치
pip3 install clickhouse-driver[lz4]
python3 -m pip install clickhouse-driver[lz4]

 

from clickhouse_driver import Client
 
try:
    client = Client('192.168.123.50',
                user='user',
                password='password',
                verify=False,
                database='default',
                compression=True)
 
    result = client.execute('SELECT now(), version()')
    result2 = client.execute('SHOW databases')
    print(result)
 
except:
    print("Error while fetching Schema")

 

4. MongoDB

# MongoDB 드라이버 설치
pip install pymongo
python3 -m pip install pymongo

 

 

import pymongo
 
try:
    #conn = pymongo.MongoClient('mongodb://{user}:{password}@127.0.0.1:27017')
    conn = pymongo.MongoClient('mongodb://127.0.0.1:27017')
    db = conn.get_database('testdb')
    collection = db.get_collection('testcollection')
 
    for x in collection.find():
        print(x)
 
except Exception as e:
    print("Error while fetching Schema")
    print(e)

 

5. ElasticSearch

# ElasticSearch 드라이버 설치
pip install elasticsearch
python3 -m pip install elasticsearch

 

from elasticsearch import Elasticsearch
 
try:
    es = Elasticsearch('192.168.123.50:9200')
 
    # elastic info
    print(es.info())
 
    # get all index
    print(list(es.indices.get_alias().keys()))
 
    print(es.indices.get_mapping("products"))
    print(es.indices.get_mapping("work"))
 
except Exception as e:
    print("Error while fetching Schema")
    print(e)

 

6. Redis

# Redis 드라이버 설치
pip install scylla-driver
python3 -m pip install scylla-driver

 

import redis
 
try:
    r = redis.Redis(host='192.168.123.50', port=6379, db=0)
    r.set('k_test','v_test')
    print(r.get('k_test'))
    r.close()
except Exception as e:
    print("error")
    print(e)

 

7. ScyllaDB

# ScyllaDB 드라이버 설치
pip install scylla-driver
python3 -m pip install scylla-driver

 

import cassandra
 
print(cassandra.__version__)

 

 

 

To be continued.........

 

 

Made by 꿩