E
E
Elvis2021-01-02 21:43:52
Python
Elvis, 2021-01-02 21:43:52

Is the connection to the database correct?

Hey!
I have a flask application and a Postgres database. I am using psycopg2.
There were doubts about the correctness of the work with the database. I defined a connection function to the database

def get_db():
    if 'db' not in g:
        g.db = app.config['postgreSQL_pool'].getconn()
    return g.db

followed by routes, for example:
@app.route('/topsecret/test1')
def test1():
    title = 'Первый тест'
    s = "select * from followers as f left join clanmembers as cm on f.nick = cm.nick left join clans as cl on cm.clanid = cl.clanid order by cl.clanid, f.nick;"
    conn = get_db()
    c = conn.cursor()
    c.execute(s)
    text = c.fetchall()
    c.close()
    s = "select distinct(cl.clanimg), cl.clanid from clans as cl where cl.clanimg is not null order by cl.clanid;"
    conn = get_db()
    c = conn.cursor()
    c.execute(s)
    cli = c.fetchall()
    c.close()
    return render_template('test1.html', title=title, text=text, cli=cli)


@app.route('/topsecret/test2')
def test2():
    title = 'Второй тест'
    s = "select * from uniquemob as u order by u.mobid desc;"
    conn = get_db()
    c = conn.cursor()
    c.execute(s)
    mobs = c.fetchall()
    c.close()
    s = "select * from mobsinlabs as m order by m.name;"
    conn = get_db()
    c = conn.cursor()
    c.execute(s)
    mobsinlabs = c.fetchall()
    c.close()
    return render_template('test2.html', title=title, mobs=mobs, mobsinlabs=mobsinlabs)


@app.route('/test3')
def test3():
    title = 'Третий тест'
    s = "select * from savedress as sd order by sd.date desc limit 20"
    conn = get_db()
    c = conn.cursor()
    c.execute(s)
    sd = c.fetchall()
    c.close()
    return render_template('test3.html', title=title, sd=sd)


What confuses me: every time I access the connection conn = get_db(), work with the cursor and then close the connection. c.close()
Is this the right approach? perhaps there is a better option to strain the base less.
Also with this option, if one of the requests is processed for a long time, then other pages think and do not open until the first script has completed. I understand that it is not running in parallel, which is strange. Plus, SocketIO constantly receives data from me and, according to the same scheme, inserts, updates and selects data into the database.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
D
Dr. Bacon, 2021-01-02
@bacon

indicatively bad code and wrong approach:
1. a lot of copy-paste
2. since there are so many repetitions, it was already possible to come up with not get_db, but some kind of conditional "from_execute" function that accepts a request and returns data
3. orm knowingly came up with what would less people wrote similar horror
4. I repeat about orm, psycopg2 is a low level, even in a flask it is not customary to work with it

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question