添加外键异常使用imdbpy

问题描述：

我试图IMDB数据加载到使用IMDBPY 5.1 mysql数据库IMDB的数据加载到MySQL时抓住。但是我最终总是遇到以下问题。我需要在模式中拥有完整的主键+外键约束。任何人都可以给我一些提示，说明可能的原因是什么？是添加外键异常使用imdbpy

building database indexes (this may take a while) 
# TIME createIndexes() : 38min, 6sec (wall) 0min, 0sec (user) 0min, 0sec (system) 
adding foreign keys (this may take a while) 
ERROR caught exception creating a foreign key: Cannot add or update a child row: a foreign key constraint fails (`imdb`.`#sql-65bf_d`, CONSTRAINT `title_episode_of_id_exists` FOREIGN KEY (`episode_of_id`) REFERENCES `title` (`id`)) 
ERROR caught exception creating a foreign key: Cannot add or update a child row: a foreign key constraint fails (`imdb`.`#sql-65bf_e`, CONSTRAINT `aka_title_movie_id_exists` FOREIGN KEY (`movie_id`) REFERENCES `title` (`id`)) 
ERROR caught exception creating a foreign key: Cannot add or update a child row: a foreign key constraint fails (`imdb`.`#sql-65bf_e`, CONSTRAINT `cast_info_movie_id_exists` FOREIGN KEY (`movie_id`) REFERENCES `title` (`id`)) 
ERROR caught exception creating a foreign key: Cannot add or update a child row: a foreign key constraint fails (`imdb`.`#sql-65bf_e`, CONSTRAINT `complete_cast_movie_id_exists` FOREIGN KEY (`movie_id`) REFERENCES `title` (`id`)) 
ERROR caught exception creating a foreign key: Cannot add or update a child row: a foreign key constraint fails (`imdb`.`#sql-65bf_e`, CONSTRAINT `movie_keyword_movie_id_exists` FOREIGN KEY (`movie_id`) REFERENCES `title` (`id`)) 
ERROR caught exception creating a foreign key: Cannot add or update a child row: a foreign key constraint fails (`imdb`.`#sql-65bf_e`, CONSTRAINT `movie_link_movie_id_exists` FOREIGN KEY (`movie_id`) REFERENCES `title` (`id`)) 
ERROR caught exception creating a foreign key: Cannot add or update a child row: a foreign key constraint fails (`imdb`.`#sql-65bf_e`, CONSTRAINT `movie_info_movie_id_exists` FOREIGN KEY (`movie_id`) REFERENCES `title` (`id`)) 
ERROR caught exception creating a foreign key: Cannot add or update a child row: a foreign key constraint fails (`imdb`.`#sql-65bf_e`, CONSTRAINT `movie_info_idx_movie_id_exists` FOREIGN KEY (`movie_id`) REFERENCES `title` (`id`)) 
ERROR caught exception creating a foreign key: Cannot add or update a child row: a foreign key constraint fails (`imdb`.`#sql-65bf_e`, CONSTRAINT `movie_companies_movie_id_exists` FOREIGN KEY (`movie_id`) REFERENCES `title` (`id`)) 
# TIME createForeignKeys() : 655min, 16sec (wall) 0min, 0sec (user) 0min, 0sec (system) 
RESTORING imdbIDs values for movies... WARNING: unable to restore imdbIDs using the temporary table (falling back to dbm): missing "title_extract" table (ok if this is the first run) 
WARNING: unable to restore imdbIDs (ok if this is the first run) 
RESTORING imdbIDs values for people... WARNING: unable to restore imdbIDs using the temporary table (falling back to dbm): missing "name_extract" table (ok if this is the first run) 
WARNING: unable to restore imdbIDs (ok if this is the first run) 
RESTORING imdbIDs values for characters... WARNING: unable to restore imdbIDs using the temporary table (falling back to dbm): missing "char_name_extract" table (ok if this is the first run) 
WARNING: unable to restore imdbIDs (ok if this is the first run) 
RESTORING imdbIDs values for companies... WARNING: unable to restore imdbIDs using the temporary table (falling back to dbm): missing "company_name_extract" table (ok if this is the first run) 
WARNING: unable to restore imdbIDs (ok if this is the first run)

