databases

[lapis oriole]

Thank you. This seems to be good :

DELETE FROM test WHERE log_datetime IN (
  SELECT log_datetime FROM (
    SELECT log_datetime, log_count, LAG(log_count) OVER (ORDER BY log_datetime) AS before FROM test
  ) WHERE log_count = before
)
;

[rocky forum]

It's in the file xampp/phpmyadmin/config.inc.php you can change it there.

[torn sphinx]

when i saved it to database 403958024483504128 it saved as

[grim vault]

My guess would be some int64 <-> double conversation along the way. I think javascript still uses double instead of int64, in which case it cant represent the numbers above 2^53 correctly.

[strange jewel]

Hi everyone. I have a question. Can i read a excel file in python with only standard libraries?

[pine kettle]

cluster.Botswag.Accounts.find_one({"_id": str(ctx.author.id)})['Balance']```

this is mongodb, im trying to access the key balance in the db, but it raises a type error: python 02/26/2022, 03:30 || TypeError: filter must be an instance of dict, bson.son.SON, or any other type that inherits from collections.Mapping

[fading patrol]

I think you'd have to save it as CSV

[vivid briar]

Guys I trying sqlite3, but when I put

conn = sqlite3.connect(“whatever.db”)

it doesn’t create another file (yes I did import)

[restive umbra]

from a bit of research on Hash algorithms, I have come under the impression that it acts like a means of sorting arbitrarily large amounts of data, almost like indexing for a specific item in a list. how far off am I?

[fading patrol]

If you don't get any error message but you don't see "whatever.db", my guess would be that you're looking in the wrong directory

Or maybe you need to close the connection to write the file but I'm not sure off the top of my head if that's the case

[vivid briar]

What’s a directory

[nova cove]

A file structure that contains files or other directories

[fading patrol]

If you import os you can print(os.getcwd()) and it will show you what directory Python is running in. That's where your DB file will be if you don't specify a path

[harsh pulsar]

a "folder" on your computer

historically "directory" has been a more common name in the unix world

[pine kettle]

im trying to use motor, and im wonder if this code would work for motor or if something is missing? ```py
motor.motor_asyncio.MotorClient(key)

async def open_account(user: discord.Member):
try:
post = {"_id": str(user.id), "Balance": 0}
await cluster.Botswag.Accounts.insert_one(post)
except:
pass

@client.command(aliases=["bal"])
async def balance(ctx):
await open_account(ctx.author)
embed = discord.Embed(title=f"{ctx.author.name.title()}'s Balance",
description=await cluster.Botswag.Accounts.find_one({"_id": str(ctx.author.id)})['Balance'],
color=discord.Color.green())
await ctx.send(embed=embed)```

[torn sphinx]

im using MongoDB with pymongo and it act weird some times and gives connection error but after sometime it works fine
anyone know how is that???

im working with online version not offline version

[bleak crown]

Hi, I'm using SQLite and I have a basic game. In this game I am storing members and their matches in two seperate table. In the members table they have a column like "total_matches". I would like that column to pull data from the matches table with an expression. Is it possible?

The closest I could find was "generated columns" but that only works on the same table right?

[fading patrol]

That's a basic join query. You might need a foreign key if you don't already have one. Any basic SQL tutorial will cover this stuff, but if you have a specific problem, share the code and other details

[fading patrol]

If it's not a problem with your code, sounds like a network issue. It's unlikely anyone here can troubleshoot that for you without a lot more details

[nova cove]

I establish my aiosqlite db connection here:

async def start(self, token, reconnect) -> None:
  DB_PATH = "./bot/db/bot.db"

  async with connect(DB_PATH) as self.db:
    BUILD_PATH = "./bot/db/build.sql"

    if os.path.exists(BUILD_PATH):
      with open(BUILD_PATH, "r") as f:
        await self.db.executescript(f.read())

    await self.db.commit()
    log.info("Connection to database established.")

  await super().start(token, reconnect=True)

The connection is bound to this instance attribute: db: Connection

When I attempt a transaction in another file, for example this:

@classmethod
async def new(
  cls,
  db: Connection,
  target: Member,
  issuer: Member,
  infr_type: int,
  reason: str = None,
  expires: datetime = None
) -> Infraction:
  new_infr = await db.execute(
    "INSERT INTO infractions (member_id, member_name, issuer_id, infr_type, reason, issue_date, expiry_date)"
    "VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *;",
    (target.id, str(target), issuer.id, str(issuer), infr_type, reason, datetime.utcnow(), expires)
  )
  await db.commit()

  return cls(db, **new_infr)

I pass in self.bot.db to the db param to access that db connection as so: infraction = await Infraction.new(self.bot.db, target, interaction.author, infr_type, reason)

But, the new_infr transaction raises an error that says No active connection. I am not sure what I have done wrong as my IDE always brings me back to the db: Connection instance attribute. Help is appreciated!

[lapis oriole]

There are 2 things of note here

1. I suppose this is for a Discord bot, or some other thing intended for user interaction. If you intend to make it public, I strongly recommend that you switch to another database. SQlite is very useful, but doesn't scale well. You risk ending up with a database slow as hell, and migrating can be a pain in the ass. So think about it. I recommend PostgreSQL, with asyncpg or psycopg (it has async and pool support, don't be stumped if you only see synchronous things) but that's my choice.

2. That being said, I know where your error is :
   You are using aiosqlite.connect as a context manager, which means that the database is closed when you exit the context manager.
   you should rather do self.db = await aiosqlite.connect(**kwargs)
   And then, when you wanna close the connection, do await self.db.close()

[nova cove]

the bot is only going to be in one server, and not open source. and yes I should have noticed that 🗿 . thank you

i was using postgres for my old bot that was on multiple servers

[lapis oriole]

Okay, no issue. Just wanted you to keep that in mind, as it can be bad potentially lol

[nova cove]

yeah i know that. 👍

thank you though

[buoyant cargo]

Hey! I have a "design" question, I am currently using sockets to get some web data but am not sure which is the best way to save this data (I get currently almost a message per second), I tried using sqlite but the asynchronous nature of the WebSocket logic caused me some problems (also am not sure if this is optimal enough) so for testing purposes am just writing the data into a bunch of files separated by day, which for testing is good enough but I feel dirty

[worthy gust]

hello hello
I deployed my web app on Heroku and now I want to see database file, how can I see that ?

[fading patrol]

Sounds like a good use case for Kafka. I haven't used it myself but that sounds like what it's meant for

Or maybe Spark: https://www.educba.com/kafka-vs-spark/

[fading patrol]

https://stackoverflow.com/questions/38924458/how-to-see-files-and-file-structure-on-a-deployed-heroku-app

[empty hinge]

Kafka is not really intended to be used as a database, tho you can mess with its settings to achieve this behavior.

[empty hinge]

SQLAlchemy has async compatibility, maybe you can use it to access your database in an async context. https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html

[pine kettle]

this isnt updating the db https://paste.pythondiscord.com/ojulowizuh

[empty hinge]

Are you committing the transaction(s)?

[pine kettle]

it subtracts from the first one, but not the second

committing meaning?

[empty hinge]

https://www.postgresql.org/docs/11/sql-commit.html. Extremely similar concepts exist for all relational database engines. Tho I now realize you may not be using a relational DB

[pine kettle]

this is mongo, using motor

[empty hinge]

K, im not familiar with those tools

[nova cove]

user id should be BIGINT

oh ok

well if you want to get the users infractions, yes you can select all the data from that table with their user id

you can return an iterable from your function
eg:

@classmethod
async def get_member_infractions(cls, db: Connection, member: int) -> List[Infraction]:
  infractions = await db.execute("SELECT * FROM infractions WHERE member_id = ?;", (member,))
  await db.commit()

  return [cls(db, **infraction) for infraction in infractions]

you would probably have to specify that in the return value of said function that fetches, like shown above

idk if sql has iterables like in other programming languages

[faint hearth]

I'm sorry to interrupt but does anyone know how to format text that is being taken from a .db file (using sqlite3) into a table on another window? (The window is made using tkinter, i can provide code if it's needed)

[grim vault]

The PRIMARY KEY must be unique.

[nova cove]

that too

you coudl probably make an infraction id column, that is the primary key

A user can have many infractions, but each infraction has a different ID

i use member name, member id, guild id, and level, xp (if there is a leveling system)

[fading patrol]

It's not clear to me what you mean, "how to format"? The DB should return the data the same as it was stored.

[faint hearth]

Basically put the data that is taken from the .db into a table style output.

[nova cove]

👍

is this in Python or is it raw sql

what flavor of sql is it?

just wanna know

I prefer raw sql

Well

I create the tables in a Raw sql file

Queries are in Python files

you can use executescript()

if os.path.exists(BUILD_PATH):
  with open(BUILD_PATH, "r") as f:
    await self.db.executescript(f.read())

no no

the sql file is just for tables

CREATE TABLE IF NOT EXISTS sp_members (
    member_id      BIGINT NOT NULL,
    member_name    VARCHAR(255) NOT NULL,
    level          INTEGER NOT NULL DEFAULT 1,
    xp             INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (member_id)
);

CREATE TABLE IF NOT EXISTS infractions (
    id               INTEGER,
    member_id        BIGINT NOT NULL,
    member_name      VARCHAR(255) NOT NULL,
    issuer_id        BIGINT NOT NULL,
    issuer_name      VARCHAR(255) NOT NULL,
    infr_type        INTEGER NOT NULL,
    reason           TEXT NOT NULL,
    issue_date       DATETIME NOT NULL,
    expiry_date      DATETIME,
    expired          BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (id)
);

but i do queries in the python file, such as:

new_infr = await db.execute(
  "INSERT INTO infractions (member_id, member_name, issuer_id, issuer_name, infr_type, reason, issue_date, expiry_date)"
  "VALUES (?, ?, ?, ?, ?, ?, ?, ?) RETURNING *;",
  (target.id, str(target), issuer.id, str(issuer), infr_type, reason, datetime.utcnow(), expires)
)

it doesn't accept NULL as a value

yeah it needs a value

wdym

well you don't HAVE to specify a reason for an infraction

yeah in this case, i want a reason to be specified

[brave bridge]

If you try to insert a NULL into a NOT NULL column, you'll get an exception

[nova cove]

fix error is here😳

do utcnow

[lucid crane]

Just use timestamps

<t:1644901200>

[nova cove]

how you do that 🗿

[lucid crane]

Which part?

<t:1644901200>
<t:1644901200>

[nova cove]

does discord have like a time stamp format thing

[lucid crane]

Yeah

[lucid crane]

It's this

Why not?

[wary glacier]

it does

[lucid crane]

https://www.postgresql.org/docs/9.5/datatype-datetime.html

🤷‍♂️

[nova cove]

yeah it has timestamp

[wary glacier]

now, how do you compare a postgresql date datatype to datetime.date.today()?

[lucid crane]

Maybe I'm misinterpreting what you're doing but you know dpy has a built in helper for that, right?

For creating timestamps, yes

[wary glacier]

meh

[nova cove]

!d discord.Activity.timestamps

[delicate field]

discord.Activity.timestamps

A dictionary of timestamps. It contains the following optional keys:

• start: Corresponds to when the user started doing the activity in milliseconds since Unix epoch.

• end: Corresponds to when the user will finish doing the activity in milliseconds since Unix epoch.

[nova cove]

Not sure if that’s the right one

!d discord.utils.snowflake_time

[delicate field]

discord.utils.snowflake\_time

discord.utils.snowflake_time(id)```

[lucid crane]

It's like format_dt or smth

[wary glacier]

