dlwhittenbury / mysql_imdb_project Goto Github PK

Hi @dlwhittenbury, I believe there is a typo in a CREATE TABLE statement that results in
ERROR 1146 (42S02): Table 'IMDb.Alias_attributes' doesn't exist when loading data from file.

I believe here the second letter of the table name should not be capitalized.

Ran the program 'imdb_converter.py' and everything worked up to 'principals'. Then the program crashed with the following error:

"Reading title.principals.tsv ...

Traceback (most recent call last):
File "C:\Temp\Movie databases\MySQL_IMDb_Project-master24\imdb_converter2.py", line 258, in
title_principals = pd.read_csv(os.path.join(data_path,'title.principals.tsv'),sep='\t',na_values='\N')
File "C:\Python310\lib\site-packages\pandas\util_decorators.py", line 311, in wrapper
return func(*args, **kwargs)
File "C:\Python310\lib\site-packages\pandas\io\parsers\readers.py", line 680, in read_csv
return _read(filepath_or_buffer, kwds)
File "C:\Python310\lib\site-packages\pandas\io\parsers\readers.py", line 581, in _read
return parser.read(nrows)
File "C:\Python310\lib\site-packages\pandas\io\parsers\readers.py", line 1270, in read
df = DataFrame(col_dict, columns=columns, index=index)
File "C:\Python310\lib\site-packages\pandas\core\frame.py", line 636, in init
mgr = dict_to_mgr(data, index, columns, dtype=dtype, copy=copy, typ=manager)
File "C:\Python310\lib\site-packages\pandas\core\internals\construction.py", line 502, in dict_to_mgr
return arrays_to_mgr(arrays, columns, index, dtype=dtype, typ=typ, consolidate=copy)
File "C:\Python310\lib\site-packages\pandas\core\internals\construction.py", line 156, in arrays_to_mgr
return create_block_manager_from_column_arrays(
File "C:\Python310\lib\site-packages\pandas\core\internals\managers.py", line 1959, in create_block_manager_from_column_arrays
mgr._consolidate_inplace()
File "C:\Python310\lib\site-packages\pandas\core\internals\managers.py", line 1685, in _consolidate_inplace
self.blocks = tuple(_consolidate(self.blocks))
File "C:\Python310\lib\site-packages\pandas\core\internals\managers.py", line 2084, in _consolidate
merged_blocks = _merge_blocks(
File "C:\Python310\lib\site-packages\pandas\core\internals\managers.py", line 2111, in _merge_blocks
new_values = np.vstack([b.values for b in blocks]) # type: ignore[misc]
File "<array_function internals>", line 180, in vstack
File "C:\Python310\lib\site-packages\numpy\core\shape_base.py", line 282, in vstack
return _nx.concatenate(arrs, 0)
File "<array_function internals>", line 180, in concatenate
numpy.core._exceptions._ArrayMemoryError: Unable to allocate 3.19 GiB for an array with shape (5, 85703817) and data type object"

I just finished an attempt to run the software in both, Windows CLI and WSL-Ubuntu.
Neither runs without issues. I downloaded and installed MySQL 8
Windows-Ubuntu can't install MySQL and in Windows CLI, the program complains of syntax error at line #1 of imdb-create-tables.sql.
I installed:
"Server version: 8.0.33 MySQL Community Server - GPL"

Any suggestions?

Hello, it's been a few days since I've been studying the project and the tables hard.

But I'm having difficulties to make some queries, I would like to know if you could help, if so, could you give more details.

An example of what I was trying to do is:
Search for a name in a title titles.original_title + titles.start_year
so far so good, I can pull the title info without problems.

But when I try to pull the complete cast, and relate it to the professions, ordering by the number of episodes that each actor has in the title, I can't. The query is always limited to a few actors.

I was using groupers like this to organize multiple results from an actor to a row
GROUP_CONCAT(DISTINCT had_role.role_ order by ifnull(principals.ordering,9999)) as role_
for the query of actors by title, I should be able to show

title_id, name_id, name, principal_role, role_, profession, episodes(COUNT)

I tried several variants of this, but without a base it was complicated because we have: table of professions, names, character and the titles itself to relate.

with a more complex base maybe I can make the other queries, until then I'll keep trying and if I can post it here in case no one has a solution.

some queries of the many I tried.

SELECT DISTINCT
#*,
peoples.name_id, peoples.name_ AS name, 
GROUP_CONCAT(DISTINCT papel.role_ order by ifnull(cast.ordering,9999)) as role_, # query 100%
worked.profession, peoples.birth_year, peoples.death_year 

FROM names_ AS peoples
   left JOIN had_role papel on papel.name_id = peoples.name_id
   left JOIN principals cast on cast.name_id = peoples.name_id
            #INNER JOIN names_ AS names ON names.name_id=hr.name_id
            left JOIN name_worked_as worked ON worked.name_id = peoples.name_id
            WHERE papel.title_id = 'tt0460681' 
            GROUP BY papel.name_id

SELECT DISTINCT  *
#JSON_ARRAY(GROUP_CONCAT( DISTINCT JSON_OBJECT( COALESCE(A.region, 'LOCAL'),   A.title))) as 'region', 
#T.runtime_minutes as 'runtime',
#T.start_year as 'start_year',
#T.end_year as 'end_year'
#JSON_ARRAY(GROUP_CONCAT( DISTINCT JSON_OBJECT( N.name_,   H.role_))) as 'casting',
#JSON_ARRAY(GROUP_CONCAT( DISTINCT JSON_OBJECT(G.genre, H.role_))) as 'genres',
#JSON_ARRAY(GROUP_CONCAT( DISTINCT JSON_QUOTE( '' ))) as 'writers',
#GROUP_CONCAT( DISTINCT N.name_) as 'genres2'

/*, T.primary_title, T.original_title, T.runtime_minutes, A.is_original_title*/
FROM titles AS T
, name_worked_as AS Name_Work
#, writers AS W
#, directors as D
#, known_for as K
, principals AS P
WHERE T.original_title = 'Supernatural' /*collate utf8mb4_general_ci*/
#AND T.title_id = G.title_id
#AND T.title_id = A.title_id
#AND P.name_id  = N.name_id
#AND T.title_id = H.title_id
#AND T.title_id = W.title_id
#AND T.title_id = D.title_id
/*AND T.title_id = K.title_id*/

AND T.runtime_minutes <= 45 + 2
AND T.runtime_minutes >= 45 - 2
AND T.start_year = ('2005' +0)
/*AND A.region LIKE 'BR'*/
;

a mess no? many unsuccessful days give this.
@dlwhittenbury
if you need access to a db I can provide, just leave me your email and I'll send you the login.