Importing all the sql tables into python using pandas dataframe

Question

I have an requirement wherein I would like to import all the tables stored in the sql database into python. I have successfully created a python code for it as follows:


import pandas as pd
import mysql.connector
from pandas import DataFrame
db = mysql.connector.connect(host="localhost", user="root", passwd="********")
pointer = db.cursor()
pointer.execute("use stock")
pointer.execute("SELECT * FROM 3iinfotech")
data = pointer.fetchall()
data = DataFrame(data,columns =['date','open','high','low','close','volume'])

Using this I am able to successfully import the Tables data into a pandas Dataframe.

But as can be seen from the database schema there are multiple tables which will again increase in the future.

Full database schema along with output of the table

The dataframe looks like:

Imported table from sql converted to DataFrame

Is there any ways using loops or by any other methods that this script can be automated for all the tables in a given database.

I referred the following :

Importing multiple SQL tables using pandas

But this does not work in my case.

Thanks.....

Renaud · Accepted Answer · 2020-11-17 09:10:03Z

1

The SQL error come from ' around the table name, It's not the best but if you are working on a local application, you can workaround with the f strings:

with connection.cursor() as pointer:
    pointer.execute("use use stock")
    pointer.execute("SHOW TABLES")

    tables_tuples=[]
    for table_dict in pointer:
        for k,v in table_dict.items():
            tables_tuples.append(v)


for tables_name in tables_tuples:
    with connection.cursor() as pointer:
    
        #pointer.execute(f"SELECT * FROM %s",tables_name)
        pointer.execute(f"SELECT * FROM {tables_name}")
        
        connection.commit()


        df=pd.DataFrame(pointer)
        print(df)

edited Nov 17, 2020 at 9:10

answered Nov 17, 2020 at 7:55

Renaud

2,8172 gold badges12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Huzefa Sadikot Over a year ago

The statement: pointer.execute("SELECT * FROM %s", table) Shows an error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''3iinfotech'' at line 1"

Collectives™ on Stack Overflow

Importing all the sql tables into python using pandas dataframe

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related