Troubles using async library : aiomysql (python - bot)

Question

I'm trying to transform my standard database functions into aiomysql async functions (for a bot) but I don't really understand how does the async functions work...

Here's my actual code that I want to transform :

def connection(Database):
    conn = mysql.connector.connect(host=Database[0],
                                    user=Database[1],
                                    password=Database[2],
                                    database=Database[3])

    c = conn.cursor()

    return c, conn

def insertToTable(Database, insert, data):
    c, conn = connection(Database)
    try:
        pass
        c.execute(insert, data)
        conn.commit()
    except mysql.connector.IntegrityError as e:
    #cnx.close()

def deleteFromTable(Database, query):
    c, conn = connection(Database)
    try:
        c.execute(query)
        c.commit()
    except:
        pass

def getMax(Database, columnName, tableName):
    c, conn = connection(Database)
    c.execute("SELECT MAX(" + columnName + ") FROM " + tableName)

    result = c.fetchall()
    for i in result:
        if i[0] is None:
            return 0
        else:
            return i[0]

My projects is separed in multiples files, I got some others basics requests that I didn't transform in function :

c, conn = dbconnect.connection(DB)
                    c.execute("update Tar SET va= (%s) WHERE x=1",(va_num))
                    conn.commit()

and some select fetchall/fetchone etc

I wrote that after reading the documentations and finding some (rare) examples :

import asyncio
import aiomysql
import setup as setup

loop = asyncio.get_event_loop()

@asyncio.coroutine
def exec_sql(insert, data):
    pool = yield from aiomysql.create_pool(host=setup.DB_local[0], port=3306,
                                           user=setup.DB_local[1], password=setup.DB_local[2],
                                           db=setup.DB_local[3], loop=loop, autocommit=False)

    with (yield from pool) as conn:
        cur = yield from conn.cursor()
        yield from cur.execute(insert, data)
        yield from conn.commit()
    conn.close
    #pool.close()
    #yield from pool.wait_closed()

insert = ("INSERT into discord_rights (discord_id, discord_name, discord_role, is_admin, is_caster, is_player)""VALUES (%s, %s, %s, %s, %s, %s)")
data = (10, "lea", 0, 0, 1, 1)
sql = loop.run_until_complete(exec_sql(insert, data))

@asyncio.coroutine
def get_one_sql(sql):
    pool = yield from aiomysql.create_pool(host=setup.DB_local[0], port=3306,
                                           user=setup.DB_local[1], password=setup.DB_local[2],
                                           db=setup.DB_local[3], loop=loop, autocommit=False)

    with (yield from pool) as conn:
        cur = yield from conn.cursor()
        yield from cur.execute(sql)
        r = yield from cur.fetchone()
    conn.close
    return r
    #pool.close()
    #yield from pool.wait_closed()

sql = loop.run_until_complete(get_one_sql("SELECT * from discord_rights WHERE discord_id = 124545xxxxxxxx"))
print(sql)

But I'm not sure if this is a good way cause I create a new pool for every request, right ? Can someone help me to build on of the function (importing the pool created in an another part of the code) or something better if I'm still wrong ?

Thx for your help and sorry for the long message, I prefered to show you my codes instead of nothing !

Troubles using async library : aiomysql (python - bot)

0 Answers0