2022-02-27 <- datetime.date.today()
[<Record birthday=datetime.date(2007, 3, 26)>, <Record birthday=datetime.date(2004, 2, 15)>, <Record birthday=datetime.date(2004, 2, 27)>, <Record birthday=datetime.date(2004, 4, 30)>] <- SELECT birthday FROM users;
```how do I get the third one with datetime.date.today()

[nova cove]

that returns datetime.datetime

[lucid crane]

!d discord.utils.format_dt

[delicate field]

discord.utils.format\_dt

discord.utils.format_dt(dt, /, style=None)```
A helper function to format a [`datetime.datetime`](https://docs.python.org/3/library/datetime.html#datetime.datetime "(in Python v3.9)") for presentation within Discord.

This allows for a locale-independent way of presenting data using Discord specific Markdown...

[lucid crane]

Here we go

What?

[wary glacier]

I'm done already, how do you get 2 and 4 out of this?

it's all just dates and I can't figure it out

I tried extracting month and day to compare with extracted month and date of today but no success

        val = await self.bot.db.fetch(
            """
                SELECT id FROM users 
                    WHERE ((EXTRACT(MONTH FROM DATE birthday) = EXTRACT(MONTH FROM current_date))
                        AND 
                    (EXTRACT(DAY FROM DATE birthday) = EXTRACT(DAY FROM DATE current_date)));
            """
        )
```probably cuz I'm dumb

[wary glacier]

[]

it returns as much as []

        val = await self.bot.db.fetch(
            """
                SELECT id FROM users
                    WHERE (
                        (EXTRACT (MONTH FROM birthday) = EXTRACT (MONTH FROM current_date))
                            AND 
                        (EXTRACT (DAY FROM birthday) = EXTRACT (DAY FROM current_date))
                    )
                ;
            """
        )
```hehe got it somehow

[fading patrol]

If you just want to view the data tables, the easiest way is to use a viewer like DBeaver. You could display your data in a web browser with Flask or Django but that will require some coding work

[faint hearth]

No, I want it to be displayed in another window and it has to be done in python (school project 😑😑)

[fading patrol]

Another window? Like with print? It won't be pretty but it shouldn't be hard. Share a link you your code and other details if you're stuck on something

[faint hearth]

Can I dm you tomorrow with the code? im not at my pc rn

[fading patrol]

No, but you can grab a help channel and share the link here #❓｜how-to-get-help

[faint hearth]

Ok then, Can i ping you tomorrow?

[fading patrol]

No, please do not ping me. Anyone in this channel can help you and I will see it if I'm available

[faint hearth]

Oh ok

[sonic whale]

I want to connect two DB in Python. DB has different hosts.
If db1 and db2, respectively, I want to move the table and data of db1 to db2. What method should I use?

[fading patrol]

It's not clear what you're asking here. What is the problem you are trying to solve and what are the specific options you are looking at?

[wintry plinth]

i think u should put this question in #help-cheese

[worthy gust]

kk

[hollow pike]

any linux users?

I want to display the current date in a shell but its not outputting
i created a NOW variable $ NOW=$(date +"%m-%d-%Y")
and then in my script i added echo $NOW but the date doesnt print
any ideas?

[outer parrot]

why doesnt this work im confused asf

[ionic pecan]

#unix is probably a better channel, but did you add a space between the $ and the NOW where you set it?

[ionic pecan]

is that a dict that you're iterating over?

a.items() in that case

[frozen warren]

which sql module should i use, im new to sql? and where do i start if i want to use an sql database?

[outer parrot]

ye i saw it

in help channel

[proven arrow]

You can use connection pooling, for handling your connections. See the pinned message for examples. Also yes generally you would try to do the queries in sql rather than application. You do the calculation where the data is. Primarily because it’s much quicker for a db to handle it. Only as a last resort do it in application code.

The links are here

[proven arrow]

See this message and the one below it #databases message

[peak stag]

I have a really simple question that I'm overthinking. If I say select id, name, max(revenue) from a table group by name in mysql, will the id it returns be the same record that has the max(revenue)? It will, right?

[proven arrow]

Yeah but also you will have to add id to group by

[peak stag]

But then it'll group by the ID, which I don't want. i.e. a database line or whatever

[bleak crown]

Hi! I have 2 tables like this and I gave them example datas: The first one is members and the second one is matches. I'm trying to create a view that will hold a member's id, member's total wins, members't total matches and member's total rounds.

[peak stag]

Sample data:

__id__ | name | revenue
1 | cat | 100
2 | dog | 200
3 | cat | 500

If I do group by name, id, won't it give me separate results for 1 and 3?

Oh God, I just tested it with sample data and it doesn't, brutal.

[bleak crown]

I tried this command:

CREATE VIEW member_profiles
AS
SELECT
    members.member_id AS member_id,
    count(matches.match_id) AS win_count,
    count(matches.match_id) AS total_matches,
    sum(matches.total_round) AS total_rounds
FROM
    members
INNER JOIN matches ON matches.player1 = members.member_id or matches.player2 = members.member_id``` but i don't think it works as expected 😄 I tried to change it with:
```sql
CREATE VIEW IF NOT EXISTS member_profiles
AS 
SELECT
    members.member_id AS member_id,
    count(matches.match_id WHERE matches.player1 = members.member_id and matches.player1_result = "won" or matches.player2 = members.member_id and matches.player2_result = "won") AS win_count,
    count(matches.match_id WHERE matches.player1 = members.member_id or matches.player2 = members.member_id) AS total_matches,
    sum(matches.total_round WHERE matches.player1 = members.member_id or matches.player2 = members.member_id) AS total_rounds
FROM
    members
INNER JOIN matches ON matches.player1 = members.member_id or matches.player2 = members.member_id;``` but it didn't work either because of the syntax and how I am trying to do it 😓 . Sorry I am new to sql. 😅

[proven arrow]

It will result in indeterminate behaviour @peak stag

Sure you can omit it by changing the sql mode but you should avoid

[peak stag]

Thanks that's the worst. Appreciate your help, my brain has been a little umgekehrt today. Also hi from a pilot in Berlin

[bleak crown]

The first view generated this:

I think I'm dumb and I don't understand anything :(

[bleak crown]

Well, it is expected since in the first command, it just counts the match_ids and since I am joining it with members it doubles the size generally.

So I guess I need to filter them but idk how

[bleak crown]

Also it only generates one row 😭 but there are two members

[bleak crown]

Okay, solved it with subqueries 😄

[wary glacier]

                    await self.bot.db.execute(
                        """
                        UPDATE SET voiceid = NULL WHERE id = $1;
                        """,  member.id
                    )
```syntax error at or near =
what's the correct way? I saw this on stackoverflow just now (postgresql)

[wary glacier]

damnit

[safe ice]

Hi, I find it very hard to get started on sql. My project is: I have one single UUID. When I request a rest api using that uuid. It will return multiple uuids. I basically want to repeat that but also check if the uuid has already been used before.

[empty hinge]

that's a bit confusing: if you're interacting with a REST API, SQL shouldn't be involved

[safe ice]

I need a way to save up to 30million uuids.

And check if I have scanned them before

[ionic pecan]

so you want to check whether the UUID is in the table already?

[empty hinge]

back of the envelope math- a standard uuid is 128 bits. 30 million of that is just under half a gigabyte. Even accounting for the pyobject handlers if you store the uuids as bytes you shouldn't have any problem just throwing them all into a set in your python process - assuming a normal sized computer these days

otherwise, a simple table with one column with a unique constraint on it should be a more permanent data store

sqlite3 should easily do the trick

[safe ice]

Wait I think Im going to understand it thx

[frank gorge]

in the mysql data base
i have created column with space
like
Blood Group
Email Address
how to implement it in python

[fading patrol]

What do you mean "implement it in Python"? You want to write data to those columns?

[frank gorge]

yeah i am insterting it using python
the column has Blood Group
and i use Blood Group using python
and its says error and un matched

[fading patrol]

Are you connected to the correct db? If yes, show your code

[frank gorge]

its fixed now i removed the space from column names , now its working

[frozen warren]

where do i start if i want to use an sql database with a python project?

[fading patrol]

Not knowing anything about your project, I'd say try something like this... https://likegeeks.com/python-sqlite3-tutorial/

[lean cosmos]

how can we do difference in row value 2 to row value 3 in postgres

?

[nova cove]

wdym

[lean cosmos]

query to find the companies that have given dividend for at least 3 consecutive year in the past.

i need to find this

@nova cove

[fading patrol]

Show your attempt and any error you're getting

[lean cosmos]

company,fiscal_year

ABCD,20702071
ABCD,20712072
ABCD,20732074
ABCD,20762077
XYZ,20692070
XYZ,20702071
XYZ,20712072
XYZ,20732074
i have this dataset how can i create a logic if ABCD have 3 consecutive year like
20702071
20712072
20732074
then ABCD is labeled as a stock
how can I build a query for this ?

[brave bridge]

ask the mate, they probably know their software better 🙂

[lean cosmos]

select *,row_number() over() as rn,
fiscal_year-row_number() over() as difference
from dividend;
but i got difference
20702070
20712070
20732071
20762073
20692065
20702065
20712065
20732066
20692061
20702061
like this

[hushed urchin]

what

is the best way to record data using python to a web server database? I want to be able to use the data on other devices

[fading patrol]

"Best" depends on many things, but here's a quick tutorial on Postgres: https://pynative.com/python-postgresql-insert-update-delete-table-data-to-perform-crud-operations/

You can spin up a free Postgres DB on Heroku and use that

[dusky field]

Hi, I am trying to translate the following SQL query into Map-Reduce (using python and mrjob)

Here is the SQL query:
SELECT genre, count(distinct name) as num_movies
FROM movies
WHERE genre != 'Horror'
GROUP BY genre
HAVING num_movies >= 100

Here is my best attempt:

def mapper(self, _ , line):
key = line.split(',')[1] # genre
yield (key, 1)

def reducer(self, key, values):
if key != 'Horror':
count = sum(values)
if count >= 100:
yield (key, count)

Can someone help me figure out where I might be going wrong?

[pine kettle]

i need help with mongodb, using the motorclient, anybody know about the update_one() command?

there are no errors, but only the first one of these lines updates the database py await cluster.Botswag.Accounts.update_one({"_id": str(ctx.author.id)}, {"$inc": {"Balance": -amount}}) await cluster.botswag.Accounts.update_one({"_id": str(user.id)}, {"$inc": {"Balance": amount}})

[rigid yacht]

hello guyz good morning

[hushed urchin]

Will do

[lean cosmos]

company,fiscal_year

ABCD,20702071
ABCD,20712072
ABCD,20732074
ABCD,20762077
XYZ,20692070
XYZ,20702071
XYZ,20712072
XYZ,20732074
i have this dataset how can i create a logic if ABCD have 3 consecutive year like
20702071
20712072
20732074
then ABCD is labeled as a stock
how can I build a query for this ?

select *,row_number() over() as rn,
fiscal_year-row_number() over() as difference
from dividend;
but i got difference
20702070
20712070
20732071
20762073
20692065
20702065
20712065
20732066
20692061
20702061
like this

[frank gorge]

import mysql.connector
mydb = mysql.connector.connect(host = "localhost",user = "root",password = "123qwe",database = "patientdb")
my_cursor = mydb.cursor()
last=my_cursor.lastrowid
print(last)

it always prints 0 instead of the last id
how to fix it

[naive sandal]

This read-only property returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement or None when there is no such value available. For example, if you perform an INSERT into a table that contains an AUTO_INCREMENT column, lastrowid returns the AUTO_INCREMENT value for the new row. For an example, see Section 5.3, “Inserting Data Using Connector/Python”.

I'm guessing it's returning 0 because your table is empty

[frank gorge]

the table is not empty ,

[naive sandal]

The doc also says that it returns the value based on the previous INSERT/UPDATE, but you aren't doing either so it might be returning 0

[frank gorge]

i have inserted the value i can send u the pic of the tabl

[naive sandal]

Yes, but it seems like lastrowid is set once you run an INSERT/UPDATE query on that cursor

[naive sandal]

Yep, but are you inserting the data with the same cursor?

[frank gorge]

yeah

[frank gorge]

should i use another cursor to get the data/id?

[naive sandal]

no, you need to use the same cursor which you insert the data with

cur = db.cursor()
cur.execute('INSERT INTO Test (data) VALUES ("one");')
print(cur.lastrowid)
``` for example

otherwise you might be able to do something like ```sql
SELECT Max(PID)+1 FROM Table;

nevermind, mysql has a last_insert_id function

[naive sandal]

SELECT last_insert_id(PID) FROM Table;
``` should work

[frank gorge]

how to use this in python

[naive sandal]

cur = con.cursor()
cur.execute('SELECT last_insert_id(PID) FROM Table;')
print(cur.fetchone()[0])

[frank gorge]

it is printing 1 instead of last row id

[proven arrow]

Cursor should have a lastrowid attribute which will give you this value

[frank gorge]

i did then it prints 0

[proven arrow]

What is the column type of PID?

See the documentation for the requirements to get this to work, https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-lastrowid.html

It needs to be an auto increment column

[proven arrow]

@frank gorge I see it’s already posted here. Did you not read this?

[frank gorge]

yeah but i ddint got it

[frank gorge]

INT

[outer parrot]

why doesnt this work

D:

i litteraly copied from 1 that was working

[topaz wharf]

missing () in your commit

[outer parrot]

that also doesnt work

[mint mauve]

any errors?

[topaz wharf]

[frank gorge]

import mysql.connector
mydb = mysql.connector.connect(host = "localhost",user = "root",password = "123qwe",database = "patientdb")
my_cursor = mydb.cursor()
sql='SELECT * FROM patient_tbll'
my_cursor.execute(sql)
output=my_cursor.lastrowid
print(output)```
it is printing 0 instead of the last id row from the table

[manic zinc]

Afternoon all, I would appreciate a little bit of help. Im trying to nest a SELECT statement into an INSERT statement. I have a list of variables I am able to use to insert some data, but one of the pieces of data needs to be grabbed by a SELECT statement, how am I able to do this alongside my other variables? Currently I have: $add_job_sql = "INSERT INTO work_schedule(week_id, day_id, job_id, location_id, staff_id) VALUES($work_week_id, $work_day_id, $work_job_id, SELECT `job`.`location_id` FROM `job` WHERE id = $work_job_id, $work_staff_id)

Obviously I understand it might be a bit difficult not know my tables, etc, but I can advise further if needed

if it isnt formatted: `INSERT INTO table VALUES (variableX, variableY, SELECT statement, variableZ). How else would i go?

[fading patrol]

I would use a separate select statement to define each variable first, then insert

[manic zinc]

Thats what I wanted to do cause it made sense. But tl;dr its part of a task and states that I should use a nested SELECT statement

tried stackoverflow etc, but cant find a working example

[fading patrol]

Can you use multiple nested selects? Select job from job, select location from job, etc.

[manic zinc]

So it already gives me 4/5 variables with info

its the last one i need to find using another reference they give me

So i suppose I could just ignore their variables and select it myself?

[proven arrow]

@manic zinc what doesn’t work with the current approach you have?

Selecting a value from another table should be fine to insert

Just be sure to limit it so you only get one row

[proven arrow]

Then the error is with the syntax

The approach is correct

Select should be inside the values

[manic zinc]

then thats helpful i suppose

[proven arrow]

For example,

[manic zinc]

VALUES($work_week_id, $work_day_id, $work_job_id, SELECT `job`.`location_id` FROM `job` WHERE id = $work_job_id, $work_staff_id)

[proven arrow]

Insert into t1 values(1, 2, (select c1 from t2 limit 1));

[manic zinc]

limit?

[proven arrow]

You should wrap the select statement in brackets and limit is there so you only get one rows value back

Maybe you where filter already does that but as a safe option.

[manic zinc]

Ah it seems to be working now!

Must have been the syntax all along

really appreciate the help :)

[strong gate]

Good afternoon all, would like to ask you guys a favour regarding some robot automation work~

I am trying to write an automation task to verify if the function of a cron job works ,which mainly use to export data from one table to another (*Tables are from different Database.) The job periodically(15 mins) export data(1 hour delay) from trade table (Database_A) to exchange_trade table(Database_B) with same data structure, and data will be partitioned by order(source_sequence, create_date, create_time). And it's introduced 1 hour delay to make sure that all data have been inserted into exchange trade table.

The data look like this:

trade_id           |trade_id|source_sequence|create_date|create_time  |insert_time
-------------------+---------------+-----------+-------------+

1407414667471501638|2291953096675920384|1045371671040-0|2021043006|1619781025653|2022-03-01 13:45:26.714
1407414674178753286|2291952637146256672|1045371694080-0|2021043006|1619781025853|2022-03-01 13:43:14.726

1407414687511548006|2291950444940524416|1045371732704-0|2021043006|1619781026250|2022-03-01 13:43:04.420
1407414687511768230|1045371732704-1|2291950044145438912|2021043006|1619781026250|2022-03-01 13:42:53.013```

The cron job can be curled through POST and i define the method in python as below.

                                          truncatedValue, truncatedUnit, timePeriod):
    method = 'api/scheduler/scheduleTrigger'

    payload = {
        "schedulerJobKey": "ExchangeTradeDataJobKey",
        "triggerKey": "ExchangeTradeDataJobTrigger",
        "cronExpression": cronExpression, #value:0 */15 * * * ?
        "jobDataMap": {
            "jobName": "ExchangeTradeDataJob",
            "parameter": {
                "startTimeFromNow": startTimeFromNow, #value: -1
                "startTimeFromNowUnit": startTimeFromNowUnit, #value: HOURS
                "truncatedValue": truncatedValue, #value: 0
                "truncatedUnit": truncatedUnit, #value: HOURS
                "timePeriod": timePeriod, #value: 900000


                "schedulerType": 1
            }
        }
    }
    write_to_console(f"{method} payload: {payload}")

    response = post_monitor_request(payload, method, ip, port)
    write_to_console(f"{method} response: {response.text}")
    json_result = response.text
    return json_result```

Right now, i am using mock data and controlling the data flow and I have been defining the method below. But i am not so sure if it was good enough to validate the function while retrieving live-data, can anyone kindly suggest ?

def get_and_validate_cron_job_exchange_trade_data():
    cur = get_database_connection().cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    query = "select * from trade where " \
            "insert_time >= now() - interval '1 hour' and " \
            "insert_time < now() " \
            "order by trade_id desc"
    cur = process_query(query=query, conn=get_database_connection(), cur=cur)
    trade_record = cur.fetchall()
    cur.close()
    trade_data = pd.DataFrame.from_records(trade_record)

    trade_data.to_csv("/Users/samuelcheng/Documents/GitHub/cryptoexchange-functiontest/functional_test/test_case"
                      "/050_trading_areana/000.start_cron_job/000.con_job_trade_data.csv", header=True)
    write_to_console(f'Trade data consist row and columns :{trade_data.shape}')

    cur = get_nts_database_connection().cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    query = "select * from exchange_trade where " \
            "insert_time >= now() - interval '1 HOUR' and " \
            "insert_time < now() " \
            "order by trade_id desc"
    cur = process_query(query=query, conn=get_nts_database_connection(), cur=cur)
    exchange_trade_data = cur.fetchall()
    cur.close()
    exchange_trade_data = pd.DataFrame.from_records(exchange_trade_data)
    # write_to_console(exchange_trade_data)

    exchange_trade_data.to_csv(
        "/Users/samuelcheng/Documents/GitHub/cryptoexchange-functiontest/functional_test/test_case"
        "/050_trading_areana/000.start_cron_job/000.cron_job_exchange_trade_data.csv", header=True)
    write_to_console(f'Trade data consist row and columns :{exchange_trade_data.shape}')

    data_validation = exchange_trade_data.equals(trade_data)
    write_to_console(f'Cron Job Data Validation is : {data_validation}')
    return data_validation```

[harsh pulsar]

@strong gate this is a lot of code and the process is a bit complicated. you are trying to run some kind of monitoring job to make sure that the "main" job ran successfully?

it looks kind of like you're doing the right thing: query the database and assert that the data you expect to be there, is in fact there

it helps to come up with "invariant" properties that you can assert to be true in all cases

[fleet ibex]

using datetime with sqlite3, ive seen a lot of different ways to do it.. is there a preferred way to use it

[torn sphinx]

Manu

Manu*

Many*

[harsh pulsar]

i tend to use iso-8601 formatted strings

i believe sqlite has functions to work with that

https://sqlite.org/lang_datefunc.html

[fleet ibex]

looking into it, i meant more as breaking it up by date / time .. then calling back date if time not specific or date & time if it is.

[empty hinge]

you could do that pretty easily. Another hacky convention I've seen used and I'm sure is used in an embarrassing number of corporate databases lot is just to set the time to 00:00:00 if it's not been specified. Then store dd/mm/yy 00:00:00 as the datetime in your db

has obvious drawbacks, but it's easy to ignore in downstream analysis

[harsh pulsar]

@torn sphinx case is a keyword 🙃 either quote the column name or change it

maybe case_id?

case_number?

or just id even, that's pretty conventional

yes

if (not (await (coroutinefunc()))): ...

that's more or less how the syntax tree looks

[torn sphinx]

SELECT CONCAT(DATE("createdAt"), ' ', EXTRACT(HOUR FROM "createdAt")), AVG("finishTime" - "startTime") average
FROM task_history WHERE "status" = 'SOLVED'
GROUP BY DATE("createdAt"), EXTRACT(HOUR FROM "createdAt")
ORDER BY (CONCAT(DATE("createdAt"), ' ', EXTRACT(HOUR FROM "createdAt")))

So I have this query. Basically, the idea is to group all the rows in a table by the hour value in the createdAt column, and then get the average value of the difference between startTime and finishTime

So this query generates something like this:

My problem now becomes - how do I deal with

1. The gaps in the time stamps - See, rows 4 and 6? There is a gap of ~18 hrs between the date/hours. So when I plot this in the graph, there is just gonna be a chunk of missing data in the graph. I want the value for the missing hours to be 0, and ideally this is done by the query, and not by a for-loop after

2. Ordering - The data isn't in the right order. It needs to be from earliest timestamp -> latest timestamp

i thought maybe a time series would help, but then im not sure how to query the data from the table anymore

pls tag me when responding

[candid prairie]

Hi,
I'm using Elasticsearch and now I would like GET the price data when the price drops 20%. I have some ideas how to achieve this but not sure if its the right way to do it.

So every 1 minute the price is getting updated. I was thinking about subtract the last value of the second last value and if the difference is higher than 20% do something.

Do you think this is the right way or is there an obvious other way to do this? And is it even possible to GET second last document from Elasticsearch

[harsh pulsar]

i recommend interpolating the missing values later in python (but before plotting), instead of trying to do it in the database

[harsh pulsar]

it looks correctly sorted to me, according to the screenshot you posted

the order of the results in python should be the same as the output from the database

[torn sphinx]

row 4 and 5 for example

hour 21 comes after hour 5

same with row 9 and 10

[harsh pulsar]

oh, i see

pad the single digits with 0s

[torn sphinx]

is there no other way to achieve averaging a group of rows? I feel like the solution I have right now is super hacky

[harsh pulsar]

is it? seems like pretty standard sql to me

you can use a nested query to avoid re-computing the column value twice

SELECT * FROM (
  SELECT
    CONCAT(DATE("createdAt"), ' ', EXTRACT(HOUR FROM "createdAt")) AS "date_hour",
    AVG("finishTime" - "startTime") AS "average"
  FROM task_history
  WHERE "status" = 'SOLVED'
  GROUP BY DATE("createdAt"), EXTRACT(HOUR FROM "createdAt")
) t
ORDER BY "date_hour"

something like this maybe

untested though. would help to have a db-fiddle example to work with

https://www.db-fiddle.com/

heck you could go even one layer further but that seems icky

SELECT * FROM (
  SELECT
    CONCAT(created_date, ' ', LPAD(created_hour::text, 2, '0')) AS date_hour,
    AVG("finishTime" - "startTime") AS average
  FROM (
    SELECT
      DATE("createdAt") AS created_date,
      EXTRACT(HOUR FROM "createdAt") AS created_hour,
      "startTime",
      "finishTime"
    FROM task_history
    WHERE "status" = 'SOLVED'
  ) AS t1
  GROUP BY
    created_date,
    created_hour
) AS t2
ORDER BY "date_hour"

i guess like that? ugh

i really dont like how non-composable sql is

small things get ugly so fast

[grim vault]

I cant test, but what about:

SELECT to_char("createdAt", 'YYYY-MM-DD HH24'), AVG("finishTime" - "startTime") AS "average"
  FROM task_history
 WHERE "status" = 'SOLVED'
 GROUP BY 1
 ORDER BY 1

[harsh pulsar]

ah, to_char is much better

what is that group by 1 trick?

wouldn't you still need to group by the result of to_char?

[subtle pumice]

if i want to import a csv into a table, do the column names have to be the same as in the csv for it to work?

[grim vault]

It's the first column of the select list, you can reference by index (starting with 1).

[harsh pulsar]

ahhh, i always always forget this is an option

great idea

[harsh pulsar]

no, but it depends on if you are using some kind of import tool that enforces this

[subtle pumice]

im just using the built in sql server import and export wizard

i dont get errors but the table is empty

[fading patrol]

Never done that but guess would be that the column names do need to match the field names in the db

[torn sphinx]

