Categories
Mastering Development

Problem with storing data in sqlite with Python/Flask-SQLAlchemy code

I’m trying to store a user and password in a table I created with sqlite db. It seems that pwd column doesn’t exist, while if I check the table in sqlite it does contain the column. The error returns the following: sqlalchemy.exc.OperationalError OperationalError: (sqlite3.OperationalError) table user has no column named pwd [SQL: INSERT INTO user […]

Categories
Mastering Development

How to update two tables using id from one table to another?

I am writing a Node JS script, what I want to use for updating 2 tables from a remote database. The script does the following so far: It connects to the remote database, selects the new values since a given time. It connects to the local database, inserts or updates the new/edited rows to table […]

Categories
Mastering Development

Joining data from JSON array object field with another table

Let say, I have two tables on Postgre database like below: CREATE TABLE "MST"."Users" ( "Id" uuid NOT NULL DEFAULT uuid_generate_v4(), "Uid" varchar(32) COLLATE "pg_catalog"."default" NOT NULL, "Pid" varchar(100) COLLATE "pg_catalog"."default" NOT NULL, "Details" jsonb, "Contacts" jsonb, "Trackers" jsonb NOT NULL, "Configurations" jsonb, PRIMARY KEY ("Id"), UNIQUE ("Uid") ); CREATE TABLE "MST"."Teams" ( "Id" uuid […]

Categories
User Help

how to make an sql file copied to the server work

I am trying to create a login page on my ubuntu server. I have Apache2, php, and mysql installed and running. I downloaded a sample PHP MySQL Login System including a users.sql file. Is there a way to make this file work with MySQL or is it necessary to generate a new file? If so, […]

Categories
Database Development

Strange max(date) return in reader vs writer aurora mysql

Something very strange happening here which I can’t point out. Using AWS Aurora, select max(date) in the writer gives me one date, in the reader it returns strange date and warnings.. Column is datetime. writer: MySQL [(none)]> select max(colDT) from tbl_example; +———————+ | max(colDT) | +———————+ | 2020-02-12 00:00:00 | +———————+ reader: MySQL [(none)]> select […]

Categories
Database Development

Have I misunderstood the amount of “automatic optimization” that PostgreSQL does without me having to be “clever” when designing my tables?

Suppose I have a table such as this, for logging all web requests made to my website: CREATE TABLE weblog ( id bigserial, moment timestamp with time zone NOT NULL DEFAULT now(), ua text, — user agent ref text, — referer "IP-addy" inet NOT NULL, PRIMARY KEY (id) ) Most people will be sending the […]

Categories
Database Development

Would this kind of table structure be wasteful and stupid, or superior to my current way of doing it?

I currently have this table structure: CREATE TABLE people ( id bigserial, timestamp timestamptz DEFAULT now() NOT NULL, PRIMARY KEY (id) ); CREATE TABLE "personal information" ( id bigserial, "person id" bigint NOT NULL, timestamp timestamptz DEFAULT now() NOT NULL, "data’s timestamp" timestamptz, "field" text NOT NULL, "value" text, PRIMARY KEY (id), FOREIGN KEY ("person […]

Categories
Database Development

(Deadlock) Select Query Causes Insert Query to be Blocking in Transaction

Database: MySQL I have a running app with concurrency transactions in it. I noticed that there are so many errors occur related to deadlock. The error says SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction. Investigating Deadlock I tried to read the stack trace from my app and report […]

Categories
Database Development

UPDATE with variables to renumber column gives syntax error

Searching for a way to renumber a column within mysql, I’ve found multiple articles showing the same approach: Renumbering an Ordering Field in MySQL How to update a MySql column with ascending numbers among others. But trying it on my table I get a syntax error. mysql> SET @rankStart = 10; mysql> SET @rankInc = […]

Categories
Database Development

Why is “INSERT INTO table;” a syntax terror?

I have this: CREATE TABLE people ( id bigserial, timestamp timestamptz DEFAULT now() NOT NULL, PRIMARY KEY (id) ); CREATE TABLE "personal information" ( id bigserial, "person id" bigint NOT NULL, timestamp timestamptz DEFAULT now() NOT NULL, "data’s timestamp" timestamptz, "field" text NOT NULL, "value" text, PRIMARY KEY (id), FOREIGN KEY ("person id") REFERENCES people […]