我使用的命令如下

1）安装所有所需的软件包。

sudo apt-get install -y gcc python python-dev libssl-dev libxml2-dev libxslt1-dev zlib1g-dev python-setuptools python-pip 
easy_install -U SQLObject 
pip install MySQL-python

2）安装IMDBPY。

cd [IMDBPY_parent_directory] 
wget http://prdownloads.sourceforge.net/imdbpy/IMDbPY-5.1.tar.gz 
tar -xzf IMDbPY-5.1.tar.gz 
cd IMDbPY-5.1 
python setup.py install

3）在MySQL中，创建一个数据库 “IMDB”，并授予所有的权限为 “用户”，密码为 “密码”。

CREATE DATABASE imdb; 
GRANT ALL PRIVILEGES ON imdb.* TO 'user'@'localhost' IDENTIFIED BY 'password'; 
FLUSH PRIVILEGES;

4）下载所有IMDB数据。

mkdir [imdb_data_directory] 
cd [imdb_data_directory] 
wget -r --accept="*.gz" --no-directories --no-host-directories --level 1 ftp://ftp.fu-berlin.de/pub/misc/movies/database/

5）将IMDB数据加载到mysql。

cd [IMDBPY_parent_directory]/IMDbPY-5.1/bin 
python imdbpy2sql.py -d [imdb_data_directory] -u 
'mysql://user:[email protected]/imdb'

我的设置是：

蟒蛇：2.7
MySQL的：5.7
的Ubuntu 16.04

我也试过在MacOS 10.12 + MYSQL 5.7 + Python 2.7版和有同样的问题。

要达维德的建议使用的SQLAlchemy代替的SQLObject的：

我试图SQLAlchemy的使用下面的命令：

python imdbpy2sql.py -d [imdb_file_directory] -o sqlalchemy -u 'mysql://user:[email protected]/imdb?charset=utf8&local_infile=1'

我得到了下面的错误。

Traceback (most recent call last): 
    File "imdbpy2sql.py", line 538, in <module> 
    conn = setConnection(URI, DB_TABLES) 
    File "/Library/Python/2.7/site-packages/IMDbPY-5.1-py2.7-macosx-10.12-intel.egg/imdb/parser/sql/alchemyadapter.py", line 489, in setConnection 
    engine = create_engine(uri, **params) 
    File "/Library/Python/2.7/site-packages/sqlalchemy/engine/__init__.py", line 387, in create_engine 
    return strategy.create(*args, **kwargs) 
    File "/Library/Python/2.7/site-packages/sqlalchemy/engine/strategies.py", line 160, in create 
    engineclass.__name__)) 
TypeError: Invalid argument(s) 'local_infile' sent to create_engine(), using configuration MySQLDialect_mysqldb/QueuePool/Engine. Please check that the keyword arguments are appropriate for this combination of components.

我使用的是sqlalchemy 1.1.8版本。当我切换到sqlalchemy版本0.5时，我收到了同样的错误。当我切换到SQLAlchemy的0.4，我收到此错误：

Traceback (most recent call last): 
    File "imdbpy2sql.py", line 323, in <module> 
    from imdb.parser.sql.alchemyadapter import getDBTables, setConnection 
    File "/Library/Python/2.7/site-packages/IMDbPY-5.1-py2.7-macosx-10.12-intel.egg/imdb/parser/sql/alchemyadapter.py", line 54, in <module> 
    UNICODECOL: UnicodeText, 
NameError: name 'UnicodeText' is not defined

我有没有指定“local_infile”错了吗？

答

这是最有可能在代码中的错误。

作为一种变通方法，请尝试使用替代的SQLObject SQLAlchemy的。尝试安装它，使用它加入“-o SQLAlchemy的”（不含引号），以imdbpy2sql.py的命令行选项。

添加外键异常使用imdbpy

相关推荐