Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Insert error not raised #9

Open
mverrilli opened this issue Mar 30, 2014 · 4 comments
Open

Insert error not raised #9

mverrilli opened this issue Mar 30, 2014 · 4 comments

Comments

@mverrilli
Copy link

Had a hard time figuring out my problem, however it turns out I was sending a POSIX date value where I should have been sending in a datetime. For whatever reason, the insert pretended to work with no raised error (and in fact, last_inserted_params() also showed me all the values I thought I was inserting). Once I fixed the data coming in, everything worked.

I'll try to simplify the case. Below, if you set event time properly, the insert happens and is committed. However, in both cases, the code thinks the insert happened (even though the time.time() version should have thrown an error since the insert never occurred).

I am new with SQLAlchemy, but if you would like me to output some more debugging code let me know. I noticed this actually is using COPY FROM STDIN with NATIVE (I have a feeling this is happening at the ODBC driver level). Is there a way for me to get precisely what is being sent to the ODBC driver in SQLAlchemy?

file_name='FILE.CSV'
spool_path='/tmp'
event_type='Spooled'

event_time=time.time()

event_time=datetime.datetime.now()

Table('log_file_events', metadata,
Column('file_name', String(128), primary_key=True, nullable=False),
Column('spool_path', String(128), primary_key=True, nullable=False),
Column('event_type', String(128), primary_key=True, nullable=False),
Column('event_time', DateTime, primary_key=True, nullable=False),
)

ins = log_file_events.insert(
values={'file_name': file_name,
'spool_path': spool_path,
'event_type': event_type,
'event_time': event_time,
})
print ins, ins.compile().params
res = session.execute(ins)
print res.last_inserted_params()
session.commit()

@mrab54
Copy link

mrab54 commented Jun 23, 2015

I experienced a similar issue. The object I was attempting to insert had a Python Decimal as a column value when the table column was an Integer type. A flush() and commit() of the object completed with no exceptions raised, but the data was never actually inserted. I changed the Decimal to an int() and the data was inserted successfully.

@mverrilli
Copy link
Author

I have a feeling that since a COPY is being used, that the line is simply getting rejected. I think that the COPY statement should have ABORT ON ERROR set in order to force a failure if any row cannot be inserted. I'm not sure if this execution is happening in the ODBC driver itself or as part of vertica-sqlalhemy.

@mverrilli
Copy link
Author

I did some more research and I think this is using an insert array, and the ODBC driver is converting it to a COPY for efficiency. This is likely causing rejected rows to get lost unreported. I'll bet that if you execute SELECT GET_NUM_REJECTED_ROWS(); right after the insert execution, you'll see a non-zero value returned.

@mrab54
Copy link

mrab54 commented Jun 23, 2015

Indeed, SELECT GET_NUM_REJECTED_ROWS(); returns the number of rejected rows (all of them in my case). Thanks for the pointer.

Lordshinjo referenced this issue in Lordshinjo/sqlalchemy-vertica-python Jun 8, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants