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
0answers
130 views

barman + corrupted wall + how it can be

I am using barman for bsackup version 2-1.1 and restore postgresql first after 2 restore that was successfully I perform some show-backup on other backup , and I notices all backup return this ...
-1
votes
2answers
85 views

How to optimize for a 50million+ row table read with SELECT statement?

I have a large table (32 GB) with about 52 million rows. The table structure involves a few numerical and a few string columns, out which I select only one varchar column. I don't need the rest. ...
2
votes
1answer
1k 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("PRAGMA ...
0
votes
1answer
62 views

Transfer of data (lots) from NAS based MariaDB to Postgres at AWS

I have a bunch of remote autonomous data harvesting systems running, each collects approx 28,800 records over an 8 hour run. The remote systems are Rasp Pi based, running Python, logging to MariaDB ...
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 ...
0
votes
1answer
520 views

How to query data in MySQL and be able to save in SQL Server? (Python)

Good day I have a doubt, I have a code generated in Python which he does is to query a MySQL table named INFORMATION_SCHEMA.PROCESSLIST as follows: SELECT *, IF (time> = 10, "Bad", "Good") AS ...
1
vote
2answers
2k 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 ...
0
votes
1answer
19 views

Which programming languages will have problems pulling dash named column names?

I sometimes work in clojure, a language promoting the use of dashes in variable names (much more readable than Java camelCasing I can say). Taking this a step further, I'd favor having column name ...
0
votes
1answer
368 views

how to configure influx properly [closed]

I am storing some data in influx and it is quite confusing that influx is 4-5 times slow as Mysql. I try to test by inserting 10000 rows in mysql and then in influxdb. and the stats are below. ...
0
votes
1answer
271 views

Use of MySQL trigger to insert row's week number

I have a table of orders in a MariaDB database that currently has a datetime column. For the dashboard I am building for this database, I have a statistic I would like to display which is "Orders This ...
5
votes
1answer
10k 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) ...
0
votes
1answer
103 views

InnoDB Concurrent writes being ignored

Sorry if this isn't the right place, this is my first time posting anything here. Anyway, I know I'm not supposed to but I can think of no other way. I need to use a small MySQL table (70 rows x 6 ...
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 ...
1
vote
1answer
1k views

Connecting client application to MariaDB Galera cluster

I currently have a REST API written in Flask that connects to a MariaDB server. I'm thinking about replacing the server with a Galera cluster to improve availability and ensure continuity in case one ...
0
votes
1answer
222 views

Mongo Update keeps adding to the the size of the collection

I am running a scraper to check for this one thing to change. So I have no idea why my mongo data base keeps growing when all I do is an update on this single data. I only have one item in the ...
1
vote
1answer
616 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 ...
2
votes
1answer
834 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 ...
1
vote
1answer
248 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: ...
0
votes
1answer
13 views

Am fetching data from database i need to pass variable in my query.but am not getting how to pass variable in query

import MySQLdb as mdb con=mdb.connect(host="localhost",user="root", passwd="yogi@123", db="wisdomjo_db2011") cur=con.cursor() cur.execute("SELECT suggestion,related FROM `auto_related` WHERE ...
1
vote
0answers
50 views

Create Database within the same Cluster without closing active connection?

Is there a way to clone a master database without closing an active connection? I have a single master database and multiple slave database. To clone a new database I use the following command ...
1
vote
1answer
74 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
288 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
0answers
505 views

Import Data From csv Into Postgresql WITHOUT COPY

I am writing a function for a python environment which needs to import data from a csv file into an existing table. The scope in which the function can execute does not allow for a db SUPERUSER to run ...
2
votes
3answers
1k 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 ...
0
votes
1answer
723 views

BULK INSERT: Trials and Tribulations

I recently ran into several problems with a BULK INSERT task, so I thought I'd share my experiences. I'm not looking for help, just offering help for others that may experience something similar in ...
1
vote
1answer
12k 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 ...
0
votes
1answer
53 views

Create simple database with secure data entry GUIs [closed]

I need to create a relatively simple database management environment with a secure graphic user interface, including forms, for data entry. In my co What has been used since to fill this need is an ...
2
votes
1answer
3k 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 ...
0
votes
1answer
45 views

Booleans, CONSTANTS or mapping table for 'status'-like fields?

I am modelling a User table which needs to have the following information about the users: is_active? is_detained? has_voluntarily_deactivated? is_temporarily_suspended? and so on... Basically, ...
0
votes
2answers
999 views

Incremental backup MongoDB from oplog

I was trying to take backup using oplog based on ts using below code import pymongo import traceback from pymongo.cursor import CursorType c = pymongo.MongoClient('mongodb://localhost:...
0
votes
2answers
17k views

mysql error 1265 data truncated for column

I created a table in mysql like this : create table if not exists data (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, execute_time DATETIME NOT NULL default current_timestamp, db VARCHAR(25) NOT NULL, ...
1
vote
1answer
269 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
0answers
938 views

postgresql Idle for transaction while migrating 30GB database

I'm migrating part of a mysql database into a postgresql database with a different Schema. I've written a python script, using mysql-connector and psycopg2 modules. It takes care(should) to convert/...
2
votes
1answer
932 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 ...
1
vote
1answer
377 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(...
0
votes
1answer
2k views

Can't connect to ODBC database with pyodbc. Which SQL Server driver should I install on the Raspberry Pi?

I'm trying to connect to a SQL Server database using a python script. I'm using the pyodbc library to connect to it. cnxn = pyodbc.connect('DRIVER={SQL Driver}; SERVER=192.168.80.30 ;DATABASE=users;...
2
votes
0answers
618 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 ...
0
votes
1answer
480 views

Select after an Uncommited Update in Python-MySQLdb

May be this is a newbie question but want to confirm as this is important for my app: I'm using MySQLdb on Python and my code looks like: db = MySQLdb.connect("localhost","root","",test) cursor = db....
11
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 ...
3
votes
1answer
90 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 ...
0
votes
2answers
374 views

Database view listing distinct days

I have a list of sensors of many machines, that stores on a second base (i.e., 86400 records each day for each machine). One of the many columns is the datetime of the value (that can also be stored ...
3
votes
1answer
922 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 ...
1
vote
0answers
32 views

Heirarchial Database with Time Tracking

Working in Python/Django 1.8 I have a problem along the following lines: Assembly Table |Part |Parent (FK to other Part) i.e.: Car |--Tire |--Bolt A |--Bolt B Journey Table |Date |...
0
votes
0answers
10 views

How to load a .dmp into PostgreSQL using pgadminIII [duplicate]

I have a dump file from oracle that I wish to load into pgSQL database. I can use psycopg2 or pgAdminIII to accomplish this. Any tutorials or references would be much appreciated as I cant seem to ...
0
votes
2answers
2k views

SQLAlchemy connection error with Postgres

I am trying to write a basic python script that uses SQLAlchemy to connect to my Postgres database. In the connection details, if I use "LOCALHOST", it connects fine. If I reference the server by name,...
0
votes
2answers
677 views

How to add new columns to MySQL database retaining existing data?

In implementing upgrades to an existing Python application, on a development server, one MySQL table has gained an additional column but another has gained 23. What is the best way to add these to to ...
1
vote
1answer
355 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 ...
0
votes
0answers
2k 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
4k 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. ...
0
votes
1answer
187 views

plpy.execute('…') statements not in logs

How can I get the SQL statements executed by plyp into the postgres log files? CREATE FUNCTION history_monitor() RETURNS trigger LANGUAGE plpythonu AS $_$ ... plpy.execute('...') $_$; ...