Yeah, this is a real mess. Both MySQL and PostgreSQL use backslash-escapes for this by default. This is a terrible pain if you’re also escaping the string again with backslashes instead of using parameterisation, and it’s also incorrect according to ANSI SQL:1992, which says there are by default no extra escape characters on top of … Read more
I would use the urlparse module to parse the url and then use the result in the connection method. This way it’s possible to overcome the psycop2 problem. from urlparse import urlparse # for python 3+ use: from urllib.parse import urlparse result = urlparse(“postgresql://postgres:postgres@localhost/postgres”) username = result.username password = result.password database = result.path[1:] hostname = … Read more
This question is really old, but still pops up on Google searches so I think it’s valuable to know that the psycopg2.connection instance now has a closed attribute that will be 0 when the connection is open, and greater than zero when the connection is closed. The following example should demonstrate: import psycopg2 import subprocess … Read more
Python 2: sudo apt-get install python-dev Python 3: sudo apt-get install python3-dev
cur.execute(“INSERT INTO product(store_id, url, price, charecteristics, color, dimensions) VALUES (%s, %s, %s, %s, %s, %s)”, (1, ‘http://www.google.com’, ‘$20’, json.dumps(thedictionary), ‘red’, ‘8.5×11’)) That will solve your problem. However, you really should be storing keys and values in their own separate columns. To retrieve the dictionary, do: cur.execute(‘select charecteristics from product where store_id = 1’) dictionary = … Read more
To insert null values to the database you have two options: omit that field from your INSERT statement, or use None Also: To guard against SQL-injection you should not use normal string interpolation for your queries. You should pass two (2) arguments to execute(), e.g.: mycursor.execute(“””INSERT INTO products (city_id, product_id, quantity, price) VALUES (%s, %s, … Read more
the answer to this is a combination of: http://initd.org/psycopg/docs/module.html#psycopg2.connect Any other connection parameter supported by the client library/server can be passed either in the connection string or as keywords. The PostgreSQL documentation contains the complete list of the supported parameters. Also note that the same parameters can be passed to the client library using environment … Read more
The problem is you should not be running python base.py as part of the RUN directive. The RUN directive is executed only when you are building the image. The postgres container is not running at this point, nor has the network been created. Instead you want to use the CMD directive. Change the Dockerfile to … Read more
Python tuples are converted to sql lists in psycopg2: cur.mogrify(“SELECT * FROM table WHERE column IN %s;”, ((1,2,3),)) would output ‘SELECT * FROM table WHERE column IN (1,2,3);’ For Python newcomers: It is unfortunately important to use a tuple, not a list here. Here’s a second example: cur.mogrify(“SELECT * FROM table WHERE column IN %s;”, … Read more
If don’t want to have to commit each entry to the database, you can add the following line: conn.autocommit = True So your resulting code would be: import psycopg2 try: conn = psycopg2.connect(“dbname=”djangostack” user=”bitnami” host=”localhost” password=’password'”) conn.autocommit = True except: print “Cannot connect to db” cur = conn.cursor() try: cur.execute(“””insert into cnet values (‘r’, ‘s’, … Read more