SELECT DATE_TRUNC('hour', "createdAt"),
AVG("finishTime" - "startTime")
FROM task_history WHERE "status" = 'SOLVED' 
GROUP BY DATE_TRUNC('hour',"createdAt")
ORDER BY DATE_TRUNC('hour',"createdAt");```

Ended up doing this, works just like i wanted it to

only problem now is the gaps between the time stamps

which honestly shd be done using python anyway

or like a default chart value

[fading patrol]

I'm looking at the docs, do you see your data in the preview panel or no? https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-flat-file-wizard?view=sql-server-ver15

[empty hinge]

@torn sphinx you could as well fill them by generating a temp table using a range over the time stamps b\w the min and max, then joining to it via a left/right join and filling in your AVG value with zero in the case the DATE_TRUNC is null

would be not so bad to write if you use CTEs

but, probably better to do that in the consuming code

[torn sphinx]

yea prob

[subtle pumice]

yes i can see it in the preview but when it's finished theres nothing in the actual table

[outer parrot]

i dont get the error

[burnt turret]

you can't insert arbitrary python objects in the database; member.status is an instance of https://discordpy.readthedocs.io/en/stable/api.html?#discord.Status

you likely want to store it as a string

[scenic olive]

Hi! :) Im having some issues with a query i cant really wrap my head around. Given the query in the picture i get as you see 2 entries pr name in return. I only want the newest one based on eventTimeUtc but i cant figure out how. I can use any_value() and disable ONLY_FULL_GROUP_BY but as i understand its bad practise and i want to understand how to solve this.

[unkempt prism]

I like to use a window function for this. Perhaps a rowNum/RANK partitioning using same params as your group by, and ordering the window by the time col desc.

What db engine are you using?

[scenic olive]

ill try to do some googling on that - thanks :) I am using mysql and mariadb (it runs on mysql but trying to make it compatible with both, since RPi)

[dense wharf]

if in a folder new data/file is getting added after an interval i.e. streaming data. How to store this streaming data in mongodb using python. New data will be created in the folder and new data created should get automatically detected and get stored in mongodb by python. Any idea how to implement this ?

[fading patrol]

while true:
    check_the_folder()
    load_the_data()
    move_the_file()

Where's the problem?

Cron job might be better than a while loop

[dense wharf]

Actually there are already files in the folder..first we need to store those files then keep the code running such that it loads all the files which keep getting added in thr folder

Data is fetched from server so we need to wait for a particular time interval

[fading patrol]

Why not just wait for the data itself? If you have to wait for certain time though, time.sleep

[empty hinge]

you could periodically check for files newer than a timestamp which is updated after each upload process, or keep a set of which files you've seen before that you can compare to the files in the folder. Either way you should probably save that data somewhere you can retrieve it in case the process crashes.

[fading patrol]

I'd you have a really complicated sequence of steps maybe Airflow would help, but it doesn't sound like you're really doing anything that complex

[dense wharf]

Is there something called 'change data capture' . Can it be used here?

[empty hinge]

that's a way of automatically detecting and acting on changes to data, and that specific term usually refers to a process you can setup with your database engine, such as SQL Server. It would respond to changes in the data within a particular table by doing some thing with the data that was affected by the change. There is no such corresponding automation for filesystems AFAIK

[lean cosmos]

SELECT R1.company AS valuestocks
FROM (dividend R1 JOIN dividend R2 ON R1.company = R2.company AND R1.fiscal_year = R2.fiscal_year-10001)
JOIN dividend R3 ON R1.company = R3.company AND R2.fiscal_year = R3.fiscal_year-10001;
How can i Convert This result to json format as shown in figure

[grim vault]

!pypi watchdog

[delicate field]

watchdog v2.1.6

Filesystem events monitoring

[foggy stump]

i'm trying to make a business database generator

and i'd like to have built in generators like name generators

or location generators

i'm kind of confused as to what types of databases i can generate and what predefined generators would be useful

[delicate field]

fake v0.8

Make Python's Fabric act like Ruby's Capistrano

[fading patrol]

!pypi faker

[delicate field]

Faker v13.3.0

Faker is a Python package that generates fake data for you.

[fading patrol]

@foggy stump this will make the fake data for you

[foggy stump]

well i want to make it myself haha

to learn how to do it

but thanks

[empty hinge]

oh that's nice

[fading patrol]

You still have to design a database and decide what to put in it.

If you really don't want to use faker, you literally just need to type out a bunch of names in a list and use random to pick one... Tedious and pointless when faker does it for you

[foggy stump]

ig so

thanks

[woeful plover]

I am currently contemplating the best DB design approach for an event scheduler and recurring events (Django 4.0 implementation - sqlite3 in dev, PostGRES in prod) .

The objective is to have both originating events and recurring events. My instinct is to create a recursive relationship (same table with recursive FK) rather than a parent-child relationship (of two tables). My reasoning is that recurring events (children to the originating parent event) are functionally identical to regular events with one exception, that being the recursive FK field pointing from the child to the parent. Are there any thoughts on this? I also suspect I'll have another field tracking whether the record is a parent or child (recurring) event. This will make for simpler query building in Django (unless of course it's simple to query by an empty recursive FK field).

In turn, a benefit of this will be easier queries on the application layers as it will lead to simpler queries for presenting all events in chronological order, while still allowing me to query for all related events (original plus recursive). I'm delving into this over the coming days and would appreciate any related insights or experiences. Thanks in advance for your time.

EDIT: Actually, identical records is not correct. The parent would have recursive event information, while the recurring (child) event records would not have any recurring event information (obviously, lest it all melt by brain). There could be benefit from two related table design.

[fading patrol]

Sounds like maybe a good use case for a graph db instead of SQL? I've never built one myself, but just a thought

[woeful plover]

Interesting thought. If the application where just events that would be one thing. However, there are other aspects such as venues, hosts, wait lists (and other tables still). In addition, the connections are not the primary focus. For example, users won't care about the connections between the original event and all recurring events. They will care that an event is something they can schedule/book, and that future events are displayed. In addition, the data is of a transactional nature and complex analysis of the relationships is not needed. Thanks for the thought though. Even though this does not appear to be a solid use case for a Graph DB, it's good to think through this. AND, I'd rather be thinking through this in the early stages while I'm still building the initial app module. Also, I don't have experience developing a Graph DB into a Django web application, so that's one less hurdle to manage.

[proven arrow]

@woeful plover what kind of events? Real life events where people attend or system events for something like an event driven system?

[empty hinge]

@woeful plover this is an interesting design problem. I'm having a hard time visualizing the schema exactly, do you have a simple ERD you could share?

I know a significant issue with tables of calendar events is resolving the locking problem - if two users try to reserve the same location at the same time, do your schema constraints successfully block that from happening?

The only option I've really heard of so far is to brute force it and make tables of timeslots for each location, and update timeslots to include the near past and near future on a rolling basis. That's what I remember from a similar example problem in Designing Data Intensive Algorithms

[severe coral]

I have seen DB designs like these ....it can be done at the penalty of increased complexity of reconstructing the tree via recursive functions....

[severe coral]

I encountered locking issues several times in prod... sometimes we solved it with locking hints at the db level

https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15

You can lock a table or a row or not lock at all using hints depending on your situation

[empty hinge]

yeah that's definitely a useful tool. The issues pointed out in DDIA are that it's difficult to define any sensible schema structure for a problem with multiple things that need locking in an interdependent way - like rooms and time slots - with a typical normalized relational schema

you can row-lock the timeslot table, and row-lock the rooms table, but that's not really correct: There's nothing wrong with booking the same room for two different times simultaneously. If you table-lock both tables, you're crippling database performance by overdoing it

also with timeslots, there's the issue of recurring or far-in-the-future meetings

are you gonna have a table of timeslots by the half hour for the next year? five years? ten years?

how do you put locks on timeslots that don't even exist yet?

[severe coral]

It is hard to debug and fix ...you have to be pragmatic

[empty hinge]

It's a really tricky design problem

yeah I think there's a lot of interesting potential solutions

with different pros/cons

[severe coral]

I encounted it in a Uni IT dept and in two at scale providers of services to banks and insurance providers in US and Europe

[severe coral]

We did apply a nolock hint sometimes to increase performance then handle the fallout of that in code lol

[chrome gulch]

anyone have a good aiosqlite tutorial im trying to switch from mongodb to aiosqlite

[slender spoke]

https://youtu.be/Wd5bbTs4Zco
Machine Learning Project Fashion Classifier using TensorFlow | Classifier using Neural Network

[versed oxide]

Hi i need help,
I have a project in which i need to connect to a remote server that is using CentOS Linux 8 and in that server i need to get information from a psql database. I can connect to the server in terminal using ssh Team2@'ip_address' -p 22 and the password. once i'm in i can connect to the database using psql -d 'database_name'.
Can anyone direct me/ send a link in how to connect to the server and get the database in python?
i am making a flask application and need it to communicate to the database.

[lean frost]

How do I recover data from a MySQL database from only the .ibd and .frm files? When following tutorials, I just get ERROR 1030 (HY000): Got error 194 "Tablespace is missing for a table" from storage engine InnoDB. I've been trying to write a jank python script to extract the important data, but it does not work. (hopefully this goes here, does this have to be directly python-related)

[runic mirage]

how to store latex code (maths equations) in MySQL?

[fading patrol]

Never tried it but something like this should work.

https://towardsdatascience.com/how-to-connect-to-a-postgresql-database-with-python-using-ssh-tunnelling-d803282f71e7

[fading patrol]

Doesn't a string work?

[runic mirage]

no

\ are replaced by some other in MySQL

[outer parrot]

how do u set a tablenames as a var

i have this but this doesnt work

[runic mirage]

q = 'CREATE TABLE IF NOT EXIST %s(col1, col2...)' %s (table_name)
then execute (q)

@outer parrot

[drifting pewter]

who can help me?

[outer parrot]

it worked ty

[outer parrot]

nvm help!

this thing doesnt like me

[runic mirage]

not this

define query in other variable

then c.execute (query)

[outer parrot]

k

[outer parrot]

like this

[drifting pewter]

and always do:

[outer parrot]

in?

[drifting pewter]

database.commit()
database.close()

[outer parrot]

frick

[drifting pewter]

for the safe if im right

[fading patrol]

Maybe just a character set issue... https://forums.mysql.com/read.php?103,642383,642616#msg-642616

[outer parrot]

hhshshshsshhss

how do i fix this

[runic mirage]

idk how to use this

[drifting pewter]

you have probably opened your database in a text editor

or

in the app: database browser for SQLite

[runic mirage]

cursor.execute('CREATE TABLE IF NOT EXISTS tablename(col TEXT CHARECTER SET utf8mb4)'
it's not working how to rectify this?

[outer parrot]

i do

[drifting pewter]

close it and than it might work

[outer parrot]

k

[drifting pewter]

else do this

except OperationalError:
  #  some stuff```

[compact marlin]

I need help

I used a json file to store data for my economy bot

But there was too much lag so i changed to an sqlite file

But then i was told that im still using json But im like putting the information in a file named sqlite

[queen rose]

Why dont u just leave it at "TEXT" ?

[fading patrol]

You have at least two general options. 1) If you want to keep your data in JSON but also use a database, you might want to use a document store like MongoDB which is designed for this. 2) if you're data could be restructured easily into tables of rows and columns, SQLite is fine. You just need to translate your JSON into an appropriate schema. If you've never done this it may be worth learning

[queen rose]

If u want to convert an sql table to json u can do it easily with pandas

[runic mirage]

its not working because it does not take \ as a entry so i update it using re.escape(inpute_str) so it is working now, thank you

[compact marlin]

Can i dm u my code and see if im doing it right?

Cus everything is working But i was told im still using json

[fading patrol]

No, please don't DM. You can share your code here if you want to.

If it's working, why not leave it as it is? If this is a school assignment talk to a fellow student or teacher

[compact marlin]

its not a school assingment

i will show u my code here then

[fading patrol]

!paste

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.pythondiscord.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[fading patrol]

If you need it ^^^

[compact marlin]

with open("money_data.sqlite", "w") as outfile: json.dump(member_list, outfile)

[fading patrol]

I see, yes, you're literally saving a JSON dump and calling it a SQLite file. What you want to do instead is use sqlite3. There are lots of good tutorials so look those up and see how far you get

Depending on what your JSON looks like, this could be pretty simple or rather complicated

[compact marlin]

is sqlite3 an sqlite client?

cus i use repl.it for coding and i've heard it has a sqlite client already

[fading patrol]

https://replit.com/talk/learn/How-to-create-an-SQLite3-database-in-Python-3/15755

[compact marlin]

thanks man

i will read up on it and use it thank u

[lunar pier]

def fetchone(query, data):
    return cur.execute(query, data).fetchone()```
does anyone know, why this function returns first data item, not cell value?

like if i use it with fetchone("SELECT ? FROM storage WHERE serial = ?", ('warehouse', '00100')), it will return ('warehouse',)

ah, yeah, ty, i dit it again 😅

[brave bridge]

The query you're forming is: SELECT 'warehouse' FROM storage WHERE serial = '00100'

what do you want to do?

[lunar pier]

I want to get cell value from 'warehouse' column at row where serial = 00100

[brave bridge]

Basically, you're SELECTing a string. You can select a literal or a literal computation, for example: SELECT 2 + 2

[lunar pier]

oh...

one sec

[brave bridge]

You can't substitute a column dynamically like this. You either need to use format or replace (with a whitelisted set of column names).
Or rather, just don't have this helper function. I don't see much use in it

actually no, the function is ok

[lunar pier]

i'm using it because database connection placed in another file

[brave bridge]

but just do fetchone("SELECT warehouse FROM storage WHERE serial = ?", ('00100',))

[lunar pier]

i think i need to use f-string then

[brave bridge]

why?

just write the column name inside the query

[lunar pier]

because i need to pass different column names

depends on what button was pressed

var = 'warehouse'
fetchone(f"SELECT {var} FROM storage WHERE serial = ?", ('00100',))```

this works, thanks

[brave bridge]

can you show more code?

There is probably a better way

