databases

[pale crest]

Sorry, I don't know what you mean. Can you give an example of what the data looks like, and what you want to get from it?

[chilly creek]

the website stores pictures with indexes
you say domain/pics/[index]
and get to the picture

you can also GET domain/pics/[index].json - this contains a json with a lot of metadata
like author and tags

[pale crest]

By index you mean an id?

[chilly creek]

yeah

saying index because I iterate over the entries, sorry for confusion

[pale crest]

Ok, so you want to create a map of all images on the site keyed on tag?

[chilly creek]

basically

[pale crest]

What are you gonna use that for?

[chilly creek]

first intention is just research: trying out how I could make that work and what the limitations of these GB of data are

later it could be used for machine learning

or as a bot

"show me pictures of cats"

I know that if it is just about the pictures, websites like imagenet exist

[pale crest]

Right. Well, one way you could do it is to create an SQL database with a schema like (id, image_id, tag) and then create one row per image/tag pair (and push them in as you retrieve each page). Then you could query the database directly on tags, and get a list of image IDs.

like SELECT image_id FROM table WHERE tag == 'some_tag'

[chilly creek]

I guess I could also make tag a secoundary key

[runic pilot]

can one image have more than one tag?

[pale crest]

You could create an index on the tag

@runic pilot Presumably, yes.

[chilly creek]

I guess I will make it:
tab1: image_id(PK), url (1:1)
tab2: image_tag_id(PK,SK) image_id tag_id (n:m)
tab3: tag_id(PK) name

[runic pilot]

then you'd probably want a separate table where you pair up (tag, image_id) and have an index on both columns

[pale crest]

@runic pilot That's what I said?

[runic pilot]

oh sorry, wasn't reading thoroughly

great idea

[pale crest]

@chilly creek You only need one table for the tag -> images map.

[chilly creek]

I have much more than 3 keys (about 50 I think), so I will need to figure out a concept first (and what the keys even mean)

[pale crest]

Or well, I guess that's what you meant with tab2: image_tag_id(PK,SK) image_id tag_id (n:m)

[chilly creek]

@pale crest ik I could set PK = (image_id, tag_id) but I prefer surrogated keys

[pale crest]

No, agreed.

@chilly creek But yeah, if you have other metadata you want to store per image, you can put that in a separate table with the image id as PK

If you need other features besides the tag lookup, you might need other tables

[chilly creek]

author lookup for example

[pale crest]

Don't think you need a tag(PK) table

tag can just be an indexed column in the (image, tag) table.

Unless you need metadata for the tags.

[chilly creek]

I could do it with just tagname, but that would break 3rd NF
and I think 2nd as well?
been some time since I used SQL

[pale crest]

3rd NF?

[chilly creek]

or how does an indexed column works?

https://en.wikipedia.org/wiki/Third_normal_form

[pale crest]

It just speeds up queries on that column

But uh, well, alright

Maybe string indexes are inefficient

I don't recall

Maybe your approach is better

Author is one to many, so I don't think you need a separate table for that.

[chilly creek]

I think it is better to not have "hey_this_is_a_cat" as value for 400k values

[runic pilot]

it'll be fine for performance

[pale crest]

You can apparently create indexes on shorter string columns

So you probably don't need a tag table, actually

And yeah, databases are designed to handle way larger datasets than yours, so I wouldn't worry too much about it.

Unless you actually experience performance issues in practice, which I doubt.

[runic pilot]

as long as you don't return 400k results, it'll be okay, and even then, the db is not the bottleneck, just the network

[solemn ridge]

Only reason that could happen is due to incapable server power or bad programming

[chilly creek]

I have heard SELECT * is a good idea

well, I will have a select random from 400k results

[solemn ridge]

Is this SQL or NoSQL?

oh wait

mb

its sql

[chilly creek]

base data is json that I retrieve

[solemn ridge]

you can take a random row out of it

[pale crest]

They haven't picked a db yet

We just suggested them to use one

[chilly creek]

but sql sounds better than nosql

[pale crest]

Almost always the cae

case*

[solemn ridge]

SELECT FIRST 1 column from table ORDER BY RAND()

[chilly creek]

data doesnt have to be modified

once on server

[solemn ridge]

that picks one random line

[chilly creek]

and only 1 server, 1 user

[vocal hare]

sqlite must have a function where i can search a table, instead of for looping it right

[solemn ridge]

wdym?

@vocal hare of course it does

[vocal hare]

duh

[solemn ridge]

SELECT * FROM MYTABLE WHERE CONDITION=CONDITION_VALUE

[pale crest]

@vocal hare SQL queries are searches.

sqlite supports SQL queries, being an SQL database.

[vocal hare]

makes sense

ty

[somber hatch]

how can i check if an int is 0 in mongo

uwu = uwu.find({"guild id": f"{ctx.guild.id}", "uwu": int}) if uwu == 0: await print("no more uwus") i tried this lol

[torn sphinx]

@wicked fog I just tried out sqlalchemy and it looks like its exactly what i want 🙂 do you know how to create subdocuments tho?

@somber hatch is the int for your database being stored as a string?

becase if uwu = "0", it wont check as an int,

[somber hatch]

int32

[torn sphinx]

ok, also, im confused as to what uwu prints out because it looks like it might return an object. Like uwu is an object of guild id and uwu

[wicked fog]

@torn sphinx by that do you mean parent/child relationships between different tables (different types of objects)?

[torn sphinx]

@wicked fog i think that would work, what i mean is if i create a guild table i want to be able have a column thats a table of users

but the users for each gild would be differenet

[wicked fog]

@torn sphinx so, what you're describing is a many to many relationship

in sql, to handle many-to-many relationships, you need a third table that stores ID pairs

[somber hatch]

oh yeah true it returns <pymongo.cursor.Cursor object at 0x05AAF9E8>

[wicked fog]

one column foreign keyed to the IDs of table A, and another column keye dto the IDs of table B

for example, users subscribed to boards

each subscription would be stored as a row with a user ID and a board Id

lemme pull up my own example of that

[torn sphinx]

alright thanks @wicked fog

and @somber hatch so you would have to use if uwu["uwu"] == 0

[wicked fog]

@torn sphinx for my own project, this file has subscriptions (users subscribing to boards) and follows (users subscribing to other users)

https://github.com/ruqqus/ruqqus/blob/master/ruqqus/classes/subscriptions.py

done in sqlalchemy

[torn sphinx]

oof this is a lot differenent then what im used to haha, much easier in mognodb

[wicked fog]

never used mongo, but sql is a relational system so the different data types are all defined in terms of what other data types they connect to and how

@torn sphinx for one-to-many relationships (for example, users/posts, where one person has many posts, but each post only has one author)

you just have a foreign key in the "child" table to the parent table

no third table needed

@torn sphinx example from the same project: https://github.com/ruqqus/ruqqus/blob/master/ruqqus/classes/submission.py#L27

[torn sphinx]

ok thank you 🙂 And in mongodb(in nodejs havent done it in python) you make a scheme for your frist table and use it as a type subdoc with the colum as that

[somber hatch]

TypeError: index 'uwu' cannot be applied to Cursor instances

[wicked fog]

...

oh and @torn sphinx you probably figured this out, but for one-to-one relationships, just do it the same as a one to many. Pick one table and add a foreign key to the other.

[torn sphinx]

@somber hatch ooooh wait ok so you used find, which returns an array, so i think it has to be uwu[0]["uwu"]

@wicked fog let me type something up and then ill post it here so you can tell me if itll work

[wicked fog]

k

[somber hatch]

Thanks it worked ❤️

[torn sphinx]

np

@wicked fog With this (sorry for the screenshot im using vim and cant copy) it should link a new user for every guild right?

[wicked fog]

@torn sphinx so as of now, User.guild is an integer

I would suggest naming that column guild_id

Then

guild=relationship("Guild")

A property which makes sqlalchemy actually get the target object

[torn sphinx]

ok, like this?

[wicked fog]

Yep

[torn sphinx]

cool, how do i insert into this now haha

[quick acorn]

hello guys, whats a database?

im new hahaha

wondering if my question fits here

[rain wagon]

A one to one relationship is 99% of the time a design issue

If you have that, merge the value into one of the tables

[wicked fog]

true but I was going for completeniess

so

inserts

[torn sphinx]

this is a discord bot so like it doesnt have to over the top

[rain wagon]

@quick acorn A database is a system that handles data, plainly said

It mostly uses SQL to query data

SQL is NOT a programming language, it is a query language

Standard Query Language

[quick acorn]

😦

i want to cry lol

[rain wagon]

There are systems that are server based and systems that are not server based, such as Sqlite

Microsoft Access is also a serverless database system

[wicked fog]

@torn sphinx near the beginning of your code when you are setting up your engine:

session=sessionmaker(bind=your_engine)()

[rain wagon]

and uses SQL internally

or DBase from Open/Libre Office

[wicked fog]

you will use that session throughout your code to access the db

to make a new object

[quick acorn]

so this is for online? @rain wagon ?

[wicked fog]

and to query th edb

[quick acorn]

online stuffs

[rain wagon]

@quick acorn Online, Desktops, Apps, everyone is using databases

[somber hatch]

how can i make custom _id thing, so like i can get the last _id and +1 for the next one

[rain wagon]

Browsers use it to store cookies or undo/redo stacks

[quick acorn]

hmm if i'm trying to make a tree does that count

[wicked fog]

my_new_object = User(propery="value", property2="value2"...)
session.add(my_new_object)
session.commit()

@torn sphinx ^

[rain wagon]

@quick acorn Tree?

[quick acorn]

yeah a decision tree

[wicked fog]

nest if statements

/s

[rain wagon]

You can store the data needed in a db yeah, but not the whole tree

[torn sphinx]

alright @wicked fog

[quick acorn]

hmmm what if i have an excel

and i have used pd.read.csv in it

[rain wagon]

Have a look at pandas to work with an excel file

[wicked fog]

@torn sphinx example from the same project, this is new post creation https://github.com/ruqqus/ruqqus/blob/master/ruqqus/routes/posts.py#L328-L346

[quick acorn]

yeah i just want to know how i can store the data to a decision tree

[rain wagon]

Any way you want, you can also store it as yaml or json

It all depends on the data and which format is the best fit for it

[wicked fog]

