TypeError: Object of type ‘DataFrame’ is not JSON serializable
Your df is still a data frame because you haven’t assigned it as json. df = df.to_json() This should work. Let me know if not.
psycopg2
Getting affected row count from psycopg2 connection.commit()
commit() can’t be used to get the row count, but you can use the cursor to get that information after each execute call. You can use its rowcount attribute to get the number of rows affected for SELECT, INSERT, UPDATE and DELETE. i.e. db_crsr = _cxn.cursor() db_crsr.execute(_stmt) rowcount = db_crsr.rowcount _cxn.commit() db_crsr.close() return rowcount If … Read more
Checking if a postgresql table exists under python (and probably Psycopg2)
How about: >>> import psycopg2 >>> conn = psycopg2.connect(“dbname=”mydb” user=”username” host=”localhost” password=’foobar'”) >>> cur = conn.cursor() >>> cur.execute(“select * from information_schema.tables where table_name=%s”, (‘mytable’,)) >>> bool(cur.rowcount) True An alternative using EXISTS is better in that it doesn’t require that all rows be retrieved, but merely that at least one such row exists: >>> cur.execute(“select exists(select … Read more
SQLAlchemy or psycopg2?
SQLAlchemy is a ORM, psycopg2 is a database driver. These are completely different things: SQLAlchemy generates SQL statements and psycopg2 sends SQL statements to the database. SQLAlchemy depends on psycopg2 or other database drivers to communicate with the database! As a rather complex software layer SQLAlchemy does add some overhead but it also is a … Read more
Parameterized queries with psycopg2 / Python DB-API and PostgreSQL
psycopg2 follows the rules for DB-API 2.0 (set down in PEP-249). That means you can call execute method from your cursor object and use the pyformat binding style, and it will do the escaping for you. For example, the following should be safe (and work): cursor.execute(“SELECT * FROM student WHERE last_name = %(lname)s”, {“lname”: “Robert’); … Read more
How to install psycopg2 with pg_config error?
Debian/Ubuntu Python 2 sudo apt install libpq-dev python-dev Python 3 sudo apt install libpq-dev python3-dev Additional If none of the above solve your issue, try sudo apt install build-essential or sudo apt install postgresql-server-dev-all With pip Install the psycopg2-binary PyPI package instead, it has Python wheels for Linux and Mac OS. pip install psycopg2-binary
Create a Postgres database using python
Use ISOLATION_LEVEL_AUTOCOMMIT, a psycopg2 extensions: No transaction is started when command are issued and no commit() or rollback() is required. import psycopg2 from psycopg2 import sql from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT # <– ADD THIS LINE con = psycopg2.connect(dbname=”postgres”, user=self.user_name, host=””, password=self.password) con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) # <– ADD THIS LINE cur = con.cursor() # Use the psycopg2.sql module … Read more
psycopg2 not actually inserting data
You didn’t commit the transaction. Psycopg2 opens a transaction automatically, and you must tell it to commit in order to make the data visible to other sessions. See the psycopg2 FAQ and the connection.commit() method.
Can’t install psycopg2 with pip in virtualenv on Mac OS X 10.7
Just would like to share. The following code worked for me: env LDFLAGS=’-L/usr/local/lib -L/usr/local/opt/openssl/lib -L/usr/local/opt/readline/lib’ pip install psycopg2==2.5.2 I am using macOS Sierra and psql 9.6.1. I got the lib path from the pg_config command.
django.core.exceptions.ImproperlyConfigured: Error loading psycopg module: No module named psycopg
I had the error as well; although psycopg2 was installed on my system using apt-get, my virtualenv couldn’t find it: >>> import psycopg2 Traceback (most recent call last): File “<stdin>”, line 1, in <module> ImportError: No module named psycopg2 It was fixed by doing a pip install psycopg2-binary inside the virtualenv (or pip install psycopg2 … Read more