Python Psycopg error and connection handling (v MySQLdb)
Is there a way to make psycopg and postgres deal with errors without having to reestablish the connection, like MySQLdb? The commented version of the below works with MySQLdb, the comments make it work with Psycopg2:
results = {'felicitas': 3, 'volumes': 8, 'acillevs': 1, 'mosaics': 13, 'perat\xe9': 1, 'representative': 6....}
for item in sorted(results):
try:
cur.execute("""insert into resultstab values ('%s', %d)""" % (item, results[item]))
print item, results[item]
# conn.commit()
except:
# conn=psycopg2.connect(user='bvm', database='wdb', password='redacted')
# cur=conn.cursor()
print 'choked on', item
continue
This must slow things down, could anyone give a suggestion for passing over formatting errors? Obviously the above chokes on apostrophes, but is there a way to make it pass over that without getting something like the following, or committing, reconnecting, etc?:
agreement 19
agreements 1
agrees 1
agrippa 9
choked on agrippa's
choked on agrippina
Asked by: Lily957 | Posted: 28-01-2022
Answer 1
First of all you should let psycopg do the escaping for you by passing to the execute() method the parameters instead of doing the formatting yourself with '%'. That is:
cur.execute("insert into resultstab values (%s, %s)", (item, results[item]))
Note how we use "%s" as a marker even for non-string values and avoid quotes in the query. psycopg will do all the quoting for us.
Then, if you want to ignore some errors, just rollback and continue.
try:
cur.execute("SELECT this is an error")
except:
conn.rollback()
That's all. psycopg will rollback and start a new transaction on your next statement.
Answered by: Dainton170 | Posted: 01-03-2022Answer 2
I think your code looks like this at the moment:
l = "a very long ... text".split()
for e in l:
cursor.execute("INSERT INTO yourtable (yourcol) VALUES ('" + e + "')")
So try to change it into something like this:
l = "a very long ... text".split()
for e in l:
cursor.execute("INSERT INTO yourtable (yourcol) VALUES (%s)", (e,))
so never forget to pass your parameters in the parameters list, then you don't have to care about your quotes and stuff, it is also more secure. You can read more about it at http://www.python.org/dev/peps/pep-0249/
also have a look there at the method .executemany() which is specially designed to execute the same statement multiple times.
Answered by: Miranda518 | Posted: 01-03-2022Similar questions
python - handling [Errno 111] Connection refused return by requests in flask
I have my backend developed in java which does all kind of processing. And my frontend is developed using python's flask framework. I am using requests to send a request and get a response from the apis present in java.
Following is the line in my code which does that:
req = requests.post(buildApiUrl.getUrl('user') + "/login", ...
bukkit - Python Error Handling & Connection Errors
I'm working on a Telnet client. I started coding on my notebook(Windows) and at the finish I uploaded it on my server(Debian). Both systems works with Python 3. At my notebook the script works well, but on Debian, it does make errors.
The Code:
import telnetlib
import sys
try:
HOST = sys.argv[1]
user = sys.argv[3]
password = sys.argv[4]
cmd= sys.argv[5]
port=int(sys.argv[2])
...
Python Socket Handling Multiple Connection
I'd like to let more than one client connect to my server, and have the server send them different items. For example send "hi" to the first client and "goodbye" to the second . Here's my code:
Server
import socket
file_num = 0
inp = raw_input("Name of the wordlist file = ")
inp2 = input("Number of lines for every wordlist = ")
with open(inp) as in_file:
for line_num, line in enumerate(in_file):
...
python - Handling socket error with try, except [Errno 104] Connection reset by peer
I'm trying catch the exception but the script continues and outputs none when something goes wrong with the proxy connection such as Socket Error: Socket error: [Errno 104] Connection reset by peer for the expiration date of google
import socket
import socks
import whois
import requests
try:
r = requests.get('http://gimmeproxy.com/api/getProxy?protocol=socks5&maxCheckPeriod...
python - AWS Lambda & MySQL Connection Handling
I am currently using AWS Lambda (Python 3.6) to talk to a MySQL database. I also have Slack commands triggering the queries to the database. On occasion, I have noticed that I can change things directly through MySQL Workbench and then trigger a query through Slack which returns old values. I currently connect to MySQL outside of the python handler like this:
BOT_TOKEN = os.environ["BOT_TOKEN"]
ASSET_TABL...
error handling for multiple database queries with single connection in python
What is the best way to execute an array of queries with try catch on each query in python?
Currently i loop over all queries in the database within a try catch block to catch overall query errors. How do i convert it to a system where i can catch errors on the query level?
conn = get_connection()
try:
for query in queries:
execute(query)
except Exce...
python - Handling connection with imaplib while using context manager
I am trying to connect to mail server using python's imaplib . Here is how my code currently stands:
@contextlib.contextmanager
def get_connection(username: str, password: str, host=GMAIL_IMAP_HOST, port=GMAIL_IMAP_PORT):
'''
ARGS:
username: User username
password: User Password
host: IMAP server address
post: IMAP port
Connect to IMAP server and clo...
python - Handling Connection Error when using Tweepy
Hope you're all well.
I'm trying to extract tweets continuously for a long period of time, using tweepy library. For that I have a python script with an infinite loop running. The only problem is that everytime the connection fails, for any possible reason, my script will crash and I will have to restart it. So I wanted to do it robust specially to connection errors,and for that I am using try...except condition but...
Testing socket connection in Python
This question will expand on: Best way to open a socket in Python
When opening a socket how can I test to see if it has been established, and that it did not timeout, or generally fail.
Edit:
I tried this:
try:
s.connect((address, '80'))
except:
alert('failed' + address, 'down')
python - Mysql Connection, one or many?
I'm writing a script in python which basically queries WMI and updates the information in a mysql database. One of those "write something you need" to learn to program exercises.
In case something breaks in the middle of the script, for example, the remote computer turns off, it's separated out into functions.
Query Some WMI data
Update that to the database
Query Other WMI data
Update that to t...
python - Connection refused on Windows XP network
This is only marginally a programming problem and more of a networking problem.
I'm trying to get a Django web app to run in my home network and I can't get any machines on the network to get to the web page. I've run on ports 80 and 8000 with no luck. Error message is: "Firefox can't establish a connection to the server at 192.168.x.x."
I've tried using sockets in python from the client:
i...
tcp - db connection in python
I am writing a code in python in which I established a connection with database. I have queries in a loop. While queries being executed in the loop , If i unplug the network cable it should stop with an exception. But this not happens, When i again plug yhe network cabe after 2 minutes it starts again from where it ended. I am using linux and psycopg2. It is not showing exception
java - How do you create an anonymous Python telnet connection?
I am trying to telnet into a server using Python on Windows XP. I can connect successfully by typing 'telnet HOST PORT' which creates an anonymous connection. But Python's telnetlib.Telnet(HOST, PORT) returns 'Connection refused'. Telnetting in Java also fails. Spelunking shows that Python tries to create an anonymous socket connection. My admin says he doesn't allow anonymous connections. But neither Python nor Java...
python db connection
I am having a script which makes a db connection and pereform some select operation.accroding to the fetch data i am calling different functions which also perform db operations.How can i pass db connection to the functions which are being called as i donot want to make new connection
file - SSH Connection with Python 3.0
How can I make an SSH connection in Python 3.0? I want to save a file on a remote computer where I have password-less SSH set up.
python - Running programs w/ a GUI over a remote connection
I'm trying to start perfmon and another program that have GUI's through a python script that uses a PKA ssh connection. Is it possible to do this? If so could anyone point me in the right direction?
python - How to increase connection pool size for Twisted?
I'm using Twisted 8.1.0 as socket server engine. Reactor - epoll. Database server is MySQL 5.0.67. OS - Ubuntu Linux 8.10 32-bit
in /etc/mysql/my.cnf :
max_connections = 1000
in source code:
adbapi.ConnectionPool("MySQLdb", ..., use_unicode=True, charset='utf8',
cp_min=3, cp_max=700, cp_noisy=False)
But i...
python - Making sure that psycopg2 database connection alive
I have a python application that opens a database connection that can hang online for an hours, but sometimes the database server reboots and while python still have the connection it won't work with OperationalError exception.
So I'm looking for any reliable method to "ping" the database and know that connection is alive. I've checked a psycopg2 documentation but can't find anything like that. Sure I ...
Still can't find your answer? Check out these communities...
PySlackers | Full Stack Python | NHS Python | Pythonist Cafe | Hacker Earth | Discord Python