Stack Overflow
Stack Overflow

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
85
votes
3 answers

Python db-api: fetchone vs fetchmany vs fetchall

I just had a discussion today with some coworkers about python's db-api fetchone vs fetchmany vs fetchall. I'm sure the use case for each of these is dependent on the implementation of the db-api that I'm using, but in general what are the use cases…
Alex Q
  • 3,080
  • 2
  • 27
  • 29
79
votes
9 answers

How to check if a result set is empty?

I have a sql statement that returns no hits. For example, 'select * from TAB where 1 = 2'. I want to check how many rows are returned, cursor.execute(query_sql) rs = cursor.fetchall() Here I get already exception: "(0, 'No result set')" How can…
Tao Venzke
  • 893
  • 1
  • 6
  • 6
55
votes
13 answers

Escape SQL "LIKE" value for Postgres with psycopg2

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…
EMP
  • 59,148
  • 53
  • 164
  • 220
52
votes
7 answers

Transactions with Python sqlite3

I'm trying to port some code to Python that uses sqlite databases, and I'm trying to get transactions to work, and I'm getting really confused. I'm really confused by this; I've used sqlite a lot in other languages, because it's great, but I simply…
David Given
  • 13,277
  • 9
  • 76
  • 123
47
votes
3 answers

difference between cursor and connection objects

I am confused about why python needs cursor object. I know jdbc and there the database connection is quite intuitive but in python I am confused with cursor object. Also I am doubtful about what is the difference between cursor.close() and…
Aman Deep Gautam
  • 8,091
  • 21
  • 74
  • 130
42
votes
7 answers

How to get a single result from a SQL query in python?

Is there an elegant way of getting a single result from an SQLite SELECT query when using Python? for example: conn = sqlite3.connect('db_path.db') cursor=conn.cursor() cursor.execute("SELECT MAX(value) FROM table") for row in cursor: for elem…
iliaden
  • 3,791
  • 8
  • 38
  • 50
25
votes
9 answers

Inserting JSON into MySQL using Python

I have a JSON object in Python. I am Using Python DB-API and SimpleJson. I am trying to insert the json into a MySQL table. At moment am getting errors and I believe it is due to the single quotes '' in the JSON Objects. How can I insert my JSON…
Aran
  • 3,298
  • 6
  • 32
  • 33
20
votes
3 answers

how to safely generate a SQL LIKE statement using python db-api

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…
laramichaels
  • 1,515
  • 5
  • 18
  • 30
15
votes
1 answer

What's psycopg2 doing when I iterate a cursor?

I'm trying to understand what this code is doing behind the scenes: import psycopg2 c = psycopg2.connect('db=some_db user=me').cursor() c.execute('select * from some_table') for row in c: pass Per PEP 249 my understanding was that this was…
serverpunk
  • 10,665
  • 15
  • 61
  • 95
15
votes
4 answers

Python DB-API: how to handle different paramstyles?

I'm implementing a Python ontology class that uses a database backend to store and query the ontology. The database schema is fixed (specified in advance), but I don't know what type of database engine is being used. However, I can rely on the fact…
Tamás
  • 47,239
  • 12
  • 105
  • 124
13
votes
5 answers

Set database connection timeout in Python

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…
oneself
  • 38,641
  • 34
  • 96
  • 120
11
votes
2 answers

How do you change the SQL isolation level from Python using MySQLdb?

The documentation I've run across researching this indicates that the way to do it for other databases is to use multiple statements in your query, a la: >>> cursor = connection.cursor() >>> cursor.execute("set session transaction isolation level…
jodonnell
  • 49,859
  • 10
  • 62
  • 67
11
votes
3 answers

inserting numpy integer types into sqlite with python3

What is the correct way to insert the values of numpy integer objects into databases in python 3? In python 2.7 numpy numeric datatypes insert cleanly into sqlite, but they don't in python 3 import numpy as np import sqlite3 conn =…
David Nehme
  • 21,379
  • 8
  • 78
  • 117
11
votes
3 answers

Follow up: Execute .sql files from python

Over a year ago someone asked this question: Execute .sql files that are used to run in SQL Management Studio in python. I am writing a script in python that connects to a SQL server and creates and populates a database based on SQL commands in a…
Neal Kruis
  • 2,055
  • 3
  • 26
  • 49
10
votes
2 answers

Why close a cursor for Sqlite3 in Python

Is there any benefit to closing a cursor when using Python's sqlite3 module? Or is it just an artifact of the DB API v2.0 that might only do something useful for other databases? It makes sense that connection.close() releases resources; however,…
Raymond Hettinger
  • 216,523
  • 63
  • 388
  • 485
1
2 3
…
8 9