except I named my session "db"

[quick acorn]

do you have any videos i can watch man?

i have a nonsensical assignment from my prof --

[rain wagon]

Is it graded?

[quick acorn]

yeah

fck cs

[rain wagon]

yeah, then we can't help you

[quick acorn]

ahhhh

okay then

[rain wagon]

!rule 5

[delicate field]

Rules

5. Do not provide or request help on projects that may break laws, breach terms of services, be considered malicious/inappropriate or be for graded coursework/exams.

[quick acorn]

thanks man

didn't see that

[rain wagon]

But just take a look at different data formats, then look at your data and make a decision

[quick acorn]

will do bro thanks!

[torn sphinx]

@wicked fog im getting an error saying sessionmaker is not defined

[wicked fog]

@torn sphinx from sqlalchemy.orm import sessionmaker

[torn sphinx]

yeah i just saw that in your code haha

[wicked fog]

👍

@torn sphinx you'll also have to do from sqlalchemy.orm import relationship for relationships

[torn sphinx]

thanks

[wicked fog]

@torn sphinx the whole site is built on flask and sqlalchemy so feel free to poke around and if you have questions about how shit works feel free to ask

[torn sphinx]

alright i will, i dont use flask but i will probably soon, i need it for another project

[wicked fog]

flask is stupid easy to learn

very extensible

and super customizable

if you can already code and you're doing database stuff flask should be a piece of cake

[torn sphinx]

i'm new to python but have an extensive background in nodejs so i used expressjs

[wicked fog]

@app.route("/url/endpoint/string")
def function_name():
    # do stuff
    return render_template("html_template.html", **kwargs)

like

that's it

and then where "do stuff" is, you add in stuff like loading info from database, etc

[somber hatch]

how can i check if a string is already in a database, like if i was storing guild ids how could i check if the guild id is already there

[torn sphinx]

oof, yeah its not that easy with express its something like:

  router.get('/route/', (req, res, next) => {
  res.render('templete engine' ,'file')

})

[wicked fog]

@somber hatch example https://github.com/ruqqus/ruqqus/blob/master/ruqqus/helpers/get.py#L200-L210

[upbeat lily]

@quick acorn If you're just trying to make a decision tree. Then what you need to know is Pandas and SKLearn. I'd suggest looking around for tutorials on those. Some more info about exactly what you're trying to do would let us point you in the right direction a bit more effectively. Also, assuming this is oriented around decision trees, #data-science-and-ml is a better fit - and you can @ me there

[wicked fog]

x=session.query(ObjectClass).filter_by(property=value).first()
if x:
    print('it exists')
else:
    print('nothing found')

@somber hatch

wait

hang on

i'm assumign we're still talking sqlalchemy

@somber hatch if you're not talking sqlalchemy then ignore that

[torn sphinx]

for mongodb you just try to find the value and check if it exists

[somber hatch]

elif attack["guild id"] == str(ctx.guild.id): yeah i tried something like that but then i relsied that looks dumb why would it work

[torn sphinx]

so like ```
guild = db.find({'guild.id': guild_id})
if len(guild) > 0:
print("Exists")
else:
print("does not exist")

so what that does is tries to find all docuemnts with that id

and if the length of that array is greater then 0 then it means its already in the database

idk what i'm doing wrong XD I have a command $addfc that lets users add their friend code to a database. I printed out the code that I'm updating just before updating it in the database and it printed normally. But when I reload the database, it has some random looking negative number..
my code:

print(friend_code)
db = sqlite3.connect('userinfo.sqlite')
cursor = db.cursor()
cursor.execute(
    f'UPDATE userinfo SET fc = {friend_code} WHERE id =     
    {ctx.author.id}')
await ctx.message.add_reaction('👍')
db.commit()

This is what it prints:

and this is what shows up in the database:

[somber hatch]

    if len(guild) > 0:
TypeError: object of type 'Cursor' has no len()

[torn sphinx]

wait I figure out the number thing lol

figured*

[somber hatch]

im go eatt if anyone wana tell me smthing please at me

[torn sphinx]

Is there any way to put this in as a string without python/the database actually evaluating it as an equation?
3423-1267-2302

@somber hatch you can try if guild[0]: that will check if the first item in the array exists

or maybe just if guild:

[rain wagon]

if guild: tests if there are any results

or, if using Sqlalchemy, if guild.first():

[somber hatch]

mongodb, and it just alwasy returns true, even if there is absaultely nothing to look for

    @commands.command(name='find')
    async def find(self, ctx):
        guild_id = str(ctx.guild.id)
        guild = attack.find({'guild.id': guild_id})
        if guild:
            await ctx.send("Yeet")
        else:
            await ctx.send("but uuuwu and dostn woaksd")

it just says yeet xd

[somber hatch]

fixed it i just had to find_one

[vocal hare]

say i have a list of numbers (123,124,125,126) etc, and they were in 1 column. I have a number i want to find in it, such as 123. whats would i do to see if 123 specifically exists in that column? (sqlite 3)

[torn sphinx]

I have this code written:

db = sqlite3.connect('userinfo.sqlite')
cursor = db.cursor()
cursor.execute('''
              INSERT into serverinfo
              (Items, Prices, Descriptions, Listings) VALUES (?,?,?,?)''',
(item.content, desc.content, newprice, add_listing))
done = discord.Embed(
      title='💰 For sale!',
      description=f'{item.content} is now for sale for {newprice} bells!',
      color=discord.Color.green()
)
await ctx.channel.send(embed=done)```
And the embed sends, but when I reload my sqlite database, nothing is there.

[vocal hare]

do you regularly commit() @torn sphinx ?

[torn sphinx]

yeah

[vocal hare]

hm

[torn sphinx]

oh XD good point

[vocal hare]

database is empty?

[torn sphinx]

that's probably it. I just forgot to commit

yup lol ty

[thorn nymph]

Hey guys, i am making databases and i have 3 tables, i was wondering how i can protect db from exploiting data

[celest blaze]

!sqlinjection

nuts

@thorn nymph google "SQL injection"

[thorn nymph]

I know that but someone said in here that escaping string is not real way to protect db so i was wondering if someones know how to do it really @celest blaze

[torn sphinx]

So I am trying to make a column in postgresql a primary key, is there a way I can alter that column?

I am using python btw.

Ping me if you reply please

[reef hawk]

Does anyone know which operator type I should put for integers? I've been trying to find int4

[burnt prism]

does anyone have time to help me on sql access trying to pull from two tables and its not letting me when im also using a count() for one of the tables

need to make my table on the left look like the right

But im not quite sure how to add the names and last name from other table

Do i need to user inner join for the stu_nums?

this what the student table looks like

and enroll

[toxic rune]

I have this small fiddle, I'm having trouble organizing it: https://www.db-fiddle.com/f/7LnFCcSs6w544vY9VGcUEi/0

How can I map a character to an upgrade id? Basically each character can have one upgrade and each upgrade has multiple levels or upgrades (basically different rows.) But the upgrade tree can be used by multiple characters.

[rain wagon]

@toxic rune https://www.db-fiddle.com/f/7LnFCcSs6w544vY9VGcUEi/1

You need a n:m resolution table

[severe cobalt]

for anyone who does at-home development with large-ish databases, would it be worthwhile to buy a custom, proliant, or poweredge server to alleviate processor and memory usage on a workstation?

[toxic rune]

@rain wagon Ah I see, wanted to make sure thanks!

[toxic rune]

Oh now that I just checked, I'm not sure how that'd work since I don't have a primary key on that table. (Tree) Since you have:

(id -> upgrade_id)
Tree 1 Upgrade 1
Tree 1 Upgrade 2
Tree 1 Upgrade 3
Tree 1 Upgrade 4

Tree 2 Upgrade 1
... etc

Since id is repeated I can't have a primary key on it, and upgrade_id is out of the question since it's the upgrade level.

[vague haven]

how can i check the size of my mongo db?

[peak willow]

Why not defined (django)?

[trail blade]

anyone have experience using Pandas Databases?

I've got a problem that I feel is easily fixed, but the function im doing seems really niche, and i cant find examples anywhere online that help me out

nvmd, got it fixed lol

[clever topaz]

@peak willow You cannot use and between query options. What you want is .filter(engine__type="vee", engine__volume_lte=300) (where lte stands for less than or equal to) - https://docs.djangoproject.com/en/3.0/ref/models/querysets/#lte

[peak willow]

Thanks

[somber hatch]

in mongo what array can i use to add somethin colpetly new like {"thing" : "thing"}, {"$thingToAddABoolenAndNotDeleteAnythingElse": {"somethingNew" : True}}

bc like $set delete all the others

is that even possible xd

[thorn nymph]

guys i have 3 tables, how can i check if something is already in database

like if email is already in db

in flask

[runic pilot]

Select * from users where email={email}

[dusty helm]

guys, how to make this query get descending data?

tweets = Tweet.query.all()

it's already getting all the data, but ascending

[thorn nymph]

can it be done like this

or how to fix it

if passwords == submitpw and Users.query.filter_by(email).all() == []:

TypeError: filter_by() takes 1 positional argument but 2 were given

@runic pilot

[runic pilot]

...filter_by(email=email).first() is None

it's mostly the JS side of me, but I don't like comparing []

[thorn nymph]

Ooh okay will try it

[dusty helm]

@runic pilot could you help me with descending query? :x

tweets = Tweet.query.all()

[runic pilot]

is that sqlalchemy?

[dusty helm]

i've tried tweets = Tweet.query.filter_by(id='dec').all()

yes

but no sucess

[runic pilot]

Tweet.query.order_by(Tweet.id.desc()).all()

[dusty helm]

oh

i'll try

one sec

voila

it worked

thank you!! 😄

[runic pilot]

happy coding!

[dusty helm]

😄 same!

[somber hatch]

uinfo.update_one({"User id": f"{ctx.author.id}"}, {"Weapons": {"$inc": {"Stone Sword": +1}}})

so like i have this, but i have no idea how to do it i get this error
ValueError: update only works with $ operators

ive put it in every way i could im still getting an error and its annoying

my thing looks like this

[runic pilot]

I think the update has to start with the operator

{"$inc": { "Weapons.Stone Sword": 1}}

[somber hatch]

pymongo.errors.WriteError: Cannot increment with non-numeric argument: {Weapons: { Stone Sword: 1 }}

