Why does django and python MySQLdb have one cursor per database?

Question

Example script:

MySQL works with one server -> HOSTNAME

Two MySQL databases on this server -> USERS, GAMES.

Task → Extract 10 latest games from GAMES.my_games_table and get users playing these games from USERS.my_users_table (do not accept any connections)

In Django, as well as in Python MySQLdb, why is it preferable to use one cursor for each database?

What is the disadvantage of an extended cursor, which is unique for each MySQL server and can switch between databases (for example, by querying "use USERS"), and then work with the corresponding database

MySQL connections are cheap, but not the only connection is better than many if there is a linear stream and there are no complex tranasactions that might require two cursors?

Answer 1

The answer will be shorter: "MySQL does not support this type of cursor," so Python-MySQL is not suitable either, so the reason why one join command is preferable is because MySQL works like that. This is a kind of tautology.

However, a longer answer:

• A "cursor" by your definition will be a type of object that accesses tables and indexes within an RDMS that can maintain its state.
• A “connection”, by your definition, will accept commands and either select or reuse the cursor to execute a command action, returning its results to the connection.
• By your definition, a “connection” could / could control multiple cursors.
• You think that this will be the preferred / effective way to access the database, since the “connections” are expensive and the “cursors” are cheap.

But:

• A cursor in MySQL (and other RDMS) is not a user-accessible mechanism for performing operations. MySQL (and others) perform operations as "installed", or rather, compile your SQL command into an internal list of commands and execute many complex bits depending on the nature of your SQL command and the structure of your table.
• A cursor is a specific mechanism used in stored procedures (and only there), providing the developer with a way to work with data in a procedural way.
• A "join" in MySQL is what you consider a "cursor", sort of. MySQL does not reveal its insides for you as an iterator or pointer that just moves through tables. It provides internals as a “connection” that accepts SQL and other commands, translates these commands into an internal action, performs this action, and returns the result.
• This differs from the “set” style and the “procedural” execution style (which really concerns the control granularity, which you, the user, are granted access to, or at least the granularity inherent in how RDMS referrals from its internal components, when it provides them through API).

Answer 2

As you say, MySQL connections are cheap, so for your case I’m not sure that there is a technical advantage in any case, outside the organization of the code and the flow. It may be easier to manage two cursors than keep track of which database one cursor is currently talking to, painstakingly tracking SQL statements "USE". The mileage with other databases may vary - remember that Django aims at database agnostic.

Also consider the case where two different databases, even on the same server, require different access credentials. In this case, two connections are required so that each connection can authenticate successfully.

Answer 3

A single cursor per database is not necessarily preferable; it is simply the default behavior.

The rationale is that different databases are most often found on different servers, use different mechanisms, and / or require different initialization parameters. (Otherwise, why should you use different “databases” in the first place?)

In your case, if your two databases are just table namespaces (which should be called "schemas" in SQL jargon), but they are on the same MySQL instance, then, in any case, they use the same connection. (How to configure Django to do this is actually a completely different question.)

You are also right that one connection is better than two if you only have one thread and you really don't need two working databases at the same time.