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
1
vote
2answers
3k views

Psycopg2 - PGBouncer - Postgresql > Server does not support SSL but SSL was required

I've setup my Django application to use SSL while connecting to the Postgresql database via pgbouncer. I'm using Psycopg2 library. I've done this before successfully, so I just did the same steps ...
1
vote
1answer
75 views

Are lots of columns or joins better in Django

I am writing a simple Django app that stores and displays user objects with lots of attributes. The user may have up to three hundred attributes. These attributes however, can be grouped off. For ...
1
vote
1answer
874 views

High Global Lock % on Mongodb

I've just started using Mongodb v2.4.8 and the global lock % averages 80% which seems rather high to me. The CPU usage is around 120% on a 2 core, 2GB RAM, SSD VPS running Ubuntu 12.04 64bit. iotop ...
1
vote
3answers
457 views

Anyone want to try a script bash or python?

I need to automate master binary log purging to occur so that purging never goes back further than the last slave has the updated info for. These slaves are mobile and there are 3 of them, and sync ...
1
vote
1answer
171 views

How to protect Sqlite3 database with password that using in desktop application

I am creating database with Qtsql.QSqlDatabase using with in python. I want to protect my Database with password. I am using this database for my desktop applications. If it is possible, How to do ...
1
vote
1answer
239 views

Unable to convert a table to hypertable due to unique index error

