Questions tagged [python-db-api]

Questions about how to use the Python Database API Specification 2.0 -- PEP 249. Do include details about your database library

This API has been defined to encourage similarity between the Python modules that are used to access databases. By doing this, we hope to achieve a consistency leading to more easily understood modules, code that is generally more portable across databases, and a broader reach of database connectivity from Python.

See: https://www.python.org/dev/peps/pep-0249/

134 questions
5
votes
5 answers

Python call sql-server stored procedure with table valued parameter

I have a python script that loads , transform and calculates data. In sql-server there's a stored procedure that requires a table valued parameter, 2 required parameters and 2 optional parameters. In sql server I can call this SP: USE…
5
votes
1 answer

MySQL python DBAPI. How to get a dictionary instead of a tuple?

Python DB API returns a list of tuples when fetchall is invoked on an executed cursor. If you work with the _mysql module, fetch_row has a how parameter that allows to get a dictionary instead of a tuple. How can I achieve the same with the cursor ?
Stefano Borini
  • 138,652
  • 96
  • 297
  • 431
5
votes
1 answer

Catch python DatabaseErrors generically

I have a database schema that might be implemented in a variety of different database engines (let's say an MS Access database that I'll connect to with pyodbc or a SQLite database that I'll connect to via the built-in sqlite3 module as an simple…
Drew Hall
  • 28,429
  • 12
  • 61
  • 81
5
votes
2 answers

Pyscopg DB - Error Adding Persistence to code

I am working on an online project by Udacity. I am using vagrant configured by them, to run the server containing the Database. Unfortunately when I tried to give the code persistence, the server returns an error everytime. I am new to python so…
Abhishek Ghosh
  • 2,593
  • 3
  • 27
  • 60
4
votes
1 answer

Why teardown_appcontext required to close db connection?

According to flask docs, we should close the database connection when app context tears down: def get_db(): """Opens a new database connection if there is none yet for the current application context. """ if not hasattr(g,…
max
  • 49,282
  • 56
  • 208
  • 355
4
votes
1 answer

Python async transactions psycopg2

It is possible to do async i/o with psycopg2 (which can be read here) however I'm not sure how to do async transactions. Consider this sequence of things: Green Thread 1 starts transaction T GT1 issues update GT2 issues one transactional update GT1…
freakish
  • 54,167
  • 9
  • 132
  • 169
4
votes
1 answer

Python/pg8000 WHERE IN statement

What is the correct method to have the tuple (names) be available via %s in the SQL statement? names = ('David', 'Isaac') sql = 'SELECT * from names WHERE name IN %s' cur.execute(sql,(names,)) The response in…
Tsachi
  • 57
  • 6
3
votes
1 answer

Connecting to MySQL using python without installing Mysql

Thanks for reading this. I am working on a project that involves database sync with python. The remote machine is a linux machine with MySQL. The client does not want me to install anything except python and python libraries required for the…
user1077344
3
votes
1 answer

time complexity to get auto incremented row id in mysql

I am a newbie at mysql and databases. I have a simple question. I have created a table that has an integer type id column that is auto incremented. After each insert I get the last row inserted id (in python using cursor.lastrowid, or…
assassin
  • 19,899
  • 10
  • 30
  • 43
3
votes
0 answers

Python's sqlite3 module: get query with escaped parameters

The sqlite3 module allows one to use parameter substitution for queries like so: import sqlite3 con = sqlite3.connect(":memory:") cur = con.cursor() cur.execute("create table lang (name, first_appeared)") cur.execute("insert into lang values (?,…
bbayles
  • 4,389
  • 1
  • 26
  • 34
3
votes
1 answer

PyGreSQL is returning unexpected result for a one column select statement

I have the following PostgreSQL database table: TABLE session_monitor ( id int, customer_name varchar(150) ) When I am running the following code: seq = pg_cur.execute("SELECT id ,customer_name from session_monitor") for id, customer_name…
dani shamir
  • 81
  • 1
  • 9
3
votes
1 answer

Remote Oracle DB connection in python

I want to connect to remote oracle db using python. Tried to using cx_Oracle. Here is my code: import cx_Oracle adr = 'server_addres' uid = 'user_id' pwd = 'pwd' port = 'port' cx_Oracle.connect(uid + "/" + pwd + "@" + adr) After execute, I am…
user6133328
3
votes
1 answer

Is having a global database connection allowed in WSGI applications?

I need to create a simple project in Flask. I don't want to use SQLAlchemy. In the code snippet below, everyone that connects to the server uses the same connection object but for each request, a new cursor object is created. I am asking this…
amone
  • 3,712
  • 10
  • 36
  • 53
3
votes
2 answers

ValueError: operation parameter must be str

I'm trying to update some database fields using a python function to an SQLite DB. I keep getting the following error: ValueError: operation parameter must be str Below is my code. I would love to know how to update multiple columns in an sqlite…
Evans Wanjau
  • 29
  • 1
  • 4
3
votes
2 answers

Using Python quick insert many columns into Sqlite\Mysql

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 =…
Merlin
  • 24,552
  • 41
  • 131
  • 206
1 2
3
8 9