#🔒 Filtering duplicates in database (ie youtube urls)

84 messages · Page 1 of 1 (latest)

viscid jay
void sapphireBOT
#

@viscid jay

Python help channel opened

Remember to:

  • Ask your Python question, not if you can ask or if there's an expert who can help.
  • Show a code sample as text (rather than a screenshot) and the error message, if you've got one.
  • Explain what you expect to happen and what actually happens.

:warning: Do not pip install anything that isn't related to your question, especially if asked to over DMs.

Closes after a period of inactivity, or when you send !close.
#

table.sql line 1

CREATE TABLE IF NOT EXISTS music (```
small flare
#

not really sure what the question is

viscid jay
#

the general url is the same

#

except the ?si=

#

bit

#

I am looking to regex it and remove it

#

then remove the url and re-add it without it

#

and then check for duplicates

wet rose
#

you can set a unique constraint on your column.

#

and with postgres, I think you can specify to "do nothing" when an "insert into" fails on a unique constraint

#

(otherwise it would just error with an exception that you could catch with try/except)

small flare
#

probably would rather just url.split('/')[-1] rather than regex as well. Pretty simple without it

wet rose
#

yea, that or urllib.parse.urlsplit to be extra precise

viscid jay
#

I already check for duplicates

#

the problem is this for example

#

see?

wet rose
#

is the "si=" parameter relevant? can't you store it without it?

viscid jay
#

hence having the si parameter

small flare
#

can you just strip it out before you save it

viscid jay
#

I wasn't sure how to handle the si thing

#

I should probably make an owner command to specifically take a json of my own messages

#

and filter it out

#

hold on let me get it again

wet rose
# 
>>> from urllib.parse import urlsplit, urlunsplit, parse_qs, urlencode
>>> 
>>> parts = urlsplit('https://youtu.be/0_gOgp1Rg2w?si=VyRe4O1wM0vyC55z')
>>> parts
SplitResult(scheme='https', netloc='youtu.be', path='/0_gOgp1Rg2w', query='si=VyRe4O1wM0vyC55z', fragment='')
>>> q = parse_qs(parts.query)
>>> q
{'si': ['VyRe4O1wM0vyC55z']}
>>> del q['si']
>>> newparts = parts._replace(query=urlencode(q))
>>> urlunsplit(newparts)
'https://youtu.be/0_gOgp1Rg2w'
#

here a rough overview of what is possible

viscid jay
#

i need to parse like playlists and funky things

#

and get the user id but path may help with that

wet rose
#

so you also store playlist ids? or do you extract the video id from playlists?

viscid jay
#

I think looking at my data would probaly be best

#

regular youtube urls

wet rose
#

in the end, if you want to store youtube videos, the only thing you really need is the video id... if you have a piece of code that can extract it from any youtube url (there are maybe only 5 different kind of urls or so - wild guess) .. then you just need to store the id.

viscid jay
#

I just wanted something where I could run a slash command

wet rose
viscid jay
#

simple

#

I hook it to a discord bot

#

and have it pick randomly

#

for me and others

#

since music can be hard to find

wet rose
#

randomly from what?

viscid jay
#

I have a command called find to do a search/lookup

wet rose
#

okay. it seems we're not talking about the same thing

viscid jay
void sapphireBOT
#

cogs/find.py line 115

url = random.choice(proper_urls)```
wet rose
#

that only works if the DB has "proper urls"

#

I'm still at the point of how to get them into the DB to begin with

viscid jay
#

owner has the commands to add one

wet rose
#

okay. then what exactly is your question anyway?

viscid jay
# 
import json
from io import BytesIO
import utils



file = _message.attachments[0]
urls = json.load(BytesIO(await file.read()))

url_size = len(urls)

for url in urls["messages"]:
  await _bot.db.execute("INSERT INTO to_watch VALUES ($1, $2, $3)", 168422909482762240, url, "YouTube")

urls = await _bot.db.fetch("SELECT * from to_watch")
yield urls

yield len(urls) == url_size

(bulk add)

#

with jsk

viscid jay
#

and filter out repeats now

wet rose
#

I recommend to normalize the URLs, strip out the parts that are irrelevant, and store only the important part. e.g. only the youtube video id.. and add a unique constraint to the column with the video id.

#

that might add a bit work now, but prevents duplicates in the future

#

or you could convert any youtube url into the youtu.be/id format or something like that. doesn't really matter. als long as all urls have the same format.

viscid jay
#

someone did suggest this regex:

((?:\w{11}$)|(?:(?<=v=)\w{11})|(?:list=[^&]+))
#

But would regex be the best method?

wet rose
#

no

#

the best method would be to use urllib.parse

viscid jay
#

alright I am going to try to jsk it really quick and get an output

viscid jay
wet rose
#

it's not always the path, sometimes it's in the query as quert string, and sometimes it's just part of the path. (e.g. the /embed/... url)

#

'/playlist'

viscid jay
#

hence the code you made earlier

wet rose
#

if it's a playlist, you have to check if the url actually contains a video id.. only a playlist url is probably useless, no?

viscid jay
#

Only playlist url is a problem

wet rose
#

you could probably extract the playlist id and use the youtube api to extract the video ids, and add all of them? assuming that's a desired feature

#

something like that

void sapphireBOT
#
Python help channel closed

This help channel has been closed and it's no longer possible to send messages here. If your question wasn't answered, feel free to create a new post in #1035199133436354600. To maximize your chances of getting a response, check out this guide on asking good questions.