파이썬으로 MariaDB 접속하기

Posted on: 2020년 05월 19일 2020년 05월 19일
Categories: 강의자료, 라즈베리파이

파이썬으로 MariaDB 접속하기

pymysql 모듈 설치

$ pip3 install pymysql

pi@raspberrypi:~/11_pi_Coding/py/PMS7003 $ pip3 install pymysql
Looking in indexes: https://pypi.org/simple, https://www.piwheels.org/simple
Collecting pymysql
  Using cached https://files.pythonhosted.org/packages/ed/39/15045ae46f2a123019aa968dfcba0396c161c20f855f11dea6796bcaae95/PyMySQL-0.9.3-py2.py3-none-any.whl
Installing collected packages: pymysql
Successfully installed pymysql-0.9.3
pi@raspberrypi:~/11_pi_Coding/py/PMS7003 $

pi@raspberrypi:~/11_pi_Coding/py/PMS7003 $ pip3 install pymysql

Looking in indexes: https://pypi.org/simple, https://www.piwheels.org/simple

Collecting pymysql

Using cached https://files.pythonhosted.org/packages/ed/39/15045ae46f2a123019aa968dfcba0396c161c20f855f11dea6796bcaae95/PyMySQL-0.9.3-py2.py3-none-any.whl

Installing collected packages: pymysql

Successfully installed pymysql-0.9.3

pi@raspberrypi:~/11_pi_Coding/py/PMS7003 $

파이썬 프로그램 SELECT 테스트(sample_select.py 로 저장)

import pymysql

conn = pymysql.connect(host='localhost', user='smart', password='123',
               db='dustdb', charset='utf8')

with conn.cursor() as cursor:
    sql = 'select * from testtable'
    cnt = cursor.execute(sql)
    result = cursor.fetchall()

    if cursor.rowcount == 0:
        print("Empty Table")
    else:
        print(result)
conn.close()

import pymysql

conn = pymysql.connect(host='localhost', user='smart', password='123',

db='dustdb', charset='utf8')

with conn.cursor() as cursor:

sql = 'select * from testtable'

cnt = cursor.execute(sql)

result = cursor.fetchall()

if cursor.rowcount == 0:

print("Empty Table")

else:

print(result)

conn.close()

실행 : python3 sample_select.py <– 파이썬 3.7로 실행

파이썬 프로그램 INSERT 테스트(sample_insert.py 로 저장)

import pymysql

conn = pymysql.connect(host='localhost', user='smart', password='123', db='dustdb', charset='utf8')

with conn.cursor() as cursor:
    #sql = 'insert into testtable(pos, ppm) values("p777", 234);'
    sql = 'insert into testtable(pos, ppm) values(%s, %s);'
    cnt = cursor.execute(sql, ('p888', 123))
    r = conn.commit()

    if r == 0:
        print("Failed")
    else:
        print("Save Ok")

conn.close()

import pymysql

conn = pymysql.connect(host='localhost', user='smart', password='123', db='dustdb', charset='utf8')

with conn.cursor() as cursor:

#sql = 'insert into testtable(pos, ppm) values("p777", 234);'

sql = 'insert into testtable(pos, ppm) values(%s, %s);'

cnt = cursor.execute(sql, ('p888', 123))

r = conn.commit()

if r == 0:

print("Failed")

else:

print("Save Ok")

conn.close()

실행 : python3 sample_insert.py <– 파이썬 3.7로 실행

파이썬 프로그램 INSERT테스트 2(sample_insert2.py로 저장)

import pymysql

def insertDB(data):
    conn = pymysql.connect(host='localhost', user='smart', password='123', db='dustdb', charset='utf8')

    with conn.cursor() as cursor:
        sql = 'insert into testtable(pos) values(%s);'
        cnt = cursor.execute(sql, (data))
        r = conn.commit()

        if r == 0:
            print("Failed")
        else:
            print("Save Ok")

    conn.close()

insertDB(10)

import pymysql

def insertDB(data):

conn = pymysql.connect(host='localhost', user='smart', password='123', db='dustdb', charset='utf8')

with conn.cursor() as cursor:

sql = 'insert into testtable(pos) values(%s);'

cnt = cursor.execute(sql, (data))

r = conn.commit()

if r == 0:

print("Failed")

else:

print("Save Ok")

conn.close()

insertDB(10)

whan0623

516