For example PyMySQL . How it will be more correct to use cursors?

Option 1 :

connect = pymysql.connect() cursor = connect.cursor() cursor.execute('INSERT INTO user(name) VALUE('%s')', ('John',)) last_id = cursor.lastrowid cursor.execute('SELECT name FROM user') result = cursor.fetchall() cursor.close()

Option 2 :

 connect = pymysql.connect() cursor = connect.cursor() cursor.execute('INSERT INTO user(name) VALUE('%s')', ('John',)) last_id = cursor.lastrowid cursor.close() cursor = connect.cursor() cursor.execute('SELECT name FROM user') result = cursor.fetchall() cursor.close()

Those. Is it possible to use one created cursor for all queries, or create a new cursor each time for a new query?

And, if you use one cursor for the same type of query with different data? For example, option 3 :

 connect = pymysql.connect() cursor = connect.cursor() cursor.execute('INSERT INTO user(name) VALUE('%s')', ('John',)) last_id_1 = cursor.lastrowid cursor.execute('INSERT INTO city(name) VALUE('%s')', ('Moskow',)) last_id_2 = cursor.lastrowid cursor.close() cursor = connect.cursor() cursor.execute('SELECT name FROM user') result_1 = cursor.fetchall() cursor.execute('SELECT name FROM city') result_2 = cursor.fetchall() cursor.close()

Which option is better / more correct to use (and in terms of performance too)?

  • the first option is better or you can go further and use connection pooling - MaxU
  • @MaxU, i.e. Can I create only one cursor and always work through it? For example, a web server that always works: at the beginning we create a cursor and all the time the server is running we send requests and receive answers (if any) only through this one cursor. Will it be correct? There will be no errors, incorrect data, etc. in progress? - user203925 pm
  • one
    It depends on your application and on the load - one may be too little ... read about “Connection pooling” - MaxU
  • @MaxU, I get it, thanks! - user203925

0