[brave bridge]

Can you show the schema for storage?

[lunar pier]

and there's also around 18 other columns which i can't show 😅

[brave bridge]

why so many? and why do you need to dynamically select the column?

If you really want to dynamically create the query, please check it against a whitelist:

column = ...
if column not in {"warehouse", "name", ...}:
    raise ValueError("Illegal column selected")
fecthone(...)

[lunar pier]

This is telegram bot for storage purposes, it must "move" items between sites. User must select sites from inline buttons

like, someone picked an item and want to move it from storage, to one of the sites

i thought about foreign keys and another table for sites, but this db is not big, around 150 rows, and will not get bigger than 1000 rows

i think f-string is fine, thanks :)

[brave bridge]

So each site is a column? And if you want to add or remove a column, you need to change the schema?

[lunar pier]

no, number of sites won't change

[brave bridge]

How many sites are there?

[lunar pier]

and yes, each site is an INT column

22

[brave bridge]

So each column represents the quantity of an item on each site?

[lunar pier]

yes

[brave bridge]

and count needs to be tracked to be the sum?

[lunar pier]

yes, it's fixed i think, but i'll increase it when new things appear

CREATE TABLE "storage" (
    "id"    INTEGER UNIQUE,
    "serial"    TEXT,
    "name"    TEXT,
    "type"    TEXT,
    "count"    INTEGER,
    "warehouse"    INTEGER,```

here's better schema

[brave bridge]

I would recommend something like this:

items
  id : integer (unique)
  serial : text
  name : text
  type : text

sites
  id : integer (unique)
  name : text

item_quantities
  item_id : integer
  site_id : integer
  qty : integer
  unique (item_id, site_id)

[lunar pier]

too complex and hard for newbie like me. This bot will be used around 3 month and then there will be no need in it

it's local, for around 18 people

[brave bridge]

that's fine, I'm not saying you should change your program

[lunar pier]

i think i'll get back to here, when i start to learn relational databases :)

i'll definately start to use them in my next project

[brave bridge]

SQLite is a relational database

[lunar pier]

sorry if i'm incorrect, when i'm going to use more than one table and FK :)

for now, i just need to get numbers from db, count them and write back

[brave bridge]

in that case it's fine

but do check that the column is from a whitelist - otherwise you can accidentally have something like 2 + 2; DROP TABLE storage -- as the column

[lunar pier]

no, i won't use drop table anywhere in code

and buttons return only column names

[brave bridge]

how do you know that?

[lunar pier]

because i made them..

[brave bridge]

Maybe I'm misunderstanding what these buttons are.

[lunar pier]

there's 'callbacks' in aiogram, when button pressed, handler catches callback and i can use it in code

so handlers stands as filters already, because different handlers, works with different list's of callbacks

they just don't catch anything if user somehow send different callback

[brave bridge]

If the response comes from some network call, you should definitely check the column

It's not a very complicated check (you can just check something like column.isalnum()), but it ensures that you won't get an injection

[lunar pier]

hm.. those 18 people doesn't even know how this all works :)

but this is what you trying to tell me right?

[brave bridge]

data['from_site'] in Lists.sites_eng and query.data in Lists.sites_eng

[lunar pier]

will it be better to get factory, made all changes i need and write values back?

[brave bridge]

?

[lunar pier]

the problem with factory, that i need to iterate through each row

[brave bridge]

what factory are you talking abou?

what change do you want to make to the data?

[lunar pier]

https://stackoverflow.com/questions/44009452/what-is-the-purpose-of-the-row-factory-method-of-an-sqlite3-connection-object

here, second answer

wery useful if you need to access value by column name

[brave bridge]

ah, that

so what's wrong with it?

[lunar pier]

but omg, code gets complicated so fast

i know this can be optimised

but not on my level :)

[proven arrow]

What is it supposed to do?

[grim vault]

!or

[delicate field]

When checking if something is equal to one thing or another, you might think that this is possible:

if favorite_fruit == 'grapefruit' or 'lemon':
    print("That's a weird favorite fruit to have.")

While this makes sense in English, it may not behave the way you would expect. In Python, you should have complete instructions on both sides of the logical operator.

So, if you want to check if something is equal to one thing or another, there are two common ways:

# Like this...
if favorite_fruit == 'grapefruit' or favorite_fruit == 'lemon':
    print("That's a weird favorite fruit to have.")

# ...or like this.
if favorite_fruit in ('grapefruit', 'lemon'):
    print("That's a weird favorite fruit to have.")

[versed oxide]

Thanks! I’ll try it out

[pure mortar]

TIL how to use power query to parse out HTML tags in a sql query

it was super useful

recommend that if you have my same problem

[bronze urchin]

I have this line at the bottom of settings.py.
STATIC_URL = 'static/'
I read the documentation & it tells me to create a file called static & put my images into that file.
NOMATTER what I do It ALWAYS FAILES grab the image

I have no idea where Im suppost to place the static file in my system please help befor I break my keybourd

[torn sphinx]

i have 4 duplicate rows in mysql anyone knows how can i delete them now? like i cannot use delete and identify each row with some special condition now

[brave tree]

what's the schema?

are the primary keys (somehow) also duplicated?

[proven ginkgo]

which database is good for discord bot ?

[proven ginkgo]

how i can download heroku database?

[torn sphinx]

i m so dumb that i legit forgot to make a primary key , i wouldn't be here if i had a primary key

the columns are like username , password, age , gender 🙂 , i have 4 entries with all field entries identical

nd idk how to delete the 3 of them now

[queen rose]

U can read the Table with pandas, use df.duplicated to eliminate the double rows, and then do df.to_sql...

[torn sphinx]

i m just using mysql 😦 and using php

[queen rose]

Still applies

[torn sphinx]

how to use df.duplicated?

[sinful rivet]

def add_item(name, amount, item):
    ae = item.upper()
    list = ae.split(" ")
    if len(list) > 1:
        item = list[0]+list[1]
    else:
        item="".join(list)
    cur.execute("UPDATE item_list set ? = ?+? where NAME=?", (item, item, amount, name))
    conn.commit()``` disnake.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "?": syntax error :V why

[torn sphinx]

Holla guys, I need help creating REST APIs

Since I've never done any DB related job, I want to ask how are things done, I've used Python only for data science before without the need of APIs

[slender atlas]

You might want to ask these in #software-architecture or in #databases

[sinful rivet]

this is databases xd

[slender atlas]

Wow

[torn sphinx]

I CAME TO THE RIGHT PLACE MAN HAHAHA

[slender atlas]

I guess you would have data in a database, and handle API requests using some library like FastAPI or something.

[torn sphinx]

The thing is that I need to handle this in Django...

[slender atlas]

I have never used Django

[proven ginkgo]

can i use import other apps database to an app ?

in heroku

after doing backup

and downloading

[halcyon hatch]

Hi! I'm using pyrebase to make an app. I want to have a function to add a child to another child. The issue is that if i use the set function, it replaces the existing child, but if i use the push function it adds an unnecessary child outside

this is what it looks like when i use push

but when i use set it will replace the previous child

db.child("users").child(emailPath).push(data) will give it that timestamp

[lunar pier]

Search through all database or only in selected sites

[river matrix]

Hey all! Question.

What it the syntax for giving a preexisting USER/GROUP a PASSWORD?

Edit: I came right. Thanks guys.

[proven arrow]

You can probably do this with just sql then. But I don’t know what your table or data looks like.

[lunar pier]

Yeah i can, but factory gives me easy access to column names :)

[proven arrow]

You can still use the factory to access the data by column names later

[queen rose]

https://youtu.be/DNsOn31xfeg

[torn sphinx]

i already solved my problem but thank u so much for ur help

[queen rose]

👍

[sinful rivet]

#discord-bots message ;-;

[sinful rivet]

jesus 😂

def check_item(user, item):
    user = str(user)
    if item != "brewing stand" or item != "sword" or item != "armor":
        item = item.upper().replace(" ", "_") if " " in item else item.upper()
        cur.execute(f"SELECT {item} from item_list where NAME = ?", (user,))
        it = cur.fetchone()
        item = it[0]
    elif item == "brewing stand":
        cur.execute("SELECT * from users_data where NAME = ?", (user,))
        conn.commit()
        it = cur.fetchone()
        item = it[10]
    elif item == "sword":
        cur.execute("SELECT * from users_data where NAME = ?", (user,))
        conn.commit()
        it = cur.fetchone()
        item = it[11]
    elif item == "armor":
        cur.execute("SELECT * from users_data where NAME = ?", (user,))
        conn.commit()
        it = cur.fetchone()
        item = it[12]
    return item```

[lucid crane]

Since you're making a discord bot consider using aiosqlite. The sqlite3 module is blocking and will freeze your bot whenever you have to fetch data from your database.

!pypi aiosqlite

[delicate field]

aiosqlite v0.17.0

asyncio bridge to the standard sqlite3 module

[lucid crane]

!blocking

[delicate field]

Why do we need asynchronous programming?
Imagine that you're coding a Discord bot and every time somebody uses a command, you need to get some information from a database. But there's a catch: the database servers are acting up today and take a whole 10 seconds to respond. If you do not use asynchronous methods, your whole bot will stop running until it gets a response from the database. How do you fix this? Asynchronous programming.

What is asynchronous programming?
An asynchronous program utilises the async and await keywords. An asynchronous program pauses what it's doing and does something else whilst it waits for some third-party service to complete whatever it's supposed to do. Any code within an async context manager or function marked with the await keyword indicates to Python, that whilst this operation is being completed, it can do something else. For example:

import discord

# Bunch of bot code

async def ping(ctx):
    await ctx.send("Pong!")

What does the term "blocking" mean?
A blocking operation is wherever you do something without awaiting it. This tells Python that this step must be completed before it can do anything else. Common examples of blocking operations, as simple as they may seem, include: outputting text, adding two numbers and appending an item onto a list. Most common Python libraries have an asynchronous version available to use in asynchronous contexts.

async libraries
The standard async library - asyncio
Asynchronous web requests - aiohttp
Talking to PostgreSQL asynchronously - asyncpg
MongoDB interactions asynchronously - motor
Check out this list for even more!

[sinful rivet]

what to do with it :V

[subtle hound]

HI guys

Is there a way to get Microsoft SQL server on M1 Macbook?

[sinful rivet]

🗿 how big does it wil affect my current db

[sinful rivet]

db = await aiosqlite.connect('database.db')
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
SyntaxError: 'await' outside function

now i hate aiosqlite

[nova cove]

bro

[sinful rivet]

🗿 waht

[grim vault]

This: if item != "brewing stand" or item != "sword" or item != "armor": is true for everything. Your elifs are never reached.

[mellow yoke]

I’m using mongodb and got this error when an ran the file

    raise ServerSelectionTimeoutError(
pymongo.errors.ServerSelectionTimeoutError: cluster0-shard-00-00.7xzee.mongodb.net:27017: connection closed,cluster0-shard-00-02.7xzee.mongodb.net:27017: connection closed,cluster0-shard-00-01.7xzee.mongodb.net:27017: connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 622243b32deaba5516766fd5, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('cluster0-shard-00-00.7xzee.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-00.7xzee.mongodb.net:27017: connection closed')>, <ServerDescription ('cluster0-shard-00-01.7xzee.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-01.7xzee.mongodb.net:27017: connection closed')>, <ServerDescription ('cluster0-shard-00-02.7xzee.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-02.7xzee.mongodb.net:27017: connection closed')>]>

[halcyon cobalt]

Hello @rough quartzone, I had a question that Can a foreign key be null ?

[candid saddle]

when ever i use db from replit when im logged in it works,but if somebody else tries to use the program it gives this error:

[brave bridge]

that's because db is None

can you share the repl?

[candid saddle]

sure: https://replit.com/@charzard/For-Mr-Garbutt-Z7quVb01Gr-MI#main.py

cuz when i use it it works flawlessly

[brave bridge]

A database is stored separately for each user, it's not shared. So, I suppose, if one didn't set up the database or maybe didn't use an account, they would get None

[candid saddle]

oooh, so i cant use replit's db?

[brave bridge]

hm?

[candid saddle]

replits database

[brave bridge]

I meant that, for example, if you fork a repl, you will not get the data from the database

[candid saddle]

so if somebody else tries to use it, they wont have access to the data that i put in there

[brave bridge]

maybe, not sure

[solid coral]

Hey Guys, any recommendation on Postgres hosting services?

[empty hinge]

AWS RDS does the job nicely

[thick adder]

digitalocean ?

[glossy matrix]

➜ wbanalysis git:(gcp) ✗ make upload_data [🐍 warren-buffet]
CommandException: Destination URL must name a directory, bucket, or bucket
subdirectory for the multiple source form of the cp command.

why is GCP saying the destination url must name a directory even though I give it a directory?

[unkempt prism]

Currently at work we use AWS RDS and its solid though seriously considering moving to crunchy bridge . Reasons are crunchy data are great contributors to Postgres and its a similar price with what I hope would be better service.

For smaller / hobby projects I'd just use supabase.

[topaz stump]

I'm a college student with a question for programmers who work in data. Are panda dataframes regularly used? I really love them.

[sinful rivet]

wait, how???

[torn sphinx]

#algos-and-data-structs

[grim vault]

If item is one of the three words only one of the != is False the other two are True, as you are using a or only one of them has to be True which is always the case.
Say item = "sword" ->

if item != "brewing stand" or item != "sword" or item != "armor":
if ^^^^^^^^ True ^^^^^^^^^ or ^^^^ False ^^^^ or ^^^^ True ^^^^^:

-> True or False or True equals True```
If `item` is any other word, all three are true. I guess you want either an `and` instead of the `or`,  or a not in list: `if item not in ("brewing stand", "sword", "armor"):`

