NOTE: the imdbpy2sql.py script, used to populate a database using the data in the IMDb's plain text data files, is a critical piece of IMDbPY: it's based on an ORM to be database-independent and contains a lot of tricks to be as fast as possible; however there are huge margins for improvements; if you want to help, please read the TODO.txt file and subscribe the imdbpy-devel mailing list at: http://imdbpy.sf.net/?page=help#ml NOTE: see README.currentRole for information about characters support. SQL === Since version 2.1 it's possible to transfer the whole IMDb's database from the plain text data files into a SQL database. Starting with version 2.5 every database supported by the SQLObject Object Relational Manager can be used to store and retrieve movies and persons information. This means that MySQL, PostgreSQL, SQLite, Firebird, MAX DB, Sybase and MSSQL are supported and, as your read this text, maybe other database backends were added. Since release 3.8, SQLAlchemy (version 0.4 and 0.5) is also supported (this adds at least DB2/Informix IDS to the list of supported databases). Since release 3.9, there's a partial support to output large tables in a set of CSV (Comma Separated Values) files, to be later imported in a database. Actually only MySQL, PostgreSQL and IBM DB2 are supported. In version 4.1 the imdbpy2sql.py script has the '--fix-old-style-titles' command line argument; if used, every movie title will be converted to the new style ("The Title", instead of the old "Title, The"). This option will go away in 4.2, and is intended only to support old set of plain text data files. Since version 4.2 --fix-old-style-titles is no more needed, being turned on by default. The --do-not-fix-old-style-titles was introduced in case you want to turn it off for some strange reason. REQUIREMENTS ============ You need one of SQLObject or SQLAlchemy (both can be installed safely: by default IMDbPY first tries SQLObject; if not present it fall-backs to SQLAlchemy). [SQLObject] You need the SQLObject package, at least version 0.8; even better if you can download the latest SVN snapshot. SQLObject home page: http://sqlobject.org/ SVN command to download the latest development version: svn co http://svn.colorstudy.com/SQLObject/trunk SQLObject [SQLAlchemy] Support for SQLAlchemy is still in beta (please report any bug!) and a bit slower than SQLObject; anyway, you need version 0.4 or 0.5. SQLAlchemy home page: http://www.sqlalchemy.org/ SVN command to download the latest development version: svn checkout http://svn.sqlalchemy.org/sqlalchemy/trunk sqlalchemy [OTHER REQUIRED MODULES] Obviously SQLObject and SQLAlchemy can access databases only through other specific modules/packages, that you need to have installed (e.g.: 'mysql-python' for MySQL, 'psycopg' for PostgreSQL, and so on). SQL DATABASE INSTALLATION ========================= Select a mirror of the "The Plain Text Data Files" from the http://www.imdb.com/interfaces/ page and download every file in the main directory (beware that the "diffs" subdirectory contains _a lot_ of files you _don't_ need, so don't start mirroring everything!). Starting from release 2.4, you can just download the files you need, instead of every single file; the files not downloaded will be skipped. This feature is still quite untested, so please report any bug. Create a database named "imdb" (or whatever you like), using the tool provided by your database; as an example, for MySQL you will use the 'mysqladmin' command: # mysqladmin -p create imdb For PostgreSQL, you have to use the "createdb" command: # createdb -W imdb To create the tables and to populate the database, you must run the imdbpy2sql.py script: # imdbpy2sql.py -d /dir/with/plainTextDataFiles/ -u 'URI' Where the 'URI' argument is a string representing the connection to your database, with the schema: scheme://[user[:password]@]host[:port]/database[?parameters] Where 'scheme' is one in "sqlite", "mysql", "postgres", "firebird", "interbase", "maxdb", "sapdb", "mssql", "sybase", "ibm_db_sa". Some examples: mysql://user:password@host/database postgres://user:password@host/database mysql://host/database?debug=1 postgres:///full/path/to/socket/database postgres://host:5432/database sqlite:///full/path/to/database sqlite:/C|/full/path/to/database sqlite:/:memory: For other information you can read the SQLObject/SQLAlchemy documentation. You can force the use of SQLObject or SQLAlchemy with the '-o' command line option (i.e.: "-o sqlobject" or "-o sqlalchemy" or a list of comma separated values to specify an order of preference). TIMING ====== The performances are hugely dependant upon the underlying Python module/package used to access the database. The imdbpy2sql.py script has a number of command line arguments, useful to chose amongst presets that can improve performances, using specific database servers. The fastest database appears to be MySQL, with about 200 minutes to complete on my test system (read below). A lot of memory (RAM or swap space) is required, in the range of at least 250/500 megabytes (plus more for the database server). In the end, the database will require between 2.5GB and 5GB of disc space. The should be no difference - at insert time - between SQLObject and SQLAlchemy. As said, the performances varies greatly using a database server or another: MySQL, for instance, has an executemany() method of the cursor object that accept multiple data insertion with a single SQL statement; other database requires a call to the execute() method for every single row of data, and they will be much slower - from 2 to 7 times slower than MySQL. There are generic suggestions that can lead to better performances, like turning off your filesystem journaling (so it can be a good idea to remount an ext3 filesystem as ext2). Another option is the use of a ramdisk/tmpfs, if you have enough RAM. Obviously these have effect only at insert-time: during the day-to-day use, you can turn your journaling on again. You can also consider the use of the CSV output, explained below (but be sure that your database server of choice is able to import CSV files). I've done some tests, using an AMD Athlon 1800+, 1GB of RAM, over a complete plain text data files set (as of 11 Apr 2008, with more than 1.200.000 titles and over 2.200.000 names): database | time in minutes: total (insert data/create indexes) ----------------------+----------------------------------------------------- MySQL 5.0 MyISAM | 205 (160/45) MySQL 5.0 InnoDB | _untested_, see NOTES below. PostgreSQL 8.1 | 560 (530/30) SQLite 3.3 | ??? (150/???) - very slow building indexes. | Timed with the "--sqlite-transactions" command | line option; otherwise it's _really_ slow: even | 35 hours or more. SQLite 3.7 | 65/13 - with --sqlite-transactions and using a SSD hard disk SQL Server | about 3 or 4 hours. If you have different experiences, please tell me! As expected, the most important things that you can do to improve performances are: 1. use an in-memory filesystem or an SSD disk. 2. use the -c /path/to/empty/dir argument to use CSV files. 3. follow the specific notes about your database server. NOTES ===== [save the output] The imdbpy2sql.py will print a lot of debug information on standard output; you can save it in a file, appending (without quotes) "2>&1 | tee output.txt" [Microsoft Windows paths] It's much safer, in a Microsoft Windows environment, to use full paths for the values of the '-c' and '-d' arguments, complete with drive letter. The best thing is to use _UNIX_ path separator, and to add a leading separator. E.g.: -d C:/path/to/imdb_files/ -c C:/path/to/csv_tmp_files/ [MySQL] In general, if you get an embarrassingly high numbero of "TOO MANY DATA ... SPLITTING" lines, consider increasing max_allowed_packet (in the configuration of your MySQL server) to at least 8M or 16M. Otherwise, inserting the data will be very slow, and some data may be lost. [MySQL InnoDB and MyISAM] InnoDB is abysmal slow for our purposes: my suggestion is to always use MyISAM tables and - if you really want to use InnoDB - convert the tables later. The imdbpy2sql.py script provides a simple way to manage these cases, see ADVANCED FEATURES below. In my opinion, the cleaner thing to do is to set the server to use MyISAM tables or - you you can't modifiy the server - use the --mysql-force-myisam command line option of imdbpy2sql.py. Anyway, if you really need to use InnoDB, in the server-side settings I recommend to set innodb_file_per_table to "true". Beware that the conversion will be extremely slow (some hours), but still faster than using InnoDB from the begin. You can use the "--mysql-innodb" command line option to force the creation of a datbase with MyISAM tables, converted at the end into InnoDB. [Microsoft SQL Server/SQLExpress] If you get and error about how wrong and against nature is the blasphemous act of inserting indentity keys, you can try to fix it with the new custom queries support; see ADVANCED FEATURES below. As a shortcut, you can use the "--ms-sqlserver" command line option to set all the needed options. You probably need SQLObject 0.10 (in the svn repository, as I'm writing this). [SQLite speed-up] For some reason, SQLite is really slow, except when used with transactions; you can use the '--sqlite-transactions' command line option to obtain acceptable performances. The same command, also turns off "PRAGMA synchronous". SQLite seems to hugely benefit from the use of a non-journaling filesystem and/or of a ramdisk/tmpfs: see the generic suggestions discussed above in the TIMING section. [SQLite failure] It seems that, with older versions of the python-sqlite package, the first run may fail; if you get a DatabaseError exception saying "no such table", try running again the command with the same arguments. Double funny, uh? ;-) [data truncated] If you get an insane amount (hundreds or thousands, on various text columns) of warnings like these lines: imdbpy2sql.py:727: Warning: Data truncated for column 'person_role' at row 4979 CURS.executemany(self.sqlString, self.converter(self.values())) you probably have a problem with the configuration of your database. The error came from strings that get cut at the first non-ASCII char (and so you're losing a lot of information). To obviate at this problem, you must be sure that your database server is set up properly, with the use library/client configured to communicate with the server in a consistent way. E.g., for MySQL you can set: character-set-server = utf8 default-collation = utf8_unicode_ci default-character-set = utf8 of even: character-set-server = latin1 default-collation = latin1_bin default-character-set = latin1 [adult titles] Beware that, while running, the imdbpy2sql.py script will output a lot of strings containing both person names and movie titles. The script has absolutely no way to know that the processed title is an adult-only movie, so... if you leave it running and your little daughter runs to you screaming 'daddy! daddy! what kind of animals Rocco trains in the documentary "Rocco: Animal Trainer 17"???'... well it's not my fault! ;-) SQL USAGE ========= Now you can use IMDbPY with the database: from imdb import IMDb i = IMDb('sql', uri='YOUR_URI_STRING') resList = i.search_movie('the incredibles') for x in resList: print x ti = resList[0] i.update(ti) print ti['director'][0] and so on... The 'sql' data access system takes an optional argument, named "useORM", which can be set to a string or a list of values (the string can be a comma-separated list of items, to denote an order of preference). Valid values are "sqlobject" and "sqlalchemy". The default is ('sqlobject', 'sqlalchemy'). E.g.: i = IMDb('sql', uri='YOUR_URI_STRING', useORM='sqlalchemy,sqlobject') i = IMDb('sql', uri='YOUR_URI_STRING', useORM=['sqlalchemy', 'sqlobject']) i = IMDb('sql', uri='YOUR_URI_STRING', useORM='sqlalchemy']) ADVANCED FEATURES ================= With the -e (or --execute) command line argument you can specify custom queries to be executed at certain times, with the syntax: -e "TIME:[OPTIONAL_MODIFIER:]QUERY" Where TIME is actually one of these: 'BEGIN', 'BEFORE_DROP', 'BEFORE_CREATE', 'AFTER_CREATE', 'BEFORE_MOVIES', 'BEFORE_CAST', 'BEFORE_RESTORE', 'BEFORE_INDEXES' and 'END'. The only available OPTIONAL_MODIFIER is 'FOR_EVERY_TABLE' and it means that the QUERY command will be executed for every table in the database (so it doesn't make much sense to use it with BEGIN, BEFORE_DROP or BEFORE_CREATE time...), replacing the "%(table)s" text in the QUERY with the appropriate table name. Other available TIMEs are: 'BEFORE_MOVIES_TODB', 'AFTER_MOVIES_TODB', 'BEFORE_PERSONS_TODB', 'AFTER_PERSONS_TODB', 'BEFORE_CHARACTERS_TODB', 'AFTER_CHARACTERS_TODB', 'BEFORE_SQLDATA_TODB', 'AFTER_SQLDATA_TODB', 'BEFORE_AKAMOVIES_TODB' and 'AFTER_AKAMOVIES_TODB'; they take no modifiers. Special TIMEs 'BEFORE_EVERY_TODB' and 'AFTER_EVERY_TODB' apply to every BEFORE_* and AFTER_* TIME above mentioned. These commands are executed before and after every _toDB() call in their respective objects (CACHE_MID, CACHE_PID and SQLData instances); the "%(table)s" text in the QUERY is replaced as above. You can specify so many -e arguments as you need, even if they refers to the same TIME: they will be executed from the first to the last. Also, always remember to correctly escape queries: after all you're passing it on the command line! E.g. (ok, quite a silly example...): -e "AFTER_CREATE:SELECT * FROM title;" The most useful case is when you want to convert the tables of a MySQL from MyISAM to InnoDB: -e "END:FOR_EVERY_TABLE:ALTER TABLE %(table)s ENGINE=InnoDB;" If your system uses InnoDB by default, you can trick it with: -e "AFTER_CREATE:FOR_EVERY_TABLE:ALTER TABLE %(table)s ENGINE=MyISAM;" -e "END:FOR_EVERY_TABLE:ALTER TABLE %(table)s ENGINE=InnoDB;" You can use the "--mysql-innodb" command line option as a shortcut of the above command. Cool, uh? Another possible use is to fix a problem with Microsoft SQLServer/SQLExpress: to prevent errors setting IDENTITY fields, you can run something like this: -e 'BEFORE_EVERY_TODB:SET IDENTITY_INSERT %(table)s ON' -e 'AFTER_EVERY_TODB:SET IDENTITY_INSERT %(table)s OFF' You can use the "--ms-sqlserver" command line option as a shortcut of the above command. To use transactions to speed-up SQLite, try: -e 'BEFORE_EVERY_TODB:BEGIN TRANSACTION;' -e 'AFTER_EVERY_TODB:COMMIT;' Which is also the same thing the command line option '--sqlite-transactions' does. CSV files ========= Keep in mind that actually only MySQL, PostgreSQL and IBM DB2 are supported. Moreover, you may incur in problems (e.g.: your postgres _server_ process must have reading access to the directory you're storing the CSV files). To create (and import) a set of CSV files, run imdbpy2sql.py with the syntax: ./imdbpy2sql.py -d /dir/with/plainTextDataFiles/ -u URI -c /directory/where/to/store/CSVfiles The created files will be imported near the end of the imdbpy2sql.py processing; notice that after that, you can safely cancel these files. CSV partial processing ====================== It's possible, since IMDbPY 4.5, to separate the two steps involved using CSV files. With the --csv-only-write command line option the old database will be zeroed and the CSV files saved (along with imdbIDs information). Using the --csv-only-load option you can load these saved files into an existing database (this database MUST be the one left almost empty by the previous run). Beware that right now the whole procedure is not very well tested. Using both commands, on the command line you still have to specify the whole "-u URI -d /path/plainTextDataFiles/ -c /path/CSVfiles/" series of arguments.