Read all columns and change name of a column in mysql-CodePudding

I have a table in Mysql. The table has more than 200 columns. I want to read all the columns, however, I have to read one of the column (A) as a specific name. Now, I have to use two lines of code and it take times.

df1 = pd.read_sql("""SELECT * FROM mytable""", db1)
df2 = pd.read_sql("""SELECT A as specific_name FROM mytable""", db1)

I want to ask that is anyway to use only one line of code? Thanks

CodePudding user response：

Try something like this:

df = pd.read_sql("""SELECT T.*, T.A as specific_name FROM mytable T""", db1)

It should give all columns of my table and then added specific_name column at the end.

Update:

As a side note - if you have really big table (rows-wise) - you can just select all columns (don't query specific_name), and then rename column A with df.rename(columns={"A": "specific_name"}). Saves traffic and I would guess more elegant.

df = pd.read_sql("""SELECT T.* FROM mytable T""", db1)
df.rename(columns={"A": "specific_name"})

CodePudding user response：

I can answer this with 1 line of SQL and 1 line of python if that would help:

df2 = pd.read_sql("""SELECT A as specific_name , * FROM mytable""", db1)
df2 .drop('A', axis = 1, inplace = True)

This will read all of the columns form mytable into a df then once you've created your df you can simply drop the 'A' column since you renamed it to something else anyways.