[sinful rivet]

lol ok

[grim vault]

As for your unknown column, you might have selected from the wrong table because of this if?

[sinful rivet]

yes ;-;

[solid flint]

Hello! I'm using SQL with Laravel if that matters. Still somewhat new.

I wonder, how would you structure categories and sub-categories, maybe even sub-sub-categories of a product?

For example, we have: Cars -> Brand -> Model -> FInish
Industry->subcat->subcat->subcat

Then we might have: Sport -> Type(martial arts) -> (kung fu etc)

Imagine a list of ALL industies
Travel
Cars
Sport

Then each of then some might have a sub-category.
Travel -> [plane, train, car, tram]
Cars -> [brand] -> [model] -> f[inish]
Sport -> [martial arts, golf, running, swimming etc] -> [martial arts: kunug fu, karate, mma etc]

Would you:

A) Have a table of categories containing ALL categories. ID, Name, Parent_id(to group)
B) Have a table for each category and group there?
C) Something else.

Its basically all going to go on one product.

But what would you do if you had categories and unique sub-categories to go with a specific category, and then perhaps even more 3rd-sub-categories.

There will be a LOT of categories. These rows will contain thousands of rows. Thousands of rows. Say 100-1000m of rows on launch date, and just 20k rows each week added, and its going to be heavy filtered site and there will be 6more categories to create, this is just first category(industry) I'm creating.

[balmy scaffold]

anone have experience with programming towards a databse within a gui program?

#help-bread

[primal notch]

In postgres, do I need to recreate a partial unique index so it takes newly added entries into account?

[empty hinge]

Yes

[vivid briar]

How to auto create SQL database

[errant ridge]

anyone have a clue about the following using sqlite3

im running this query

details = cursorDB.execute("""SELECT name,skills,pref,number,hours FROM overtime WHERE station=? AND rank=? AND actup=? AND watch=?""",(query[0],query[1],query[2],query[3]))

im trying to return all the rows in the db with those criteria

but its only returning one value

how do i return all the rows in the db with those criteria

[proven arrow]

You can have a nested categories table.

@errant ridge show more of the code

[proven arrow]

The database will handle this automatically

[errant ridge]

    def searchQuery(self):
        query = []
        query.append(str(self.station.get()))
        query.append(str(self.variableRank.get()))
        query.append(str(self.variableAct.get()))
        query.append(str(self.variableWatch.get()))

        details = cursorDB.execute("""SELECT name,skills,pref,number,hours FROM overtime WHERE station=? AND rank=? AND 
        actup=? AND watch=?""",(query[0],query[1],query[2],query[3]))

thats the entire function

do you need to see the db too

[primal notch]

Thanks

[proven arrow]

Just ask your question, or are you taking a poll?

[errant ridge]

the parameters are correct for multiple rows

but its just returning one

[proven arrow]

So how are you outputting the data?

[errant ridge]

        for i in details:
            details = i

        print(i)

this under it

didnt seem relevant

[proven arrow]

What database?

I thought you would need to do some sort of fetchall to get rows. I don’t remember to well what execute does.

Which database library are you using ?

[errant ridge]

this is the relevant parts of the db

some of it has personal info on so dont want to send the entire thing over

its an sql db

ill try a fetchall

[proven arrow]

Doesn’t help as there are different ones. But seems execute should work. You should output the generated query and try against the actual database to see if it returns the same rows.

[errant ridge]

fetchall didnt change anything

execute is just the cursor running a command isnt it?

running a query i should say

[proven arrow]

Yes

My guess is your filters are only returning one matching row.

[errant ridge]

i worked it out cheers

i got rid of the for loop

just changed it to details.fetchall()

has worked

[grim vault]

Looking at your indention you only printed once and you did change the loop list inside the loop

[errant ridge]

doing some research on stackoverflow i think the for loop iterates over and over but only assigns the last row it iterates on

so whilst it is getting each row, im only printing it once

[grim vault]

        for row in details:
            print(row)```

[errant ridge]

so now its a fetchall() i can just give it a variable name and access values with [0] etc

cheers

[sour ore]

how can I get RowNumber to save and be able to use? py c.execute("SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowNumber from info") c.execute("UPDATE info SET ID = ? WHERE ID = RowNumber", (index,)) sqlite3.OperationalError: no such column: RowNumber

[grim vault]

You don't. The row_number() is only generated for the select and is not part of the table itself.

You'll need to remember the primary key for the rownumber you want to change.

[sour ore]

oh

How can I do that?

[empty hinge]

You can use a sub query, or a Common Table Expression, to have the select and update run in the same query. Refer to the docs for your database for guidance on the syntax specifics for how to do that

[trim lintel]

@solid flint yes spod I see u from laravel server 😅

Should’ve asked big boy bean. 😅

[solid flint]

Well, I want to get different perspectives and I know in python you have some smart AI/data structure people 😄

[trim lintel]

Tbf bean actually gives good advice. I would listen to what he said if he mentioned anything

[solid flint]

He didnt reply to this. Two people replied and gave two different answers

but I'm also trying to learn the 'why'

[trim lintel]

Although I don’t like him I got to give it to him. In web dev he’s pretty much seen most of it before

[trim lintel]

The way I did this in the past was the answer someone replied to you here earlier

You can add a parent_id column to have it nested as long as you want

In other words self referencing key

[solid flint]

Something like this essentially ish ? I think not quite right, this seems to be a lot more complex? Like some tree structure

[solid flint]

Yeah. But I must say, some advice I asked in JS and PHP servers, for something more algorithmic - I couldn't get an answer in any of these servers, and I've decided, where are some smart people that do this for a living lol well, python, data structures, algorithms right - came here, got an answer in 15minutes. I've spent like a few days figuring that out myself and couldn't do it. So I think the type of pople here and there is also umm senior dev or not, there everyone has some weaknesses 😄 And the guy who solved the little algorithm said he was a beginner so xd but senior devs couldn't help me. That was a bit of an eye openig to me as well tbh. So I really value python discord server when it comes to that stuff xD

[solid flint]

And how would you group categories? and give them a grouped name?

I feel like I get this, but not fully

[trim lintel]

Your question is about how to model this to be able to store data. Not sure what it has to do with an algorithm. Seems you are over optimising and thinking about performance now before your app is even built.

[trim lintel]

With the parent_id. All categories belonging to another category (group) would have the same parent id.

[solid flint]

Oh no, algortihm was a different example, different topic when I needed help with 😄 I think I made that confusing

All I meant is that Python community is better to ask such question than in js/php, when it comes to algorithms and such, as in js/php senior devs couldn't help me out

[solid flint]

But going to the last sentence, I'll be adding 20k of new records per week(all images) - So I want this to be maintanable for sure

[trim lintel]

Well yeah the community is bigger, but I wouldn’t say necessarily better. All the senior devs from my company for example don’t really hang around on discord.

[solid flint]

Right, but people that do Python are most likely going to know more algorithms and data-structures right. A bigger chance to meet such people here.

Obeviously it depends on person to person, but a Python dev to a front-end dev that does just some HTML/CSS and some JS is no match knowledge wise

[trim lintel]

Php is not front end but ok, that’s another topic.

[solid flint]

I'm talking about JS and PHP 😄

[trim lintel]

You type too much man can’t keep up

[solid flint]

Yeah xd

I'm going to try what you told me

[solid flint]

This?

// Categories
//
//
// id   | name          | parent_id 
// 01   | Travel        | null
// 02   | Cars          | null
// 03   | Sport         | null
// 04   | Tram          | 01
// 05   | Plane         | 01
// 06   | Car           | 01
// 07   | Train         | 01
// 09   | Bus           | 01
// 08   | Martial Arts  | 02
// 10   | Golf          | 02
// 11   | Swimming      | 02
// 12   | Running       | 02
// 13   | Kung Fu       | 08
// 14   | Aikido        | 08
// 15   | Judo          | 08
// 16   | Sprint        | 12
// 17   | Marathon      | 12

    
    // Travel/Industry
    //     Tram
    //     Plane
    //     Car
    //     Train
    //     Bus

    // Sport/Industry
    //     Martial ARts
    //         Kung fu
    //         Karate
    //         MMA
    //         Aikido
    //         Judo
    //     Golf
    //     Swimming
    //     Running
    //         Sprint
    //         Marathon
    //     Jumping

    // Cars/Industry
    //     Ferrari
    //         Italia
    //             Xys
    //     Lambo

@proven arrow Hey, did you also mean this?

[trim lintel]

Yeah like that @solid flint

Null parent id means it’s the root

[solid flint]

Yeah. I think a different guy gave me a wrong idea about this when 365 was explaining - but might be also the fact I was messing something up.

I think, the majority of people uncluding yourself said to go this so I'll go this.

This is another thingg with web dev, soemthimes different devs give different solutions, which do you go with?

So just trying to learn the why.

What do you think about "The nested model" http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

This seems a bit too complicated for me, but maybe in future?

I think wordpress might be doing parent_id right.

Obeviously, that there seems a bit advandced, but I'm just explorning stuff for the future. I will go with this solution that you and 365 provided, because it keeps the momentum as well, since I've been on this two days already : p

[trim lintel]

They give different solutions for different use cases. Or sometimes it’s what they’ve known from their past experience in a similar problem.

[solid flint]

Fair enough. The one downside I've seen with the solutin is maintanability, adding hundreds of tables - but then, perhaps his solution was good if there was small amount of categories I suppose, so knowing the app ontext matters too I guess

[trim lintel]

Yeah and no point designing and overly expensive solution before launch especially if it’s a client who’s not even paying for this

[solid flint]

Well, its myself and I'm learning xD

But also want to deploy this as a product

Learning by building some gigs, stuff I wish existed

and hopefully in summer can get a back-end dev job

I think though, I will need to ask for folder structure and retriving images somewheree here as well.

Since I'd be having like 100,000 - 1,000,000 images, how would they get stored and retrived right.

Where do you think it would be the best to ask such question?

[trim lintel]

Yes good luck

[solid flint]

No luck,just pure skill and commitment 😛

And really appriciate your help btw 😄

[trim lintel]

Depends how these images are to be used by the user, how the file names are generated all matters ok how you decide

I liked the approach by Spatie in one of their packages where they have one folder for each file.

You may want to inspect their package and see how they do it

This is good if you want to allow multiple files with the same name

Folder name can be the database column unique id/primary key

[solid flint]

I will need go to the drawing board for this one again : p

I guess I'll do that after the industries

There's more to this than I though generally speaking - but everything seems easier than it actually is right

[trim lintel]

Hahah yeah, something always comes up. Which is why when you give an estimate I always double it

[solid flint]

Right, or triple xd

[trim lintel]

😅😅

[lunar pier]

what is wrong with syntax here? can someone show me de wae? 😅

def commit(query):
    cur.execute(query)
    con.commit()```

sqlite3

while debugging, debug cursor just jumps between first and third row..

i think problem is somewhere in python syntax

[slender atlas]

Too much f-strings and too little query variables

[lunar pier]

In f-strings you mean {variables} right?

[slender atlas]

Yeah

It may be prone to SQL injections or human errors because f-strings kind of paste literals

What is this, sqlite3?

[lunar pier]

Yeah, i still can't understand 😅

but sql injections is not a problem here, this is private bot

[slender atlas]

Well, probably some value got pasted too literally for SQL to make sense of it

[lunar pier]

The strangest thing about this, is how debugger keeps jumping between Functions.commit and SET lines

[slender atlas]

SQL query variables look like this

with connection.execute("SELECT * FROM SomeTable WHERE SomeValue > ?", [25]) as cursor:
    print(cursor.fetchall())

Or

with connection.execute("SELECT * FROM SomeTable WHERE SomeValue > $1", [25]) as cursor:
    print(cursor.fetchall())

That leaves a query string and a list of arguments to be used for query variables in the query string

[lunar pier]

yeah, i know i can do this using '?'

but f-string must work out too

[slender atlas]

