The tag has no usage guidance.

learn more… | top users | synonyms

7
votes
2answers
1k 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, ...
6
votes
1answer
1k 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 ...
6
votes
1answer
493 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, ...
4
votes
1answer
656 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 ...
4
votes
1answer
3k 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: ...
3
votes
4answers
2k 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 ...
3
votes
1answer
190 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 ...
3
votes
0answers
31 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
0answers
2k views

DB2 Query Error SQL0204N Even With The Schema Defined

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
1answer
588 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 ...
2
votes
2answers
724 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 ...
2
votes
1answer
756 views

SQL Server: how to troubleshoot login timeout expired error

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
404 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
173 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
1answer
3k views

Launching a python script from an insert trigger [closed]

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 ...
1
vote
2answers
610 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
vote
1answer
319 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
2answers
546 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 ...
1
vote
1answer
846 views

Cannot use python3 as stored procedure language in posgresql database

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 ...
1
vote
3answers
319 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
195 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 ...
1
vote
2answers
127 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
2k 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
15 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
37 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
1k 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
901 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
30 views

MySQL: database setup to properly deliver 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
2answers
20 views

How to restore postgres database which is created using pg_dumpall?

I have dumped all of my databases using pg_dumpall and now I want to restore them. I used the command psql -f db.out postgres, but I am still not able to see my databases. Can anyone help?
1
vote
0answers
35 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 ...
1
vote
1answer
25 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 ...
1
vote
0answers
18 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 |...
1
vote
0answers
69 views

SQLite reference table question

I'm using SQLite and Python for making a sensor network database. I have a table holding the nodes: NODE, another 2 tables for sensor specification: SENSOR_TYPE ("Temperature, Humidity...) and ...
1
vote
1answer
61 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 ...
1
vote
0answers
12 views

Adding new sproc to the exisiting database using connectors?

The project I'm currently working on is mostly pythonic, by this reason I'll likely use mysqldb python connector or sqlalchemy. The question arose when I've tried to find out the way of adding new ...
1
vote
0answers
610 views

Creating Pandas DataFrame on Python From a MongoDB Document containing Embedded documents

I am working on a machine learning algorithm on python's scikit.learn, but this time the data are in Mongodb documents format. I would like to pull my data into a dataframe. Here is an exemple of the ...
1
vote
0answers
326 views

Migrate from SQLite to MySQL (then back)

For various reasons, I must use a SQLite database with a local application. However, I wrote a Flask app that enables users to more easily classify items in the database, and it runs very slow on ...
1
vote
0answers
115 views

Trouble adding foreign key on lots of data in Postgres

Working with PostgreSQL 9.0.8 on linux. Can't add foreign key. I have two tables: Table1 and Table2. Table1 has 10 columns and 200 000 rows filled with some data, where first column is 'id' (primary ...
1
vote
0answers
302 views

Does a high CPU usage -say 98%- necessary mean the database will be slower?

I have 4 python scripts each running in their own virtual machine. The scripts are accessing a central postgresql database. All scripts are reading from the same table but posting to different tables ...
1
vote
1answer
4k 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 ...
0
votes
2answers
36 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 ...
0
votes
1answer
132 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;...
0
votes
2answers
1k views

Read massive flat file into relational database

I have a massive flat text file with ~4M records that I would like to read into MySQL using Ruby/Rails, Python, or any other method. The flat file is roughly this format: business_name,address,city,...
0
votes
1answer
51 views

look for missing datetime records in database

I have a python script that scrapes a webpage every minute of every day and saves the results in a postgres database. As I browse through the database, I've noticed there are minutes here and there ...
0
votes
2answers
69 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 ...
0
votes
1answer
76 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
1answer
35 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('...') $_$; ...
0
votes
2answers
111 views

Cassandra - save file

I'm new with Cassandra, and even more - I never have such task for habitual RDMS systems, like MariaDB or Oracle. So, how can I store file in to Cassandra's table? Preferably using Python-driver, but ...
0
votes
2answers
763 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 ...
0
votes
1answer
275 views

Scale MySQL trigger for thousands of inserts per seconds

We are using MYSQL database for an Analytics app. I have two table token and token-big (for overview of actual data) both have id(int) unix_starttime(int) unix_endtime(int) details(varchar 256) A row ...