Database 분석 (3)

3일차 복습

SQL 실행 순서

SELECT

Aggregation 집계 함수

(*) null

ALTER TABLE

CRUD

INSERT

DELETE

___________________________________________________________________

PyMySQL로 데이터 분석하기

PyMySQL

Aiven 

https://aiven.io/

host = 'localhost',

user = 'root',

password = '비밀번호',

db = 'training_db',

charset='utf'

cur = conn.cursor()

SQL 실행하기: execute()

cur.execute

fetchone()

fetchall()

cur.execute

CREATE, INSERT, UPDATE, DELETE

commit()

commit()/rollback()

conn.commit()

conn = 

conn = pymysql.connect(

host = host,

user =user,

password = password,

db=database,

port=port,

charset='utf8'

)

with conn.cursor() as cur:

conn.close()

___________________________________________________________________

with conn.cursor() as cur:

cur.execute('SELECT*FROM patients;')

result = cur.fetchall()

print(result)

with conn.cursor() as cur:

cur.execute('SELECT*FROM patients;')

result = cur.fetchall()

display(result)

with conn.cursor() as cur:

cur.execute('SELECT*FROM patients;')

result = cur.fetchone()

display(result)

___________________________________________________________________

with conn.cursor() as cur:

  cur.execute('SELECT * FROM patients WHERE patient_id=%s', (2,))

            

  result = cur.fetchone()

  print(result)

  cur.execute('SELECT * FROM patients WHERE patient_id=%s OR patient_id = %s', (2, 2))

  result = cur.fetchall()

  print(result)

___________________________________________________________________

with conn.cursor() as cur:

   cur execute('SELECT * FROM visits ORDER BY visit_date DESC LIMIT 5,')

   result = cur.fetchall()

   print(result)

___________________________________________________________________

with conn.cursor() as cur:

   cur.execute('SELECT * FROM visits ORDER BY visit_date DESC LIMIT 5;')

   result = cur.fetchall()

   display(result)

with conn.cursor() as cur:

       sql_query = 'SELECT * FROM visits ORDER BY visit_date DESC LIMIT 5'

       

       cur.execute(sql_query)

       

       result = cur.fetchall()

       display(result)

___________________________________________________________________

SELECT p.name, v.visit_date, v.reason

FROM visits v

JOIN patients p ON v.patient_id = p.patient_id

ORDER BY v.visit_date DESC

LIMIT 5;

___________________________________________________________________

with conn.cursor() as cur:

    cur.execute(sql_1)

    results = cur.fetchall()

    display(results)

___________________________________________________________________

___________________________________________________________________

NoSQL

___________________________________________________________________

https://cloud.mongodb.com/

___________________________________________________________________

!pip install pymongo

from pymongo import MongoClient

mongodb_uri = "본인 mongodb 주소와 비밀번호"

client = MongoClient(mongodb_uri)

db = client.sample_mflix

movies = db.movies

___________________________________________________________________

updated_result_one = temp_collection.update_one(

       {"title: "My Test Movie"},

       {"$set": {

           "year":2025,

            "status":"updated"

}

}

)

print(updated_result_one.matched_count)

___________________________________________________________________

updated_result_one = temp_collection.update_one(

       {"title": "My Test Movie"},  # 여기서 오류 수정

       {"$set": {

           "year": 2025,

            "status": "updated"

       }

       }

)

print(updated_result_one.matched_count)

___________________________________________________________________

updated_result_many = temp_collection.update_many(

{"year":2024}

{""$set": {"status":"reviewed"}}

)

___________________________________________________________________

updated_result_many = temp_collection.update_many(

    {"year": 2024},  # <-- 여기에 쉼표 추가

    {"$set": {"status": "reviewed"}}

)

print("매칭된 도큐먼트:", updated_result_many.matched_count)

print("수정된 도큐먼트:", updated_result_many.modified_count)

___________________________________________________________________