uinfo.update_one({"User id": f"{ctx.author.id}"}, {"$inc": {"Weapons": {"Pink Sword": + 1}}})

first is the error

[runic pilot]

"Weapons.Stone Sword"

not "Weapons": {"Stone Sword" ...

this is what I'm reading https://docs.mongodb.com/manual/reference/operator/update/inc/#example

[ripe helm]

can someone tell me why this isn't working?

for poll in pollinfo:
            if poll['name'] == name:
                message = await pollchannel.fetch_message(poll['id'])
                conn = sqlite3.connect('hierarchy.db')
                c = conn.cursor()
                c.execute(f"DELETE FROM polls WHERE name = '{poll['id']}'")
                conn.commit()
                conn.close()
                break```
it just continues without anything happenning
it doesn't delete the row from the db 
and continues on
i've already ran print tests to see if it gets called, in which im 100% sure it does

[somber hatch]

isnted of the 0 1 can i use true false, can i change something to true without delleting everything else, i could find it

[nocturne blade]

import sys
import ibm_db
import pandas


#Replace the placeholder values with your actual Db2 hostname, username, and password:
dsn_hostname = "dast"
# e.g.: "dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net"
dsn_uid = ""        # e.g. "abc12345"
dsn_pwd = "^"      # e.g. "7dBZ3wWt9XN6$o0J"

dsn_driver = "{IBM DB2 ODBC DRIVER}"
dsn_database = "BLUDB"            # e.g. "BLUDB"
dsn_port = "50000"                # e.g. "50000" 
dsn_protocol = "TCPIP"            # i.e. "TCPIP"
connOption = {ibm_db.SQL_ATTR_AUTOCOMMIT: ibm_db.SQL_AUTOCOMMIT_ON}

#DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter
#Create the dsn connection string
dsn = (
    "DRIVER={0};"
    "DATABASE={1};"
    "HOSTNAME={2};"
    "PORT={3};"
    "PROTOCOL={4};"
    "UID={5};"
    "PWD={6};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port, dsn_protocol, dsn_uid, dsn_pwd)

#print the connection string to check correct values are specified
try:
    ibm_db2.connect("dsn;", "", "")
    print("CONNECTED")
except:
    print("NOT CONNECTED")

I can't get it to connect to my IBM Database

I moved it to help chromium

[brisk crest]

I dont get something in sqlalchemy Table object

In the following.c.followers,
What is c?

[safe pagoda]

Is CSV still used?

[runic pilot]

as a database?

[torn sphinx]

hi

my brain stopped working

what am I doing wrong

(SELECT GeoFIPS, GeoName, Total_employment, Percapita_personal_income FROM 
`bigquery-public-data.sdoh_bea_cainc30.fips` WHERE year = '2018-01-01')
JOIN (
# census bureau data 2018 (latest)
SELECT geo_id, pop_16_over, civilian_labor_force, nonfamily_households, family_households, median_age, median_income FROM `bigquery-public-data.census_bureau_acs.county_2018_1yr`
)
ON GeoFIPS = geo_id

it says error near JOIN

got it

learned something new

SELECT
*
FROM
(SELECT GeoFIPS, GeoName, Total_employment, Percapita_personal_income FROM 
`bigquery-public-data.sdoh_bea_cainc30.fips` WHERE Year = '2018-01-01') fips
JOIN 
(SELECT geo_id, pop_16_over, civilian_labor_force, nonfamily_households, family_households, median_age, median_income 
FROM `bigquery-public-data.census_bureau_acs.county_2018_1yr`) census
ON GeoFIPS=geo_id

[toxic rune]

@rain wagon Sorry for the second ping but I tried to come up with a second alternative (because of what I mentioned earlier)

https://www.db-fiddle.com/f/aSegUoD9kjiy1zJRzDzeEb/0

But the issue is having a table just with an ID field seems a really bad design in itself.

[strong kiln]

Steam's IsPlayingSharedGame web api isn't working anymore and I'm trying to look for people who are evading bans, so I wrote a log analyzer that has two tables, user and events with sqlalchemy.

User is account id the game assigned them and a one to many relationship to events.
Event has "state (connect/disconnect), ip address, datetime, and acctid (foreignkey to user.account)".

Is there a way using sql or sqlalchemy I can easily just find duplicate users with the same ip address?

Models: https://pastebin.com/DFwv9dfm

[torn sphinx]

log analyzer eh

that sounds fun, do you need help

[runic pilot]

If you’re using postgres you can use the iaddr type and then index that then query based on it efficiently

[torn sphinx]

hi

can you help me with my query

I do a join over two selects, but end up with duplicate columns for the thing I'm doing a join on

SELECT
*
FROM
(SELECT GeoFIPS, GeoName, Total_employment, Percapita_personal_income FROM 
`bigquery-public-data.sdoh_bea_cainc30.fips` WHERE Year = '2018-01-01') fips
JOIN 
(SELECT geo_id, pop_16_over, civilian_labor_force, nonfamily_households, family_households, median_age, median_income 
FROM `bigquery-public-data.census_bureau_acs.county_2018_1yr`) census
ON GeoFIPS=geo_id
JOIN `mit-covid-19-datathon.TeamB02TrackE.dex_county_level`
ON CAST (GeoFIPS as NUMERIC)=county

here you see, GeoFIPS, geo_id and county are all the same thing.. and I just want to keep one of those

but I'm not sure how

[runic pilot]

If you’re curious about the ip column stuff I wrote about it here https://link.medium.com/UBwOmBvbt6 if I wasn’t on my phone I’d type out the full example but it’s towards the bottom of that article

[torn sphinx]

how do I use multiple selects to create additional columns

select (select max(date) as dex_max, min(date) as dex_min from `tableid1`) as table1,
(select max(date) as mobility_max, min(date) as mobility_min from `bqtableid2`) as table2

this doesn't seem to work

basically, I want these columns next to each other

[ripe helm]

I've had this anoyying bug for so long where my sqlite3 database is closed at a specific line. Is there any good way to track down where else it's been open?

[sour bough]

what is the best database software or system?

[gray dune]

for i in cursor.execute("SELECT gol FROM settings WHERE id = {}".format(ctx.guild.id)):

No operator matches the given name and argument types. You might need to add explicit type casts.

[torn sphinx]

Don’t use .format()

That leads to SQL injection

[gray dune]

How can i fix it?
Without .format, is not working, the same mistake...

for i in cursor.execute(f"SELECT gol FROM settings WHERE id = {ctx.guild.id}"

[rich trout]

You should be using parameterization

which looks like this:

cursor.execute('SELECT "gol" FROM "settings" WHERE "id" = ?', (ctx.guild.id,))

[gray dune]

@rich trout
SyntaxError: syntax error at end of input

for i in cursor.execute(f'SELECT "gol" FROM "settings" WHERE "id" = ?', (ctx.guild.id,)):

[rich trout]

no f

[gray dune]

I removed f but nothing changed..

[rich trout]

hm.

try dropping all the double quotes

[thorn nymph]

@runic pilot hey u remember the way u told me to solve my problem with checking if email is already in database, what is first email and what is 2nd email??

oh wait nvm

[gray dune]

How can I take only a number from the database????

[dapper bison]

is there any other\simpler way to to this? without redis etc https://stackoverflow.com/questions/49873545/reset-increment-value-in-flask-sqlalchemy

[rich trout]

The issue is that sqlite (for example) keeps the gaps because otherwise you'd have shift in your data

what happens if you have six rows and delete row #3?

should row #4 change?

In addition, while sqlite doesn't support simultanous inserts (only one at a time) many other databases do. As a result, they need to cooordinate who gets what ID, and freedom of the id's not being perfectly sequential makes this substantially easier

If you do, in fact, want perfectly sequential id's then perhaps you should have a column to manage that in your own code, which would allow you to keep them up to date etc

[dapper bison]

i use postgresql

[runic pilot]

why do you want to reset the increment value?

[rain wagon]

Micromanaging something trivial as id's in a database is futile and just hampers performance

[rich trout]

Postgres has a Serial column type for when you don’t want gaps. But it doesn’t solve the delete problem

[rain wagon]

a deleted id is gone, no matter the dbms

[grave carbon]

How do I store multiple json dicts into a txt file

when I do ```py
with open('db.txt', 'w') as out:
for obj in portfolioStocks:
json.dump(portfolioStocks[obj], out)

with open('db.txt', 'r') as read:
data = json.load(read)
for obj in data:
print(data[obj])```

it fails because there is no seperators between the dicts

[rain wagon]

open it in append mode

[grave carbon]

ValueError: must have exactly one of create/read/write/append mode

[rain wagon]

It's a+ my bad

😄

[grave carbon]

json.decoder.JSONDecodeError: Extra data: line 1 column 632 (char 631)

[rain wagon]

You will need to implement loading each chunk yourself

If you want all in one file

Or just use multiple files

[grave carbon]

mmm

Can I encase my multiple dicts in another dict

[rain wagon]

sure

[grave carbon]

I.e. instead of dict1 = {} dict2 = {} etc

[rain wagon]

https://stackoverflow.com/questions/21560433/how-to-write-a-nested-dictionary-to-json

[grave carbon]

have myDicts = { dict1: {}, dict2: {} } ?

[rain wagon]

If it is not data, but a config, also have a look at yaml

it's a really nice format that is easy to write and read

[grave carbon]

Its data

😉

[sonic mural]

When using SQLALchemy ORM
Is there a way of querying a table when you don't have access to the model class?

Without using .execute

[runic pilot]

you can declare the table and query it without using a declarative base model

[sonic mural]

Sorry, I've been working on this for a while and my brain has turned to mush. Could you show me the syntax to do that please

[runic pilot]

here's an example I did a while back:

import sqlalchemy
from sqlalchemy.sql import select
from sqlalchemy.dialects.postgresql import BIGINT, CIDR, REAL

# Configuration from environment variables or '.env' file.
config = Config('.env')
DATABASE_URL = config('DATABASE_URL')


# Database table definitions.
metadata = sqlalchemy.MetaData()

geo_id_to_name = sqlalchemy.Table(
    "geo_id_to_name", # tablename
    metadata,
    sqlalchemy.Column("geoname_id", BIGINT),
    sqlalchemy.Column("locale_code", sqlalchemy.String),
    sqlalchemy.Column("continent_code", sqlalchemy.String),
    sqlalchemy.Column("continent_name", sqlalchemy.String),
    sqlalchemy.Column("country_iso_code", sqlalchemy.String),
    sqlalchemy.Column("country_name", sqlalchemy.String),
    sqlalchemy.Column("subdivision_1_iso_code", sqlalchemy.String),
    sqlalchemy.Column("subdivision_1_name", sqlalchemy.String),
    sqlalchemy.Column("subdivision_2_iso_code", sqlalchemy.String),
    sqlalchemy.Column("subdivision_2_name", sqlalchemy.String),
    sqlalchemy.Column("city_name", sqlalchemy.String),
    sqlalchemy.Column("metro_code", sqlalchemy.String),
    sqlalchemy.Column("time_zone", sqlalchemy.String),
    sqlalchemy.Column("is_in_european_union", sqlalchemy.Boolean),
)

[noble oak]

Where's the best place to learn PostgreSQL database

[runic pilot]

learning what about it? SQL syntax? postgresql specific features? advanced sql techniques?

[noble oak]

Just in general

Syntax probably

[sonic mural]

I see, then can you query that as if it were a normal model?

[noble oak]

Like adding stuff to it and calling for stuff

[runic pilot]

I don't have a reference for that, but here's a result from a quick google search https://www.khanacademy.org/computing/computer-programming/sql

here's more code from that same example:

import databases # pypi package
database = databases.Database(DATABASE_URL)
database.connect()
# get the requesting ip address in the "requested_ip" variable
where_clause = ip_to_geo_id.c.network.op('>>=')(requested_ip)
joined = ip_to_geo_id.join(geo_id_to_name, geo_id_to_name.c.geoname_id == ip_to_geo_id.c.geoname_id)
query = select([ip_to_geo_id, geo_id_to_name]).select_from(joined).where(where_clause)
result = database.fetch_one(query)

this also references the ip_to_geo_id table that I didn't show in the example yet

[noble oak]

Is it better to have it's own seperate file?

If I'm making a discord bot

[runic pilot]

it's always a good idea to separate logic into different files when possible

here, just open sourced the full example if you want a reference https://github.com/Rdbaker/IPLookup/blob/master/app.py

[sonic mural]

Hit me with

TypeError: Additional arguments should be named <dialectname>_<argument>

Regarding the Columns

oh im an idiot

nvm

can't do column_name=Column(...)

New problem during query
AttributeError: 'Table' object has no attribute 'guild_id'

[runic pilot]

what's the full stacktrace and code?

[sonic mural]

def _getguild(self, guild):
    if query := self.session.query(self.config_table).filter(self.config_table.guild_id == guild.id).first():
        pass
    else:
        query = self._addguild(guild.id)
    return query

Traceback (most recent call last):
  File "C:\Users\Work\Projects\EmpireBot\venv\lib\site-packages\discord.py-1.3.3-py3.8.egg\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\Work\Projects\EmpireBot\EmpireBot\extensions\logger\__init__.py", line 63, in on_raw_message_delete
    log_channel = await self.bot.fetch_channel(self._get_log_channel(payload.guild_id))
  File "C:\Users\Work\Projects\EmpireBot\EmpireBot\extensions\logger\__init__.py", line 44, in _get_log_channel
    guild = self._getguild(guild)
  File "C:\Users\Work\Projects\EmpireBot\EmpireBot\extensions\logger\__init__.py", line 37, in _getguild
    if query := self.session.query(self.config_table).filter(self.config_table.guild_id == guild.id).first():
AttributeError: 'Table' object has no attribute 'guild_id'

Try not to judge my terrible ORM use

[runic pilot]

can you show me the config_table declaration?

[sonic mural]

GuildConfig = Table(
    "guild",
    metadata,
    Column("guild_id", BigInteger, primary_key=True),
    Column("log_channel", BigInteger),
    Column("prefix", String(2)),
)

[runic pilot]

config_table.c.guild_id you need the c there to tell sqlalchemy that it's a column reference

[sonic mural]

Perfect, fixed it all dude

Can't thank you enough. I'd have never figured any of this out

[runic pilot]

no problem! all this is in the documentation but IIRC it's kinda hard to find because SQLAlchemy recommends using the declarative style so much

[sonic mural]

Some times I think ORM causes more problems that it solves

[runic pilot]

it just pushes the problems to a different place

but I'd rather deal with ORM problems than the security & query problems that it solves for sure

[sonic mural]

true

Well, swings and roundabouts I guess

Thanks again for the help

[ripe helm]

People keep telling me not to use f strings with sqlite3

cuz injection attacks whatever

then what would i use instead?

[rain wagon]

bound statements

Here is why f-strings are bad:

SELECT value1 from table WHERE id=1;
``` This is a simple SQL statement, right?

So far so good

[torn sphinx]

i need russian channel

[rain wagon]

Now let's have a look at an f-string: ```py
db.execute("SELECT value1 from table WHERE id={id}")

```sql
; DROP TABLE users; --```

It will end up like this:

SELECT value1 from table WHERE id=; DROP TABLE users; --

Now, this will generate an error and if the db is not set to stop when an error occurs, it will execute the statement

and drop the table

Losing some data is just the acceptable part

what if I query the meta/master tables and look for actual data, such as credit cards etc?

That is why such sequences have to be escaped

and bound statements ensure those inputs are passed as strings and cannot be executed

nb.: The -- ensures that following/nested sql statements are commented out and ignored

e.g. sql SELECT value1 from table WHERE id=; DROP TABLE users; -- ORDER BY id ASC;

as you can see by the highlighting, it's a grey comment now

But I guess he didn't stay even that minute it took me to respond and I have explained all that for nothing

some ppl..

[hollow field]

I have a question regarding reading dates from an Excel File using python, how can I get it into a date format. Like I'm currently getting it as what im assuming is a timestamp, however it has decimals in it, which im not sure why.

[rain wagon]

datetime.datetime.strptime

[hollow field]

okay so say I'm getting this, is this a valid number for a timestamp?
43783.598125

[rain wagon]

Oh that's microsoft's brand of date

[hollow field]

ohh

how would i read that, since im getting it from an excel file

[rain wagon]

That is elapsed days since Jan 1, 1900

use datetime.timespan

[hollow field]

okay ill try that, thanks

also quick question

[rain wagon]

the floats are hours, minutes and seconds

[hollow field]

is the datetime in what you sent

a variable name

or calling the library

[rain wagon]

it's a function

[hollow field]

ohh ok

[rain wagon]

import datetime or from datetime import timespan

[hollow field]

so if i call the value date, i just have to do date.datetime.timespan?

[rain wagon]

It's a bit more complicated than that

[hollow field]

oof

[rain wagon]

https://stackoverflow.com/questions/31359150/convert-date-from-excel-in-number-format-to-date-format-python

Apparently there is another way using fromordinal

Because using Epoch was too easy for microsoft

gotta have your own format

[hollow field]

okay thanks!

[torn sphinx]

anybody thing it is cool that all our conversations about sql are contained in a cassandra database?

[hollow field]

So for the most part it works, it's just that it gives 2020-05-06 00:28:19, when the original date is 2020-05-06 11:19:46 AM

[rain wagon]

timezone differences

as what timezone was the data saved and what timezone has your PC?

The value supplied by Microsoft is an offset

so you need to apply the timezone offset as well

excel sheets are a horrible way to save any data that is supposed to be processed

or anything from Microsoft Office in general

[hollow field]

ohh okay, thanks

[ripe helm]

@rain wagon sorry, lmao i read all of it

thanks

[stable glen]

yo any of yall know how to do the aiomysql?

[pliant spire]

Hey guys, i'm trying to pull json data from a sqlite table

SELECT json_extract(api_data,'$.items') FROM Lil_Wayne_Songs WHERE page_number=1

Is what i'm running now
I get a list of the items in 'items'
But i'm trying to iterate through each item in that list and grab the data in it
I can't seem to do that tho

SELECT json_extract(api_data,'$.items[0].name') FROM Lil_Wayne_Songs WHERE page_number=1

This works
but it doesn't work for

'$.items.name'

Just returns null
Anyone know how I can run through the whole list?

[near moon]

Not really sure what's wrong with the statement after SELECT, but I would just recommend you to not SELECT conditionally

looking that page_number column is a integer value it would work faster if you would ORDER BY a SELECTed set by page_number

and add then add subquery with condition

it will make it faster as of the fact SQL goes from the top to the bottom so it will check conditionally just n records where n is count of those with page_numer = 1

instead of checking whole table

[pliant spire]

I'm just adding the page_number for my testing, in production I'll be selecting the entire column, but thank you for the tip 👍

Still don't know how to iterate through the inner json 😰

[stable glen]

yo im trying to do a thing,

    author = ctx.author.id
    async with conn.cursor() as cur:
        await cur.execute("SELECT * FROM discordsrvx_accounts WHERE discord = ?", (author,))
        print(uuid)
    conn.close()

(Im using discord.py here as well),
but am getting this error:

  File "bot.py", line 77, in bf
    await cur.execute("SELECT * FROM discordsrvx_accounts WHERE discord = ?", (author,))
  File "C:\Python38\lib\site-packages\aiomysql\cursors.py", line 237, in execute
    query = query % self._escape_args(args, conn)
TypeError: not all arguments converted during string formatting

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Python38\lib\site-packages\discord\ext\commands\bot.py", line 892, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Python38\lib\site-packages\discord\ext\commands\core.py", line 797, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Python38\lib\site-packages\discord\ext\commands\core.py", line 92, in wrapped
    raise CommandInvokeError(exc) from exc

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: not all arguments converted during string formatting

what could be the cause? Cause im a bit confused :/

my full thing:

@client.command()
async def bf(ctx):
    conn = await aiomysql.connect(host='myhost', port=3306,
                                  user='myuser', password='mypassword',
                                  db='s72_statz',)
    author = ctx.author.id
    async with conn.cursor() as cur:
        await cur.execute("SELECT * FROM discordsrvx_accounts WHERE discord = ?", (author,))
        print(uuid)
    conn.close()
    print("NO")

[stable glen]

help please T-T

[clever topaz]

@stable glen It seems that you have to use %s instead of ? with MySQL:

await cur.execute("SELECT * FROM discordsrvx_accounts WHERE discord = %s", (author,))

[stable glen]

Ohh but why tho? (For future reference)

[stray thorn]

Is it okay if i have a strictly sparql question in here?

[rain wagon]

Hey guys, i'm trying to pull json data from a sqlite table

@pliant spire I hate very letter in that sentence

[hollow field]

How do I connect to a database from MySQL workbench, there's a sandbox db at a certain hostname and im given this "root/...". How would I connect to that database

I'm still new to SQL, so I'm not so familiar with it

like the end goal is I want to make a python script that inserts values into the database

[tawdry pelican]

If you want to use python, I would suggest using sqlalchemy with declarative base

[ripe helm]

am i doing this correctly?

c.execute('SELECT ? FROM ? WHERE ? = ?', (value, table, where, what))```

its not really working

its not giving any errors

[clever topaz]

@ripe helm You cannot have a ? for the table name

[noble oak]

Anywhere to learn postgresql?

[ripe helm]

oh ok

still doesnt work

:I

c.execute(f'SELECT ? FROM {table} WHERE ? = ?', (value, where, what))```

@clever topaz

[clever topaz]

No.

Can you try the full statement first and see if that works?

[ripe helm]

wdym

what full statement

[clever topaz]

c.execute(f'SELECT {value} FROM {table} WHERE {where} = {what}')

[ripe helm]

yeah that works

thats what ive been doing

[clever topaz]

MySQL?

[pliant pendant]

that looks quite odd

instead of $n and args, you use {}

is that sanitized by default hm

[chilly creek]

if I have a statement like
WHERE id = 1 (with id being a unique key), should I still use LIMIT 1 for better performance?

[runic pilot]

you can, but it probably won't change anything

try putting EXPLAIN ANALYZE ... before your query with and without the LIMIT to see the difference in the query plan

[chilly creek]

EXPLAIN ANALYZE SELECT ... FROM ...?

for example?

edit - I will just google

not gonna waste your time. Thanks

[chilly creek]

I am now trying to get f"EXISTS (SELECT * FROM post WHERE idpost = {d['id']})" to work. I want to get a True if the key exists and False (or even None would be okay) if not

mysql

edit, fixed, SELECT EXISTS

[ripe helm]

Can someone tell me why this is telling me "Cant operate on a closed database"?
https://mystb.in/oqoselabol.py
The error occurs at line 46, Another pair of eyes would be greatly appreciated

[chilly creek]

Looks like you only have read and no write rights to the DB

although you have rights to delete on invite

lines 27/45
can be shortened into:

if alreadyin:
else:

[runic pilot]

is this happening consistently or intermittently or just once?

[chilly creek]

Can sb explain me the following:

I am trying to use mysql_connector to INSERT INTO a database. For some reason, this line throws an error:
cur.execute(f"INSERT INTO post(idpost, url) VALUES ({d['id']}, {d['large_file_url']})")

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '://somewebsite/original/d3/4e/d34e4cf0a437a5d65f8e82b7bcd02606.jpg)' at line 1

VALUES (1, 'test') for some reason

d = {'id': 1, 'large_file_url': 'https://cdn.website.us/original/fd/b4/fdb47f79fb8da82e66eeb1d84a1cae8d.jpg'}

please @ me

[ripe helm]

@runic pilot wdym?

[runic pilot]

how many times have you seen this happen?

[ripe helm]

every time its called

[runic pilot]

ok so my hunch is that it's because you're calling close on the connection

[ripe helm]

@runic pilot but it’s never called before that line..?

The insert statement works

But not the delete

[runic pilot]

just a hunch ¯_(ツ)_/¯

[ripe helm]

oof ok

[stable glen]

yo can yall help me out a bit more? Im basically now trying to get the third colum of the table to be all added up, but only if the uuid matches. Ive go this:

        await cur.execute("SELECT * FROM statz_blocks_placed WHERE uuid = %s", (uuid,))
        Bplacedraw = cur.fetchall()
        print(Bplacedraw)
        print(tuple(Bplacedraw))
        Bplace = 0
        for obama in Bplacedraw:
            print("OBAMA")
            Bplace += obama[2]
        print(Bplace)

and these are the prints:
<Future finished result=((2, '73ee0243-f3d...-a40f31d2ef04', 1, 'survival', 'BLUE_BED'), (1, '73ee0243-f3d...-a40f31d2ef04', 1, 'survival', 'COBBLESTONE'))>
()
0

no OBAMA

://

[vocal hare]

how do i select a random row in sqlite3 library

well i know its execute but

[vocal hare]

nvm i think i got it

can i connect to 2 sqlite databases?

at once?

[winged mica]

SELECT stuID, fname, lname, mi FROM student WHERE fname='Tyler';

Can anyone tell me how that is not returning anything?

Originally it was an issue with Python, then we found out it doesn't even work in Sqlite3

[torn sphinx]

@vocal hare yes, you definitely can

[somber hatch]

is it possible to make a timer in mongo?

[thorn nymph]

heey guys is there a way to set boolean column in flask sqlalchemy db??

[errant idol]

does anyone know pymongo?

I'm having an issue

I can't use count_document because what I plan to do is check if the user entered is in the database and if not await that they aren't, if they are in the database I want to give await an embed with their info

The problem is, if I use count document it only returns if a document exists, not if their information in the document exists. Thus if count_document != 0 it will say they are in the database when they aren't

I want it to be like if their information doesn't exist because they didn't do the previous command to put their info in (I already have this code to allow to put their info in. I just want to have another command to show their info and if not await that they haven't registered)

I have this code already
https://mystb.in/vemafumimu.py

everyone is so slow to help with databases lmao

[rain wagon]

Most people use relational databases, so asking for help with mongo is often not very fruitful here

@thorn nymph db.Column(db.Boolean)

[dusky acorn]

Hello Does anyone here no how to code on a chromebook

[errant idol]

wish people knew pymongo more rip

[rain wagon]

People know it

it's just that relational is more suitable for most tasks

[errant idol]

relational?

[rain wagon]

I have exactly 1 project at work where we use a nosql, nonrelational database

[errant idol]

ohh

[rain wagon]

I am not coding it, just using it

[errant idol]

is pymongo not a relational database?

[rain wagon]

Our icinga snmp monitor for about 8000 routers and switches

Values come fast and a lot, so using a nosql db that just eats the values is better

but for most webstuff, sql is the way to go

[errant idol]

well I can only mongodb since I can't get the figuration in postgresql to allow access outside of localhost

no matter what I do in the configs it doesn't work

localhost does not allow ssl require

[rain wagon]

did you edit pg_hba?

[errant idol]

Yes

It didn't work

[rain wagon]

But even then, why should it be accessible from the outside?

usually a db is used via ::1

But it does work, trust me

[errant idol]

because I need it for when I host my bot? Not local. I don't want to keep using it when my computer is on

mongodb has an easy option to have it access everywhere

I changed it in the pg.hba and it still said localhost cannot do ssl but it was put to require lmao

[rain wagon]

Do you have a certificate?

[errant idol]

I think I do. does it come with the package when you download postgresql?

[rain wagon]

No, you need a TLS certificate to initiate a secure connection. it also should be confirmed by a root-ca, such as let's encrypt, however, self signed certs can be used as well

but a ca confirmed cert is always preferrable

[errant idol]

So I need to create one? And if so, how?

I read the docs and it's way too complicated

[rain wagon]

a self signed?

[errant idol]

One of those

[rain wagon]

You can create one by using openssl, here is an example: openssl req -config mydomain.conf -new -x509 -sha256 -newkey rsa:2048 -nodes -keyout mydomain.key.pem -days 36500 -out mydomain.pem

for the conf you need to have a look at the man page

certbot is a bit easier, but it requires a publicly owned domain to work

so if you don't own a domain, that is out of the window

and for the self-signed one, you cannot just use any, use one ending in .local, that is kind of free to use

since it's not a used tld

[final geyser]

Hey guys is there a way to migrate data from one db to another

???

I thought about doing it for making pins in my forum

Pinned posts*

[rain wagon]

sure there is

[west beacon]

One easy (less code, but unreliable) way is to export the data from each table into CSV, then use Pandas to create the schema in the target database based on inferred format of the data: pd.io.sql.get_schema.

The problem with Pandas is that it's not meant for extracting the whole data like this, so it's best to stick to CSV download + CSV upload.

The other way is database-aware schema reader and translator. Usually reading tables like information_schema can yield information about tables and columns and data types. You can translate data types (ex. TEXT->VARCHAR or whatever), and generate SQL statements that accurately reflect the data

But you only have to worry about it if the RDBMS is different. If it's the same RDBMS then simply search how to do an SQL Dump and you don't even need Python for it.

[abstract berry]

Is there anyway I can append data to a pandas dataframe pickled?

[errant idol]

@rain wagon so after you create the certificate what do you do?

[somber isle]

can anyone here help me w pandas?

[torn sphinx]

yes

[tulip hare]

i need some very general advice on how to set up my database
i want to create a habit tracker for daily / certain days a week /weekly habits and keep track of when the habit was done and when not

i was thinking of "encoding" it into a string
one field with "12345" or "135" to mark the days of the week i want them to be "active"
and another textfield with "1"(done) and "0" (not) for each day after the creation date of the habit

is that ok or how would anyone with some experience map this kind of data to a SQL Database?

[amber umbra]

@tulip hare It depends on the assumptions you wanna make about how you may need to query that data later. Based on what you've said, I would probably be a table of habits with a primary key, A table of days (i.e. Mon, Tues, Wed) that that , and a table of dates that it occurred?

[kindred python]

I'm trying to wrap my head around databases, but being a visual learner is making it problematic. I'm learning Django and it uses an sqlite3 database.

From what I understand, a database is kind of like an excel file. There can be multiple sheets (tables) and you pretty much just pick a sheet (table) and send search for stuff using queries. The results you get back are in a list called a QuerySet.

Is that right?

[amber umbra]

There's definitely arguments to be made about storing a whole week in a row or something - date storage is tricky, so there's no right answer

@kindred python That's correct, but the main thing that makes a database powerful is relationships - to extend your excel metaphor, do you know what a vlookup is?

[kindred python]

Thanks for the confirmation, and no I do not know what vlookup is.

[tulip hare]

yea your schema would definately do the trick.
i was thinking about having an easier time with encoding it to a string since i would not have to query 3 different tables

but that might be due to the fact that i find it way easier to write my own python functions to encode stuff than using SQL queries or some ORM

since that thing might someday be used by many users i could delegate that work the frontend and have each users machine do the calculations instead of the server

but then again i maybe should just get a deeper understanding of relational databases, it gives me headaches especially when some ORMs do alot of "magic" i cant see

[amber umbra]

@kindred python vlookup is an excel thing, so it's not a huge issue - was just gonna use it as an example. The point is, you can create a relationship between two tables based on some piece of data, they have in common (which is generally an id number). We can then join tables together based on whether that data matches when we query them later - something like https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ this might help

@tulip hare SQL is definetly powerful - and it's significantly faster in just about any situation to do queries like this server side rather than client side. In any modern database, joins are fast, certainly many orders of magnitude faster than python code, and you're going to be sending significantly less data over the wire in any case, which is going to be another major source of speedup.

Do you know what the "main" query you'd be using is? Like getting the most recent week's worth of data, or what?

[somber isle]

@torn sphinx do you know if Pandas.to_csv(filename) overwrites the csv file if it already has stuff on it?

[tulip hare]

well i was thinking about it like this.

if a user logs in he all his active habits (just the names)
when picking one he then gets the complete data for that habit.

it might be wrong to think like that but i am kind of trying to have a small number of requests from the Frontend to the Backend (i dont have a good reason for that but seeing that every call triggers code for authentication and creates often ?redundant? or very similar database hits and data transfers i am thinking it might be worth to send one big chunk of Data (not really big) instead of triggering many request just because the user is looking at different timescales of his habits

[amber umbra]

That's a pretty reasonable way to split it, yes

[tulip hare]

thanks for taking the time to answer my question @amber umbra

[oak schooner]

yo so
i have this json setup

"649030561632878617": {
    "warns": 0,
    "warnings": []
  }

how can i add {"someID": "Reason"} to "warnings":[]

[tulip hare]

json["649030561632878617"]["warnings"].append( {"someID": "Reason"})

[oak schooner]

oh

thank

[kindred python]

@amber umbra
Thanks, I think that did help out. I know that in Django, I'm using a .ForeignKey(User) method. When I pass .ForeignKey() a User object from another table, it creates a relationship and allows me to later get only the objects that have that User as their ForeginKey (So when they're logged in). I'm pretty sure that's what he was explaining just now with his diagram.

[errant idol]

Can someone help thank you xoxo

oops wrong chat

this is not a database question

[oak schooner]

ok so i have this json

"warnings": [
      {
        "id": 711390341789646919,
        "reason": null
      },
      {
        "id": 711390672858513499,
        "reason": null
      },
      {
        "id": 711390687408685076,
        "reason": null
      },
      {
        "id": 711390704311730187,
        "reason": null
      }
    ]

how can i make it print each one like
7878902787203722 | None
7256397461290367 | None
and not [{'id': 711390341789646919, 'reason': None}]

@ me when replyin

[stray thorn]

Is it possible to use regular string formatting to insert values into a sparqlWRAPPER? or will the syntax mess it up?

[torn sphinx]

@ me when replyin
@oak schooner

import json
with open('o.json') as json_file:
    data = json.load(json_file)
#7878902787203722 | None
for i in data["warnings"]:
  print(str(i["id"]) + ' | ' + str(i['reason']))

[oak schooner]

no not like taht @torn sphinx

everything at once

all the objs in warnings

at once

[torn sphinx]

import json
with open('o.json') as json_file:
    data = json.load(json_file)
#7878902787203722 | None
out_arr = []
for i in data["warnings"]:
  out_arr.append((str(i["id"]) + ' | ' + str(i['reason'])))
print('\n'.join(out_arr))

¯_(ツ)_/¯

[oak schooner]

damn

thank

[torn sphinx]

np

[torn sphinx]

So I'm experimenting with using repl.it with databases and it's working how I want it to, but I uploaded an already existing db file to it and now, when I try to view it in the db browser, none of the info is updated there. It's still updated somewhere else though. So how do I open whatever repl.it is writing to inside the db broswer? (I'm using SQLite)

[mild tendon]

Is there a way to make a currency bot?

[torn sphinx]

Yes @mild tendon

@mild tendon What is a currency bot?

#discord-bots

[mild tendon]

I ment an economy bot @torn sphinx

[torn sphinx]

I still am not sure what that is

If this is a Discord bot, you'd want #discord-bots like how @torn sphinx said

[little pumice]

Morning!

Im looking to write a CLI tool with python and will need to store data securely, what would be the best way to go about this?

it would need to be fairly lightweight

The easiest thing i can think of would to be have a Json file with everything in there, but im not well versed in this side of things, mainly just program in python. are there any established design patterns for this sort of thing?

[quartz star]

Hi, I'm a med student ... I have a project I'd like to do in using Ai for education in medicine was looking for someone interested in taking on this project with me ... Anyone up for a medical adventure?

[cosmic cloak]

what is the best python has to offer with handling data in databases?
I'm looking to sort the statistics of my code runs inside a database that I can handle, read, write, and analyze, and later show in a terminal

[torn sphinx]

is there any way to compare a part of your screen to a picture

[crisp ravine]

is there any way to compare a part of your screen to a picture
@torn sphinx can give example?

[calm charm]

@cosmic cloak you might want to look into SQL database in Python. For setting up a database with a username and a port, try looking into MySQL, PostgreSQL. If it's for a small project, try looking into SQLite

@quartz star Try looking in #680716760134975491

[cosmic cloak]

thank you so much @calm charm ! Yes it's actually a small project and the database should actually be a file in the same folder with the code running that's creating the data to load and using the data

[calm charm]

Then SQL would be perfect!

SQLite, sorry

[gloomy pike]

Hello, how do I set up an SQLite database in pycharm?

I installed the database plugin (the one with 2mil downloads) and got a connection but im not sure where to go from there

[calm charm]

Code?

[gloomy pike]

i have a .db file already set up on my comp with sqlite browser but i dont know how to import that

wdym code ?

[calm charm]

Can you show us what you done so far

[gloomy pike]

CREATE TABLE "DiscordServers" (
    "AuthorID"    INTEGER,
    "ServerID"    INTEGER,
    "ServerAttr"    TEXT,
    "CategoryAttr"    TEXT,
    "TextAttr"    TEXT,
    "VoiceAttr"    TEXT,
    "MemberAttr"    TEXT,
    "RoleAttr"    TEXT,
    "MemberList"    TEXT,
    "BanList"    TEXT
);```

thats in the sql

.db file

this is my cog

[delicate field]

Hey @gloomy pike!

Uh-oh! It looks like your message got zapped by our spam filter. We currently don't allow .txt attachments, so here are some tips to help you travel safely:

• If you attempted to send a message longer than 2000 characters, try shortening your message to fit within the character limit or use a pasting service (see below)

• If you tried to show someone your code, you can use codeblocks
(run !code-blocks in #bot-commands for more information) or use a pasting service like:

https://paste.pythondiscord.com

[gloomy pike]

https://pastebin.com/raw/2rQwdxvs

[calm charm]

You wanna set up a connection, then set up a cursor, then do what you want with the rest.

[gloomy pike]

i did this at the beginning

db = sqlite3.connect("DiscordServers.sqlite")
cursor = db.cursor()

[calm charm]

Also if it's for discord.py, try migrating to aiosqlite

Sqlite3 blocks

[gloomy pike]

blocks?

when i run the bot it raises this error OperationalError: no such table: DiscordServers

so i think i set up the connection wrong

[calm charm]

oh no

It's DiscordServers.db

it's a .db file

[gloomy pike]

ok

oh

i tried to create it but then this happened

Cannot connect to "Connection".
opening db: 'DiscordServers.db': Access is denied

when i run the sql create table code in the connection

i installed aiosqlite3

[calm charm]

ok

Can you show the error @gloomy pike

[gloomy pike]

that was the error

when i tried to run the script

seems like i need to make a schema

[calm charm]

Are you sure the .db is in the same directory as your python file?

[gloomy pike]

yes

says "access is denied" and i cant find a way out of it

idk how to create a schema

which i think it wants me to do

[little pumice]

Morning, I didnt get a reply so juat trying agaih.

Im looking to write a CLI tool with python and will need to store data securely, what would be the best way to go about this?

The easiest thing i can think of would to be have a Json file with everything in there, but im not well versed in this side of things.

[crimson urchin]

Good morning, is it possible to read json from the bottom

[solemn cove]

Good morning, is it possible to read json from the bottom
@crimson urchin Usually JSON decoders work on full JSON decode-able data, so basically the whole file read into memory. If the JSON is a basically a list of items then you can seek the end of the list directly after decode.

[chilly creek]

Hi, I was wondering if there is an option to "shorten" the select answer.
What I have:

id | url | tag | tagtype
1    foo   bar   general
1    foo   qux   general
1    foo   <3    meta

what I want:

id | url | tag | tagtype
1    foo   bar   general
           qux   general
           <3    meta

so that I have less repeating data
or would you just seperate that into 2 SELECT requests? 1 where I get the url corresponding to the id and 1 with only tags and tagtype?

[runic pilot]

yes, you'd need 2 different select statements for that, you can't partially choose columns for different rows

[naive delta]

Ok so i need help...
I need a counter that wont reset to the original value like everytime i restart a code

[runic pilot]

you need a database! 🎉

do you have a place that you already store data that persists between code restarts?

[torn sphinx]

I am having a hard time implementing a database in my discord.py bot

Any good guides / tutorials

[zealous schooner]

Hey guys so I’m implementing a Django website. In one model I want to be able to have multiple foreign keys that pertain to the same model. I know we can add multiple with the related_name attribute but is there a more sufficient way since the multiple foreign keys might not be all used

Could we have a one to many relationship, as a model can have many relationships to a foreign key

[half lintel]

What is the best / most effiecent way to connect to a database that you go to multiple times with sqlAlchemy? Right now I just instansiate a class at the begining of my script and pass that too the arguments that need it.

[safe sail]

If I have a table which looks like:

id | key | value | user_id

is there a way to ensure that there is only one key for a given user_id?

[runic pilot]

you can add a unique constraint to the table that only allows one entry per user_id

which database are you using?

[safe sail]

Sorry I mean there can be multiple keys for a user, but just not duplicate keys for that user

Sqlite3

[runic pilot]

you can add a unique constraint to the tuple of (key, user_id)

are you using sqlalchemy?

[safe sail]

yes

[runic pilot]

ok, let me dig up an example

[safe sail]

ty 🙂

[runic pilot]

https://github.com/Rdbaker/weasl-api/blob/master/weasl/end_user/models.py#L223-L226

those lines add 2 unique constraints to the "EndUser" table, one for (org_id, email) and one for (org_id, phone_number)

from sqlalchemy.schema import UniqueConstraint

...

class MyModel(MyDeclarativeBaseClass):
    ...
    __table_args__ = (
        UniqueConstraint('user_id', 'key', name='_user_id_key_uc'),
    )

and I'm not sure about sqlite, but if you ever switch to postgres, adding a unique constraint automatically adds an index, which makes lookups much faster

[safe sail]

Thanks a lot! It was quite hard to find any search results (guess I wasn't using the right keywords 😛 )

[runic pilot]

happy to help!

[gritty wind]

how do I make/use a database?

[torn sphinx]

Huh?

[gritty wind]

idk much about databases and was wondering if this might help me with a program i'm making

[torn sphinx]

Do you want to create a database or use a existence database

[gritty wind]

create new, i guess

[last yew]

I'm using mongodb (flask-mongoengine) with flask-login and flask-user for a web app and I'm wondering how or if I can do role based login with this setup:

from flask_mongoengine import MongoEngine
from flask_user import UserMixin

db = MongoEngine()


class User(db.Document, UserMixin):
    # User authentication information
    username = db.StringField(default='')
    password = db.StringField()
    roles = db.ListField(db.StringField(), default=[])

class Vendor(db.Document):
    brand = db.StringField(default='')
    location = db.PointField(auto_index=False)
    max_occupants = db.NumberField()
    current_occupants = db.NumberField()
    user_queue = db.NumberField()
    number_up = db.NumberField(),
    meta = {
        'indexes': [[("location", "2dsphere"), ("datetime", 1)]]
    }

class Order(db.EmbeddedDocument):
    id = db.ObjectIdField(required=True, default=db.ObjectId,
                    unique=True, primary_key=True)
    vendor = db.ReferenceField(Vendor)
    order_number = db.NumberField(required=True)
    called_number = db.BooleanField()

class Customer(db.Document):
    # User information
    first_name = db.StringField(default='')
    last_name = db.StringField(default='')
    orders = db.ListField(db.EmbeddedDocumentField(Order))

I'm not sure what channel this falls into #databases or #web-development

honestly prob more #web-development

[oak schooner]

yo
im creating a database with a discord guild id as name
but it seems to give me a syntax error

c.execute(f"CREATE DATABASE {ctx.guild.id}")

ProgrammingError: 1064 (42000): You have an error in your SQL syntax

ok nvm
got it to work by adding a prefix at the beginning

[wet nest]

Hello guys, please i really need your help here

Describe the difference between objects and values using the terms “equivalent” and “identical”. Illustrate the difference using your own examples with Python lists and the “is” operator.

Describe the relationship between objects, references, and aliasing. Again, create your own examples with Python lists.

Finally, create your own example of a function that modifies a list passed in as an argument. Describe what your function does in terms of arguments, parameters, objects, and references.

Create your own unique examples for this assignment. Do not copy them from the textbook or any other source.

[torn sphinx]

Anyone has exp. with pythoneverywhere.com and Flask with MongoDB?

from flask_pymongo import PyMongo

app = Flask(__name__)
app.config["MONGO_URI"] = "mongodb+srv://user:[e-mail]/CLUSTERNAME?retryWrites=true&w=majority"
mongo = PyMongo(app)

mongo.db.COLLECTIONNAME.find_one({"_id": "ABC123})´

Is this the correct way to search a mongo DB?

[vocal hare]

@torn sphinx its not multiline

try ```python
'''this is a
multiline
string'''

[gloomy pike]

how do i create an sqlite table with utf8 encoding?

[torn sphinx]

how can i create sql database?

[torn sphinx]

@torn sphinx Can you give more info? Which framework or none and OS?

Also I have question. I use Django aggregation, trying to aggregate few Counts. For example

SELECT COUNT(DISTINCT table.code) as all_code,
COUNT(CASE WHEN table.exist=1 THEN 1 ELSE NULL END) as all_name

In second count I want to get distinct code as in first one, but also then attribute exist is true

[lean raven]

My model doesn't let me migrate it after i introduced two not null fields with some existing entries. It doesn't let me delete those entries too. I'm on Django Sqlite3

NOT NULL CONSTRAINT FAILED > My model doesn't let me migrate it after i introduced two not null fields with some existing entries. It doesn't let me delete those entries too. I'm on Django Sqlite3
@lean raven

SELECT COUNT(DISTINCT table.code) as all_code,
COUNT(CASE WHEN table.exist=1 THEN 1 ELSE NULL END) as all_name
@torn sphinx what engine are you using ??

[runic pilot]

Add the columns first then add values in that column to the existing rows then add the NOT NULL constraint

[lean raven]

Add the columns first then add values in that column to the existing rows then add the NOT NULL constraint
@runic pilot tried it,,, doesn't let me do that. I cannot even remove the values from admin/dbshell/shell

[pliant pendant]

how do i get the second last item of an array

[lean raven]

how do i get the second last item of an array
@pliant pendant array[:-2]

[pliant pendant]

is that it??

[lean raven]

yepp

[runic pilot]

array[-2]

[pliant pendant]

yikes it was soo much easier than i thought, @runic pilot wont work for SQL

??

[lean raven]

Sorry @runic pilot is right

typo

[pliant pendant]

awar

oh ye

with :

[lean raven]

what ?? @pliant pendant

[pliant pendant]

no wait

you say that [-1]

would work

it wont work in SQL

[lean raven]

wait

you want an second last item from a sql column ???

[pliant pendant]

array

not column

[lean raven]

you should've told that then

sql arrat???

that's new

[pliant pendant]

-1 returns NULL

[lean raven]

sql doens't have arrays

can you show an ss ??

[pliant pendant]

SELECT p[array_length(p, 1)] FROM regexp_split_to_array('test.exe'::text, E'\\.') p;

that is for the last item in array

but 2nd last i cant just put 2 or 0

using plpsql [Postgres]

[lean raven]

try (p,2)

[pliant pendant]

wont work

still NULL

its really odd

[lean raven]

to me , this is new, never used psql tho

[pliant pendant]

it has its epic pros with functions and string manipulations

so u dont need to do it in-code

also u can port SQL to plpsql

but yeah im still stukkie wukkie in this part smh

[lean raven]

yeah igga imma use it in my production server this time

[pliant pendant]

igga?

[lean raven]

i guess xD

[pliant pendant]

i see

doe if its huge db i dont recommend switchint unless u wanna put lots of effort to it

i mean... not really

[lean raven]

its a startup im working on

[pliant pendant]

ah i se

[lean raven]

like a multi platform application

a services app, so might be users > 2k

not sure haha

[pliant pendant]

i see

havent worked with any bussiness thingies just yet

im still making large hobby projects smh

[brazen charm]

Our main production DB is datomic 😳

aka the biggest ram hog ever

[runic pilot]

I always use & recommend psql, tons of features and it's so stable

[lean raven]

psql is stable indeed

my boss is a blind fan !!!

[runic pilot]

I'm also a pretty biased fan of it, though it's not worth switching if there's a ton of production data already using something else

[lean raven]

yes truee dat

i develop on sqlite and switch to psql for production

btw dbshell doesn't open on my django project

i use sqlite 3 on Ubuntu

donno what's wrong ,,,checked my settings.py

[brazen charm]

I use postgres alot but havent ever got it to 60GB+ at a time

what command are you doing to enter it?

[lean raven]

python manage.py dbshell

[runic pilot]

I think I've got about 60GB on my postgres db and it's doing great

[lean raven]

what are you using it for ??

ML ?

[runic pilot]

yeah, we're storing and predicting energy data

[lean raven]

aye that's cool man

i did that once

used an LSTM neural net

[runic pilot]

I think we're doing something more simple, since the inputs are very distinct levers

but I'm also not a ML engineer so I don't totally understand it

[lean raven]

oh its okayy

python manage.py dbshell
@lean raven anyone ???

[livid cairn]

I'm completely new to SQL. I'm thinking about using MySQL to store my application's data, which should be structured like this:

• User info.

• Each user can have up to 99 accounts.
• Each account have it's own settings.

What is the most appropriate way to scheme this and how can I link user, account and settings in a way each row of settings is linked to a specific account ID? And for each account created, a new row of settings is created for it (should it be automated by the app or SQL itself)?

[brazen charm]

SQL has a auto_increment function

btw

easiest to get started with is SQLite as it doesnt require setting up a db server etc...

but if u are gonna use a db server like mysql

use postgres

[torn sphinx]

I want to make a database to store information & read information out of it, how would I do this?

I dont want it to be stored locally either

I have a SQL server on google cloud, I just have no idea how to use SQL even after watching many videos on it

also I'm storing things like user, password, key

[lean raven]

use firebase > I have a SQL server on google cloud, I just have no idea how to use SQL even after watching many videos on it
@torn sphinx

[torn sphinx]

alr thanks, ill look into it

[gloomy pike]

how would i create a .db encoding to utf8?

in sqlite3

[lean raven]

its default utf-8 only

you need to create it spedicfically

[gloomy pike]

oh

[lean raven]

are you on django??

[gloomy pike]

no

im on aiosqlite3

i put in this code on on_ready

db = await aiosqlite3.connect("DiscordServers.db")
    await db.execute("""
    create table if not exists DiscordServers(
    AuthorID int,
    ServerID int,
    ServerAttr text,
    CategoryAttr text,
    TextAttr text,
    VoiceAttr text,
    MemberAttr text,
    RoleAttr text,
    MemberList text,
    BanList text
    )
    """)
    await db.commit()
    await db.close()```

but when i open the .db file it says "file was loaded in the wrong encoding: utf-8"

@lean raven

[lean raven]

you are using the db shell for sqlite? @gloomy pike

[gloomy pike]

no

im not using any browser

i just did that and execute sql statement within pycharm

[winged yacht]

Anyone using gspread ?

I have mini question

[gloomy pike]

I can open it in db browser fine

but i want to be able to open it in pycharm

for convenience

[lean raven]

cant help man @gloomy pike v sry

[tidal prism]

Making a discord bot using SQlite and pycharm
According to a few google searchers you need to link the database by going to view>tool window>database, but I don't see database there?

[brazen charm]

You only get the Db stuff if you have professional addition

and you dont need it

it just helps you visualise the schemas and tables

[eternal breach]

What's this

this is about me

[brazen charm]

?

[tidal prism]

well the issue I'm having is that the bot isn't recording XP gaiin or seemingly messages. I'd thought it would be an issue with it and sqllite3

[gloomy pike]

@brazen charm discord.py ppl said sqlite is very useful for small db

cuz saving via text files/json will corrupt a lot of data

[brazen charm]

ik

im saying you dont need pycharm's db viewer

[tidal prism]

How does a DB and pycharm link?

[gloomy pike]

oh

[brazen charm]

If your on professional you get stuff like this

[frosty barn]

Hey! I have an SQL database with Item name and details about that item. I want to let the user search the database for an item, pretty simple to do an exact match search or search for items containing the given word, but I want a smarter searching system, like maybe the user slightly mispelled the word, or if the name is 2 words and he forgot to add a space inbetween, is there a library or something that will let me do that? If not maybe someone could point me in the right direction to where to look

[gloomy pike]

there is a json type in sql?

i thought there was not

only text

[brazen charm]

Only postgres

[gloomy pike]

o

[brazen charm]

if you want to use json data all the time you might aswell use mongo

[rain wagon]

@frosty barn this highly depends on the dbms how it works

The term you are looking for is fuzzy match

[round dagger]

Hello!
I have a discord.py bot that uses motor asyncio and has a mongo database of around 10k documents; I receive quite frequently updates to this db in form of json files: the update is always a subset of the existing entries, but they usually contain new fields and update to existing ones. Update size is usually around 5-6k entries.

Currently, I load the update json, loop over it and use each element to build a bulk UpdateOne() operation and push it to a list, then send the list in a bulk_write operation, like so:

requests = []
for dl_card in data_json:
    card_name = dl_card['name']
    requests.append(UpdateOne({"name": card_name}, {"$set": dl_card}))
await db.cards.bulk_write(requests)

While inserting the whole initial db of 10k entries only takes around 10 seconds, making this update usually takes in the order of a couple of minutes. Is there any way to make this operation more efficient? Ideally I would like to send the json update as it without looping over it.

Thanks in advance!

[clever topaz]

@round dagger Have you tried upserting?

[round dagger]

Would that be more efficient? My guess is that upserting is a superset of the actions an update does, so it can only be equal or worse

[clever topaz]

Try it and see, I guess.

I think it should be faster if you let the DB/interface do all the handling itself.

[round dagger]

I think what's making it slow is that I'm requesting 6k different operations, where on each the db has to make a search... This is not improved by the upsert.
I was wondering if there is in mongo something like SQL's join, when you give two big collections and just say "much these using this equality"

[clever topaz]

@round dagger https://stackoverflow.com/a/33511166 if you haven't already found it.

[ripe helm]

def write_value(table, where, what, value, overwrite):
    conn = sqlite3.connect('hierarchy.db')
    c = conn.cursor()
    c.execute(f"UPDATE {table} SET {value} = {overwrite} WHERE {where} = {what}")
    conn.commit()
    conn.close()```

People keep telling me not to use f-strings and i understand why

but how would i do something like this with the ? thing

[clever topaz]

c.execute(f"UPDATE {table} SET ? = ? WHERE ? = ?", (value, overwrite, where, what))

Should work.

[round dagger]

@round dagger https://stackoverflow.com/a/33511166 if you haven't already found it.
@clever topaz thanks, but unfortunately that looks like it's only for querying, not updating... I guess I hit the limit of how performing an update could be, in mongo

[clever topaz]

I doubt it.

Can you do it faster using direct queries?

[round dagger]

What do you mean?

[clever topaz]

i.e. Using raw SQL statements.

[round dagger]

sql in mongodb?

[ripe helm]

@clever topaz ah thanks a lot

[clever topaz]

OK, raw MongoDB query statements.

@round dagger https://studio3t.com/knowledge-base/articles/sql-query/

[ripe helm]

it didnt really work..

[round dagger]

Looks like, again, that's just for querying, not updating

[clever topaz]

it didnt really work..
@ripe helm How not?

[ripe helm]

i have no idea

[clever topaz]

What didn't work?

[ripe helm]

i can show the error later

no errors

cuz i have a handler

[clever topaz]

Can you paste the error?

@round dagger Can you try:

requests = []
for dl_card in data_json:
    card_name = dl_card['name']
    requests.append(UpdateOne({"name": card_name}, {"$set": dl_card}, upsert=True))
await db.cards.bulk_write(requests)

Also, if you don't need the operations to be ordered, you can use:

await db.cards.bulk_write(requests, ordered=False)

https://api.mongodb.com/python/current/api/pymongo/collection.html#pymongo.collection.Collection.bulk_write

ordered (optional): If True (the default) requests will be performed on the server serially, in the order provided. If an error occurs all remaining operations are aborted. If False requests will be performed on the server in arbitrary order, possibly in parallel, and all operations will be attempted.

[round dagger]

I'll definitely try the ordered to False, that sounds like a great improvement for sure... The upsert doesn't really work for me because I don't want an update to be inserted without an existing field, as it doesn't contain all the required fields for my app

[clever topaz]

"Without an existing field"?

[round dagger]

I meant an existint document matching it

[clever topaz]

In that case it will simply update it.

[round dagger]

no, upsert inserts the new document if it can't find one to update

So I could end up with updates without an original document

[clever topaz]

Oh so you want to only update.

No inserts if something doesn't already exist?

[round dagger]

Yeah, I guess I could try it, if makes performance much better I can modify the app to ignore documents that are missing certain fields

or simply run a delete_many afterwards

[clever topaz]

Why not simply exlude results if the card_name doesn't exist?

Rather than running a conditional update.

Which may be the cause of the slowdown (not sure how MongoDB filters, but it's having to search for every card I assume).

Instead filter rows before and then chuck them all in without any worries.

(Still need the filtering)

10,000 rows, multiple minutes sounds wrong.

@ripe helm Can you paste the error?

[round dagger]

Why not simply exlude results if the card_name doesn't exist?
Rather than running a conditional update.
...filter rows before and then chuck them all in without any worries.
What do you mean?

[ripe helm]

@clever topaz I don’t have access to my pc rn, I’ll do it when I can

[alpine patio]

SELECT 
    MAX(SALARY)
FROM
    hwDB.Worker 
GROUP BY DEPARTMENT;```
how can I display the name for who owns the highest salary in
each department?

SOLUTION : ```SELECT FIRST_NAME, DEPARTMENT, SALARY
FROM hwDB.Worker
WHERE SALARY IN
(SELECT max(SALARY) AS salary
From hwDB.Worker
GROUP BY DEPARTMENT)

[spiral dirge]

I cannot connect to my posgresql database with asyncpg but I can connect with psql, asyncpg raises InvalidPasswordError what can I do about this

ping me with solution thank you

[chilly creek]

@ripe helm instead of using c.execute(string) you use c.execute(string, tuple)

you do that to prevent SQLi (injection). Doesnt really matter if you dont let users input variables, but it is better practice to still do it

c.execute(f"UPDATE ? SET ? = ? WHERE ? = ?", (table, value, overwrite, where, what))

oh, sorry. Seems like my chat didnt scroll down.

also, remember if your tuple only contains 1 element, you have to write it like this:
(var,)

[brazen charm]

last time i checked sqlite doesnt support place holders for tables or columns no?

[rose plank]

im trying to access firebase data with pyrebase, my data (posts.json) looks like this:

{"-id1":{"book_id":1,"post":"Lorem Ipsum Dolor","post_author":"Admin"},"-id2":{"book_id":1,"post":"Very nice book","post_author":"Admin2"}}

and my request looks like this:
posts = db.child('posts').order_by_child('book_id').get().each()
but im getting this error:

requests.exceptions.HTTPError: [Errno 400 Client Error: Bad Request for url: https://db.firebaseio.com/posts.json?orderBy=%2522book_id%2522] {
  "error" : "orderBy must be a valid JSON encoded path"
}

am i making the request incorrectly?

[grizzled roost]

what's your python and pyrebase version @rose plank?
(also, I found this, which might work for you https://github.com/thisbejim/Pyrebase/issues/294)

[torn sphinx]

I am creating a discord bot with a database and want to ask which way is better

1. Keep the connection open to the db all the time
2. Open it on command and close it afterwards

[lean raven]

Im working on django application and have a function to write a time field on a certain field

but everytime i write the time, i get this warning

  warnings.warn("DateTimeField %s received a naive datetime (%s)"```

i dont understand this and is this something i should be worried about

im using python datetime.now() to set the date

[somber hatch]

in mongo, can i add something without deleteing everything else? like i have

things:
{
thing: 1
thing2: 2
}
and i want to add thing 3 without doing anything to thign1 and 2

[lean raven]

yes you can append it

@somber hatch

[somber hatch]

how? if i may ask

[lean raven]

db.collection.update({"$set": {"items.thing3": 3});

did it work ?? @somber hatch

[somber hatch]

i was off my pc and just saw it im atry it wait

it probably will work i jsut have to do lots of things so i cant test it, but thanks

[lean raven]

peace

[torn sphinx]

When I try using an update query in sqlite, I keep getting an error that there's no column found named "no."
This is the line I'm using
cursor.execute(f'UPDATE tickets SET open = no WHERE channelid = {ctx.channel.id}')
open is a column and tickets is a table. But for some reason, instead of changing the value to "no" under the "open" column, it's trying to find a column named "no"

[vocal moon]

import mysql.connector

db = mysql.connector.connect(
    host="x",
    user="dareal",
    passwd="X",
    database="economy"
)

raise errors.InterfaceError(
mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'X:3306' (10060 A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond)

[brazen charm]

dont you need the port

also

ip

👀