[Python] mariadb

1. pymysql

install pymysql

---

2. 첫번째 행 갖고 오기

github

data = cursor.fetchone()

 

def sample01(ip:str, port:int, user:str, passwd:str, db:str, query:str)->tuple:
    """mariadb 첫번째 행 갖고 오기

    Args:
        ip: ip
        port: port
        user: id
        passwd: password
        db: 기본 db
        
    Returns:
        없음

    """

    import pymysql

    db = pymysql.connect(host   = ip,
                         port   = port,
                         user   = user,
                         passwd = passwd,
                         db     = db,
                         charset='utf8',
                         autocommit=True)

    cursor = db.cursor()

    cursor.execute(query)

    data = cursor.fetchone()

    print(data)
    print(type(data))

    db.close()

    return data

---

3. 묶음으로 갖고 오기

github

datas = cursor.fetchall()

 

def sample02(ip: str, port: int, user: str, passwd: str, db: str, query: str) -> tuple:
    """mariadb 여러 행 갖고 오기

    Args:
        ip: ip
        port: port
        user: id
        passwd: password
        db: 기본 db
        query: query

    Returns:
        없음

    """

    import pymysql

    db = pymysql.connect(host=ip,
                         port=port,
                         user=user,
                         passwd=passwd,
                         db=db,
                         charset='utf8',
                         autocommit=True)

    cursor = db.cursor()

    cursor.execute(query)

    datas = cursor.fetchall()

    db.close()


    for item in datas:
        print(item)


    print(type(datas))

    return datas

 

---

4. insert into

github

def sample03(ip: str, port: int, user: str, passwd: str, db: str, query: str):
    """mariadb insert

    Args:
        ip: ip
        port: port
        user: id
        passwd: password
        db: 기본 db
        query: query

    Returns:
        없음

    """

    import pymysql

    db = pymysql.connect(host=ip,
                         port=port,
                         user=user,
                         passwd=passwd,
                         db=db,
                         charset='utf8',
                         autocommit=True)

    cursor = db.cursor()

    cursor.execute(query)

    db.commit()

    db.close()

 

---

5. insert into 多

github

def sample03_Ex(ip: str, port: int, user: str, passwd: str, db: str):
    """mariadb insert

    Args:
        ip: ip
        port: port
        user: id
        passwd: password
        db: 기본 db
        query: query

    Returns:
        없음

    """

    import pymysql

    db = pymysql.connect(host=ip,
                         port=port,
                         user=user,
                         passwd=passwd,
                         db=db,
                         charset='utf8',
                         autocommit=True)

    cursor = db.cursor()

    for num in range(1,10):
        query = ''
        query = query + "insert into mellowlee.splunk_test_db (S_DATE, C_NAME, S_VOLUME) VALUES"
        query = query + "('20200501','MELLOWLEE','{0}')".format(num)
        print(query)

        cursor.execute(query)

    db.commit()

    db.close()

 

생성된 query

insert into mellowlee.splunk_test_db (S_DATE, C_NAME, S_VOLUME) VALUES('20200501','MELLOWLEE','1')
insert into mellowlee.splunk_test_db (S_DATE, C_NAME, S_VOLUME) VALUES('20200501','MELLOWLEE','2')
insert into mellowlee.splunk_test_db (S_DATE, C_NAME, S_VOLUME) VALUES('20200501','MELLOWLEE','3')
insert into mellowlee.splunk_test_db (S_DATE, C_NAME, S_VOLUME) VALUES('20200501','MELLOWLEE','4')
insert into mellowlee.splunk_test_db (S_DATE, C_NAME, S_VOLUME) VALUES('20200501','MELLOWLEE','5')
insert into mellowlee.splunk_test_db (S_DATE, C_NAME, S_VOLUME) VALUES('20200501','MELLOWLEE','6')
insert into mellowlee.splunk_test_db (S_DATE, C_NAME, S_VOLUME) VALUES('20200501','MELLOWLEE','7')
insert into mellowlee.splunk_test_db (S_DATE, C_NAME, S_VOLUME) VALUES('20200501','MELLOWLEE','8')
insert into mellowlee.splunk_test_db (S_DATE, C_NAME, S_VOLUME) VALUES('20200501','MELLOWLEE','9')

 

---

6. Insert Into bulk

DataFrame으로 작업한 경우 tuple로 변경하면 bulk 형태로 insert할 수 있다.

github

def sample04(ip: str, port: int, user: str, passwd: str, db: str):
    """

    mariadb bulk insert

    Args:
        ip: ip
        port: port
        user: id
        passwd: password
        db: 기본 db
        query: query

    Returns:
        없음

    """

    import pymysql

    db = pymysql.connect(host=ip,
                         port=port,
                         user=user,
                         passwd=passwd,
                         db=db,
                         charset='utf8',
                         autocommit=True)

    cursor = db.cursor()


    # query 문
    query = '''
             insert into mellowlee.splunk_test_db (S_DATE, C_NAME, S_VOLUME) 
             VALUES (%s, %s, %s)
            '''

    # DataFrame형태 Data
    import pandas as pd

    datas = pd.DataFrame({'S_DATE':['20200425','20200425','20200425','20200425'],
                          'C_NAME':['comp1', 'comp2', 'comp3', 'comp4'],
                          'S_VOLUME':[123, 123, 1, 2]})


    print(datas)
    print(datas.info())
    print(datas.describe())

    # DataFrame to tuple로 변환
    changeTuple = [tuple(x) for x in datas.values]

    # insert
    cursor.executemany(query, changeTuple)

    db.commit()

    db.close()

 

 

※ 참고

https://www.fun-coding.org/mysql_basic6.html

https://stackoverflow.com/questions/9758450/pandas-convert-dataframe-to-array-of-tuples

https://www.w3schools.com/python/python_mysql_insert.asp