Comments (7)
1.Are you connecting with autocommit on or off?
2.Post an ODBC trace.
from pyodbc.
1.Are you connecting with autocommit on or off? - Auto commit is off
2.Post an ODBC trace.
ODBC_Trace.LOG
from pyodbc.
above mentioned code is workaround where I am passing same cursor to all the written method which is giving me expected results i.e. rollback in case of failure
``
import pyodbc
import pandas as pd
from pyodbc import apilevel
class DBConnection:
def init(self):
self.conn = None
def __enter__(self):
try:
# Initialize the connection
self.conn = pyodbc.connect()
# Begin transaction
self.conn.autocommit = False
self.error_occurred = False
return self
except Exception as e:
print(f"Error occurred while establishing connection: {e}")
raise
def __exit__(self, exc_type, exc_value, traceback):
try:
if self.conn:
if exc_type is not None or self.error_occurred:
# Rollback transaction if exception occurred
print(f"exception received here--->{exc_type}")
self.conn.rollback()
self.error_occurred = False
else:
print("Coming for commit--->")
# Commit transaction if no exception occurred
self.conn.commit()
# Reset autocommit to default value
self.conn.close()
except Exception as e:
print(f"Error occurred while closing connection: {e}")
def select_data(self, query):
try:
with self.conn.cursor() as cursor:
cursor.execute(query)
rows = cursor.fetchall()
if rows:
columns = [str(column[0]) for column in cursor.description]
df = pd.DataFrame.from_records(rows, columns=columns)
return df
else:
return pd.DataFrame()
except Exception as e:
print(f"Error occurred while executing select query: {e}")
raise
def bulk_update(self, table, updates):
try:
with self.conn.cursor() as cursor:
for update in updates:
update_query = f"UPDATE {table} SET {', '.join([f'{col} = ?' for col in update['columns']])} WHERE {update['condition']}"
cursor.execute(update_query, update['values'])
except Exception as e:
print(f"Error occurred while bulk updating data: {e}")
self.error_occurred = True
raise
def single_insert(self, table, data):
"""
Perform a single insert operation for a row of data into the specified table.
Args:
table (str): The name of the table where the data will be inserted.
data (dict): A dictionary representing the data to be inserted.
The keys should correspond to column names and values
should correspond to the values to be inserted.
Raises:
Exception: If an error occurs during the insert operation.
"""
try:
with self.conn.cursor() as cursor:
columns = ', '.join(data.keys())
placeholders = ', '.join(['?' for _ in data.values()])
insert_query = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
cursor.execute(insert_query, list(data.values()))
except Exception as e:
print(f"Error occurred while performing single insert: {e}")
self.error_occurred = True
raise
def bulk_insert(self, table, df):
try:
with self.conn.cursor() as cursor:
# Extract column names and values from the DataFrame
columns = list(df.columns)
values = df.values.tolist()
# Generate the INSERT INTO query
insert_query = f"INSERT INTO {table} ({', '.join(columns)}) VALUES ({', '.join(['?'] * len(columns))})"
# Execute the query
cursor.executemany(insert_query, values)
except Exception as e:
print(f"Error occurred while bulk inserting data: {e}")
self.error_occurred = True
raise
df = pd.DataFrame({
'mlc_component': ['1_bj'],
'mlc_section': ['1_bj'],
'mlc_key': ['1_bj'],
'mlc_value': ['1_bj']
})
data_to_insert = {
'mlc_component': '3_bj',
'mlc_section': '3_bj',
'mlc_key': '3_bj',
'mlc_value': '3_bj'
}
data1_to_insert = {
'mlc_component': '2_bj',
'mlc_section': '2_bj',
'mlc_key': '2_bj',
'mlc_value': '2_bj'
}
with DBConnection() as db_conn:
try:
# db_conn.bulk_insert("data.data_configuration", df)
print("apilevel here--->", apilevel)
cursor = db_conn.conn.cursor()
cursor.execute("BEGIN TRANSACTION") # Start transaction
db_conn.bulk_insert('data.data_configuration', df)
db_conn.single_insert('data.data_configuration', data_to_insert)
db_conn.single_insert('data.data_configuration', data_to_insert)
cursor.execute("COMMIT TRANSACTION") # Commit if both inserts succeed
# db_conn.bulk_insert("data_configuration", df)
except Exception as e:
cursor.execute("ROLLBACK TRANSACTION") # Rollback on any exception
raise e
Trace is provided for this code where above behavior is observed.
from pyodbc.
@SumitDangat , there's a pyodbc wiki article that might be helpful here:
https://github.com/mkleehammer/pyodbc/wiki/Database-Transaction-Management
In short, you probably shouldn't be executing "COMMIT TRANSACTION" SQL commands (use conn.commit()
instead), and if you want two concurrent database transactions, you'll have to open up two connections to the database. Hope that helps.
from pyodbc.
@keitherskine : Thanks for the prompt response.
In above use case what I am trying is:
BEGIN TRANSACTION
INSERT INTO TABLE VALUES ...
UPDATE TABLE SET ...
COMMIT TRANSACTION
In above code only one connection is created and within that multiple cursors are opened which you can see in bulk_insert() and single insert. as per the above reference link it should be rolled back when within one connection if any of the execute statement fails as per the code which is not happening.
Help me with possible code changes in above code which can get me the expected results if possible :)
from pyodbc.
I think I see where the issue is, @SumitDangat . It's the use of pyodbc context managers (with self.conn...
). It's not obvious, but pyodbc cursor context managers include an implicit commit. This is described in the wiki here:
https://github.com/mkleehammer/pyodbc/wiki/Cursor#context-manager
So, instead of using with self.conn.cursor() as cursor:
, I would either use just cursor = self.conn.cursor()
or the context.closing utility, as described in the wiki:
from contextlib import closing
with closing(self.conn.cursor()) as cursor:
...
from pyodbc.
Thanks @keitherskine for providing valuable inputs. Given solution is working as per the expectations.
from pyodbc.
Related Issues (20)
- im('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: The wait operation timed out.\r\n (258) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 18 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to 10.0.0.5\\MSSQL16.MSSQLSERVER,1454. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (258)')
- Raspberry Pi, Ubuntu 22.04 arm64 ODBC18 SSL error HOT 2
- Can't build release artifacts due to cibuildwheel issue HOT 11
- pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/usr/local/lib/libmyodbc8a.so' : file not found (0) (SQLDriverConnect)") HOT 6
- Linux openSUSE Tumbleweed connection to MariaDB stopped working with version 5.x.x HOT 12
- sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000102:SSL routines::unsupported protocol] (-1) (SQLDriverConnect)') HOT 1
- Connection is busy with results for another command (0) (SQLExecDirectW) HOT 3
- MACOS 系统使用 HOT 4
- Memory leak when passing strings within table value parameter to stored proc HOT 9
- Add support for SQLTablePrivileges
- Login Timeout Error when connecting to Synapse database using pyodbc python library in Azure synapse notebook via Managed Identity HOT 1
- reference count encoding in the connect function HOT 5
- PyToCType uses internal function removed from Python 3.13 HOT 3
- I am trying to install pyodbc in MacBook Pro M3 chip - arm64. The drivers are installed succesfully for odbc 13,17 and 18. But pyodbc is not able to detect the drivers.:
- I am trying to install pyodbc in MacBook Pro M3 chip - arm64. The drivers are installed succesfully for odbc 13,17 and 18. But pyodbc is not able to detect the drivers. ERROR: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)") HOT 5
- Executing multiple queries HOT 1
- pyodbc.connect() gives an error when executing it with pytest HOT 1
- InterfaceError: Data source name not found with pyodbc despite working with isql HOT 8
- Multiple threads querying same table takes a long time HOT 19
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pyodbc.