[패스트캠퍼스python] 데이터베이스 연동(SQLite)

파이썬 인강 : 데이터베이스 연동(SQLite)

1. 커서의 위치

데이터베이스는 커서의 위치가 중요하다
데이터를 불러오고 난 다음에 커서는 해당 데이터 뒤에 위치하기때문이다

# 1개 로우 선택
print('One -> \n', c.fetchone())

# 지정 로우 선택 : size위의 숫자로 로우 갯수를 선택
print('Three -> \n', c.fetchmany(size=3))

# 전체 로우 선택
print('All -> \n', c.fetchall())

위의 명령어를 활용한 예시는 아래 이미지와 같다

터미널에서 볼 수 있듯이 1개 로우 명령어 뒤라서 커서는 1번 로우를 지난 2번로우 앞에 있다
그리고 size=3인 로우를 출력하니 2,3,4번 로우가 터미널에 출력되었음을 알 수 있다

위의 상태에서 전체 로우 print(‘All -> \n’, c.fetchall()) 명령어를 입력하면 어떻게 될까?
커서는 지금 4번로우 뒤에 위치해있기때문에 마지막 로우인 5번 데이터만 출력된다

만약 위의 상태에서 print(‘All -> \n’, c.fetchall())를 한번 더 호출하면 어떻게 될까?
커서의 위치는 마지막 로우인 5번 뒤에 있다. 따라서 호출해도 [] 빈리스트만 터미널에 출력된다.

그렇다면 처음 1번 로우를 호출하고 싶을때는 어떻게 할 수 있을까?
바로 순회하면 된다

2. 순회

순회에는 3가지방법이 있다.

1. 순회 : 변수선언 후 for in 반복문 사용
   데이터조회 명령어 c.execute(‘SELECT * FROM users’) 뒤에 실행해야 출력된다

   rows = c.fetchall()
   for row in rows:
       print('retrieve1  >', row)  # 조회 없음

2. 순회 : 변수선언 없이 for in 반복문 바로 사용
   데이터조회 명령어 c.execute(‘SELECT * FROM users’) 뒤에 실행해야 출력된다
   간편하기때문에 제일많이 사용된다

   for row in c.fetchall():
       print('retrieve2 >', row)  # 조회 없음

3. 순회
   execute(“SELECT * FROM users)는 users에 있는 전체 데이터를 조회하는 명령어이다 따라서 fetchall() 명령어와 동일한 결과값을 가진다.
   데이터조회 명령어 c.execute(‘SELECT * FROM users’) 까지 포함된 명령어로 데이터조회명령어가 따로 필요없다
   코드가 길어져서 가독성이 떨어지는 지는 단점이 있다.
   “ORDER BY id desc” 명령어를 넣어주면 역순출력이 된다

   for row in c.execute("SELECT * FROM users ORDER BY id desc"):
       print('retrieve3 > ', row)

순회1번과 순회2번은 함께 쓰일수없기때문에
순회2번과 순회3번의 출력값을 아래 이미지와 같다

3. WHERE Retrieve

6가지 방법이 있고 핵심이므로 꼭 다 알고 있어야한다.

1. WHERE Retrieve1
   튜플형태로 3번로우인 Lee를 출력
   fetchone()에서 3번 로우 하나만 불러왔기때문에 그 다음 명령어로 fetchall()로 전체를 불러와도 [] 빈 리스트만 출력된다

   param1 = (3,) 
   c.execute('SELECT * FROM users WHERE id=?', param1)
   print('param1', c.fetchone())
   print('param1', c.fetchall())

2. WHERE Retrieve2
   튜플형태로 1번로우인 Kim을 출력
   %s :문자열형, %d:정수형, %f: 실수형
   fetchone()에서 1번 로우 하나만 불러왔기때문에 그 다음 명령어로 fetchall()로 전체를 불러와도 [] 빈 리스트만 출력된다

   param2 = 1
   c.execute("SELECT * FROM users WHERE id='%s'" % param2) 
   print('param2', c.fetchone())
   print('param2', c.fetchall())

3. WHERE Retrieve3
   이번엔 딕셔너리형태로 1번 로우출력
   id= 뒤에 :Id를 넣어준뒤 컴마찍고 딕셔너리형태로 {“Id”: 1} 첫번째로우를 호출하면 된다

   c.execute("SELECT * FROM users WHERE id= :Id", {"Id": 1})
   print('param3', c.fetchone())
   print('param3', c.fetchall())

4. WHERE Retrieve4
   리스트형태로 파라미터 2개를 받아서 1번로우와 4번로우 출력
   파라미터 여러가지를 가져오려면 IN(?,?)로 넣으면 된다
   이젠 한개가 아니니까 fetchone()은 쓸 수 없고 fetchall()로 출력하면 된다

   param4 = (1, 4)
   c.execute('SELECT * FROM users WHERE id IN(?,?)', param4)
   print('param4', c.fetchall())

5. WHERE Retrieve5
   위와 똑같은 결과값이지만 다르게 표현할 수 있다 물음표대신 정수값($d)을 넣어주면 된다
   간단하기때문에 많이 사용한다

   c.execute("SELECT * FROM users WHERE id In('%d','%d')" % (1, 4))
   print('param5', c.fetchall())

6. WHERE Retrieve6
   딕셔너리형태로 1번과 4번로우 출력
   OR 사용

   c.execute("SELECT * FROM users WHERE id= :id1 OR id= :id2", {"id1": 1, "id2": 4})
   print('param6', c.fetchall())

지금까지 id를 이용했지만 username, date등을 이용해서도 출력할수있다

4. Dump 출력

데이터베이스 백업 시 중요하다
아래 명령어를 실행하면 새로운 dump.sql이라는 파일이 형성되고 그 안에 작성한 데이터테이블이 백업된다.
이를 활용하여 sql에 붙여넣으면 다른 컴퓨터나 환경에서도 데이터사용가능하다
실무에서 흔히 ‘덤프떠와’ 라고 말한다.

with문을 사용하면 자동으로 close()를 해줘서 편리하다

• with open() as f: 를 실행했으므로 f.close()가 자동으로 되었고
• with conn: 을 실행했으므로 conn.close()가 자동으로 되었다

with conn: 
    with open('본인이 원하는 경로/dump.sql', 'w') as f:
        for line in conn.iterdump():
            f.write('%s\n' % line)
        print('Dump Print Complete.')

위의 코드를 실행시키면 출력값은 아래 이미지와 같다.