Wait, is it SQL syntax error or SyntaxError?

[lunar pier]

There's no errors

It's just does nothing

gimme one sec, i'll record

[slender atlas]

Is Function.commit able to run queries? I am not too knowledgeful

[lunar pier]

Yes, code of this function is right after screenshot :)

[slender atlas]

Ah

You may want to execute it from the connection instead of from the cursor

[lunar pier]

🤔

[slender atlas]

I mainly use Python SQL cursors to read from 'em

[lunar pier]

I'll try this, ty :)

[slender atlas]

I execute from connection objects

[somber crag]

i have an excel file that's 26,315KB and when i use

pd.read(

it won't load the file...do i not have enough memory on my computer to do so?

i'm using Visual Code

[lunar pier]

what did you use to work with excel?

[somber crag]

microsoft visual code

[lunar pier]

what library?

[somber crag]

pandas

[lunar pier]

oh, sorry then didn't used pandas ever, but i worked with excel tables with openpyxl

[slender atlas]

So... How did it go?

[lunar pier]

same strange behavior of debugger...

tried to do it like that

now i'm getting OperationalError: near "?": syntax error

[slender atlas]

You can't have table names as query variables, unfortunately.

[lunar pier]

oh

[slender atlas]

Well, I meant, the left side of SQL operations, if you know what I mean

I think that must be either an actual name or *

[lunar pier]

names can vary, so f-string looks like only way here

yeah, worked out with f-string :)

thanks

[slender atlas]

👍

[lunar pier]

can i somehow make column only positive values and return something if query trying to make negative?

or only in python?

like, getting value, checking everything, only then execute query

[slender atlas]

https://www.sqlitetutorial.net/sqlite-functions/sqlite-abs/

[lunar pier]

Ty

My mind literally stopped working when i wrote this query xD

[solid flint]

Does this looks like a good data structure? I'm using MySQL with laravel

Bare in mind, every company will have probbaly 500 records added on average.

Looking at 25k records added in a week, and it willl also bring the image URL. So this will retrive images, lots of images.

// Companies Table
// id  | name      |  category
//------------------------
//  1  | Discord   | foreignId(1)
//  2  | Stripe    | forgeignId(3)


// Platform categories Table
// id  |   name     
//----------------------------------------
//  1  |   Website
//  2  |   Application
//  3  |   Mobile


// Website Pages Table
// id  |    name        | company_id    | parent_id
//-------------------------------------                             
//  1  |    Index       |   1         |  null
//  2  |    About       |   1         |  null
//  3  |    Contact     |   1         |  nulll
//  4  |    Checkout    |   1         |  null
//  5  |    Cart        |   1         |  4
//  6  |    Address     |   1         |  4
//  7  |    Shipping    |   1         |  4
//  8  |    Cool Page   |   null      | null 

// Website Pages Content Table
// id  | website_page_id       |  Mobile   |  Dark   | Version
//-----------------------------------------------------------                             
//  1  |   1           |  true     |  false  |     v1        |   
//  2  |   1           |  false    |  false  |     v1        | 
//  3  |   1           |  true     |  true   |     v1        | 
//  4  |   1           |  false    |  true   |     v1        |   
//  5  |   1           |  true     |  false  |     v2        |   
//  6  |   1           |  false    |  false  |     v2        | 
//  7  |   1           |  true     |  true   |     v2        | 
//  8  |   1           |  false    |  true   |     v2        |   
//  9  |   2           |  true     |  false  |     v1        |   
// 10  |   2           |  false    |  false  |     v1        |   
// 11  |   3           |  true     |  true   |     v1        |   
// 12  |   3           |  false    |  true   |     v1        |   
// 13  |   8           |  false    |  false  |    null       |   


// Application Pages Table
// id  | applicatoin_page_id       |  Dark   | Version
//-----------------------------------------------------------   

// Mobile Pages Table
// id  | mobile_page_id       |  Dark   | Version
//-----------------------------------------------------------   

Basically, if you retrive a company, you will be able to see the company page in: dark mode, mobile, desktop, light mode, first version, second versoin etc... for each screenshot. One screenshot will have between 1-8 variations essentially

Just wondering if this is a good enough structure. It'll probably have like 1,000,000 records quickly

[proven arrow]

I’m missing the context here but most of it looks fine. Only thing that stands out is you seem to have separate tables for application/mobile pages, which both have the same structure just different type.

[solid flint]

I decided to give that different table because I feel like that deserves a new table - but maybe I'm wrong.

Both will have a tremendous amount of data, and they are two separate things.

However, they will lack 'is mobile' but what mobile and application will have instead of the web is operating system e.g. Windows/iOS or Android/iOS for mobile

So a bit of a difference there.

BUt then again, if there are millions of records for the web, and if one user will never use application or desktop, I feel like it would be a waste to loop over mobile and application that the user will enver ever look for, since the user will use one of the three at the time.

The context here is:

A user can search for a company. Be it Discord, Stripe, Facebook - every page of that company will be screenshoted.

It will be screenshoted for desktop and mobile. With a variation of dark mode if exists, and it will be versioned, so if they update their UI, a new screenshot will appear with a new version. So the user will be able to see versions of the website or application or whatever.

[solid flint]

From what I understood

