MySQL connection (Question)

[romkazor]

Hello. How can i create a permanent connection to database with aiodbc and use it for all queries like this:

cnxn   = pyodbc.connect(...)
cursor = cnxn.cursor()
cursor.execute(...)

I don't want to create a new connection to the database every time to make a query.
Thanks.

[julianit]

You can create a singleton class that keeps the connection open. The class should have a method that returns the cursor i.e. def cursor(self): return self.conneciton.cursor(), as this is what you need in order to execute a query. I hope, that helps.

[romkazor]

Thanks. If you could write an some small example, its will be great.

[jettify]

Singletons should be avoided, it is code smell in most of cases. aioodbc provides connection pooling for this purposes.

[jettify]

aioodbc/examples/example_pool.py

Lines 1 to 22 in 0124556

	import asyncio
	import aioodbc
	loop = asyncio.get_event_loop()
	async def test_pool():
	dsn = 'Driver=SQLite;Database=sqlite.db'
	pool = await aioodbc.create_pool(dsn=dsn, loop=loop)
	async with pool.acquire() as conn:
	cur = await conn.cursor()
	await cur.execute("SELECT 42;")
	r = await cur.fetchall()
	print(r)
	await cur.close()
	await conn.close()
	pool.close()
	await pool.wait_closed()
	loop.run_until_complete(test_pool())

[julianit]

This is how I use it:

class DatabaseConnection:

instance = None
def __init__(self, connection):
    self.connection = connection

def cursor(self):
    "Form the cursor"
    return self.connection.cursor()

@classmethod
async def get(cls):
    "Return database connection"
    if cls.instance is None:
        connection = await connect("Connection detailes here - DSN")
        cls.instance = DatabaseConnection(connection)
    return cls.instance