1

I've been trying to write a bulk insert to a table.

I've already connected and tried to use SQLAlchemy bulk insert functions, but it's not really bulk inserting, it inserts the rows one by one (the dba tracked the db and showed me).

I wrote a class for the table:

Class SystemLog(Base):
    __tablename__ = 'systemlogs'

   # fields goes here...

Because of the fact that the bulk insert functions doesn't work I want to try to use a stored procedure.

I have a stored procedure named 'insert_new_system_logs' that receives a tablevar as a parameter.

How can I call it with a table from python SQLAlchemy?

My SQLAlchemy version is 1.0.6

I can't paste my code because it's in a closed network.

I don't have to use SQLAlchemy, I just want to bulk insert my logs.

TZHX
  • 5,291
  • 15
  • 47
  • 56
wa11a
  • 183
  • 1
  • 7
  • 1
    Possible duplicate of [this question](http://stackoverflow.com/questions/3563738/stored-procedures-with-sqlalchemy) – lrnzcig Aug 28 '15 at 08:45
  • Not exactly because I didn't succeeded seding a table to a procedure. In adcance, I dont have to use the stored procedure, I just want to bulk insert rows. – wa11a Aug 28 '15 at 08:51
  • Sorry, don't really understand then. How do you pass the table to the stored procedure? Can you at least post the header? – lrnzcig Aug 28 '15 at 08:54
  • When my friend did it he just declared a new table in 'user defined data types' and insert everything there, and then used the peocedure the dba gave us that accepts the user defined type as a parameter. The reason this is not good is that for bulk insert to the table you do a lot of inserts to other table and that's still a lot of requests to the server. If this explanation doesn't help I would get the code example next time im in the office, within two days. – wa11a Aug 28 '15 at 09:01
  • Yes, I understood why you don't want to use the bulk insert. But I just don't see how your stored procedure will pass a parameter in a different way (i.e. compared to the question in my comment above). Please do share some code when possible. – lrnzcig Aug 28 '15 at 09:07
  • Maybe there's no different way. That's why I'm asking. Maybe the only way is to consruct one big statement. – wa11a Aug 28 '15 at 09:24
  • 1
    Well, honestly I'm not sure about what are the options in `sql-server`. I've faced a very similar problem in `oracle` (in the sense that using inserts one by one is too slow) and I use `external tables`, indeed generating some big statements in python, and writing the rows to a `csv` file, so that `oracle` loads it. My times are reduced by an order of magnitude, even taking into account the time to write the `csv` files. – lrnzcig Aug 28 '15 at 09:38
  • Thanks, maybe the best way is really write a csv file and load it. I'll try. If it wirks I'll post the solution here. – wa11a Aug 28 '15 at 09:46
  • Possible duplicate of [How to call stored procedure with SQLAlchemy that requires a user-defined-type Table parameter](https://stackoverflow.com/questions/50141058/how-to-call-stored-procedure-with-sqlalchemy-that-requires-a-user-defined-type-t) – Ilja Everilä May 03 '18 at 20:33

0 Answers0