136 questions
0
votes
0
answers
242
views
Use Jinja template for table name inside SQLExecuteQueryOperator params or parameter
I am using SQLExecuteQuery Operator to query a table, which name is dynamically generated (e.g. staging_{{ts_nodash}}).
I know I can, but I don't want to use the jinja template inside sql (example ...
- 73
2
votes
2
answers
1k
views
Efficient SQL query with pandas using databricks-sql-python
Databricks allows to make SQL queries via an API using the databricks-sql-python package.
There are then two ways of creating a connection object that can be put into a pd.read_sql_query(sql, con=...
- 11.3k
2
votes
1
answer
71
views
Connection and cursor still usable outside with block
My system:
Windows 10 x64
Python version 3.9.4
SQLite3 module integrated in the standard library
A DB connection and a cursor are both considered resources, so that I can use a with clause.
I know ...
1
vote
1
answer
433
views
Why do I get unexpected type when using None for Google BigQuery DB-API?
I'm trying to migrate data from a MySQL table to a BigQuery table using Python, but it gives me the following error when I try to insert a NULL value:
google.cloud.bigquery.dbapi.exceptions....
- 11
-1
votes
1
answer
1k
views
Can't catch connection error clickhouse-driver db api
I'm trying to catch a connection error when connecting to kx using the clickhouse-driver db api in python. But for some reason, the try: block passes without errors, and I don't get exception
def ...
- 153
1
vote
0
answers
253
views
Reveal the meaning of DBAPI error message dictionary keys
For a few months Ive been working with pg8000 and have noticed when it throws an error like dbapi.ProgrammingError, the message is followed by a dictionary with some keys and values. Where can I find ...
- 553
0
votes
1
answer
1k
views
How to catch exception clickhouse_driver dbapi?
I want to catch exception while executing scipts/connecting to base using clickhouse_driver-drive dbapi.
Can I catch errors codes and errors message like
errorcodes.lookup(e.pgcode)
and
e.diag....
- 153
0
votes
1
answer
2k
views
Pandas dataframe to SQL table using presto-python-client syntax error: mismatched input ';'
I am connecting to a presto db, trying to write a dataframe into a sql table. I can "CREATE TABLE" but df.to_sql throws a syntax error:
PrestoUserError: PrestoUserError(type=USER_ERROR, name=...
- 408
3
votes
0
answers
51
views
Python's sqlite3 module: get query with escaped parameters [duplicate]
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,...
- 4,557
5
votes
1
answer
4k
views
How to fetch data from Clickhouse in dicitionary/name-tuple using clickhouse-driver (python)?
When we fetch data using the DB API 2.0 cur.execute("select * from db.table") we get a cursor which seems like a generator object of list of tuples.
Whereas in pymongo, when we fetch we get ...
0
votes
1
answer
533
views
How to preform insert into BigQuery table of list of values using dbapi
I have a question regarding performing insert into a table in BigQuery using the DBAPI .
The simple insert of 1 value I was able to preform , my question is how to preform insert of list of values.
I ...
- 626
0
votes
1
answer
671
views
OpenTelemetry Python Instrumentation dbapi is not capturing the odbc database commands
I am trying to capture the database commands using Instrumentation DBapi, but i don't see anything to be reported to Jaeger. I am accessing database using pyodbc and pandas and i see while debugging ...
- 41
0
votes
1
answer
1k
views
Why do sqlite3 DB_API qmark and named style not work in "select where" queries?
Assuming I have a database with table users with a row:
ID = 0, name = 'myName'
I can get the ID value (assuming I'm connected) by using either:
cursor.execute("""SELECT ID FROM users ...
- 371
0
votes
1
answer
959
views
How can I use elasticsearch-dbapi with Open Distro for ElasticSearch, ignoring SSL certificates?
How can I use elasticsearch-dbapi with Open Distro for Elasticsearch, ignoring ssl certificates?
I am using Open Distro for Elasticsearch 1.13.2, Python 3.7, elasticsearch client 7.12.1, sqlalchemy: 1....
- 36
1
vote
4
answers
8k
views
IBM Db2 : An unexpected token "as" was found following "SELECT test_score " (SQL0104N)
I am now having trouble with IBM Db2 using queries. I have a code below:
test_score_distribution = %sql SELECT test_score as "Test Score", count(*) as "Frequency" from ...
-1
votes
1
answer
2k
views
Microsoft Access database engine cannot find the input table or query
I'm trying to establish connection with Microsoft Office and to run a query but it is showing error
Here is my code:
import pyodbc
conn = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *....
- 1
0
votes
1
answer
541
views
doubts about DB-API e SQLAlchemy
link da questão
Briefly, I want to know what this "DB-API" mechanism is.
Are there multiple DB-APIs (there are more than 1 DB-API)?
Is it just a 'rules' document?
have a source code?
What ...
user14522494
1
vote
1
answer
165
views
DB-API x database module
What is a DB-API (Python) and what is a database module and how do they interact ?
I have a TCC project and I need to understand the difference and the purpose of these terms. I thought DB-API was, ...
user14522494
2
votes
0
answers
310
views
What is actual SQL produced by cursor.execute() through python dbapi 2.0?
I'll admit it. I've been doing this for literally decades:
sql = 'select * from whatever where key = "%s"' % ('thing')
cursor.execute(sql)
One big reason is that
print(sql)
tells me ...
- 8,677
0
votes
1
answer
808
views
Python stops working when try to connect to SAP HANA
I'm trying to connect with SAP Hana using python on Anaconda.
The SAP team has already given me the data to connect, but every time I try to run the code it simply loads for two seconds and then shows ...
1
vote
1
answer
2k
views
Is there a way to select schema in python hdbcli dbapi?
According to documentation, parameters needed to connect to HANA database are host, port, user and password.
from hdbcli import dbapi
conn = dbapi.connect(
address="<hostname>",
...
1
vote
1
answer
682
views
How to determine if Python DB-API connection object is of a certain DBMS (e.g., PostgreSQL, MySQL)
Let the driver be a DB-API driver (e.g., psycopg2, pymysql) and you can get the connection object via connnection = driver.connect(...). How do I check what kind of DBMS the connection object is ...
- 2,348
3
votes
1
answer
81
views
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 ,...
- 81
0
votes
1
answer
311
views
Floating point exception when using SQL when run but not when debugged
As part of my Python program, I have created a method which runs sql queries on a Db2 server. Here it is:
def run_query(c, query, return_results=False):
stmt = db.exec_immediate(c, query)
if ...
- 153
0
votes
1
answer
262
views
Why does this rollback function not work as expected
I am going to execute 2 sql queries and I want to put them in a transaction, if any query failed then call rollback(). The code is shown as following and 2 queries are
str_trunction: truncate the ...
- 815
1
vote
1
answer
753
views
Implementing Python DB-API
I'm trying to implement the python DB-API for a small "database" that we built internally. This database does not expose an ODBC interface (or JDBC for that matter). My goal is to create a sqlalchemy ...
- 2,011
1
vote
1
answer
2k
views
snowflake python connector - Time to make database connection
Python code is taking around 2-3 secs to make the snowflake database connection. Is it expected behaviour ? OR are there any parameters which will speed up connection time.
Here is the sample code:
...
- 2,328
6
votes
0
answers
4k
views
Difference between JDBC driver vs Python Adapters for Amazon Redshift
To connect Amazon Redshift, I used the python psycopg2 module to inject the dataset on Amazon Redshift and it is working fine. Just to mention that I'm using the Redshift's endpoint URL to connect via ...
- 38.8k
0
votes
0
answers
403
views
share db connection pool across python processes
I have build an application (python3.6) which keeps checking a directory for incoming files and when there are files it spawns some python processes in order to work on those files.This work involves ...
- 337
0
votes
0
answers
662
views
DBAPI Error; SSL SYSCALL error: EOF detected when running docker image
I am trying to deploy a python app that filters my database on Redshift then inserts the aggregated rows into a new table. The insert works fine from the python script but when running the docker ...
- 1
1
vote
1
answer
2k
views
Eager Inner Join in SQLAlchemy
I must be a moron, because I cannot figure out how to ask SQLAlchemy to perform a simple, non-lazy inner join. In other words, return all the results in a single query.
The raw SQL query I would like ...
- 3,425
1
vote
1
answer
4k
views
How can I run multiple SQL statements in a query in python?
I'm implementing migrations for my program, and I need some way to run multiple statements in a query.
I have a dict of migrations:
MIGRATIONS = {
"test": ("-- apply", "-- revert", "does ...
- 2,064
2
votes
2
answers
8k
views
SAP HANA Python Connection HDBCLI
After successfully installing the HDBCLI driver and connecting SAP HANA with Python, I created the below code to test the connection, however, I got an error:
Error: (-10719, "Connect failed (invalid ...
- 41
0
votes
2
answers
4k
views
How to use pyhive sqlalchemy to connect to Databricks clusters?
I'm using following code to connect to Databricks clusters. But got thrift.transport.TTransport.TTransportException: TSocket read 0 bytes
. I think there is some error of the my_url in create_engine.
...
- 828
10
votes
2
answers
7k
views
Identifying sqlalchemy.exc.OperationalError
I'm trying to catch mysql/sqlalchemy OperationalErrors and replace handle access denied (1045) differently from connection refused (2003)
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError)...
- 827
-5
votes
1
answer
1k
views
Table input parameters and scalar output parameters
I have the following table and the following stored procedure (simplified to the bare basics needed to demonstrate the problem):
CREATE TABLE T(C INT);
CREATE PROCEDURE PROC(IN T TABLE(C INT), OUT X ...
- 286k
0
votes
1
answer
1k
views
syntax error when using substring in a join expression with PostgreSQL
I'm working with postgresql using the Python DB API.
The substring expression I'm using works fine when on its own, but when I put it in the context of a join command I get a syntax error, and I can'...
- 531
0
votes
1
answer
622
views
Does Ruby or Rails have an equivalent of pandas.read_sql?
So in Python, I am used to using something like
pd.read_sql(sql_query, connection_object)
in order to grab data from a remote database. But when forming a similar connection object in Ruby:
require '...
- 2,957
-2
votes
2
answers
3k
views
Python SQLite query returns None
The following SQLite query returns 3:
SELECT MAX(depth) FROM mpitree WHERE child = 2
But this code evaluates to None:
def ancestors_depth(self):
self.cursor.execute("SELECT MAX(depth) FROM ...
- 37
6
votes
5
answers
13k
views
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 ...
- 101
0
votes
0
answers
267
views
Python DBAPI cursor fetchmany in a generator is closed too early
import dbapi2
conn = dbapi2.connect("db", autocommit=True)
def fetch_generator():
cursor = conn.cursor()
for res in cursor.execute('select * from table'):
yield res
def generator_1():...
- 2,180
2
votes
1
answer
554
views
Is Python DB-API 2.0 for relational database only?
Is Python DB-API 2.0 for relational database only, or also for some nonrelational databases?
https://wiki.python.org/moin/DatabaseInterfaces seems to imply no.
https://wiki.python.org/moin/...
- 101k
3
votes
1
answer
5k
views
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(...
user6133328
2
votes
1
answer
1k
views
Python Psycopg executescript method
the Python db-api implementation for sqlite has a convinient method executescript() to execute multi-statement SQL scripts.
It is very useful for instance for db creation.
See sqlite driver ...
- 1,932
3
votes
1
answer
1k
views
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 ...
- 3,892
0
votes
0
answers
2k
views
how to pass a parameter to a sql script from python
I am trying to pass the parameter to the SQL script from python.
below is the script I am trying; please let me know what I am doing wrong here...
#!./bin/python
_author_='Mohammed'
import boto3
...
- 59
0
votes
1
answer
842
views
INSERT INTO using Postgres on PYTHON DB-API
I'm trying to learn to add backend to a simple web app using postgreSQL and Python DB-API.
When running the app, why do I get an error if the function get_posts() in forumdb. python uses c. execute ("...
- 63
0
votes
0
answers
148
views
connection and cursor in the same class in a DBAPI 2.0 implementation
I have a basic Python interface to a database, where what would be the "connection" class does the querying and retrieveing as well.
I was wondering whether it would make sense to write a dummy ...
- 2,948
0
votes
1
answer
74
views
Correct use of keys when joining tables in SQL
Currently learning data science with SQLite and Pandas. Working on a political contributions dataset. I'm wondering what is the purpose of WHERE contributors.candidate_id = candidates.id in the code ...
- 438
0
votes
1
answer
243
views
Is there a lightweight Windows SDK core?
I need to compile the C++ source code of a version of Python DB API for IBM DB2:
I am new to this and according to the readme, I need to first download
Visual C++ 2003 (isnt it a too old version!?),...
- 11