python: mysql DB query 요청

안녕하세요. 제이콥입니다.

이번 글에서는 python으로 mysql db에 접근, query를 요청하는 것에 대해 정리를 해봤습니다.

다른걸로 공부하시다 이렇게 사용할 수 있구나 정도.. 보시면 될 것 같습니다.


이전 글에서는 mysql-workbench를 설치했었습니다.

mysql DB, mysql-workbench setup on Ubuntu (Vultr 호스팅)

설치를 한 후, schema와 table을 생성해야하는데요.

이 생성과정은 mysql-workbench에서 하면 아주 편합니다.

UI에서 테이블 이름, 필드 이름, 타입 등을 넣어주면 됩니다.

그러면 자동으로 mysql query 문이 생성됩니다.

image.png


이제 파이썬으로 mysql을 다뤄보겠습니다.

먼저 pymysql 라이브러리를 설치합니다.

pip install pymysql


설치 다하셨나요? ㅎㅎ

제가 작성한 코드로 설명해보겠습니다.

저는 object map라는 테이블을 만들었고 object를 테이블에 넣고 지우고 업데이트하고 가져오는 작업을 해봤습니다.


먼저 특정 db의 schema에 연결을 합니다.

연결이 되면 이 conn이라는 객체를 이용해서 여러 query를 수행합니다.

DB가 설치된 서버 host name,

DB에 접근할 user명, 비밀번호,

Schema 등을 입력합니다.

[python]

def initDB(self):
conn = pymysql.connect(
host= ‘test.net’,
user= ‘test’,
password= ‘test_pw’,
db= ‘your_schema’,
charset= ‘utf8mb4’)
return conn

[/python]

아래 함수는 select로 테이블 내의 값들을 가져와서

Object라는 객체에 값을 채워넣는 일을 합니다.

이 코드에서는 처음에 정의한 initDB로 connection을 한 후, 쿼리를 수행하는 것을 볼 수 있습니다.

[python]
def loadObjectMap(self):
conn = self.initDB()
result = None
try:
with conn.cursor() as cursor:
sql = ‘SELECT * FROM ObjectMap’
cursor.execute(sql)
result = cursor.fetchall()
# print(result)
except Exception as e:
print(‘I got error on search table’)
conn.close()
conn.close()
objectArray = []
for object_quary in result:
obj = Object()
obj.id = object_quary[0] # auto id
obj.tag = object_quary[2]
obj.tagName = object_quary[3]
obj.directionX = object_quary[4]
obj.directionY = object_quary[5]
obj.directionZ = object_quary[6]
objectArray.append(obj)
return objectArray

[/python]

아래 함수들은 물체를 지우고, 넣고, 업데이트 하는 함수들입니다.

[python]

def delete_object(self, auto_id): #delete an object which has auto_id
pass # DELETE FROM athena.ObjectMap WHERE AI_ID=’15’;
conn = self.initDB()
try:
with conn.cursor() as cursor:
sql = ‘DELETE FROM ObjectMap WHERE AI_ID = %s’
cursor.execute(sql, (auto_id))
conn.commit()
print(‘I successed on deleting ID’)
except Exception as e:
print(‘I got error on deleting ID’)
print(e)
conn.close()
conn.close()

def insert_object(self, obj): # insert new object to DB
# obj.id, obj.tag, obj.tagName, obj.directionX, obj.directionY, obj.directionZ
conn = self.initDB()
try:
with conn.cursor() as cursor:
# print obj
sql = ‘INSERT INTO ObjectMap (objectMapID, tagID, name, directionX, directionY, directionZ) VALUES (%s, %s, %s, %s, %s, %s)’
cursor.execute(sql, (obj.id, obj.tag, obj.tagName, obj.directionX, obj.directionY, obj.directionZ))
conn.commit()
print(‘I successed on inserting ID’)
except Exception as e:
print(‘I got error on inserting ID’)
print(e)
conn.close()
return 0
conn.close()
return 1

