Python 으로 MariaDB 연동(1)

[DBConnector.py 생성]

import mariadb

import sys

class MariaDBConnector:

    def __init__(self, config):

        self.config = config

        self.conn = None

    def connect(self):

        try:

            self.conn = mariadb.connect(**self.config)

            return self.conn

        except mariadb.Error as e:

            print(f"마리아디비 연결시 에러: {e}")

            sys.exit(1)

    def close(self):

        if self.conn:

            self.conn.close()

config = {

    'host': '127.0.0.1',

    'port': 3306,

    'user': 'admin',

    'password': '1234',

    'database': 'Chatbot'   데이터베이스 이름

}

 

 

[chatbot_data.py 생성]

-. 테이블 생성

 

from utils.DBConnector import MariaDBConnector

import utils.DBConnector

config=utils.DBConnector.config

db = MariaDBConnector(config)

conn=db.connect()

try :

   sql = '''
    CREATE TABLE tb_student (
        id int primary key auto_increment not null,
        name varchar(32),
        age int,
        address varchar(32)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    '''

#테이블 생성

    with conn.cursor() as cursor:

        cursor.execute(sql)

             

except Exception as e:

        print(e)

finally:

    if db is not None:

         db.close()

 

 

[chatbot_data_insert.py 생성]

from utils.DBConnector import MariaDBConnector

import utils.DBConnector

import pandas as pd

config=utils.DBConnector.config

db = MariaDBConnector(config)

conn=db.connect()

# 데이터 정의

students = [

            {'name': 'Kei', 'age': 36, 'address' : 'PUSAN'},

            {'name': 'Tony', 'age': 34, 'address': 'PUSAN'},

            {'name': 'Jaeyoo', 'age': 39, 'address': 'GWANGJU'},

            {'name': 'Grace', 'age': 28, 'address': 'SEOUL'},

            {'name': 'Jenny', 'age': 27, 'address': 'SEOUL'},

        ]

# 데이터 db에 추가

for s in students:

        with conn.cursor() as cursor:

            sql = '''

                    insert tb_student(name, age, address) values("%s",%d,"%s")

                    ''' % (s['name'], s['age'], s['address'])

            cursor.execute(sql)

            conn.commit()

    # 30대 학생만 조회

cond_age = 300

with conn.cursor() as cursor:

        sql = '''

        select * from tb_student where age > %d

        ''' % cond_age

        cursor.execute(sql)

        results = cursor.fetchall()

    # 이름 검색

cond_name = 'Grace'

with conn.cursor() as cursor:

        sql = '''

        select * from tb_student where name="%s"

        ''' % cond_name

        cursor.execute(sql)

        result = cursor.fetchone()

print(result[1], result[2])

    # pandas 데이터프레임으로 표현

df = pd.DataFrame(results)

print(df)

 

 

테이블 확인

-. 데이터 자동으로 DB에 입력