What's the best way to make psycopg2 pass parameterized queries to PostgreSQL? I don't want to write my own escpaing mechanisms or adapters and the psycopg2 source code and examples are difficult to read in a web browser. If I need to switch to something like PyGreSQL or another python pg adapter, that's fine with me. I just want simple...
When using the sqlite3 module in python, all elements of cursor.description except the column names are set to None, so this tuple cannot be used to find the column types for a query result (unlike other DB-API compliant modules). Is the only way to get the types of the columns to use pragma table_info(table_name).fetchall() to get a des...
I'm trying out the Concurrence framework for Stackless Python. It includes a MySQL driver and when running some code that previously ran fine with MySQLdb it fails. What I am doing: Connecting to the MySQL database using dbapi with username/password/port/database. Executing SELECT * FROM INFORMATION_SCHEMA.COLUMNS This fails with me...
I'd like to run a PostgreSQL query in Python using psycopg2, which filters by a column of type timestamp without timezone. I have a long list of allowed values for the timestamp (rather than a range) and psycopg2 convenient handles arrays, so I thought that this should work: SELECT somestuff FROM mytable WHERE thetimestamp = ANY (%(time...
I am trying to assemble the following SQL statement using python's db-api: SELECT x FROM myTable WHERE x LIKE 'BEGINNING_OF_STRING%'; where BEGINNING_OF_STRING should be a python var to be safely filled in through the DB-API. I tried beginningOfString = 'abc' cursor.execute('SELECT x FROM myTable WHERE x LIKE '%s%', beginningOfStrin...
Does psycopg2 have a function for escaping the value of a LIKE operand for Postgres? For example I may want to match strings that start with the string "20% of all", so I want to write something like this: sql = '... WHERE ... LIKE %(myvalue)s' cursor.fetchall(sql, { 'myvalue': escape_sql_like('20% of all') + '%' } Is there an existi...
I was attempting to test for connection failure, and unfortunately it's not failing if the ip address of the host is fire walled. This is the code: def get_connection(self, conn_data): rtu, hst, prt, usr, pwd, db = conn_data try: self.conn = pgdb.connect(host=hst+":"+prt, user=usr, password=pwd, database=db) ...
Hi, I'm creating a RESTful API which needs to access the database. I'm using Restish, Oracle, and SQLAlchemy. However, I'll try to frame my question as generically as possible, without taking Restish or other web APIs into account. I would like to be able to set a timeout for a connection executing a query. This is to ensure that lo...
I'm starting some new projects and want to know if pg8000 is considered a good choice for a production project? Obviously Python and PostgreSQL are mature products, but I'm concerned about pg8000 both when it comes to maturity and performance. Will my DB access suffer or will it be acceptable? So, please take some latitude in respondin...
If Newdata is list of x columns, How would get the number unique columns--number of members of first tuple. (Len is not important.) Change the number of "?" to match columns and insert using the statement below. csr = con.cursor() csr.execute('Truncate table test.data') csr.executemany('INSERT INTO test.data VALUES (?,?,?,?)',...