Questions tagged [python]

Python is a powerful and ubiquitous language. When using a Python variant or library (e.g. Pandas, SQLAlchemy, pg8000, psycopg2), please include it in the tags. Questions using this tag should restrict themselves to interactions directly with the database tier. General python scripting questions should be directed to [StackOverflow](https://stackoverflow.com/questions/tagged/python)

Filter by
Sorted by
Tagged with
18
votes
2answers
15k views

How to best use connection pooling in SQLAlchemy for PgBouncer transaction-level pooling?

Using SQLAlchemy to query a PostgreSQL database behind PgBouncer, using transaction-level pooling. What is the best pattern to use for this kind of set up? Should I have one-engine-per-process, ...
12
votes
1answer
11k views

Encoding issue with SQL Server VARCHAR column retrieved in Python

We recently had an issue with encoding related to a field that's being stored as a varchar(120) in SQL Server. In SSMS, the varchar appears as: "Who Killed JonBen‚t?" However, when it's brought ...
12
votes
1answer
2k views

Why is PL/Python untrusted?

According to the docs: PL/Python is only available as an "untrusted" language, meaning it does not offer any way of restricting what users can do in it and is therefore named plpythonu. A trusted ...
11
votes
1answer
8k views

pgBouncer works great but occasionally becomes unavailable

I'm running pgBouncer in front of a busy postgres 9 database. For most of the time it works fine. But every few hours I'll get an error email from my application with an exception from psycopg2: ...
10
votes
3answers
15k views

Launching a python script from an insert trigger

We have a nice piece of python that sends some emails and interacts with a cloud system. Works fine. But we have to fire it every few minutes to poll the db. We really need, for business purposes, to ...
9
votes
1answer
4k views

What exactly is Psycopg2?

This question is for a report I'm writing for school. Technically speaking, what is Psycopg2? In this tutorial they refer to it both as an "adapter" and "driver" Psycopg2 is a DB API 2.0 compliant ...
8
votes
3answers
42k views

How to work with multiple Postgres databases in psycopg2

I have two different Postgres databases on two different servers (one is actually local). Similar to this question, I would like to work with both databases at the same time. However, I can't figure ...
8
votes
1answer
26k views

Looking for simple contains method when searching text in postgresql

Im new to the PostgreSQL world so I do apologize in advance for not knowing if there is an obvious answer to these questions. Basically I'm looking for two things. First a simple "contains" based ...
8
votes
1answer
8k views

How to get minimum value in pyMongo

I have a collection with several fields, and I'd like to get the minimum value from one. I know a command for getting the max value: collection.find_one(sort=[("myfield", -1)])["myfield"] but not ...
6
votes
1answer
12k views

PostgreSQL/Psycopg2 upsert syntax to update columns

I want to have Psycopg2 update col1, col2 and col3 when there is a conflict of id. In my Python code I have the insert SQL currently as: insert_sql = '''INSERT INTO {t} (id,col1,col2,col3) ...
6
votes
1answer
715 views

SQL Server: How to get the responsiveness of a trigger, without blocking the transaction that fires the trigger?

On a thread today I posed a desire to get code to execute immediately when a record is inserted rather than resorting to polling. Triggers are powerful tools, yet dangerous places to put real code, ...
5
votes
4answers
6k views

What is the best way to store X509 certificate in PostgreSQL database?

I'm working on web authenticate system where users will digitally sign random tokens and this will be checked with X509 certificates stored on the server. Therefore I have to store several X509 ...
5
votes
1answer
4k views

Couldn't open extension control file plpython3u.control : No such file or directory

I want to have python3 in my postgresql database for writing stored procedures. Being in the psql client, when I enter the command create extension plpython3u I get the error: couldn't open ...
5
votes
1answer
35 views

Lifespan of Launchpad-Launched Exterrnal Runtime Processes

When SQL Server uses SQL Launchpad to launch an external runtime (e.g. R or Python) when sp_execute_external_script is used, what's the lifespan of the external runtime process? Does each ...
4
votes
2answers
93 views

SQL Server hashbytes seed

I'm trying to compare hashes generated by SQL Server HASHBYTES() with hashes generated with python's hash() (or hashlib) function. For both functions, the same algorithm is used. The problem is I don'...
4
votes
2answers
4k views

AttributeError: 'NoneType' object has no attribute 'value' - pgAdmin4

I Ctrl+C'd out of the initial pgAdmin4 setup aka. python pgAdmin4.py and now if I try to rerun the install, I get a mountain of errors. I'm using pgAdmin3 for now but I would like to know how to fix ...
4
votes
3answers
35k views

How to uninstall pgAdmin4 on Ubuntu 16.04?

I successfully installed pgAdmin4 with pip install pgadmin4-2.0-py2.py3-none-any.whl But afterward, pip list doesn't show anything related to pgAdmin, so I don't know what argument to pass to pip ...
4
votes
2answers
3k views

How do long string field names affect MongoDB database size?

I am using MongoDB and the names of my fields are strings with 10-20 characters. A typical document consists of 30.000 columns filled mostly with floats, like 1.2, 10.5, 2.55. It's size is 1MB. Do ...
4
votes
1answer
122 views

Does SSMS v17 allow user input in Python scripts?

With the introduction of Python feature shipped with SQL Server 2017, I would like to know if anyone has been able to use SSMS v17.x and run a python script that requires a user input. Here is a ...
4
votes
2answers
135 views

Python script leaves the database in restoring mode

I am attempting to use python to automate some of our database restores. When I run the SQL query the database restores and comes back up like normal, but when I run the python script that uses the ...
4
votes
1answer
1k views

SQL Server 2017 & External Python Modules

I'm doing some PoC work with Python integration in SQL Server 2017. I have got the basic steps working, & have completed this example successfully: https://docs.microsoft.com/en-us/sql/advanced-...
4
votes
1answer
788 views

Flask mongodb assertion 13 not authorized for query on flask_session.sessions

Need help resetting mongodb back to its original state. We have a flask app running with mongodb with authentication turned off. Running on Ubuntu with std Flask. We tried ALL sorts of combinations ...
3
votes
1answer
3k views

PgBouncer Uncertainties

I'm trying PgBouncer for the first time with a simple Python web application (Flask + psycopg2), and I have a hard time interpreting the meaning of its log messages by simple googling. I'm basically ...
3
votes
2answers
70 views

Is using CLR for regular expressions safer than using external scripts?

Problem The main problem we need to use regular expression on MS SQL Server 2019, with the capability of at least the level on the POSIX Regular expression. Possible solutions This Q/A from ...
3
votes
2answers
3k views

Using AWS DynamoDB Geospatial index with Python

Amazon's AWS DynamoDB features Geospatial Indexing, which facilitates geo queries: Query Support: Box queries return items that fall within a pair of geo points that define a rectangle as ...
3
votes
1answer
735 views

When I run mssql-cli, I get “No module named mssqlcli”

I just installed mssql-cli with pip, pip3 install mssql-cli Then I try to run mssql-cli and I get $ mssql-cli /usr/bin/python: No module named mssqlcli
3
votes
1answer
108 views

Should I do the maths within PostgreSQL for ACIDity?

It is my first question on Database Administration Stack Exchange. Please be kind if something is not done the nice way.. I would like to be advised concerning the way I'm working on my database, in ...
3
votes
1answer
393 views

Have several 128 elements lists, need to obtain distance between them

Is storing them in a CUBE type field and then getting euclidean distance, (using <->) the only way? I can not save them as a list of floats, since i get: "can't adapt type numpyndarray" Can ...
3
votes
2answers
191 views

Python MySQL prepared statements slower than regular queries

I'm trying to use prepared statements with python to improve query performance, but my prepared statements are slower (by a factor of ~7.5 no matter how many iterations I set in my benchmark script). ...
3
votes
1answer
157 views

Removing sp_execute_external_script Start-Up Lag

On a freshly-restarted instance of SQL Server, the first time Python is executed using sp_execute_external_script, there's a lag before results are returned. After the initial Python script execution, ...
3
votes
0answers
131 views

TCP Resets when using psycopg2

I'm trying to do a simple health check for keepalived and other services via a python script and psycopg2 library. All seems to be alright, until I close the connection, at which point a packet with ...
3
votes
1answer
953 views

select two tables and print the columns side by side with matching key in hive

I have the below tables: table_1 name_1 id_1 value_1 avi 1 21 adi 2 X table_2 name_2 id_2 value_2 avi 1 21 adi 2 22 metadata_info: can be read from ...
3
votes
1answer
4k views

DB2 Query Error SQL0204N Even With The Schema Defined [closed]

I'm using Python to access tables in DB2 10.1.0 I have a login account named foobar and a schema with the same name. I have a table named users under the schema. When I'm logged in as foobar, I can ...
2
votes
3answers
2k views

SQL Server backup. PowerShell vs Python [closed]

I'm about to refactor inherited scripts deployed on multiple servers that backup many other servers. They are a mixture of cmd, vbs and sql. I'm pondering what benefits would bring doing it with ...
2
votes
3answers
5k views

Connection pooling with python mysql-connector

I would like to use connection pooling with mysql-connector 2.1.5 On Ubuntu everything works fine, but I would like to switch to Alpine and have used this file to install the connector (as described ...
2
votes
2answers
1k views

Idle connection plus schema-modifying query causing locked database

As part of our automated deployment process for a web app running on a LAMP stack, we drop all our triggers and stored procedures and recreate them from source control. It turns out there was a hidden ...
2
votes
2answers
84 views

Allow only one reader to a table

I have a simple Postgres table which port numbers to listen on. If one port is used it can't be used again. However due to multiple worker/threads they many times read simultaneously. I want that ...
2
votes
1answer
7k views

psycopg2 connection already closed

In one of our environments we execute quite heavy query from python script using psycopg2. It takes more then 10 min to execute but we get a result and can consume received data. When we move the ...
2
votes
1answer
693 views

Fetch Only new data from auto-updating online sql database

I have an online MySQL (presto) database which keeps on auto-updating (i.e., new rows keep getting added to the tables(views, to be exact) every day). I want to fetch the data from it every day but ...
2
votes
1answer
12k views

How to set a auto-increment value in SQLAlchemy?

This is my code : class csvimportt(Base): __tablename__ = 'csvimportt' #id = Column(INTEGER, primary_key=True, autoincrement=True) aid = Column(INTEGER(unsigned=True, zerofill=...
2
votes
1answer
2k views

Unexpected behavior of foreign keys

I want to use Sqlite3 with Python and created queries as follows: import sqlite3 from sqlite3 import Error db = sqlite3.connect("temp.sqlite") cursor = db.cursor() cursor.execute("...
2
votes
1answer
4k views

Query against JSON array in PostgreSQL

The data I'm working with is in a column ('transaction') of JSONB type, containing documents similar to this {"transactions":[{"id":"ABC123", ...}, {"id":"DEF456", ...}, {"id": "GHI789", ...}]} I'm ...
2
votes
1answer
1k views

presentation of non-ascii characters (ñ)

I'm facing the following problem: I have a MySQL database which contains non-ascii characters in some records. Characters like "ñ", for example. What kind of setup do I need to perform at the ...
2
votes
1answer
855 views

for Insert multiple rows in one single insert MySQL

I have done bulk-insertion in MySQL using multiple row insertions method by using python script. My testing csv file consists of 100,000 lines only (real one consists of millions lines). so I perform ...
2
votes
0answers
621 views

Redis along with Mysql

I'm currently working on a mobile app alike Google Maps or Wave, but also including parking. The application its running with MySQL primarily. With at this stage most of our data is loaded by python ...
2
votes
0answers
3k views

psycopg2 - drawbacks of reusing cursors?

I am new to Python and the psycopg2 module. context Working on a script that select data from MySQL, perform some operations (conversion of data-types and other "transformations"), and finally ...
2
votes
1answer
5k views

SQL Server: how to troubleshoot login timeout expired error [closed]

I have a production SQL Server 2012 (SP1) running on Windows Server 2008 R2 Enterprise edition. A lot of time while connecting to this server from a remote host I to face connection timed out error. ...
2
votes
0answers
734 views

MongoDB communication issues on Azure

I have a Python uWSGI app running behind Nginx using pymongo to communicate with a 4 machine replica set running Mongodb 2.6.3. The replica set is configured using domain names for each machine mdb-0[...
2
votes
2answers
1k views

Query to Access a specfic data in the row using python

Am using wordpress and phpmyadmin for handling my database Below is the code I use to view entries on my form using python. In the form I ask users to check/uncheck specific questions. It works as ...
1
vote
2answers
2k views

What is better? Delete and insert or update? [closed]

I have a python script in which I have to update a column in 67000 rows. The value of the desired column can be unique for each row. I found that inserting 67000 rows takes about 5 seconds but ...

1
2 3 4 5