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
0
votes
1answer
29 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.
0
votes
0answers
17 views

Postgres “CREATE TABLE AS (SELECT …)” stuck

I am using Python with psycopg2 2.8.6 against Postgres 11.6 When I am running a query CREATE TABLE tbl AS (SELECT (row_number() over())::integer "id", "col" FROM tbl2) Code is ...
0
votes
0answers
23 views

Postgresql thinks that insert value is a column [closed]

I'll try to explain this as straight forward as possible. Short explanation of setup: Python psycopg2 connection to postgresql Data is read from information_scheme in mssql and counts all rows in all ...
1
vote
0answers
16 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 ...
0
votes
0answers
12 views

Ubuntu PostgreSQL - Upgrade adding Python extension

I have an existing and populated PostgreSQL 10 instance, to which I'd like to add Python support as a Procedural language. As far as I can tell this can be done initially by installing postgresql and ...
0
votes
1answer
18 views

Need help with this sql query for Postgres DB for a Python flask application

@app.route("/home/search/details/<string:isbn_result_isbn>") def book_search_results(isbn_result_isbn): book_details = db.execute("SELECT * FROM books WHERE isbn=:g", {&...
0
votes
1answer
23 views

Change default timezone to America Pacific

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone; Result: SYSTEM SYSTEM SELECT CURRENT_TIME; Result: 06:25:07 But MY System time is 11.55 AM IST and PST TIME is 11.25 PM How can I change ...
0
votes
0answers
15 views

Snowflake/S3 Pipeline: ETL architecture Questions

I am trying to build a pipeline which is sending data from Snowflake to S3 and then from S3 back into Snowflake (after running it through a production ML model on Sagemaker). I am new to Data ...
3
votes
2answers
41 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). ...
0
votes
1answer
30 views

How can I speed up my count queries? [closed]

Right now I am creating counts for each hour for the whole week. I am using python to connect to the database, and I am looping through each hour so str(i) is the current hour that is being updated. ...
0
votes
2answers
127 views

Using Python to insert JSON into PostgreSQL [closed]

I have the following table: create table json_table ( p_id int primary key, first_name varchar(20), last_name varchar(20), p_attribute json, quote_content text ) Now I basically ...
0
votes
0answers
38 views

how to run db.adminCommand() using pymongo for mongodb 3.6

I want to run this command and get it's output using pymongo module. db.adminCommand({getParameter: 1, logLevel: 1}) or say I want to run this command db.adminCommand( { setParameter: 1, ...
0
votes
0answers
17 views

Try to run Python programm from command line. ImportError: No module named mysql.connector

I'm new in programming. So I have a question about this error. I'm trying to run programm from command line in PyCharm, and get this Error. root@kali:~# python /root/PycharmProjects/lesson/ProL3/ner....
0
votes
0answers
14 views

Storage fluctuates when creating a table as select from another table via python

My table "test_table2" holds around 12mil records and has 6 columns with small records (short strings, dates, etc.). The size of the table is cca 1 GB. When I'm running this query: create table db....
0
votes
1answer
22 views

Getting proper output of my SELECT clause. SQLite

I am coding a bus connection search engine. My goal is to see the time when the bus is departing, while the stop_from and stop_to names are provided. I have created the STOP table CREATE TABLE STOP (...
0
votes
0answers
22 views

PostgreSQL limiting number of rows for a certain condition

arena_stat_id | user_id | date_set | level | trophies ---------------+--------------------+----------------------------+-------+---------- 1 | ...
0
votes
1answer
9 views

SQL optional where clauses without exponentially large number of only slightly different queries

I'm working with postgres in python and I want to write a clear query that optionally allows WHERE clause subsetting, but doesn't require me writing an exponential explosion of whether the subsetting ...
1
vote
1answer
48 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
27 views

SQLite where condition - compare two last rows

I have a SQLite DB table like this: [Stock_price_Table]: +-----+-------------+--------+------------+--------+--------------+----------------------+ |code | date | first | open | close ...
0
votes
1answer
15 views

heroku postgres sql changes not reflected in db [closed]

I wrote this program import psycopg2 try: #connect to db cursor = connection.cursor() except Exception as e: raise e else: print("connection Success") cmd = "" while(True): ...
0
votes
0answers
4 views

Kafka producer for API results

I'm dealing with an application that has an API you can call that returns JSON results My question is how to produce these results into Kafka Would a JSON producer work fine and be best practice? ...
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 ...
0
votes
1answer
54 views

Error using convert MySQL in Python

I'm practically newbie with MySQL. I'm trying to do a query to retrieve a preformatted date, obtained from a subtraction, using CONVERT. This is the query: sql = f"SELECT CONVERT(timestamp - {...
0
votes
0answers
58 views

MongoDB - Update all documents NOT returned in aggregate query

I'm using PyMongo to write a Mongo update script. I have an aggregate query which returns a sum of all searches performed by each user in the last 30 days: cursor = mongo_search_logs_collection....
0
votes
0answers
13 views

Pymongo: Query data from specific wiredtiger (.wt) files in mongodb

I am new in pymongo. I have some wiredtiger(.wt) available in mongodb as below. I wanted to know what data are being stored in each of the following (.wt) file. All these files are belongs to one ...
0
votes
0answers
8 views

New dataframe for different groups

I have a dataframe with a column 'Country'. Every country has multiple records. I want to write a function in python using pandas so that for every country it can return a seperate dataframe. Having ...
1
vote
1answer
26 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
299 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
0answers
20 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
1answer
75 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 ...
0
votes
0answers
21 views

Populate mysql db to Shotgun server using Python

I've an internal mysql database. On each creation, modification or deletion of the content of this db, I need to run a python script to replicate those information into another software web server. ...
0
votes
2answers
26 views

inserting multiple values into SQL with %

This works: cursor = conn.execute("""Select FAMILY,F_NAME from birthday where B_MONTH = '%s' """ % (currentMonth)) I cannot seem to expand it to work using an AND clause to also include day, I get a ...
0
votes
0answers
405 views

Error: duplicate key value violates unique constraint, but the column does not have a unique constraint? postgreSQL

By the way, I'm trying to automate the below using python, but I've isolated the SQL code and tried it by itself on pgAdmin III, and it still doesn't work. So I have a table called tableName, and I ...
0
votes
3answers
21 views

Overwriting MySQL database to only store 1 month of data

We are logging data on hardware with only small memory (short on disk storage) only 4GB. We only require the data to be stored for 1 month and then be over written in a way that it overrides older ...
0
votes
1answer
43 views

Generated DDL doesn't match with CREATE TABLE statement

I'm using MySQL with InnoDB engine. I create a new table that contains a foreign key, eg: CREATE TABLE rooms ( id INTEGER NOT NULL AUTO_INCREMENT, my_id VARCHAR(15), house_id INTEGER, ...
-4
votes
1answer
93 views

how to get all the table existing in a database [closed]

import pandas as pd import sqlite3 conn = sqlite3.connect('Db-IMDB.db') imdb = pd.read_sql_query('''SELECT table_name FROM information_schema.tables''',conn) but it is giving error: DatabaseError: ...
0
votes
1answer
30 views

MongoDB | Problem inserting embedded documents into document

I am trying to insert data in a nested format. A snippet of my models (there are many, many more): class LifetimeMode(db.EmbeddedDocument): meta = {'collection': 'lifetime_mode'} career = ...
0
votes
2answers
427 views

Run SQL query with python in SQL Server raises error: SqlSatelliteCall error: Unsupported input data type in column

I am trying to run Python in SQL Server and want to define a variable which contains the result of a simple select query. exec sp_execute_external_script @language = N'python', @script = N' import ...
0
votes
0answers
217 views

SSHtunnelForwarder to Linux Box, then connect to Postgres RDS (AWS)

I'm trying to write a python script to update DDL in a Aurora RDS (postgres) on AWS. In Order to do this, I need to SSHTunnel to a linux box that sits at the edge of the VPC, then do another jump to ...
-2
votes
2answers
56 views

Python - Best database for exponentially growing database? [closed]

So I am making a Discord (social media) Bot in Python using discord.py-rewrite, and I need to save informations of lots of servers and users (such as how much money they have, what items thay have…). ...
0
votes
0answers
44 views

Neo4j: Connection closed after few minutes: defunct connection Address (Python)

I have a server (hosted in Azure) in which the connection is opened (python): self._driver: Driver = GraphDatabase.driver(credentials["uri"], auth=(credentials["user"], credentials["password"]), ...
0
votes
1answer
31 views

Inserting records into Mongodb using Pymongo is using up my RAM incrementally and is eventually exhausting all memory

I am writing Python code that parses through a huge XML document of size 17GB(Wikipedia collection) and inserts each page title along with the links in that page (storing as a list) present on the ...
0
votes
1answer
23 views

Is it necessary to connect to a postgresql database in order to create a new server?

I am writing a script in python 3.7, using psycopg2 to set up a new database from scratch. In all the tutorials I have seen, using psycopg2 starts by connecting to a database and getting a cursor ...
0
votes
1answer
387 views

CSV (UTF-8) to Postgres database (UTF-8), doesn't show äöå characters

I have csv-file that is UTF-8 encoded. I'm using python (psycopg2) to copy it into a Postgres database. My Postgres database is using UTF-8. However I have problems to show Ä, Ö and Å characters. If ...
1
vote
2answers
721 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 ...
0
votes
0answers
21 views

How to increase idle time of “LaunchPad” for SQL Server external scripts?

sp_execute_external_script can take a while to initialize when not run for a short time. How do I set the idle time on the controller Launchpad process to keep it from shutting down too soon? Note I ...
0
votes
1answer
966 views

How to Bulk Insert sqlalchemy subclasses

I am trying to bulk insert SQL-Alchemy Subclasses into the parent table and their respective tables ie fruits tables -> Apple Table and so I insert a table of APPLE and it will insert both the row ...
1
vote
0answers
235 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","...
0
votes
2answers
894 views

Psycopg2 Errors on SQL statement when trying to copy data from CSV file into PostgreSQL database

I am not a developer or PostgreSQL DB admin, so this may be basic questions. Logistics: Windows 10 server / pgAdmin 4 / Postgres 10 / Python 2.7.13 I'm using a python script to ingest external data, ...
1
vote
0answers
19 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
2 3 4 5