def update_object(self, updateID, obj): # update obj info to another object which has updateID.
# self.update_object(obj.id, obj.directionX, obj.directionY, obj.directionZ)
conn = self.initDB()
try:
with conn.cursor() as cursor:
sql = ‘UPDATE ObjectMap SET tagID = %s, name = %s, directionX = %s, directionY = %s, directionZ = %s WHERE AI_ID = %s’
cursor.execute(sql, (obj.tag, obj.tagName, obj.directionX, obj.directionY, obj.directionZ, updateID))
conn.commit()
print(‘I successed on updating ID’)
except Exception as e:
print(‘I got error on updating ID’)
print(e)
conn.close()
conn.close()

[/python]

끝!

감사합니다.

mysql DB, mysql-workbench setup on Ubuntu (Vultr 호스팅)

안녕하세요. 제이콥입니다.

윈도우에서 서버 db에 쉽게 접근해서 확인하고 수정을 하기 위한 셋팅에 적어봤습니다.

먼저 mysql DB 셋업 과정, 그리고 방화벽 port 관련 설정, mysql-workbench 설치 및 설정 등을 할 것입니다.


제 환경은 우분투이고, vultr 가상 호스팅 서버입니다. (서버가 우분투환경이면 저랑 똑같이 하면 됩니다.)

Vultr에 대해 궁금하시면 아래 글을 봐보세요!

https://steemit.com/kr-dev/@jacobyu/vultr

순서대로 시작해보겠습니다.


Vultr 내에서 셋팅 #0 db 계정 생성

ssh로 접속을 합니다. 아래 계정이름과 ip는 임의로 적어봤습니다.

ssh testid@123.123.55.123

이미 워드프레스는 설치를 했다면, mysql은 같이 설치되어있습니다.

mysql 계정을 하나 만들어보겠습니다.

먼저 루트 계정으로 접속합니다. 처음에 mysql를 설치할 때 정했던 아이디와 비밀번호를 입력합니다.

계정을 생성합니다. ‘test’ 는 아이디, 그리고 ‘12345678’은 비밀번호입니다.

mysql -u root -p

use mysql;

create user ‘test‘@’%’ identified by ‘12345678‘;

계정을 생성한 다음에 잘 생성됐는지 확인합니다.

select user from user;

다음으로, 권한을 주는 것을 깜빡했는데요.

권한을 줘야합니다. 외부에서 mysql-workbench로 접속을 해서 쉽게 변경을 할 수 있도록이요.

grant all privileges on *.* to ‘test’@’%’ ideintified by ‘12345678’;

flush privileges;


Vultr 내에서 셋팅 #1 방화벽 db 접근 port 열기

또한 외부에서 접속하기 위해서는 port를 열어줘야합니다.

ufw를 설치합니다. ufw는 쉽게 포트를 설정할 수 있도록 해줍니다.

설치 후, 상태를 한번 봅니다.

어떤 포트가 열려있고 닫혀있는지 적혀있습니다.

sudo ufw status verbose

다음으로는, mysql접근을 위한 3306 포트를 엽니다 (기본이 3306). 혹은, 자신만의 포트로 변경을 해도됩니다.

sudo ufw allow 3306


Window PC #0 MYSQL workbench 설치 및 셋팅

https://dev.mysql.com/downloads/workbench/에서 다운로드합니다.

다운로드 후, manage connection을 들어가서 해당 서버의 ip/혹은 domain 그리고 db ID/pw를 입력하면 끝!

그리고 실제로 연결해보시면 됩니다.

 

image.png

image.png

mysql workbench 장점으로는

DB를 GUI에서 보고 수정가능하고, 커맨드로 날려보고 여기 하나에서 다해볼 수 있습니다.

여기까지 따라오시면서 주의해야할점은

계정의 권한 확인, 방화벽 포트 확인이 있습니다.


추가사항 – linux에서 mysql-workbench 설치

mysql-workbench를 사용하는 방법은 같다.

아래 명령어로 설치를 한다.

sudo apt-get install mysql-workbench

 


자! 정리를 간단히 해봤습니다!

다음 글에서는 이어서 테이블도 만들어보고 파이썬에서 접근도 해볼 예정입니다.

감사합니다.