CODEWITHSUNDEEP
pythonmysqldjangomysql-python
DhruvPathak
DhruvPathak

Reputation: 43265

Why django and python MySQLdb have one cursor per database?

Example scenario:

MySQL running a single server -> HOSTNAME

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

Task -> Fetch 10 newest games from GAMES.my_games_table , and fetch users playing those games from USERS.my_users_table ( assume no joins )

In Django as well as Python MySQLdb , why is having one cursor for each database more preferable ?

What is the disadvantage of an extended cursor which is single per MySQL server and can switch databases ( eg by querying "use USERS;" ), and then work on corresponding database

MySQL connections are cheap, but isn't single connection better than many , if there is a linear flow and no complex tranasactions which might need two cursors ?

Upvotes: 7

Views: 1453

Answers (3)

Jack Shedd
Jack Shedd

Reputation: 3531

A shorter answer would be, "MySQL doesn't support that type of cursor", so neither does Python-MySQL, so the reason one connection command is preferred is because that's the way MySQL works. Which is sort of a tautology.

However, the longer answer is:

  1. A 'cursor', by your definition, would be some type of object accessing tables and indexes within an RDMS, capable of maintaining its state.
  2. A 'connection', by your definition, would accept commands, and either allocate or reuse a cursor to perform the action of the command, returning its results to the connection.
  3. By your definition, a 'connection' would/could manage multiple cursors.
  4. You believe this would be the preferred/performant way to access a database as 'connections' are expensive, and 'cursors' are cheap.

However:

  1. A cursor in MySQL (and other RDMS) is not a the user-accessible mechanism for performing operations. MySQL (and other's) perform operations in as "set", or rather, they compile your SQL command into an internal list of commands, and do numerous, complex bits depending on the nature of your SQL command and your table structure.
  2. A cursor is a specific mechanism, utilized within stored procedures (and there only), giving the developer a way to work with data in a procedural way.
  3. A 'connection' in MySQL is what you think of as a 'cursor', sort of. MySQL does not expose it's internals for you as an iterator, or pointer, that is merely moving over tables. It exposes it's internals as a 'connection' which accepts SQL and other commands, translates those commands into an internal action, performs that action, and returns it's result to you.
  4. This is the difference between a 'set' and a 'procedural' execution style (which is really about the granularity of control you, the user, is given access to, or at least, the granularity inherent in how the RDMS abstracts away its internals when it exposes them via an API).

Upvotes: 10

Tobia
Tobia

Reputation: 18821

One cursor per database is not necessarily preferable, it's just the default behavior.

The rationale is that different databases are more often than not on different servers, use different engines, and/or need different initialization options. (Otherwise, why should you be using different "databases" in the first place?)

In your case, if your two databases are just namespaces of tables (what should be called "schemas" in SQL jargon) but reside on the same MySQL instance, then by all means use a single connection. (How to configure Django to do so is actually an altogether different question.)

You are also right that a single connection is better than two, if you only have a single thread and don't actually need two database workers at the same time.

Upvotes: 0

Jeffrey Froman
Jeffrey Froman

Reputation: 6623

As you say, MySQL connections are cheap, so for your case, I'm not sure there is a technical advantage either way, outside of code organization and flow. It might be easier to manage two cursors than to keep track of which database a single cursor is currently talking to by painstakingly tracking SQL 'USE' statements. Mileage with other databases may vary -- remember that Django strives to be database-agnostic.

Also, consider the case where two different databases, even on the same server, require different access credentials. In such a case, two connections will be necessary, so that each connection can successfully authenticate.

Upvotes: 2

Related Questions