How to automatically rename columns from same table join?
SQLite is designed as a small, embedded database to be accessed from a 'real' programming language, so it has no built-in mechanism for dynamic SQL.

You have to construct the SQL query from inside that other programming language; something like this:

cols = []
for tab in tables:
    for col in columns:
       cols.append("c_{}_{}".format(tab, col))
sql = "SELECT " + ", ".join(cols) + " ..."
execute(sql)