1

Problem

I am writing a program that reads a set of documents from a corpus (each line is a document). Each document is processed using a function processdocument, assigned a unique ID, and then written to a database. Ideally, we want to do this using several processes. The logic is as follows:

  1. The main routine creates a new database and sets up some tables.
  2. The main routine sets up a group of processes/threads that will run a worker function.
  3. The main routine starts all the processes.
  4. The main routine reads the corpus, adding documents to a queue.
  5. Each process's worker function loops, reading a document from a queue, extracting the information from it using processdocument, and writes the information to a new entry in a table in the database.
  6. The worker loops breaks once the queue is empty and an appropriate flag has been set by the main routine (once there are no more documents to add to the queue).

Question

I'm relatively new to sqlalchemy (and databases in general). I think the code used for setting up the database in the main routine works fine, from what I can tell. Where I'm stuck is I'm not sure exactly what to put into the worker functions for each process to write to the database without clashing with the others.

There's nothing particularly complicated going on: each process gets a unique value to assign to an entry from a multiprocessing.Value object, protected by a Lock. I'm just not sure whether what I should be passing to the worker function (aside from the queue), if anything. Do I pass the sqlalchemy.Engine instance I created in the main routine? The Metadata instance? Do I create a new engine for each process? Is there some other canonical way of doing this? Is there something special I need to keep in mind?

Additional Comments

I'm well aware I could just not bother with the multiprocessing but and do this in a single process, but I will have to write code that has several processes reading for the database later on, so I might as well figure out how to do this now.

Thanks in advance for your help!

Improve this question

1 Answer 1

Reset to default
5

The MetaData and its collection of Table objects should be considered a fixed, immutable structure of your application, not unlike your function and class definitions. As you know with forking a child process, all of the module-level structures of your application remain present across process boundaries, and table defs are usually in this category.

The Engine however refers to a pool of DBAPI connections which are usually TCP/IP connections and sometimes filehandles. The DBAPI connections themselves are generally not portable over a subprocess boundary, so you would want to either create a new Engine for each subprocess, or use a non-pooled Engine, which means you're using NullPool.

You also should not be doing any kind of association of MetaData with Engine, that is "bound" metadata. This practice, while prominent on various outdated tutorials and blog posts, is really not a general purpose thing and I try to de-emphasize this way of working as much as possible.

If you're using the ORM, a similar dichotomy of "program structures/active work" exists, where your mapped classes of course are shared between all subprocesses, but you definitely want Session objects to be local to a particular subprocess - these correspond to an actual DBAPI connection as well as plenty of other mutable state which is best kept local to an operation.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

You might want to mention that the session object is not thread-safe in case OP decides to use threads instead of processes. The whole “commit” operation pretty much depends on the object being immutable during the commit.
Thanks for your response, but you might need to explain this in a little more detail. Let's pretend, for the sake of argument, that I'm reaaaaally stupid and don't understand most of the big words used here... I don't know if I understand this correctly: do I not need to do anything special at all? Do pass the Table instances as arguments to the worker function and just do adds and commits off that?
I'm not using the ORM yet as I haven't really figured out work it in detail (very new to all this) but I might need to, as I basically am working with two tables, with rows in ManytoMany relations... That requires using ORM, right?
Also I'm not sure I understand the point about not binding metadata. Indeed, that's what a lot of tutorials seem to tell me to do. I'm not sure why this is bad, or what the alternative practice might be... :P Thank you for your patience!
I discourage binding engines to metadata only because it confuses people, and here your app is using lots of engines - one for each subprocess, so you wouldn't want "just one" attached to the metadata. Table object can be passed wherever you'd like, it's generally immutable (which means, it isn't changed once created). Also no, the ORM is not required for any kind of SQL operation, it is just an extra layer of automation that makes work easier for most. Working without it would allow you to understand the mechanics of things better, perhaps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.