I am creating a table using Flask SQL Alchemy as follows: class Price(db.Model): __tablename__ = "prices" id = db.Column(db.Integer, primary_key=True) country_code = db.Column(db.String(2)...
1
vote
1answer
58 views

Speed up AWS PostgreSQL insertion via Python script

I have 120,000 csv inside my AWS EC2 instance, each containing 120,000 rows. I can't insert each of them as is into my AWS RDS postgresql DB, that will be 120,000^2 = 14,400,000,000 records. Each csv ...
1
vote
1answer
167 views

Storing many pandas DataFrames in SQLite with metadata

I need some help with designing a database. My aim is to persistently store a number of pandas DataFrames in a searchable way, and from what I've read SQLite is perfect for this task. Each DataFrame ...
1
vote
1answer
216 views

MS SQL 2017 Batch insert performance degradation

This one's a bit strange so bear with me: I'm seeing extreme insert performance degradation after an insert was interrupted in a dirty fashion (the transaction was not committed and the connection ...
1
vote
1answer
2k views

Python version issue on Cassandra 3.11.2

Searching around I found a lot of thread about this problem (coming from 2012) but I can't find an official solution: running cqlsh.bat File "C:\apache-cassandra-3.11.2\bin\\cqlsh.py", line 146 ...
1
vote
2answers
600 views

Database Stuck in “Restoring” in SSMS After Running From an Outside Process

I am running a RESTORE DATABASE procedure with RECOVERY and REPLACE from an outside process (Windows Batch using a Python script) and when I do so, it makes my database stuck in "Restoring..." mode ...
1
vote
1answer
3k views

Python Postgres psycopg2 ThreadedConnectionPool exhausted

I have looked into several 'too many clients' related topic here but still can't solve my problem, so I have to ask this again, for me specific case. Basically, I set up my local Postgres server and ...
1
vote
1answer
33 views

Poor Performance from ABS in WHERE

I have a sqlite database with around 12,000 rows, the relevant columns are width1, height1, width2 and height2 My dynamic input is an arbitrary width and height, and I want to find all rows where the ...
1
vote
1answer
2k views

Can't connect to PostgreSQL instance using Python

I have installed vagrant and virtual box on my laptop to finish a class I am working on. I have created a database called tournament. I can connect to it directly, create tables, insert data, etc. ...
1
vote
1answer
667 views

Call python machine learning model from UDF or SP

I would like to run records from my database (Postgres), after an update, through a machine learning model. This model resides in a model.py file. Next to simply porting the python model over to a ...
1
vote
1answer
395 views

mysql to postgresql, migrate files directly ( longblob > pg_largeobject )

I'm working on a python(2.6.6.) script and I'm evaluating what approach to take to migrate files from mysql-server to PostgreSQL . I've read the following posts: How to insert (file) data into a ...
1
vote
2answers
1k views

Why do the queries slow down after a few minutes, when trying to build a DB?

First - I am a complete newb to DBA. Please be gentle. (And I am fairly new to python as well.) Oh, and I have no idea how to change any MySQL settings or the like, so all the MySQL DB settings are ...
1
vote
2answers
437 views

Mysql date_add returns wrong row in python, right row in phpmyadmin [closed]

I am trying to get the first and last record from a month. In phpmyadmin I ge the correct result. In python I don't. In fact python returns the same row for both of these queries. First number is ...
1
vote
2answers
3k views

How can I add a column to multiple tables at once in MySQL Workbench?

I have a MySQL Workbench model (diagram) that belongs to a Ruby on Rails application. I now need to add the created_at and updated_at timestamp columns to each of the tables. What is a quick and easy ...
1
vote
1answer
103 views

How to access oracle PDB remotely?

I have create oracle 12c instance with 2 PDBs, I am able to connect to CDB from python using cx_Oracle module but not sure how to connect to PDB.
1
vote
1answer
52 views

SQLite where condition - compare two last rows

I have a SQLite DB table like this: [Stock_price_Table]: +-----+-------------+--------+------------+--------+--------------+----------------------+ |code | date | first | open | close ...
1
vote
2answers
23 views

How do I return a json object of my database table [closed]

I'm creating a web client that uses data from my database to plot markers on a google map. So my collumns are id, name, address, lat, lon, description. My client gets the data from a route in my ...
1
vote
1answer
1k views

How to see unclosed PostgreSQL connection while using psycopg2? [closed]

I run python code: con = psycopg2.connect(some_dsn) cur = con.cursor() cur.execute(some_sql) res = cur.fetchone() #con.close() <-- connection is not closed Then I try: ps auxf | grep postgres ...
1
vote
2answers
982 views

psycopg2 connection with multihosts [closed]

I have 3 db servers on docker containers, and i used to connect like this from my python app: psycopg2.connect(host="db-0", user="user", password="password", database="data") This works,but if i add ...
1
vote
1answer
200 views

No output when use pymongo watch()

I try to access real-time data changes with pymongo.mongo_client.MongoClient.watch(), code example (Python 3.5.3, pymongo==3.7.2): from pymongo import MongoClient DB_CON_STR = "mongodb://10.10.10....
1
vote
2answers
389 views

MySQL HA_ERR_LOCK_WAIT_TIMEOUT ignoring innodb_lock_wait_timeout (deleting most of a table)

I am issuing some long-running delete queries to some databases, and I am getting the following error: Last_Error: Could not execute Write_rows event on table DATABASE.TABLENAME; Lock wait timeout ...
1
vote
1answer
650 views

Python code in T-SQL environment gives “access a socket in a way forbidden by its access permissions” Error

Help on the following please. I have this code Python Code import requests r = requests.get("https://www.google.com") r.status_code print(r.status_code) print("Python Rawks!!!!!!") which ...
1
vote
1answer
413 views

ibm_db on HADR primary database?

Yesterday I discovered a peculiar thing, it looks like a bug but I'll post it here in case someone seen it before. Testscript: #!/usr/bin/python import sys import getopt import re import os import ...
1
vote
2answers
1k views

Calling MySQL stored procedure in python with field names

I am trying to call a MySQL stored procedure that returns a single row, I can get the values back back using cursor.callproc() and cursor.stored_results(), but I can't get name of the field back '...
1
vote
1answer
3k views

Psycopg2 error at or near %

Note location of cursor. At first I thought it was the lack of a semi colon, but as you can see, putting one in does not solve the problem or change the error. I compared this code to samples I found ...
1
vote
1answer
323 views

Alter postgres password from python?

When I run this query from psql shell everything works fine: ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'somePassword'; but when I try exactly the same query from python (with psycopg2 package) ...
1
vote
2answers
23k views

Unable to import mysql.connector in a Python venv session

I've used venv to create a virtual environment for Python 3.5.2. I have installed the mysql.connector. The connector works fine as long as I am not in an activated venv environment. from mysql....
1
vote
1answer
258 views

Algorithm for populating tables

I'm writing an algorithm to populate all tables in a database in Postgres. I already get all tables that have no relationship populated, as follows: I get all the tables with the following query: ...
1
vote
1answer
429 views

MongoDB Implicit And

I'm trying to understand mongodb queries, but one of my queries is returning a record that it shouldn't. I am trying to call db.user.find({'referral.money':{'$ne':0, '$ne':null}}) to get all users ...
1
vote
1answer
13k views

Database Error relation does not exist [closed]

I am trying to perform the seemingly simple, even routine task of importing a TSV file into a Postgresql 9.4 database table. 2 Questions: Why can't it find this table, and Why is it telling me it ...
1
vote
1answer
281 views

Run lo_export as client through psycopg2

Python 2.7 psycopg2 When I run: cur.execute("SELECT lo_export(123456, '/tmp/test.jpg')") Produces error - need to be superuser to do this. This makes sense as it is running a server side ...
1
vote
1answer
409 views

Design db schema in correct way for parent child kind of requirement

I am creating schema of tables. But not able to understand best way to do it. Following are the tables: Car Model - It contains all car models with types. For eg. Mercedes(brand) - GLA 250(...
1
vote
1answer
4k views

How to take backups of MySQL databases, explicitly based on PyMySQL

I am looking for a purely Python solution to take backups of every MySQL database on localhost. I already use PyMySQL to run queries on these databases. Any help is highly appreciated. EDIT After ...
1
vote
3answers
4k views

Connecting to Oracle Database with ODBC/Python

We've recently installed a new piece of software that uses an Oracle Database to store the data. My goal is to connect to that database and pull the data from the back end to do analysis on. I'm ...
1
vote
1answer
8k views

How to run DB2 export command in shell

I am trying to run the following DB2 command through the Python pyodbc module: DB2 export to C:\file.ixf of ixf select * from emp_hc I am successfully connected to the DSN using the pyodbc module in ...
1
vote
0answers
7 views

Query not well formed in Sqlalchemy for select [migrated]

I am trying to generate the following raw query using sqlalchemy core: SELECT library.name, (SELECT ARRAY (select book.title from books where book....
1
vote
0answers
48 views

Copying a dependency structure

I'm looking for performing a copy of a "dependency structure". What I mean is: there is a tree structure representing a product and its sub components. Each of this products may have one or ...
1
vote
0answers
17 views

Pyspark error inserting into mysql database table that exists

I am trying to insert into an existing mysql table using Pyspark JDBC connection however I get the following error(picture attached) Can I get assistance on this error. The table exist in the MySql ...
1
vote
0answers
22 views

How to migrate data from a large amount of csv files with time x space measurements to a database that needs to be available for analysis [closed]

I have made a simulation experiment to obtain time series measurements at different points along a power line. These measurements were done for different types of power lines in different test ...
1
vote
0answers
301 views

operand should contain 1 column Use Variables in SQL statement in Python

Trying to insert four columns and multiple rows with variables. The table in mysql has 8 columns in total. Four columns to insert is as following: report_type= tuple(("ada","asda","frfe","adaaa","...
1
vote
0answers
23 views

Custom Export from power BI service

I'm trying to make a custom button that exports filtered tables to a csv and downloads them to my computer. I am aware that you can do this with the '...' button but this functionality is for other ...
1
vote
0answers
87 views

Designing a table with precalculated team values for fast read queries

My version of PostgreSQL is 10.7. As it's something I can't calculate on the fly due to the amount of times it might be used, I decided to store all the 11 million combinations and their result on a ...
1
vote
1answer
42 views

How to point SQL Server Python to a specific Python install?

SQL Server 2017 installs an instance of Python to "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES" (documentation link) that it uses for its Python calls. This is installed ...
1
vote
0answers
339 views

PGPool II + Django w/ psycopg2 load balancing

I've a pool of applications running django 1.6 with psycopg2 on top of a PGPool II with two backends PostgreSQL servers. But all queries (read and/or write) are going to master PostgreSQL even SELECT. ...
1
vote
0answers
194 views

Cassandra pool warning displaying continuously

I am using the Cassandra driver for python in Spyder. I am trying to fetch some data from Cassandra table. Here is my code: from cassandra.cluster import Cluster cluster=Cluster(['some_ip']) session=...