CODEWITHSUNDEEP
pythonmysql
José Carlos
José Carlos

Reputation: 2922

Python don't return data when joining two tables by a field

I've got a query that returns the data correctly in MySQL but in Python only returns part of the data.

The query is:

select sc.* from tbl030_shots_chart sc, tbl006_player_team tc where
sc.id_fiba = tc.id_player_feb and
tc.id_team_club = 5

This query in MySQL returns 1030 rows like you can see in this screen cap.

enter image description here

However, If I execute this query with python, I've got only 67 rows. This is my code:

connection = pymysql.connect(host = DDBB.DDBB_FIBA_HOST,
                      user = DDBB.DDBB_FIBA_USER,
                      password = DDBB.DDBB_FIBA_PSWD,
                      db = DDBB.DDBB_FIBA_NAME,
                      charset = DDBB.DDBB_FIBA_CHARSET,
                      cursorclass=pymysql.cursors.DictCursor)
with connection.cursor() as cursor:
    totalRows = cursor.execute("select sc.* from tbl030_shots_chart sc, tbl006_player_team tc where sc.id_fiba = tc.id_player_feb and tc.id_team_club = %s", [5])
    print("Total Rows: " + str(totalRows))

And this is the exit:

enter image description here

Why I've got les data from Python than MySQL?

These are the definition of the tables:

tbl030_shots_chart enter image description here

tbl006_player_team enter image description here

Edit I:

With inner join doesn't work in python but works in MySQL

enter image description here

However, with python, still returns 76 rows and not 1030 like MySQL.

connection = pymysql.connect(host = DDBB.DDBB_FIBA_HOST,
                      user = DDBB.DDBB_FIBA_USER,
                      password = DDBB.DDBB_FIBA_PSWD,
                      db = DDBB.DDBB_FIBA_NAME,
                      charset = DDBB.DDBB_FIBA_CHARSET,
                      cursorclass=pymysql.cursors.DictCursor)
with connection.cursor() as cursor:
    totalRows = cursor.execute("select sc.* from tbl030_shots_chart as sc inner join tbl006_player_team as pt on sc.id_fiba = pt.id_player_feb and pt.id_team_club = %s", [5])
    print("Total Rows: " + str(totalRows))

enter image description here

If I've got the total rows from the cursor with this code:

connection = pymysql.connect(host = DDBB.DDBB_FIBA_HOST,
                      user = DDBB.DDBB_FIBA_USER,
                      password = DDBB.DDBB_FIBA_PSWD,
                      db = DDBB.DDBB_FIBA_NAME,
                      charset = DDBB.DDBB_FIBA_CHARSET,
                      cursorclass=pymysql.cursors.DictCursor)
with connection.cursor() as cursor:
    cursor.execute("select sc.* from tbl030_shots_chart as sc inner join tbl006_player_team as pt on sc.id_fiba = pt.id_player_feb and pt.id_team_club = %s", [5])
    totalRows = cursor.rowcount
    print("Total Rows: " + str(totalRows))

I've got 76 rows returned and not 1030.

enter image description here

Upvotes: 1

Views: 325

Answers (2)

nacho
nacho

Reputation: 5397

Try to use the cursor rowcount attribute:

with connection.cursor() as cursor:
    cursor.execute("select sc.* from tbl030_shots_chart sc, tbl006_player_team tc where sc.id_fiba = tc.id_player_feb and tc.id_team_club = %s", [5])
    totalRows=cursor.rowcount
    print("Total Rows: " + str(totalRows))

In the .execute method there are no return values defined, so you can get anything.

Upvotes: 0

Lord Elrond
Lord Elrond

Reputation: 16032

You can try creating a view for this query.

CREATE VIEW your_view AS (

    SELECT 
    t1.id,
    t1.id_game,
    t1.line,
    ...

    t2.id_team_club,
    t2.id_player_feb,
    ...

    FROM tbl030_shots_chart t1
    LEFT JOIN
    tbl006_player_team t2
)

Then in your python code:

sql = 'SELECT * FROM your_view WHERE id_fiba =id_player_feb AND id_team_club = %s'
with connection.cursor() as cursor:
    cursor.execute(sql, (5))

Upvotes: 1

Related Questions