In this SO post, there are two answers that -combined together- provide a nice solution for successfully using ON CONFLICT. The example below, uses ON CONFLICT DO NOTHING;: BEGIN; CREATE TEMP TABLE tmp_table (LIKE main_table INCLUDING DEFAULTS) ON COMMIT DROP; COPY tmp_table FROM ‘full/file/name/here’; INSERT INTO main_table SELECT * FROM tmp_table ON CONFLICT DO NOTHING; … Read more
Here is the binary equivalent of COPY FROM for Python 3: from io import BytesIO from struct import pack import psycopg2 # Two rows of data; “id” is not in the upstream data source # Columns: node, ts, val1, val2 data = [(23253, 342, -15.336734, 2494627.949375), (23256, 348, 43.23524, 2494827.949375)] conn = psycopg2.connect(“dbname=mydb user=postgres”) curs … Read more
from psycopg2.extensions import AsIs song = { ‘title’: ‘song 1’, ‘artist’: ‘artist 1’ } columns = song.keys() values = [song[column] for column in columns] insert_statement=”insert into song_table (%s) values %s” # cursor.execute(insert_statement, (AsIs(‘,’.join(columns)), tuple(values))) print cursor.mogrify(insert_statement, (AsIs(‘,’.join(columns)), tuple(values))) Prints: insert into song_table (artist,title) values (‘artist 1’, ‘song 1’) Psycopg adapts a tuple to a record … Read more
Yeah, I would vote for COPY, providing you can write a file to the server‘s hard drive (not the drive the app is running on) as COPY will only read off the server.
For MacOS users After trying all the above methods (which did not work for me on MacOS 10.14), that one worked : Install openssl with brew install openssl if you don’t have it already. add openssl path to LIBRARY_PATH : export LIBRARY_PATH=$LIBRARY_PATH:/usr/local/opt/openssl/lib/ install psycopg2 with pip pip3 install psycopg2
On the Mac, if you’re using Postgres.app, the pg_config file is in your /Applications/Postgres.app/Contents/Versions/<current_version>/bin directory. That’ll need to be added to your system path to fix this error, like this: export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/<current_version>/bin So for example, if the current Postgres.app version is 9.5, this export line would be: export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.5/bin With more recent versions of the … Read more
Python module io(docs) has necessary tools for file-like objects. import io # text buffer s_buf = io.StringIO() # saving a data frame to a buffer (same as with a regular file): df.to_csv(s_buf) Edit. (I forgot) In order to read from the buffer afterwards, its position should be set to the beginning: s_buf.seek(0) I’m not familiar … Read more
Ok, so I was curious and went and looked at the source of psycopg2. Turns out I didn’t have to go further than the examples folder 🙂 And yes, this is psycopg2-specific. Basically, if you just want to quote a string you’d do this: from psycopg2.extensions import adapt print adapt(“Hello World’; DROP DATABASE World;”) But … Read more
The right way is this one (connect_timeout instead of connection_timeout): create_engine(db_url, connect_args={‘connect_timeout’: 10}) …and it works with both Postgres and MySQL docs sqlalchemy connect-args ps: (the timeout is defined in seconds)
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