If you have say three categories, lets say: Lady Dog | Lady Cat | Lady Horse (couldn't come up with anything better : p )

If a dog goes on a website, and is interested in dating, he will never go and see a lady cat or a lady horse - it will always be a lady dog.

So if the dog searches for something, filters etc... there will be less data to loop on the lady dog table right. And there might be billion of records, which 2/3 would never intereste the dog if it was merged with lady cat and lady hhorse

[proven arrow]

Is this application already live or you are building it?

[solid flint]

I'm building it

[proven arrow]

And so at the moment you don’t have users?

[solid flint]

No, but this is what I will manually add

25k records a week

users wont upload anythnig

[proven arrow]

Still 25k is nothing a week

[solid flint]

25k pictures

[proven arrow]

Where are the pictures stored?

File storage like aws or in database

[solid flint]

Well, I'm thinking to use digital ocean, and I think I won't be able to go opast say £60 a month plus

So I guess maybe I could upload like 500gb worth of data/pictures

[proven arrow]

Databases can handle this fine.

They are designed to store lots of data 😄

[solid flint]

I yet need to structure the folders, i think that's the worse thing right

[solid flint]

Right, it blows my mind every time xd

Hopefully, if this side gig would make money then I would carry on uploading more pictures

and then Id just re-invest it all into hosting

coz hosting will get bit expensive(for me at least)

[proven arrow]

Exactly why you should not do any pre mature optimisation now.

[solid flint]

Oh, I'm also learning btw 😄

So I want to spend more time and become a better dev meanwhile

Lets treat this more of a learning experience

[proven arrow]

So then I would leave it in its own single table, and follow the general rules.

If performance is becoming a problem due to the app blowing up later on then that’s a good thing at which point you will have more resources to invest and improve your architecture.

[solid flint]

What do you think based on what I said with the performance? Is that how it works?

I would think we would divide that based that 2/3 of data won't be ever needed for the user.

But does that matter how many tables I have? or why not have 3tables now?

[proven arrow]

It’s common to refactor the schema later on several times

[solid flint]

Yeah, there are some solutions that are too hard for me to implement anyway

[solid flint]

Right, fair enough

So as long as this is decent enouhg

I should be fine

and once this earns money, if even enogh, or hopefully by then Id be better at dev, I could refactor the schema with live data right

You know how they say there is 10ways to code one solution?

well the first 7 are bad, the 8 and 9th are decent, the 10th is the best xd

I want to at least make it a decent structure.

As I do want to get a job as well in back-end dev around/past summer

I'll prob build one more gig before then too, with language and stuff

[proven arrow]

The way I always think of this is, you can spend months engineering an overly complicated and expensive solution or you can just do what you need now. That is pretty much how all big sites/products started.

[solid flint]

I feel like, having three tables for this would be easier, technically I'll just need two at the start, won't do mobile

[proven arrow]

Yes but over time as you see more problems and solve more problems, you become better at picking the better solution more quickly.

[solid flint]

Right, very focused on MVP, get money, and re-invest them back, isntead of running with no money, and then hitting your face on the pavement - technically I got limited money too heh

[solid flint]

So what I got now is something I understand at least, I can map in my head, just need to code it - I suppose I'll go with this if you say its good enough for now

I know it can be done better, but like you said no point doing that now plus its too much work as well

[proven arrow]

Yes exactly, just read this with fathom analytics recently how they did this. I think there was a post on their site.

[solid flint]

Last question regarding this, would it change a lot to have one or three tables for the web/application/mobile categories?

Just trying to understand this bit better in general

on why you said to put it in one, not divide in trhee tables

From technical view as well maybe

[proven arrow]

Because the only difference between those tables is the type.

[solid flint]

Application/Mobile will have additional ios/Android and won't have mobile column

and desktop will enver have operating system e.g. android/iOS or windows/IOS

appplication will never have mobile

mobile will never have desktop

[proven arrow]

These look like attributes for the image

You can have an attributes table.

[solid flint]

Hmmmm

So

mobile/dark

that would be an attribute?

operating system an attribute

etc

[proven arrow]

You are using Laravel?

Nice 😄

[solid flint]

yeah

[proven arrow]

Good elegant framework

[solid flint]

Yeah, so far I like it, easy to understand

[proven arrow]

Yeah that’s one way.

But now you mention Laravel, it has support for polymorphic relations for databases.

Maybe you could look into that. It would allow you to have those separate tables as you planned, and since the framework would handle the queries for you it wouldn’t be as complicated.

[solid flint]

Wrapping my head around this, and what polymorphic relation is is already complicated 😛

So what action plan would yousay I need to do?

1. Learn about polymorphic relations in laravel
2. Go with the three tables not attributes
3. Code some of the functionality or most
4. Figure out folder structure
5. Code it

Whala! xd

[proven arrow]

Yes

[solid flint]

And if polymorphic relation wouldn't exist, then you would suggest I go with one table, and attributes table right

[proven arrow]

Yes, and if you have time try both models. I feel multiple tables increases the complexity but for you to try.

[solid flint]

Hmmmm. So if its too complicated, where I spend 5years figuring it out with no progress, then best I stick with one table and attributes and get it done in 1week 😄

[proven arrow]

Yes do that

[solid flint]

Alright, I'll read more on polymorphic relations, get database ready etc... and see how that goes, if not go with the less resistance path, one table and attributes, and then try that there(polymoprhic) in my next project, which I feel like I might actually do instead but we'll see, don't want to give up too quickly on this one with polymophic

I seriously appriciate your advice for this ^^

[solid flint]

Oh actually, last question: If it was you, would you go with single table and attributes instead of polymoprhic for this project? 😄

[proven arrow]

Yes single table I would go with

[solid flint]

Okay. I'll go with that and attributes in that case. Path of less resistance and keep going with coding this. Try poly next time 😛

Really appriciate the help 😄

[sour ore]

I am using sqlite3/asqlite and I was wondering if I have this await c.execute("SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowNumber from info") how can I assign RowNumber to a variable?

[proven arrow]

What kind of variable? Python variable or something else?

What’s you are trying to achieve here ?

[sour ore]

python variable

I have a loop that goes through every row in my table. I would like to know which row it is on each time

[proven arrow]

You can get it by index, might be easier if you select the row number first in your query. That way you can just use index 0.

[sour ore]

Well on my table I have a column called ID. What it does is it assigns each row a number. so like 1, 2, 3, 4, 5. I am trying to make it so when I delete a row in my table instead of it being like 1, 2, 4, 5 it just updates to 1, 2, 3 ,4. In my loop I call on rows using the ID and I define the ID using the index. Since a row is missing then the index is not equal to any ID causing an error. So basically I am trying to have my table update the ID every time it looped.

[proven arrow]

Now why are you even doing that in the first place

[sour ore]

So I can call on a certain row each time it loops.

[solid flint]

I think this is the last question, just to double check xd

THis is what you had in mind right?

So page will recieve its platform, e.g. user clicks 'show all website' and we show all pages with 'platform_id' of 1.

We give a page a name, such as 'Index'.

We then give content to that page, the correct image, attributes for variation, and make sure the page content is linked to the page.

So if we want to diplsay all variation of company discord, website, with version v1, all will display, then maybe we want to see only mobile and dark mode, then we filter by that.

[sour ore]

can someone help?

[solid flint]

lol have some patience

you asked that a second ago

[sour ore]

sorry

[proven arrow]

Almost, the issue here is your storing a list in a column for the attributes.

This violates the first rule of normal forms in database design

[solid flint]

You're talking about this right attributes_id?

So instead you would have the page_content id in the attributes?

So in attributes table, yo uwould have a list of forgein ids of page content id?

[sour ore]

hey its been like a hour and have not gotten help on this

[solid flint]

You might need to wait a few days sometimes, or it might not get answered at all : p

[waxen finch]

im not sure of the flaws with this suggestion but you could do a double select statement, first to enumerate the rows and then to select the id you're looking for, e.g. sql SELECT * FROM ( SELECT *, row_number() OVER (ORDER BY id) AS position FROM info ) WHERE id = 327; or if you wanted to store the row orders in the table and select from that column instead, you can do that subquery inside an UPDATE FROM: sql UPDATE info SET position = new.position FROM ( SELECT id, row_number() OVER (ORDER BY id) AS position FROM info ) AS new WHERE info.id = new.id;

[sour ore]

I don’t understand most of this as I am new to SQL but it seems pretty helpful

[waxen finch]

although i believe using the id itself as the order would mean the performance penalty of reindexing when you want to update or correct the order, hence why my second one suggests a column for that

[sour ore]

I already have a column for ID.

[waxen finch]

tbh this is prob the first time ive written this kind of query, ive been reading about window functions and i just only just found out UPDATE FROM was a thing

[waxen finch]

what i mean is that if the ID is your primary key and you modify it then sqlite has to also update the index for that column

[sour ore]

Ok

this still confuses me a lot. I only took one SQL course eek

Could you help me implement it into my code?

[sour ore]

What exactly does the second one do?

[waxen finch]

it generates the row numbers inside the FROM (...) and assigns them to the position column

[sour ore]

woah that did it

crazy stuff thanks man

[grizzled mulch]

whats the best database to use?

for a discord bot?

[waxen finch]

a lot of data from discord can be nicely fitted into a relational database, so something like sqlite (single-file) or postgresql (server) would do

[waxen finch]

So the primary key uniquely identifies a row, right? in order for that uniqueness to be preserved, an index is created to keep track of the primary keys in a sorted order - otherwise you'd need to do a linear search every time you wanted to make sure a new row is unique.
To keep the index sorted however, any keys that are inserted, updated, or deleted need to be reflected in that index, which adds some overhead.
Relating it to your problem, say you had a thousand rows and you wanted to delete the first row then update all the ids to be continuous. That would mean 999 rows have to both get updated and re-ordered in the primary key's index.
In contrast, if you use a position column to keep track of the order, it's no longer burdened by an index so updates to that column are simpler to execute.
If you want to see this performance impact yourself, you can create a table with a bunch of rows and then do the two queries: ```sql
-- Generate 10,000,000 rows (https://www.sqlite.org/lang_with.html)
CREATE TABLE test (id INTEGER PRIMARY KEY, position INTEGER);
WITH RECURSIVE
cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<=10000000)
INSERT INTO test (id) SELECT x AS id FROM cnt;

-- Update id column
DELETE FROM test WHERE id = 1;
UPDATE test SET id=new.position FROM (
SELECT id, row_number() OVER (ORDER BY id) AS position FROM test
) AS new WHERE test.id = new.id;

-- Update position column
UPDATE test SET position=new.position FROM (
SELECT id, row_number() OVER (ORDER BY id) AS position FROM test
) AS new WHERE test.id = new.id;``` for me, inserting the rows took 5s, updating the ids took 18s, and updating the position took 12s

[sour ore]

I can understand a little better now thanks. Interesting that it took that long guess thats just python. Though my code is in a task loop for my discord bot that runs every 4 minutes. So I don't think that should be a huge problem as I have so much time

[waxen finch]

oh no i ran the above in the sqlite3 command line

you're using asqlite too so it wouldnt block your bot at least

[sour ore]

Yeah can't imagine the blocking sqlite3 would have if I ever get that many rows

[proven ginkgo]

how i can add a column in a row in portgres python?

[true quail]

pymongo pls

[torn sphinx]

hi so thats my table and columns and is there a way to remove that UNIQUE ?

[nova cove]

primary key and unique??

they both do the same thing, the difference is there can only be one PRIMARY KEY

and PK's can't be null

[mighty lark]

Hi guys. I am trying to set up postgressql with valentina studio. My postgres access doesn't require password but my Vstudio requires a password and its not letting me connect to my postgres databases. Does anyone know what can be done here? I am using ubuntu. Let me know if you need any more information.

[lucid notch]

Im currently using sqlite3. I have just under 10,000 rows of data. I want to do more granular functions to get reports. Do I get the full options from sqlite3 or do I need to move to something better? I need to find all rows with a building location=X then within that list of locations return all the items that were purchased between 2015 and 2020

[true swift]

I guess move to something better is necessary. 10.000 rows aren't 'big data', but I'm not sure sqlite3 can manage them correctly

[lucid notch]

I think the hanlding is the issue. I"SELECT COUNT(state ,'AR') FROM Devices WHERE year BETWEEN '2015' AND '2020'" just returns 2698 where I know its actually around 1200 and when I change AR to TX, I get the same 2698

[queen rose]

Is recommended reading the DB thru pandas, and doing the filtering and analyzing with pandas

[queen rose]

.

[grim vault]

You're doing it wrong?
SELECT COUNT(*) FROM Devices WHERE state = 'AR' AND year BETWEEN 2015 AND 2020
or for all states, juts group by it:
SELECT state, COUNT(*) FROM Devices WHERE year BETWEEN 2015 AND 2020 GROUP BY state
and for 10,000 rows sqlite is fine.

[lucid notch]

@grim vault thank you

[finite mason]

if i have a table orders with attributes received of type date and shipped of type date, how can i add a constraint so that received < shipped always?

[grim vault]

Just add a check constraint?

create table ... received date, shipped date, check (received < shipped) ...

[finite mason]

so if i put the comma it becomes a general constraint and not a constraint associated to that attribute?

[grim vault]

Yes, that's a table constraint, but it shouldn't matter in that case it could be attached to either of the columns.

[finite mason]

if it attach to a column it says it cant reference attribute of the same table

[grim vault]

It works for sqlite, so I guess you're using another db.

[stray moss]

MySQL:
I'm making a discord clone!
I'm meeting a big problem
So in table channels there's position INT UNSIGNED NOT NULL field and a guildId INT UNSIGNED NOT NULL field
I want it position field to auto increment for each guild by default... Is there a way to do this?

[finite mason]

im using liveSQL on Oracle

[grim vault]

Yeah, looks like it doesn't allow other columns in a column constraint (mysql is the same). As long as it allows a table constraint you're good.

[finite mason]

when i do that it says this

oh

wow

[grim vault]

Sounds like one of the fk references a non existing table.

[wooden needle]

im learning mongo rn and thought id try and implement it in a bot im making but i cant seem to find out how to check for the existence of a document

does anyone know how i can do that

[wooden needle]

i also need to read certain values

[fringe gulch]

Does anyone know of a brain tumor dataset with 2k+ pictures per class?

[hasty heron]

So I made a sqlite3 database from excel sheet with a bunch of join tables and using it with a Tkinter GUI to display things. What's the best way to now delete or add things to the database as I have delete/add all the relationships contained with the DB?

[jade current]

Hi, I'm trying to make a schema that is used to map some fields to other.

Eg: from a third party api im doing some process which takes some time so i can run an api call and check the status of the a process
the processes can have states: (stopped, initiated, processing)

Im also calling some other third party api that is doing some work and it is also returning some process statuses eg: (in-progress, started, error, halt)

So my schema just stores these possibilities and i am able supposed to be able to map one status to another.

API 1    ||     API 2
stopped  ||   error, halt
processing || in-progress
initiated  ||  started

Is there any good way to store these statuses such that i can use this table to map more statuses in future?

[hasty heron]

@jade currentso you want to map the API 2 status to the API 1 status as the API 2 statuses could be unknown?

[jade current]

@hasty heron i am just trying to do some process when both api are in same states, i know i could just compare manually but i might add more APIs in future so i would rather just call the ORM or create some function to return a singular response
like if not all APIs are in stopped states, it will return some other state, etc

[jade current]

in other words, im trying to unify the process statuses across all APIs

[hasty heron]

@jade currentI suppose you could have you could consider started as 1, in progress as 2, and stopped as 3. Then you put all the statuses in some table with a lookup value of 1,2, or 3. Then a function where you input the status and it gives back a 1,2, or 3 to compare.

If you're getting the status as strings, they can be put in a dictionary even

[jade current]

yeah they are strings

I just resorted to using dictionaries but was wondering if there's a more elegant solution you know haha

[hasty heron]

you could just store that in a csv file that you can update

[jade current]

yeah i suppose

csv is still a better option, thanks

[hasty heron]

Depends on how many statuses you need to track of I suppose and if this program is running regularly.

[jade current]

hm, might limited to 4-5 apis max, so csv works

[solid flint]

Isn't that more like a 'tags' functionality?

The 'attributes table'?

[proven arrow]

Attributes and tags are the same thing. Just different words.

[cobalt bolt]

Anyone using SQLModel, Pydantic’s condecimal and Pyright? Pyright doesn’t like call expressions, i.e. condecimal being the type of a field, and although using a type alias works fine I’m wondering if anyone has another way? Working on a model for a crusty database with like 7 different MySQL decimal formats, and would prefer not to need 7 constrained decimal aliases (e.g. Decimal_10_2, Decimal_12_2, etc.)

[torn sphinx]

Is using json as database right thing?
Does that give problem in future

[river matrix]

Hey all.

What would be the best suitable answer to this question?

[keen minnow]

right thing depends a lot on the context.
Typically json implies schemaless, which implies there is a schema but it's just not speced out.

I would recommend to always start with a SQL db, unless there is a very specific need

[obsidian crater]

hey quick question, "how many records thatwould make indexing looks significantly boost the retrieval operation?"

[sand stream]

this is a really vague question

probably mentioned somewhere in the notes

but it's anyones guess

my understanding is an index costs on creation/insertion time + storage but will always speed up a query

when that occurs, who knows

it's like asking when f(1) will be significantly better than f(n), n >= 1

uhh always?

[grim vault]

Back in the days I was told around a 100-200 rows. But since than there were some updates to the hardware.

[obsidian crater]

but I think there is an exception like, when we have a small amount of records isnt it would slowdown the process because we have to look up the index first instead of the actual data file?

think like if you have 1min reading content, but you look the index for searching some sentence instead of skimming the actual content, isnt it would slow you down to find the sentence?

[proven arrow]

This is where the databases query planner kicks in. It will pick the best plan and handle these kind of optimisations for you.

For all queries you make the database will pick the best plan.

[torn sphinx]

dang, i need help with this

[torn sphinx]

this is all i got still looking:
Orders.Emoployees = Empolyees.First Name + Employees.Last Name
Orders.Ship ZIP = Shippers.ZIP = Customers.ZIP
Orders.Ship Country = Employees.Country = Customers.Country

[delicate field]

Hey @torn sphinx!

It looks like you tried to attach file type(s) that we do not allow (.pdf). We currently allow the following file types: .gif, .jpg, .jpeg, .mov, .mp4, .mpg, .png, .mp3, .wav, .ogg, .webm, .webp, .flac, .m4a, .csv, .json.

Feel free to ask in #community-meta if you think this is a mistake.

[sonic maple]

Hello guys, i've been using sqlite3 for a little idea. Is there any way to get a prettier looking output in the shell when doing requests to database?

[austere geyser]

Hello everyone i am currently building a bank application using Mysql.connector and Tkinter module.
i am expirienciencing a minor issue w.r.t to mysql connector and foreign key constraints.

query = f'INSERT INTO transactions VALUES({AUserAccNoEntry.get()},{BUserPhoneEntry.get()},{AmmountEntry.get()});'

This is my query and the .get() functions are from an entry. the key issue here is that connector seems to have certain issues with teh server and foreign key relations.

mysql.connector.errors.IntegrityError: 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bank`.`transactions``, CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`SenderAccNo`) REFERENCES 
`accounts` (`AccountNumber`))

i execute the query in the workbench
and there is no issue whatsoever
but in mysqlconnector there seems to be an error

[grim vault]

Apart from not using binding parameters, you sure you want the phone entry and not the account number for user B?

[solid flint]

Ah I see. I'm going to have tags for osmething else, I think I'll go with 3 tables, easier for me to understand this actually xD Thank you. I'll read on polymorphic relationship instead

[crystal robin]

Umm how to use outer join in mysql

[fading patrol]

I haven't used MySQL much but I guess you need to use UNION: https://stackoverflow.com/questions/2384298/why-does-mysql-report-a-syntax-error-on-full-outer-join

[main oriole]

How do I make it so that if the contents of server_id is already in the db, it doesn't insert it again

[pure sleet]

make it a unique column, it will raise an error if you try to insert duplicate data

[main oriole]

Ok

How do I do that?

[pure sleet]

what db are you using?

[main oriole]

https://deta.sh

Deta Base

[pure sleet]

wtf

[main oriole]

What

[pure sleet]

i thought you were using some sql database or something

[main oriole]

Lmao