A story I was addicted to when inserting from Python to a PostgreSQL table

problem

import psycopg2

#DB connection information
postgre_user_name = "admin"
postgre_user_password = "password"
postgre_server_port = 5432
postgre_database_name = "hogefuga"

connection = psycopg2.connect(f"host=localhost port={postgre_server_port} dbname={postgre_database_name} user={postgre_user_name} password={postgre_user_password}")
cur = connection.cursor()

result = json.dumps(result)
id = 1234
number = 1234

sql_sentence = f"INSERT INTO slip(content, id, number) values('{result}', '{id}', '{number}');"
print(sql_sentence)
cur.execute(sql_sentence)

After executing the above code, even if I check the table, it is not inserted ...

If you execute the query output by print (sql_sentence) directly from zsh, it will be inserted properly.

INSERT INTO slip(content, id, number) values('{"billing_sum":3257.21}', '111011111110000008001', '14');
INSERT 0 1

Solutions

After executing the cur object, commit () the psycopg2.connect object.

import psycopg2

#DB connection information
postgre_user_name = "admin"
postgre_user_password = "password"
postgre_server_port = 5432
postgre_database_name = "hogefuga"

connection = psycopg2.connect(f"host=localhost port={postgre_server_port} dbname={postgre_database_name} user={postgre_user_name} password={postgre_user_password}")
cur = connection.cursor()

result = json.dumps(result)
id = 1234
number = 1234

sql_sentence = f"INSERT INTO slip(content, id, number) values('{result}', '{id}', '{number}');"
print(sql_sentence)
cur.execute(sql_sentence)
connection.commit() #← This line

Transactions are enabled by default in psycopg2, so it will not be reflected unless you commit. You must set autocommit = True to disable transactions.

Recommended Posts

A story I was addicted to when inserting from Python to a PostgreSQL table
A story that I was addicted to when I made SFTP communication with python
A story that I was addicted to calling Lambda from AWS Lambda.
A note I was addicted to when creating a table with SQLAlchemy
A story that I was addicted to at np.where
What I was addicted to when using Python tornado
A note I was addicted to when running Python with Visual Studio Code
What I was addicted to when migrating Processing users to Python
What I was addicted to Python autorun
What I was addicted to when introducing ALE to Vim for Python
A note I was addicted to when making a beep on Linux
The story I was addicted to when I specified nil as a function argument in Go
When I tried to scrape using requests in python, I was addicted to SSLError, so a workaround memo
A story I was addicted to trying to install LightFM on Amazon Linux
I was addicted to creating a Python venv environment with VS Code
A story I was addicted to trying to get a video url with tweepy
Use Python from Java with Jython. I was also addicted to it.
What I did when updating from Python 2.6 to 2.7
[Python] When I tried to make a decompression tool with a zip file I just knew, I was addicted to sys.exit ()
What I was addicted to when creating a web application in a windows environment
Three things I was addicted to when using Python and MySQL with Docker
I was addicted to scraping with Selenium (+ Python) in 2020
What I was addicted to when combining class inheritance and Joint Table Inheritance in SQLAlchemy
I wrote Python code to create a table (view) dependency diagram (PlantUML) from SQL
I want to start a lot of processes from python
I want to send a message from Python to LINE Bot
[Fixed] I was addicted to alphanumeric judgment of Python strings
When I tried to install PIL and matplotlib in a virtualenv environment, I was addicted to it.
A story addicted to Azure Pipelines
When creating a pipenv environment, I got addicted to "Value Error: Not a valid python path"
I was surprised to receive a nice review when I wrote Python to CheckIO and its explanation
What I was addicted to when dealing with huge files in a Linux 32bit environment
I was addicted to multiprocessing + psycopg2
The record I was addicted to when putting MeCab on Heroku
A story that I fixed when I got Lambda logs from Cloudwatch Logs
What I was addicted to with json.dumps in Python base64 encoding
[Python] How to get & change rows / columns / values from a table.
I was addicted to confusing class variables and instance variables in Python
I was a little addicted to installing Python3.3 + mod_wsgi3.4 on Sakura VPS (CentOS), so a retrospective memo
I wrote AWS Lambda, and I was a little addicted to the default value of Python arguments
A story that didn't work when I tried to log in with the Python requests module
About the error I encountered when trying to use Adafruit_DHT from Python on a Raspberry Pi
Send a message from Python to Slack
I want to use jar from python
I was addicted to pip install mysqlclient
I want to build a Python environment
I was addicted to Flask on dotCloud
I made a plugin to generate Markdown table from csv in Vim
I was able to print the thermal printer "PAPERANG" from Python (Windows10, Python3.6)
When I tried to run Python, it was skipped to the Microsoft Store
[Python memo] I want to get a 2-digit hexadecimal number from a decimal number
[Python] Warning came out when I raised from Selenium 3 to 4 [Web Driver]
When running a Python shell from Electron, pass multiple arguments to run Python.
I want to convert a table converted to PDF in Python back to CSV
Connect to postgreSQL from Python and use stored procedures in a loop.
I get a UnicodeDecodeError when trying to connect to oracle with python sqlalchemy
The file name was bad in Python and I was addicted to import
[Python] I was addicted to not saving internal variables of lambda expressions
I was addicted to trying Cython with PyCharm, so make a note
A story that suffered from OS differences when trying to implement a dissertation
Send a message from Slack to a Python server