136 questions
1
vote
0
answers
417
views
How to use the IN operator in sqlite3 with parameters?
Python 3.5.2, stdlib sqlite3.
I'm trying to issue a SQL query with a dynamic criterion in an IN operator in the WHERE clause:
bad code (doesn't work)
def top_item_counts_in_installations(cursor, ...
- 96.6k
0
votes
0
answers
478
views
Error when connecting to sql using Python
I am using Python library pymssql to connect to a database. I have never used it before so the error might look trivial.
from os import getenv
import pymssql
server = getenv("192.xxx.xxx.xx")
user ...
- 1,092
7
votes
2
answers
517
views
Why is Twisted's adbapi failing to recover data from within unittests?
Overview
Context
I am writing unit tests for some higher-order logic that depends on writing to an SQLite3 database. For this I am using twisted.trial.unittest and twisted.enterprise.adbapi....
- 21.6k
2
votes
2
answers
508
views
Is it safe to use locals() as a means of passing parameters to a SQL query?
I have code similar to the following:
var1 = 1
var2 = 2
cursor.execute("INSERT INTO mytable VALUES (:var1, :var2)", {'var1': var1, 'var2': var2})
Is there any reason I should use the dictionary ...
- 6,532
10
votes
2
answers
4k
views
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 ...
- 229k
0
votes
1
answer
396
views
Database API specification for Java
Is there a Database API specification for Java that is similar to what exists for Python (PEP249).
I'd like to make an application that can be used to connect to databases, universally, without ...
- 144
0
votes
1
answer
70
views
SQLite DB-API syntax issues Python 3.5
I have been searching through Stack Overflow as well as some other online research looking for the correct syntax to insert variables into my SQLite query. However, none of the 3 supported syntaxes ...
user5735326
1
vote
2
answers
2k
views
how to update multiple rows in ms access from pandas dataframe
I'm trying to execute this update query from columns in a pandas dataframe:
sql = "UPDATE tblhis_ventas SET portabilidad = '%s' WHERE (contrato = '%s' and estado = '%s') " % (
df['portabilidad'], ...
- 13
3
votes
2
answers
1k
views
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 ...
- 27
18
votes
1
answer
13k
views
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
...
- 11k
2
votes
1
answer
1k
views
Create Sqlite database patches while updating
Context:
A python 3.6 script is updating a Sqlite database several times a day using sqlite3 module.
The database is ~500Mo, each update adds up ~250Ko.
Issue:
I deliver every updated ...
- 619
0
votes
1
answer
155
views
Python DB-Api when to commit on multiple inserts/updates
I have a function which updates a single row in db.
def update_one_row(conn, condition, value):
with conn.cursor() as curr:
curr.execute("""UPDATE persons p
SET ...
- 4,009
4
votes
1
answer
3k
views
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 ...
- 52.8k
5
votes
1
answer
20k
views
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 ...
- 29.2k
12
votes
3
answers
4k
views
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 ...
- 21.7k
4
votes
1
answer
6k
views
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 ...
- 57k
2
votes
1
answer
361
views
Python Database API v2 > Iteration over result set stops if a query executed during the iteration returns no results
I'm using the Snowflake Connector for Python (which implements support for the Python Database API v2 specification) in a script which pulls a number of records from one table, iterates over the ...
- 1,869
0
votes
2
answers
4k
views
How do I specify Transaction Isolation Level for MS SQL backend in Sql Alchemy Python
How do I set transaction level READ UNCOMMITED for all queries done through a SQL Alchemy engine object?
I set the isolation_level argument as notated here: http://docs.sqlalchemy.org/en/latest/core/...
- 103
4
votes
1
answer
2k
views
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 ...
- 57
5
votes
2
answers
207
views
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 ...
- 2,724
0
votes
0
answers
129
views
Instantiating SQLAlchemy engine from DBAPI connection object [duplicate]
I'm using SQLAlchemy from within Django, and I can access the thread-local database connection object in Django:
from django.db import connection
Given that connection object, can I use it to ...
- 5,431
3
votes
1
answer
770
views
Difference between ODBC and Python DB-API?
Well, I tried to understand Open Database Connectivity and Python DB-API, but I can't.
ODBC is some kind of standard and Python DB-API is another standard, but why not use just one standard? Or maybe ...
user5347440
2
votes
2
answers
4k
views
How to get column data types from pymssql?
According to the Python DB API, one of the fields in cursor.description returns a type_code; however these are always numbers. How do I convert from type_code (eg. 1) to a data type (eg. TEXT)?
- 888
6
votes
1
answer
5k
views
How do I check for open transactions on a psycopg2 connection?
How can I check for open transactions on a psycopg2 connection? I intend to add it to my unit/functional tests since Python's DB API uses implicit transactions.
- 152k
1
vote
1
answer
344
views
parameterized query Postgres for sql injection mitigation
stmt = "SELECT filecontent FROM filecontent WHERE filecontent_id = %d AND filecontent LIKE '%% %s %%'"%(int(result_file_pri[0]),str(myjson['recordType']))
curs.execute(stmt)
Trying to conevert above ...
- 707
1
vote
2
answers
497
views
What's the preferred way to handle transactions in pyscopg2?
According to psycopg2 documentation we should set autocommit to get the default PostgreSQL behaviour. This even seems to be the preferred approach according to some people. My question is, if this is ...
- 693
2
votes
1
answer
562
views
Can one disable conversion to native types when using psycopg2?
Query results from some Postgres data types are converted to native types by psycopg2. Neither pgdb (PostgreSQL) and cx_Oracle seem to do this.
…so my attempt to switch pgdb out for psycopg2cffi is ...
0
votes
1
answer
172
views
Python PYDBLITE - error adding records to pydblite
I have a Python script that uses the pydblite database library. when I run the code once the database is created and records added. if I run the code again an error is generated..Help
Code
db = ...
- 77
2
votes
2
answers
1k
views
Parameterize a quoted string in Python's SQL DBI
I am using pg8000 to connect to a PostgreSQL database via Python. I would like to be able to send in dates as parameters via the cursor.execute method:
def info_by_month(cursor, year, month):
...
- 18.5k
3
votes
1
answer
1k
views
How to prevent PyMySQL from escaping identifier names?
I am utilizing PyMySQL with Python 2.7 and try to execute the following statement:
'INSERT INTO %s (%s, %s) VALUES (%s, %s) ON DUPLICATE KEY UPDATE %s = %s'
With the following parameters:
('artikel',...
- 1,004
0
votes
1
answer
376
views
WSGI how to print raw MYSQL output on the browser
The mysql output:
b.query("select * from b where a='" + c + "' limit 1")
result = b.store_result()
d = result.fetch_row(0)
the bottom of WSGI script:
start_response('200 OK', [('content-...
- 63
0
votes
0
answers
127
views
error: list index out of range. Python variable obtained using cur.fetchall() from an SQL query is not being read into another SQL query
there is a value I get from an SQL query which is stored with the name max_dateCS. This variable needs to be input into another SQL query. I am facing a problem doing that as it gives an error ' list ...
- 61
0
votes
1
answer
240
views
How could one use Python's DBAPI to portably validate a database schema?
I'm writing a utility which I intend to be useable with at least three different database server backends (SQLite3, PostgreSQL, and MySQL). I'm not using an ORM (though I try a fork using SQLAlchemy ...
- 17.7k
8
votes
1
answer
8k
views
Python MySQL Connector database query with %s fails [duplicate]
I have a basic program that is supposed to query a database that contains user information. I am trying to select the information for a specific user and print it out to the console.
Here is my ...
0
votes
1
answer
14k
views
Inserting multiple rows using psycopg2
According to psycopg2: insert multiple rows with one query, it is much more efficient to use psycopg2's execute instead of executemany . Can others confirm?
The above StackOverflow question suggests ...
- 2,819
1
vote
1
answer
243
views
Using parameters in a CREATE TABLE ... (column DEFAULT ?) clause, in Python sqlite3 db-api
I want to use python to execute a CREATE STATEMENT clause, specifying default values for certain columns using parameter substitution with ? (so that I can safely specify defaults from python types).
...
- 273
3
votes
1
answer
1k
views
Rationale for DB API 2.0 auto-commit off by default?
PEP 249 -- Python Database API Specification v2.0 in the description of .commit() states:
Note that if the database supports an auto-commit feature, this must
be initially off. An interface method ...
- 135k
0
votes
1
answer
259
views
Why does Python's Sqlite3 module correctly parse the first instance of this parameter substitution and afterwards not? Caching?
I have a Sqlite3 table that has a LastUpdated column containing UTC datetimes formatted as "2013-12-24 07:11:21", and all the rows int that table were updated 2 days ago.
I want to write a SELECT ...
- 23.8k
1
vote
1
answer
7k
views
Postgresql: How to delete rows from table constrained by date?
I am using psycopg2 and how can I delete rows that are older than a certain date? For example:
cursor.execute('DELETE FROM datatable WHERE date < %s', datetime.date(2012, 1, 1))
If I write like ...
- 2,259
1
vote
1
answer
2k
views
Psycopg2 production appropriate mogrify?
I want to do exactly what cursor.mogrify does, but in a production appropriate way.
I'm updating some legacy Python code that builds queries by concatenating strings. I need to change this to escape ...
- 1,647
0
votes
1
answer
157
views
Choosing python client lib for Cassandra
I have worked with Pycassa before and wrote a wrapper to use batch mutation & connection pooling etc. But http://wiki.apache.org/cassandra/ClientOptions recommends now to use CQL 3 based api ...
- 4,540
1
vote
0
answers
380
views
How do I lazily pass csv rows to executemany()?
I'm using MySQL Connector/Python 1.0.11 with Python 3.3 and MySQL 5.6.12 to load a csv file into a table via cursor.executemany('INSERT INTO ... VALUES'). With sqlite3 or psycopg2 I can pass a _csv....
- 4,234
6
votes
1
answer
4k
views
How to get prepared query which is sent to db
When using database libraries like pyodbc that implement the Python Database API Specification how can you get the fully prepared query after parameter substitution has been applied. I'm making a call ...
- 27.1k
83
votes
9
answers
228k
views
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 ...
- 933
3
votes
0
answers
2k
views
checking Python DB cursor column types in a database-independent way
I have to dump some data from various databases (Oracle and Postgre, at the moment) into CSV files, while preserving the column type information so the data can be correctly loaded into another ...
- 80.5k
55
votes
7
answers
77k
views
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 ...
- 13.8k
0
votes
1
answer
507
views
how will Python DB-API read json format data into an existing database?
If we have a json format data file which stores all of our database data content, such as table name, row, and column, etc content, how can we use DB-API object to insert/update/delete data from json ...
- 977
0
votes
2
answers
1k
views
Update multiple rows with unique key
I want to update multiple rows identified by unique key without inserting new rows.
Below is my table:
CREATE TABLE `insert_update_ignore` (
`obj_id` int(11) NOT NULL,
`obj_type` tinyint(4) ...
- 2,971
7
votes
2
answers
5k
views
sql print statements from pyodbc
How do I get the output from the sql_query?
import pyodbc
sql_query = "print 'Hello World'"
conn = pyodbc.connect("DRIVER={SQL Server};
SERVER=myserver;
DATABASE=mydatabase;
UID=myusername;
PWD=...
- 175
1
vote
1
answer
76
views
Django/db-api broken for create database?
Was hoping to use Django / db-api's built in string excaping, but it looks like it doesn't work for create database commands?
from django.db import connections
cursor = connections['dbadmin'].cursor()...
- 16.2k