1

I am working with two csv files that i have merged into one dataframe that i am currently storing as an sql databse using pandas to_sql(). I am writing all my app with Flask and i would like to use SQLAlchemy. So far i have created the sql database like this:

df.to_sql("User", con=db.engine, if_exists="append")

The problem now is that i would like to keep using SQLAlchemy 'syntax' to do queries and to use features like pagination. This is how it should be if i wanted to execute a query:

users = User.query.all().paginate(...)

However i haven't created in my models.py my User table since when doing it with pandas to_sql it will automatically create the table into the database for me. But now since i don't have my table defined in my models.py file i don't know how can i define my global variable 'Users' so i can proceed to use the query and other SQLAlchemy methods.

Thank you,

Ana
  • 167
  • 3
  • 17
  • You can still define the `Model` when the table already exists. You just have to make sure that the column characteristics (name, type, nullable, primary key) of the `Model` match those of the table generated by `pandas`. – Abdou Aug 27 '17 at 16:19
  • Thank you so much for your reply. Why is this a necessary step though given that the to_sql method has already created the table for me? (i am having a hard time understing it) – Ana Aug 27 '17 at 16:30
  • The `Model` has methods that allow you to `query` from the table just the way you like it. In this case, the only reason you would need `Models` is to be able to use features like `paginate`, `filter`, etc. – Abdou Aug 27 '17 at 16:36
  • if i have no existing id column in my df and since i will be appending more dataframes to the existing database, how can i add an id column through my defined table? – Ana Aug 27 '17 at 17:20
  • You will need to add that id column to the dataframes before using `.to_sql`. Since `.to_sql` is the operation creating these tables, you have no other ***easy*** choice but to add the `id` column to the dataframes. You can try and alter the tables after the `.to_sql` operation, but that's not really a good idea. – Abdou Aug 27 '17 at 17:24
  • Thank you again! it surprised me still that is a requirement to define my table in my models file, i would have thought the to_sql method and the read_sql methos would allow me to make queries and filters using SQLAlchemy expressions to avoid repeting the step of declaring the table. – Ana Aug 27 '17 at 17:26
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/152959/discussion-between-abdou-and-ana). – Abdou Aug 27 '17 at 18:51

0 Answers0