[INSTRM-1334] Add efficient DataFrame <--> opdb methods Created: 03/Aug/21 Updated: 18/Aug/21 |
|
| Status: | Open |
| Project: | Instrument control development |
| Component/s: | spt_operational_database |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Task | Priority: | Normal |
| Reporter: | cloomis | Assignee: | cloomis |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Description |
|
With sqlalchemy 1.4, I have Opdb class methods to bulk transfer data in and out of opdb tables, directly to/from pandas DataFrames. We have had other functions to do that, but the older sqlalchemy made that very confusing and odd. |
| Comments |
| Comment by Kiyoto Yabe [ 18/Aug/21 ] |
|
I'm taking a look at your implementation (as of Jul 22) but `bulkInsert` does not work for me. I needed to change slightly like:
buf = io.StringIO()
data.to_csv(buf, header=False, index=False)
buf.seek(0)
sqlCmd = f"COPY {tablename} FROM STDIN WITH (FORMAT CSV)"
But still cannot insert anything. There seems to be a problem in
with self.engine.connect() as conn:
with conn.connection.cursor() as cursor:
cursor.copy_expert(sqlCmd, buf)
The following works simply but you like to use context manager here... conn = self.engine.raw_connection() cursor = conn.cursor() cursor.copy_expert(sqlCmd, buf) conn.commit() Maybe I'm doing something wrong. |