CODEWITHSUNDEEP
pythonmysqlpandassqlalchemy
Yas
Yas

Reputation: 97

How to print mySQL columns with sqlalchemy

We want to fetch data from our mySQL database, and we're using python (sqlalchemy) to do so. We're then saving the data on pandas dataframes. So far we're receiving data, but the column names are not included, and is automatically just indexed instead. How can we include column names, so that the true names are included and not just numbers from 0-5. 

import pandas as pd
from pandas.io import sql
from sqlalchemy import create_engine

engine = create_engine("mysql://root:DTULab@123@localhost/Afgangsprojekt?host=localhost?port=3306")
conn = engine.connect()
result = conn.execute("SELECT * FROM Weather_Station").fetchall()

df = pd.DataFrame(result)

print(df)

Output prints the following:

       0                    1     2     3     4     5
0      0  2019-07-26 14:50:13  27.3  29.8  45.0  44.0
1      1  2019-07-26 15:00:13  26.9  28.3  44.0  48.0
2      2  2019-07-26 15:10:13  28.0  28.3  41.0  48.0
3      3  2019-07-26 15:20:13  27.8  28.3  39.0  48.0
4      4  2019-07-26 15:30:13  27.0  28.3  40.0  48.0
5      5  2019-07-26 15:40:13  26.8  28.3  42.0  48.0
6      6  2019-07-26 15:50:13  27.0  28.3  42.0  48.0
7      7  2019-07-26 16:00:14  26.8  27.2  42.0  41.0
8      8  2019-07-26 16:10:13  27.0  27.2  42.0  41.0
9      9  2019-07-26 16:20:13  26.8  27.2  43.0  41.0
10    10  2019-07-26 16:30:13  26.4  27.2  44.0  41.0
11    11  2019-07-26 16:40:13  27.1  27.2  42.0  41.0
12    12  2019-07-26 16:50:13  26.2  27.2  43.0  41.0
13    13  2019-07-26 17:00:14  25.6  26.6  44.0  43.0
14    14  2019-07-26 17:10:14  25.5  26.6  47.0  43.0
15    15  2019-07-26 17:20:14  25.3  26.6  49.0  43.0
16    16  2019-07-26 17:30:14  25.1  26.6  51.0  43.0
17    17  2019-07-26 17:40:14  25.6  26.6  52.0  43.0
18    18  2019-07-26 17:50:14  24.8  26.6  55.0  43.0
19    19  2019-07-26 18:00:14  24.4  25.2  57.0  51.0
20    20  2019-07-26 18:10:14  24.6  25.2  57.0  51.0
21    21  2019-07-26 18:20:14  24.4  25.2  58.0  51.0
22    22  2019-07-26 18:30:14  24.4  25.2  58.0  51.0
23    23  2019-07-26 18:40:14  24.8  25.2  57.0  51.0
24    24  2019-07-26 18:50:14  25.0  25.2  57.0  51.0
25    25  2019-07-26 19:00:15  24.9  24.7  57.0  57.0
26    26  2019-07-26 19:10:14  25.1  24.7  56.0  57.0
27    27  2019-07-26 19:20:14  25.4  24.7  49.0  57.0
28    28  2019-07-26 19:30:14  25.4  24.7  48.0  57.0
29    29  2019-07-26 19:40:13  25.4  24.7  48.0  57.0
..   ...                  ...   ...   ...   ...   ...
822  822  2019-08-01 07:30:13  13.7  14.0  94.0  94.0
823  823  2019-08-01 07:40:13  13.6  14.0  95.0  94.0
824  824  2019-08-01 07:50:13  13.6  14.0  97.0  94.0
825  825  2019-08-01 08:00:13  13.9  13.7  97.0  94.0
826  826  2019-08-01 08:10:13  13.8  13.7  94.0  94.0
827  827  2019-08-01 08:20:13  13.6  13.7  93.0  94.0
828  828  2019-08-01 08:30:14  13.6  13.7  92.0  94.0
829  829  2019-08-01 08:40:13  13.8  13.7  92.0  94.0
830  830  2019-08-01 08:50:13  14.0  13.7  91.0  94.0
831  831  2019-08-01 09:00:13  13.9  13.8  91.0  93.0
832  832  2019-08-01 09:10:13  13.9  13.8  90.0  93.0
833  833  2019-08-01 09:20:13  13.8  13.8  91.0  93.0
834  834  2019-08-01 09:30:13  13.6  13.8  93.0  93.0
835  835  2019-08-01 09:40:13  13.6  13.8  94.0  93.0
836  836  2019-08-01 09:50:13  13.6  13.8  94.0  93.0
837  837  2019-08-01 10:00:13  13.9  13.7  94.0  92.0
838  838  2019-08-01 10:10:13  13.9  13.7  95.0  92.0
839  839  2019-08-01 10:20:13  14.0  13.7  94.0  92.0
840  840  2019-08-01 10:30:13  14.3  13.7  95.0  92.0
841  841  2019-08-01 10:40:13  14.4  13.7  95.0  92.0
842  842  2019-08-01 10:50:13  14.6  13.7  94.0  92.0
843  843  2019-08-01 11:00:13  14.9  14.3  94.0  94.0
844  844  2019-08-01 11:10:14  15.0  14.3  93.0  94.0
845  845  2019-08-01 11:20:14  15.3  14.3  93.0  94.0
846  846  2019-08-01 11:30:14  15.5  14.3  92.0  94.0
847  847  2019-08-01 11:40:13  15.5  14.3  92.0  94.0
848  848  2019-08-01 11:50:13  15.4  14.3  85.0  94.0
849  849  2019-08-01 12:00:13  15.3  15.3  86.0  91.0
850  850  2019-08-01 12:10:13  15.3  15.3  86.0  91.0
851  851  2019-08-01 12:20:13  15.3  15.3  87.0  91.0

Upvotes: 0

Views: 189

Answers (2)

Sushant Nair
Sushant Nair

Reputation: 83

Try This

To read : read_sql

To write : to_sql

import pandas as pd
from pandas.io import sql
from sqlalchemy import create_engine
engine = create_engine("mysql://root:DTULab@123@localhost/Afgangsprojekt?host=localhost?port=3306")
connection = engine.connect()
Query = "<Query Here>"
df = pd.read_sql(Query, connection)
print(df.head(50)) # For 50 Rows to be printed

Upvotes: 1

Spark
Spark

Reputation: 2487

You could try calling the read_sql and pass the connection to Read SQL query or database table into a DataFrame : read_sql

import pandas as pd
from pandas.io import sql
from sqlalchemy import create_engine
engine = create_engine("mysql://root:DTULab@123@localhost/Afgangsprojekt?host=localhost?port=3306")
connection = engine.connect()
df = pd.read_sql("SELECT * FROM Weather_Station", connection)
print(df)

Upvotes: 0

Related Questions