CODEWITHSUNDEEP
pythonmysqlmysql-python
roby
roby

Reputation: 123

loop over all tables in mysql databases

I am new with MySQL and I need some help please. I am using MySQL connector to write scripts.

I have database contain 7K tables and I am trying to select some values from some of these tables 

cursor.execute( "SELECT SUM(VOLUME) FROM stat_20030103 WHERE company ='Apple'")
for (Volume,) in cursor:
print(Volume)

This works for one table e.g (stats_20030103). However I want to sum all volume of all tables .startwith (stats_2016) where the company name is Apple. How I can loop over my tables?

Upvotes: 0

Views: 3748

Answers (3)

Ondra Žižka
Ondra Žižka

Reputation: 46806

I'd try to left-join.

SELECT tables.*, stat.company, SUM(stat.volume) AS volume 
    FROM information_schema.tables AS tables LEFT JOIN mydb.stat_20030103 AS stat 
    WHERE tables.schema = "mydb" GROUP BY stat.company;

This will give you all results at once. Maybe MySQL doesn't support joining from metatables, in which case you might select it into a temporary table.

CREATE TEMPORARY TABLE mydb.tables SELECT name FROM information_schema.tables WHERE schema = "mydb"

See MySQL doc on information_schema.table.

Upvotes: 0

Alberto Sastre
Alberto Sastre

Reputation: 61

I'm not an expert in MySQL, but here is something quick and simple in python:

# Get all the tables starting with "stats_2016" and store them
cursor.execute("SHOW TABLES LIKE 'stats_2016%'")
tables = [v for (v, ) in cursor]

# Iterate over all tables, store the volumes sum
all_volumes = list()
for t in tables:
    cursor.execute("SELECT SUM(VOLUME) FROM %s WHERE company = 'Apple'" % t)
    # Get the first row as is the sum, or 0 if None rows found
    all_volumes.append(cursor.fetchone()[0] or 0)

# Return the sum of all volumes
print(sum(all_volumes))

Upvotes: 1

r0xette
r0xette

Reputation: 908

You can probably use select * from information_schema.tables to get all tables name into your query.

Upvotes: 0

Related Questions