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
1answer
28 views

Filtering Escape Characters in PostgreSQL

I am trying to filter distinct serial number from a table and join two tables. Current my code looks like SELECT DISTINCT ON (t1.serial_nbr) t1.*, q1.serl_nbr FROM ( select serial_nbr,...
0
votes
0answers
33 views

Graphically representing SQL queries

The Problem I am trying to document a large number of SQL queries at my workplace. I'd like to programmatically parse the queries and generate graphical representations (diagrams) of the queries. For ...
0
votes
0answers
11 views

Modify owners in all tables. How to run a selected query?

I need to write a script which modifies all table owners in a Cloud SQL Postgres db. So far I have gotten this far: SELECT format( 'ALTER TABLE %I.%I.%I OWNER TO %I \gexec;', ...
-1
votes
0answers
14 views

Print A Nicely Formatted List Of Customers [closed]

We have the following python code to represent our tables with different attributes for each. import psycopg2 from urllib.parse import urlparse, uses_netloc ###########################################...
-1
votes
0answers
17 views

I cant write in arabic languge in mysql

I have a server and I create a database into this server. And I want to store Arabic strings but when I try to store Arabic strings it gives me error. joincode =self.code.get() firstq = self....
0
votes
1answer
13 views

Check if a database table has been renamed

My app has a list of an external db's tables and it needs to be regularly updated. How do I check if a table has been renamed? i.e. how to differentiate between a new table and an old table that's ...
0
votes
0answers
39 views

Use Python to parallelize processing of rows, or is there a native way?

As mentioned in a recent question of mine, I'm new to Oracle: Running Oracle's sqlldr on VirtualBox VM from my MacBook (client) machine I'm back, this time with a question about the best way to ...
0
votes
0answers
10 views

Appropriate language to write a Script that Kills Long running query MongoDB

I want to create a script that kills queries when they reach specific time threshold, I will be using db.currentOp() method to get information about currently running queries, and use db.killOp to ...
2
votes
1answer
22 views

Executing sql linked server procedures through python

I am getting an error when trying to execute SQL Server system procedures such as sp_addLinkedServer and sp_dropserver etc from python script USE [master] EXEC master.dbo.sp_dropserver @...
5
votes
1answer
109 views

Why MS SQL bigint type is implicitly mapped to float64 python type, and what is the best way to handle it? [closed]

Python integer type has unlimited precision so it is more than capable to hold a bigint value of MS SQL (64 bit). Still it is implicitly mapped to float64 python type, when passed to an external ...
1
vote
1answer
51 views

“REVOKE CREATE ON schema public FROM public” behave differently when executed via manually and via code in Postgres

In postgres, I created "test" database with superuser_name and then I created new user "app_user". Then I revoke app_user from create table for any public schema. And then I gave ...
1
vote
1answer
44 views

Duplicate key errors in Postgres queue-like table when rows should be locked by SELECT FOR UPDATE SKIP LOCKED

I have a table called queue with items that need to be processed: CREATE TABLE public.queue ( id serial NOT NULL CONSTRAINT queue_pkey PRIMARY KEY ); Another table process ...
3
votes
2answers
80 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 ...
0
votes
1answer
27 views

Variable problems in PostgreSQL with language plpython3u

I am setting up a function where recursive action is relevant. The function basically maps two floats according to a rule and then returns a string. The code below is stripped for the actual function,...
0
votes
0answers
131 views

Parallel execution of a SQL Server external script

TLDR for a reader with the same problem If your external script is too slow, you may do something in your scritpt which can be implemented in TSQL, which is a big problem. If this is not the case use ...
0
votes
0answers
130 views

How to enable use of SSL in MySQL server?

I have 2 devices on my local network: a remote MySQL server (using MariaDB 10.3) and a laptop (acting as a client with Python). I need to connect to a remote MySQL server via Python using SSL ...
-4
votes
1answer
31 views

Doing query with website [closed]

I want to create a website and I have a data set of schools locations stored in Mongodb . There will be a search box on my website and when I search for the name of the school, I want to get the ...
0
votes
1answer
91 views

MySQL optimization - more tables vs more columns/rows

Im new to SQL and looking to build a database. As an example to what I'm trying to do is have a database with about 5000 stock tickers. Each ticker will have about 35 data-points, corresponding to ...
0
votes
2answers
80 views

psycopg2 connect error: cannot pass more than 100 arguments to a function?

I am trying to pass json object as a parameter to my stored procedure. I tried many things and solved couple of error atlast I am stuck with the error : Error : psycopg2 connect error: cannot pass ...
1
vote
0answers
50 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
1answer
696 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 ...
4
votes
2answers
138 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'...
-1
votes
1answer
18 views

indexing impact

If I have a table with 4 columns(a,b,c,d) I create the following index for this table : CREATE INDEX index_t ON table(a,b,c); Will the PostgreSQL optimizer consider using the index in the following ...
0
votes
1answer
29 views

SQL Server 2017 MLS - cant add python package

I'm new-ish to MLS and have been trying to add new python packages like  attrs  to the python but it hasn't been working. Our set up is SQL Server 2017 with CU14 on Win 2016. Python version is 3.5.2 ...
0
votes
1answer
152 views

how do I prevent my postgres connection from timing out? (python, psycopg2)

very quick one from me: I have a python script that runs continuously, keeping an eye on a postgres database. I've set up a listening channel and everytime a relevant change is made, the script gets a ...
0
votes
0answers
15 views

How to concatenate three columns data and insert it in to another column when inserting the data into the table everytime

I have a SQlite3 table with six columns and their names are "title", "first_name", "last_name", "mobile", "designation" and "person". I want ...
0
votes
1answer
403 views

Unable to install pl/python for Postgresql12

To get the obvious out of the way, my question is posted after reading many other questions like this one https://stackoverflow.com/questions/60683776/install-pl-python-on-windows-for-postgresql-12 ...
0
votes
1answer
279 views

Adding Python package to Sql Server 2017 machine learning services breaks it

I am trying to get started running python inside of Sql Server 2017 with Python Machine Learning Services. Out of the box it works and I can run simple scripts like listing the installed packages: ...
4
votes
2answers
214 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 ...
2
votes
1answer
176 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
35 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 ...
1
vote
0answers
19 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
16 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
19 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
36 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
1answer
66 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
397 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
35 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
4k 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
1answer
519 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
167 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
25 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
25 views

PostgreSQL limiting number of rows for a certain condition

arena_stat_id | user_id | date_set | level | trophies ---------------+--------------------+----------------------------+-------+---------- 1 | ...
0
votes
1answer
27 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
440 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
74 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
32 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
28 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
2 3 4 5