databases

[torn sphinx]

random question, does anyone here know of a more horizontally scalable database than Cassandra, particularly in terms of availability and partition tolerance?

[little flicker]

You missed consistency!

[little flicker]

I assume you're looking for something self hosted?

[torn sphinx]

yes sir

open source

something I can deploy on my pc, or a VPS

[little flicker]

But, I mean, it really depends on way more than that. Write frequency, type of data...

If you want AP and no C, a single master with many slaves will do.

[torn sphinx]

that's incorrect, in case of node going offline you would want master-master setup, like cassandra so you dont have downtime

mongoDB would take time to be operational when master node dies out

cassandra doesn't suffer from such downsides

[little flicker]

I think we're talking about different things.

[torn sphinx]

A- Accessibility

right?

[little flicker]

Availability.

[torn sphinx]

sorry, that yea

[little flicker]

You can read SOME data.

[torn sphinx]

what about writes?

[little flicker]

You can write, but there's no consistency guarantee.

[torn sphinx]

but in master-slave, typically only master can take writes

[little flicker]

But you also wanted partition tolerance 🙂

[torn sphinx]

so in event master node dies, you're looking at downtime on writes

of which cassandra doesn't suffer

yea, cassandra handles partition tolerance

that's the AP, without the C

[little flicker]

True, but you can fix that easily by having a hot standby.

[torn sphinx]

hmm, so which database would allow for such quick resolution, other than Cassandra

I'm curious for such alternatives

[little flicker]

I've only used scylla, but definitely not because of this

[torn sphinx]

oh, cool, and how was your experience with scylla?

[little flicker]

Definitely better than with cassandra, heh. But, again, we had like 3-4 nodes

[strong robin]

hi

i need help with appending to a list

in mongo db

[little flicker]

I'm not sure how the guarantees are about this, we just moved for performance

[torn sphinx]

understandable

[little flicker]

to be fair, it wasn't, it was just a very short path to less sadness 🙂

[torn sphinx]

why was cassandra so bad? :/

may i ask what sort of issues occurred

[little flicker]

It was... 2 years ago, I don't really remember, heh. but most of our quries were enumerating some stuff and it was slow (memories of splitting some keyspace into bits to do a search comes to mind, but I really don't remember more than that), and since scylla was a drop in replacement, we just thought we'd try that

[torn sphinx]

and you say the experience was positive (the switch)?

[little flicker]

and then a few months later we just dropped it altogether

[torn sphinx]

oh

scylla too?

[little flicker]

we didn't have to migrate data, but the switch was seamless pretty much

yeah

[strong robin]

yo

[torn sphinx]

so what do you use now for database?

[strong robin]

can any of u help?

[little flicker]

we use influxdb now for that data

[torn sphinx]

can't say I've heard about this one before, sounds exotic 😮

[little flicker]

eh

it's a timeseries database

(no, never, ever, use AggreGate)

[torn sphinx]

what is influx in terms of CAP?

what it aims for

[little flicker]

bad 🙂

[torn sphinx]

😂

[little flicker]

I mean, influxdb is not really meant for any kind of consistency

[torn sphinx]

sooo AP?

[little flicker]

yeah, but it's a timeseries database

the PK of everything is a timestamp and some tags

[torn sphinx]

right

interesting

[little flicker]

yeah, well. for what it's doing, influx is awesome, especially if you're gonna pair it up with grafana (we're storing monitoring data from power plants there and that's the most commonly used UI for things)

[north ridge]

I'm about to finish teaching my first course in my career. It's an introductory database course. I loved it

It was mostly theoretical, about ERDs and why we should use databases, and we did practice some SQL

Although unpaid, it was hella fun

[little flicker]

I feel sorry for you, if you like SQL

@torn sphinx What's the actual use case of this DB?

[strong robin]

bruh

can u help me pls with mongodb

[torn sphinx]

I was considering multiple points. One would some an online store since there appears to be a lot of job offers for that. The other one would be to suggest a more performant DB in case my employer asked for such solution

[torn sphinx]

what are you doing

[strong robin]

im trying to get a log channel

and append to a whitelist

[little flicker]

Yeah, but an online store definitely wants C...

[strong robin]

.

[little flicker]

And I'm pretty sure that cassandra won't be the best solution for that.

[torn sphinx]

I'm sorry I'm unable to help you

[strong robin]

oh

[little flicker]

But, it's almost 2am here, so I'm off to bed, I'll try to read back what you said tomorrow, @torn sphinx

[torn sphinx]

so, under no circumstance would you consider Cassandra viable option for online store?

alright have a good night 👋

[proven arrow]

How big is your online store?

You most likely don’t want Cassandra as your main db.

[river ice]

this is the solution for inserting bulk data to a table using sql alchemy .. "bypassing" the duplication error i was having earlier .
Consists of retrieving all of the records matching the records we want to add , and then removing them from the queue of records to be entered. ```py
def queue(queue):
'''add stream to queue '''
print("queue" , queue)
#build a query to retrieve the current records
stream_recieve = stream_session.query(
stream_queue.stream
).
filter(or_(stream_queue.stream == stream
for stream in queue)).all()

        print("items in db", stream_recieve)
        
        print("items in our list",queue)
        stream_setup = []
        for stream12 in stream_recieve:
            if stream12[0] not in queue:
                stream_setup.append(stream12)

        print("objects to enter into db",stream_setup)

        objects = []
        try:
            for streamname in stream_setup:
                objects.append(stream_queue(stream = streamname))
            
            print("objects   ",objects)
            stream_session.bulk_save_objects(objects)
            stream_session.commit()
        except Exception as e:
            print("error" , e ,"asdasdadasd")
            print("no results to enter to db")```

which is essentially what the happens when the "no duplicate error" happens. Not sure why this isn't integrated somehow into sql alchemy . Maybe i havent found it

[torn sphinx]

I'm pretty new to this.. 😦

[proven arrow]

Which database are you using?

[faint blade]

Why is the timestamp part of the primary key? What happens if two people make transactions at the exact same time?

[torn sphinx]

hey, i was sqlite and sqlalchemy and now im trying to upgrade to postgresql, but for some reason im getting this error: ```sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "Subfilter"

[SQL:
CREATE TABLE "Postfilter" (
postid INTEGER NOT NULL,
filtername TEXT NOT NULL,
PRIMARY KEY (postid, filtername),
FOREIGN KEY(filtername) REFERENCES "Subfilter" (filtername),
FOREIGN KEY(postid) REFERENCES posts (id)
)

does someone know what could cause this? with sqlite its working fine

[torn sphinx]

It's an OLAP database

[chilly creek]

Hi, I need conceptual help on MongoDB specifically.

I am developing a tinderlike app. Users have many fields about themselfes, one of those is dateOfBirth. I want to have a public API which the Frontend will use to make DB queries (graphQL, irrelevant for the case) - and the public should not know ones dateOfBirth.

I decided to make a virtual age that calculates the current age of the user any time it is called.

It being a virtual does result in problems tho: I would like to make users able to filter show me all users older that 20 years - but as virtuals are not DB-persistent, they do not allow filtering.

So I need an option other than virtual.
Using a getter on dateOfBirth would be an option, but that would complicate things on /settings - a user should see his own dateOfBirth. afaik the filter would still be on the DateTime fields instead of the int age - so a query in the likes of "show me all users born between X and Y" would still be exploitable by an attacker.

Another option would be to make age a real field and create getters/setters in a pre-save hook - and perhaps create a scheduler that updates the age on every user once a day - but that doesnt feel right either.

Any ideas?

[faint blade]

Can you show Subfilter table? It quickly tells you that you need some unique constraint in that table. that may be wrong

[faint blade]

I may not be understanding OLAP databases correctly, but I don't think that changes anything? It is inevitable that at least one row's timestamp will match another one

[torn sphinx]

class Subfilter(db.Model):
    __tablename__ = 'Subfilter'
    __table_args__ = {'extend_existing': True}
    userid = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
    filtername = db.Column(db.Text, primary_key=True)
    filterlist = db.relationship('Subfilterlist', backref='subreddits', lazy='dynamic')
    postfilter = db.relationship('Postfilter', backref='posts', lazy='dynamic')

[faint blade]

I see that filtername is part of the primary key. Can you just explicitly set it to unique?

[torn sphinx]

im gonna try it

do i need to do it for all tables?

[faint blade]

No just change this line: filtername = db.Column(db.Text, unique=True, primary_key=True) like that I think

[torn sphinx]

okay, im gonna try if it worked

1 sec ill need to rebuild docker container

[river ice]

This is with Postgres

[torn sphinx]

im still getting this error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "Subfilter"

[SQL:
CREATE TABLE "Postfilter" (
        postid INTEGER NOT NULL,
        filtername TEXT NOT NULL,
        PRIMARY KEY (postid, filtername),
        FOREIGN KEY(filtername) REFERENCES "Subfilter" (filtername),
        FOREIGN KEY(postid) REFERENCES posts (id)
)

[faint blade]

Did you delete the tables to retry in pgAdmin? Specifically you need to delete Subfilter

[torn sphinx]

ill remove everything and then try again to be sure

[sullen pine]

[mysql] I have a column of dates in format 'year-month-date' in a table
i want to get all dates with differences of 2 dates in between. How to achieve this.
For example:
2021-10-05
2021-10-08
2021-10-10
2021-10-14
2021-10-17
i want the output to be like
2021-10-05 to 2021-10-08 (have difference of 2 days in between)
2021-10-14 to 2021-10-17
(or)
Date Diff
2021-10-05 0
2021-10-08 2
2021-10-10 1
2021-10-14 3
2021-10-17 2

[river ice]

Haven’t ever used pgAdmin before but there’s a one liner for listing all the existing tables in a database . (With alchemy) . And also a drop all command

Set the engine to echo=True and it will report to the console what it has done

@torn sphinx @faint blade

I’d write the code of what I mean when I’m at the computer , don’t know the modifications to the code off the top of my head

[torn sphinx]

i think ill need to do it for all tables since it has gone to the next table now with the same error

[torn sphinx]

nice thanks!

[proven arrow]

https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#sqlalchemy.dialects.postgresql.Insert.on_conflict_do_nothing

That will do what you wanted

[river ice]

Wow! That’s what I was looking for . Thanks!

[river ice]

I wonder if you are losing data while resetting tables .

Also wondering whether there are more advanced ways to unloading data into a copy , and reloading it , with sql alchemy.

[torn sphinx]

i am losing my data since i just purge my data on docker to make sure everything is deleted

i have some composite keys in my database, but how would that work when i need to add unique?

[harsh pulsar]

uniqueness is determined by all values in the composite key

so if your key columns are id1, id2, if you have id1=3, id2=5, you can insert id1=3, id2=7 because the pair of values is still a unique pair, even if one of the numbers is duplicated

[torn sphinx]

how will this work in sqlalchemy with postgresql? because i need to add unique=True for all primary_keys otherwise i cant migrate the db

[harsh pulsar]

uniqueness is checked by the database

surely primary_key=True already takes care of uniqueness

mixing unique and primary_key could be weird

[torn sphinx]

i had it like that first, but im getting this error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "scrapelist"

[SQL:
CREATE TABLE link (
        postid INTEGER NOT NULL,
        subreddit TEXT NOT NULL,
        amountofposts INTEGER,
        PRIMARY KEY (postid, subreddit),
        FOREIGN KEY(postid) REFERENCES posts (id),
        FOREIGN KEY(subreddit) REFERENCES scrapelist (subr)
)

i looked it up and found this: https://stackoverflow.com/questions/11966420/what-is-causing-error-there-is-no-unique-constraint-matching-given-keys-for-ref

[harsh pulsar]

@torn sphinx it's because there's no primary key or uniqueness constraint on scrapelist.subr

the foreign key just doesn't make sense under those conditions

[torn sphinx]

class Scrapelist(db.Model):
    __tablename__ = 'scrapelist'
    __table_args__ = {'extend_existing': True}
    userid = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
    subr = db.Column(db.Text, primary_key=True)
    scrapeuser = db.Column(db.Text, unique=False)
    how_many_posts = db.Column(db.Integer)
    linksub = db.relationship('Link', backref='linkscrape', lazy='dynamic')

[harsh pulsar]

the SO answers are pretty clear on this

ah

can you show the link table definition as well?

[torn sphinx]

class Link(db.Model):
    __tablename__ = 'link'
    __table_args__ = {'extend_existing': True}
    postid = db.Column(db.Integer, db.ForeignKey('posts.id'), primary_key=True, unique=True)
    subreddit = db.Column(db.Text, db.ForeignKey('scrapelist.subr'), primary_key=True, unique=True)
    amountofposts = db.Column(db.Integer, default=0)

[faint blade]

I already showed you how to fix it on your first table

[torn sphinx]

yeah but i have composite keys so adding unique wont work right?

[faint blade]

When you only reference the table (and not a specific column) it defaults to the primary key, and you have two primary keys (composite)

So in this very case you'll have to pick a column

[torn sphinx]

but i dont understand which column to pick then since they both arent unique, because i use a composite key

[river ice]

What I would do is set a primary key for both tables that is the same . (Not assuming that’s correct) . And then when calling data from either table I’d use the the primary key that match both tables

[river ice]

Can create a new column I see. A unique column that matches both tables

[faint blade]

I don't really know how to help you then sorry, you'll have to do some change in your schema? Add an identity column (basically a column that increments) as the primary key and use that

[harsh pulsar]

try it anyway and see if it works

maybe postgres allows it

i believe this is a postgres-specific limitation anyway

[faint blade]

No that won't work?

[faint blade]

Not really?

[harsh pulsar]

makes sense that it wouldn't work. i do believe mysql supports it though

it might be a mysql-specific extension

[faint blade]

A foreign key is basically a way to say "this value must appear in this table's column X, X defaulting to the primary key"

[harsh pulsar]

right

i understand that semantically it's a weird thing to do

[faint blade]

How would MySQL or PostgreSQL know how to split the column? 🤔

https://stackoverflow.com/questions/10565846/use-composite-primary-key-as-foreign-key

[harsh pulsar]

ah, you can have a composite foreign key

i actually didn't know that, good find

hopefully sqlalchemy supports it

https://stackoverflow.com/q/7504753/2954547

[faint blade]

So yeah, if you want to use a composite key as a foreign key, you basically have to say is table A's X column matches table B's Y column, etc.

Table A: X -> Table B: Y
Table A: Z -> Table B: W

[torn sphinx]

thanks! im gonna check it out

[faint blade]

You can't have:
+> Table B: Y
Table A: X -|
+> Table B: W

[torn sphinx]

oohh okay thanks! i think i can get it to work

Hi, I have been given some data to use with mysql and using Python I have to detect inconsistencies in blob reference counts, including count mismatches, references to missing blobs, and orphan blobs with nonzero reference count. I have never done this before and I don't know how to do this so any help would be amazing! 🙌

[harsh pulsar]

i'd work backwards:

1. figure out, algorithmically, what exactly you need to do
2. figure out how to lay out the data so as to accomplish (1)
3. figure out how to put data into the database in that format
4. figure out how to write the required code (sql queries, python code, or a mix of both) to accomplish the task

[torn sphinx]

Thank you, I will try and look at it this way.

[harsh pulsar]

Note that I have no idea what a blob reference count is in this case, you might have to define those terms when you're asking for help

[torn sphinx]

i have no idea too 😅

[manic turret]

Anyone who knows SQLalchemy

[river ice]

#help-candy sql alchemy ORM issue inserting objects to db . using session.bulk_save_objects(objects)

[jade swan]

users.update_one(data, {"$set": {"balance": bal-price}})
        users.update_one(
            data, {"$push": {"stock": {"name": med["name"], "price": med["sell"]}}})

Only the first operation is being done while the second one is skipped, I'm not sure why is that happening, could someone help me?

Oops wait it's pull and not push

Well, it still does not work even tho I changed it to $push and not $pull

[wary gull]

hi guys,

im trying to create a table in postres sql
CREATE TABLE [IF NOT EXISTS] table_name ( col1 INT);
problem is my postgresql is 8.3
[IF NOT EXISTS] is implemented in 9.1

[proven arrow]

Then do a manual check to see if it exists first, or pure sql way write your own function that achieves that.

[wary gull]

its not present

alternative way is to use %% func %%

which i am not familiar

[harsh pulsar]

isn't 8.3 ancient and unsupported?

[torn sphinx]

Hello help me p

pls

HIIIIIIIIIIIIIIIII

[rain plank]

You need to ask your question in order to get help.

[torn sphinx]

I need help in ETL processing

hi

[native zenith]

Tryna run this simple query

f"DELETE FROM inventories WHERE uid='{{uid}}' AND iid='{{iid}}' ORDER BY uid ASC LIMIT 1"

But then:

sqlite3.OperationalError: near "ORDER": syntax error

What did I do wrong?

[wary gull]

my org datalake version
so have to manage

[rain plank]

How are you trying to order something that you are deleting?

[harsh pulsar]

also don't use f strings for passing data into a query

use query parameters

[native zenith]

The reason I have order by on is I thought not having it caused the error
When I remove it it says python sqlite3.OperationalError: near "LIMIT": syntax error

[native zenith]

wdym?

[rain plank]

Are you trying to select?

[harsh pulsar]

https://docs.python.org/3/library/sqlite3.html
ctrl+f for "Usually your SQL operations will need to use values from Python variables." and read starting at that paragraph

[native zenith]

thanks

[native zenith]

I'm trying to delete one of the rows that matches the query

Because there can be multiple that are exactly the same

[native zenith]

how can I use that with a DELETE statement? It appears it's only for INSERT statements.

[harsh pulsar]

why do you think that?

did you try it?

[native zenith]

I don't know how because it doesn't tel you how

It only tells you how to use it for INSERT

[native zenith]

I can't because the docs don't have anything on it

oh waut a min

ohhh

I use the named style

I was confused because it was a select statement and I was looking at the qmark

Same error

sqlite3.OperationalError: near "LIMIT": syntax error```

I changed the = to LIKE and it didn't change anything

[harsh pulsar]

show your code

[native zenith]

Ok according to my research it's because I don't have SQLITE_ENABLE_UPDATE_DELETE_LIMIT enabled

[native zenith]

for?

[harsh pulsar]

whenever you have a new error, show the code that caused the error. otherwise people helping don't know what you did.

[native zenith]

oh ok thanks

here's my current:

(f"DELETE FROM inventories WHERE uid LIKE :uid AND iid LIKE :iid LIMIT 1", {"uid":uid, "iid":iid})```

And I think I have to enable SQLITE_ENABLE_UPDATE_DELETE_LIMIT

Which is enabled on build

but how to enable it in python is a mystery to me

SQLITE_ENABLE_UPDATE_DELETE_LIMIT

    This option enables an optional ORDER BY and LIMIT clause on UPDATE and DELETE statements.

    If this option is defined, then it must also be defined when using the Lemon parser generator tool to generate a parse.c file. Because of this, this option may only be used when the library is built from source, not from the amalgamation or from the collection of pre-packaged C files provided for non-Unix like platforms on the website. ```

docs ^^

But I don't think I can do that

So now I have to figure out how to delete one of many duplicate rows

According to https://stackoverflow.com/questions/16438323/how-can-i-delete-one-of-two-perfectly-identical-rows I can use SET ROWCOUNT

however

("SET ROWCOUNT 1")

causes

sqlite3.OperationalError: near "SET": syntax error```

and

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-rowcount-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

it won't affect DELETE in the future

So how do I delete only one row out of a bunch of exact duplicate rows?

[brazen charm]

sub query probably easiest

but still relies on some sort of combination that makes the row unique

[native zenith]

I figured it out by creating a Primary Key that's a unique ID

and then

SELECT * FROM inventories WHERE uid LIKE :uid AND iid LIKE :iid
/*Gets ID from SELECT statement and fetchone()*/
DELETE FROM inventories WHERE uid LIKE :uid AND iid LIKE :iid AND id LIKE :id```

I'm creating the IDs with the UUID library and uuid.uuid1()

[torn sphinx]

Hello help me not works

error: 40 - Could not open a connection to SQL Serve

Service is running I checked

[harsh pulsar]

How did you check?

Can you view the sql server error logs?

[restive thorn]

is there a way to disable the console log for when the primary key is found?? sqlite3.IntegrityError: UNIQUE constraint failed: MD5.md5 this one here , so it doesn't keep on logging it every time it finds one

[faint blade]

That's an error, you need to capture it with try: ... except: ...

[restive thorn]

try:ws = MD5(file_hash, path, file_ext, ws_type, today)
except:print(error)

like this??

[unkempt prism]

you could be more explicit and only capture the IntegrityError and not print it.

except sqlite3.IntegrityError as ex:
    pass

[restive thorn]

@unkempt prismyea thank you i was trying on the wrong line anyways , instead of the insert line xD

another quick question while we're at it is it possible to use sqlite db in js?

[unkempt prism]

its my understanding that with the introduction of html5 can have its own sqlite db as part of the browser though it won't be the same db you are accessing in python.

Edit: I looked it up it was called websql though thats been depreciated and IndexedDB is the successor.

[restive thorn]

@unkempt prismkk thanks sir , i'm just trying to pull data from it and use it in a google chart but i just found out u could google charts through python so imma keep working with python better 🙂

[graceful raptor]

Should I create a new cursor for every single query or should I just use one for all my queries?

[lean walrus]

one for all queries

[graceful raptor]

Ok thanks

[torn sphinx]

mysql.connector.errors.DatabaseError: 4031 (HY000): The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.

can't find any thing which could configure this setting

[unkempt prism]

It would be more robust to handle the exception and within exception handler reconnect and retry whatever it failed to do.

[torn sphinx]

ahhh

yes

i could make a dummy column

ty

[sharp tundra]

wat is wrong here?

database.execute("ALTER TABLE cryptoowned ADD COLUMN (?)", (arg1,))

sqlite3.OperationalError: near "(": syntax error

[grim vault]

The parameter substitution does not work for table or column names. In this case you'll have to construct the whole SQL-string yourself after making sure that arg1 is what you want.

[ripe matrix]

@commands.command()
    async def check(self, ctx: commands.Context):
    await ctx.send(self.bot.db.fetchrow("SELECT * FROM databasename"))

When I run .check on my bot it just says <coroutine object Pool.fetchrow at 0x7ff125b12440>

This is the connection pool at main.py

bot.db = bot.loop.run_until_complete(asyncpg.create_pool(user="", password="", host="", database=""))

[burnt turret]

Fetchrow is a coroutine

It needs to be awaited

[ripe matrix]

So I separate them? ```py
await self.bot.db.fetchrow("SELECT * FROM databasename")
await ctx.send("what do i put here")

[burnt turret]

sure, but you would want to assign the stuff returned by fetchrow to a variable so that you can send it

[ripe matrix]

I see, gonna find out how to do that

[wise goblet]

You know guys...

This is awesome!

https://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905
reviews are cool

they have similar manga for Cryptography, sounds interesting too

well, and a lot for math. But regretfully I did know about that during my uni time

[weary rivet]

hello .. is there any one tried mongoengine with Mongodb atlas

im having an issue with connecting .. i'd appreciate any help

the issue is about SSL certificate

[jaunty galleon]

#discord-bots message Hope this is what you meant, it's in discord.py channel but might help

[rain island]

Hello, I've been trying to run this, but i'm getting an exception- "Execution failed on sql 'SELECT COUNT() FROM master': no such table: master"
I'm fairly confused, as i've run sqlite through command prompt and the .tables command produces a list that contains master, and replacing master with any of the other tables included doesn't work either

import sqlite3
import pandas as pd
conn = sqlite3.connect("D:\Python\DataAnalysis/m4_survey_data.sqlite") 
print(conn)
cur = conn.cursor()
QUERY = "SELECT COUNT(*) FROM master"
df = pd.read_sql(QUERY,conn)
print(df.head())```

[grim vault]

Do you mean to select from sqlite_master?

[rain island]

no, i don't think so- i'm following a tutorial and master is one of the tables in the db

that's a snip of the .tables response

[grim vault]

Have you tried to quote it: QUERY = 'SELECT COUNT(*) FROM "master"'

[rain island]

i get a slightly different error- it just says 'no such table: master' to that one

[grim vault]

Maybe there is a hidden space?
Try "SELECT * FROM sqlite_master WHERE name NOT LIKE 'sqlite_%' AND type = 'table'"

and check every row.

[rain island]

do i leave in the df = pd read bit?

[grim vault]

Try it in the console like:
SELECT '#' || name || '#' FROM sqlite_master WHERE name NOT LIKE 'sqlite_%' AND type = 'table';

[grim vault]

Strange. Have you tried to execute the sql with sqlite instead of panda?

[rain island]

cur = conn.cursor()
QUERY = "SELECT COUNT(*) FROM 'master'"
cur.execute(QUERY)```

like that?

i get the same response, no such table

[grim vault]

You sure you're using the same database for the python script and the sqlite console?

[rain island]

😅

good shout

[grim vault]

import sqlite3

conn = sqlite3.connect("D:\Python\DataAnalysis/m4_survey_data.sqlite") 
cur = conn.cursor()
QUERY = (
    "SELECT '#' || name || '#'"
     " FROM sqlite_master"
    " WHERE name NOT LIKE 'sqlite_%'"
      " AND type = 'table';"
)

for row in cur.execute(QUERY):
   print(row[0])

[rain island]

i'm an idiot

thank you, and sorry for wasting your time- appreciate you helping

[untold thorn]

hi, i need help to reuse this code(for csv, patern...) and make something like this for parquet files... im new i dont know how to make change...

    try:
        src_filepath = source_dir + file_pattern
        
        log.debug("Looking for Source files {}".format(src_filepath))
        
        log.info("Create dataframe from filepath '" + src_filepath + "'")
        # src_df = spark.read.csv(src_filepath, sep='\t').withColumn('file_date', to_date(regexp_extract(regexp_replace(input_file_name(),'.tsv.gz',''), '([0-9]{4}-[0-9]{2}-[0-9]{2})',1),'yyyy-MM-dd'))\
                                                        .withColumn('file_name', regexp_replace(regexp_replace(input_file_name(),'.tsv.gz',''), source_dir, ''))
​
        return src_df


#read parquet, new patern???
src_df = spark.read.parquet(src_filepath)
print_this("src_df",src_df)
# time.sleep(60)

[restive thorn]

Hello , so i'm using an sqlite3 db on my website and i want to get the number of occurences of a certain column so i used this c.execute("SELECT COUNT(*) FROM MD5 WHERE language=?", ('pl',)) but i'm getting values like this '[(34,)]' , how can i only get the 34 instead of all that pls?

[proven arrow]

Use fetchone(), which gives you a single tuple back. Then just get the first element of that tuple by index.

[weary rivet]

i did it but same prob

[restive thorn]

@proven arrowoh thanks that was it 😄

[restive thorn]

is there something like "INSERT OR IGNORE" but for "SELECT"??

[pure cypress]

What would that do for a select statement? What kind of errors are you trying to ignore?

[restive thorn]

in the website i have a google pie chart that uses data sent back from flask

but if the db is empty i get this error 'sqlite3.OperationalError: no such table: MD5' bcs the table isn't created yet

[proven arrow]

Knowing if data exists in a table is different to knowing if the table exists

The only way is to either catch the error or check in advance if the table exists. But generally the table should already exist in your app.

Why doesn’t it exist?

[restive thorn]

i think i got a fix imma create the tables here @app.route("/") def home():

[proven arrow]

Not the best idea.

[restive thorn]

i'm creating those table in a post method

what's the best way to init a table if it doesn't exists?? currently i'm doing it manually by commenting and uncommenting the CREATE everytime i delete the db file

[pure cypress]

Depends on which SQL server you use. There are ways to check if the table exists.

For example, with sqlite it's just CREATE TABLE IF NOT EXISTS

So you'd be able to try to create your schema every time the app starts, but do nothing if it's all set up already.

[restive thorn]

    c.execute("""CREATE TABLE IF NOT EXISTS VLD (
                vld text PRIMARY KEY,
                link text,
                language text,
                type text,
                date integer
                )""")

like this?

yea that worked , thanks a lot brother 🙂

[ripe matrix]

Do I need the "let" module to assign a variable to what fetchrow returns?

[proven arrow]

Can you be more clear? What database/ code do you have, and what your trying to achieve

[ripe matrix]

@commands.command()
    async def check(self, ctx: commands.Context):
    await self.bot.db.fetchrow("SELECT * FROM dbname")

Using postgres, asyncpg. After await self....fetchrow apparently I need to assign a variable to whatever it returns but I have no idea how to do that

[proven arrow]

Assigning variables is basic stuff in anything. Not sure what you find confusing. See the basic usage for the module here which gives an example, https://magicstack.github.io/asyncpg/current/usage.html

[limber bramble]

Can anyone pls help me how to display data from google sheet to r shiny dashboard

[tight marten]

anybody have experiences in MongoDB and MySQL

[buoyant marsh]

Hi. Can I somehow count every row's value together in mysql?

[proven arrow]

Use SUM(), https://www.mysqltutorial.org/mysql-sum/

[buoyant marsh]

thank you! 🙂

[torn sphinx]

how do i make a python database for my discord bot

i need help

[humble hamlet]

I do in MongoDB

[humble hamlet]

Try https://cloud.mongodb.com

[harsh pulsar]

Sqlite

Don't use mongodb

[jaunty galleon]

Why sqlite? Why not go to PostgreSQL?

[pure cypress]

Because that's a lot more to configure and manage. It's also more complicated to deploy.

Better to start simple, unless you know ahead of time you will need to scale up.

[native bolt]

Any way to create a website with "branching" associations - for example, when you click one 'node' it branches to show other related nodes?

[rustic geyser]

if i store my user's content in my database and want to host my bot somewhere then how's that possible? the database content will get deleted

[tight marten]

I am gonna be a full stack developer, do you recommend it or I should stick with MySQL

[compact oracle]

how to export data in microsoft sql server

can you help me ?

[humble hamlet]

That's basically your choice. I prefer mongodb as it uses BSON and is easier to use for me. Each collection offers 512 MB data for free and then charges you...

[ocean basin]

hey, can someone help me develop a database for my discord bot?

[proven arrow]

People would help when you actually ask the questions.

[ocean basin]

well i just want help getting it set up

im using postgresql

and i want to be able to change prefixes, get warns for warned users, aswell as get banned users

yk?

[proven arrow]

Ok

Help in getting what setup, the actual Postgres installation setup or database and table setup for your program?

[ocean basin]

database and table setup for my program

this is what ive got so far

import discord
import os
from discord.ext import commands
import asyncio
import asyncpg


async def run():
    conn = await asyncpg.connect(user='postgres', password='pw',
                                 database='data', host='localhost')
    values = await conn.fetch(
        'SELECT * FROM mytable WHERE id = $1',
        10,
    )
    await conn.close()

loop = asyncio.get_event_loop()
loop.run_until_complete(run())


client = commands.AutoShardedBot(command_prefix="~")


@client.command()
@commands.is_owner()
async def load(ctx, extension):
    client.load_extension(f'cogs.{extension}')

@client.command()
@commands.is_owner()
async def unload(ctx, extension):
    client.unload_extension(f'cogs.{extension}')




for filename in os.listdir('./cogs'):
    if filename.endswith('.py'):
        client.load_extension(f'cogs.{filename[:-3]}')

client.run('.')

[proven arrow]

Do you know how to use asyncpg to execute statements for create, insert ?

[ocean basin]

nope, im new to all of this

[proven arrow]

And what about creating tables and insert/update/delete using just SQL?

[ocean basin]

nope

[proven arrow]

Ooh okay

[ocean basin]

yeah

sorry haha

[proven arrow]

So what’s your plan then? Do you want to learn how to do these things or just get given the code for it to work ?

[tight marten]

thankssss

[ocean basin]

yk, typically i would want to be spoonfed, and given code for it to work, but i actually wanna learn how these things work

so i would like to learn how it works

[proven arrow]

Ok that’s good choice. Well it’s too much for me to explain in chat unless you have a specific question then someone can answer. However, there’s plenty of good resources online that will make it easy for you. You just need to know the basics on how to create tables (primary key, foreign key, different data types), making relationships between tables, how to insert, update, and delete.
W3 schools is good intro to all this. The tutorial covers more than you’ll need but you just need to go over the basics to get it working. https://www.w3schools.com/sql/

[ocean basin]

the main problem is it wont connect

[proven arrow]

Well you can worry about that later.

[ocean basin]

ok, ill go learn rn

[ripe matrix]

How do I make it so that the user input from a discord bot is the column searched? For example:
!a (column name) results in SELECT column name FROM table name
Using discord.py, asyncpg

@commands.command()
    async def a(self, ctx: commands.Context, *, columnname: str):
    b = await self.bot.db.fetchrow("SELECT {columname} FROM tablename")
    await ctx.send(b)
```This is my current code

[jaunty galleon]

Don't use f-string in sql query

Since it's PostgreSQL(asyncpg), you should use it's placeholder, ($1)

($2) and so on

[ionic pecan]

you can't use placeholders for column names as far as i'm aware. the sane way to go about this is to have a whitelist of columns that the user can search, check that the argument is in the whitelist, and then format the argument into the query. that way, the user can't input arbitrary sql, which is what happens if you don't check the input argument. you could build a discord.py converter for it

[ripe matrix]

I see, I'm gonna look into this

Thank you :)

[ionic pecan]

no problem! just ask if you encounter any other issues

[sonic urchin]

I recently started an internship and the task in hand rn is to anonymize the database. What we are trying to do is that code goes through the csv/sql db and suggests user what anonymization technique could be used on what column, and then that anonymization is to be applied.
Any libraries that could be of use?

[quasi dawn]

Hmm whenever i try to open psql, i get

psql: error: could not connect to server: FATAL:  the database system is starting up
Press any key to continue . . .

any reason why? i even tried restarting pc

[wise goblet]

is there any point to be not having 3d form normalized tables?

[proven arrow]

Mostly depends on what problem your working on. But main reasons are performance, you dont want extra complexity, you don’t need integrity.

[snow iron]

Check tools for data masking.

I have used Informatica long back not aware of any library in python

https://www.informatica.com/products/data-security/data-masking.html

[snow iron]

For OLTP system - No
For OLAP System - Yes

[sonic urchin]

Thanks! I'll look into it

[surreal carbon]

How to use the % format in MySQL, I can't find the docs right now
await cur.execute('SELECT * FROM %s WHERE Name=%s', ('city', 'Bangkok',))

[brave bridge]

You can't substitute the table name via normal substitution, only data. You should either keep a whitelist of table names and very carefully format it into the query, or just find a better way.

Why do you need to dynamically substitute the table name?

[surreal carbon]

I just test it (learn) how to use the % if I have more data to find like Insert into

[brave bridge]

You can substitute data (the stuff that's usually dynamic, the values that you insert or filter by), but not table names.

I don't know why that's the case with the library you're using, but with some of them it's because the database engine (Postgres/MySQL/...) is the one doing actual substitution and is caching the compiled queries. Or something like that.

[surreal carbon]

Thank you very much I got it. I use MySQL

[worldly panther]

Hi. What's the best way to keep a view updated? I load a view with some data from the model, but the model gets constantly updated and the view doesn't reflect the new changes, only the ones added the first time. I can use threading but how could I do it?

[wise goblet]

Make the data output ed as json
And render view with javascript that fetches the data from time to find

[ebon skiff]

How can i save a dict to postgresql?
When i fetch the dict when datatype TEXT it returns as string.

[wise goblet]

You should consider saving dicts to mongodb

Or properly normalizing your data

As a worst, last resort chance, you can turn dict to json in order to store it in a char field in postrgres, but it is not cool.

[ebon skiff]

I don't really want to use mongodb just for one dict

But also adding a lot of rows seems silly

string to dict is my solution for this

[still whale]

Try using type JSON in postgresql

It's a built in type that should accept and automatically serialize python lists/dicts. Awesome.

[harsh pulsar]

jsonb in postgres is great

[blissful knot]

can anyone here point me to a tutorial on how to use geometric data types in postgresql? (Particularly open path type)

[still whale]

Excellent support for this. https://postgis.net/
https://computingforgeeks.com/how-to-install-postgis-on-ubuntu-debian/

[blissful knot]

Thanks.

[snow iron]

Check if you using materialised view. View data should always reflect updated data

[digital heron]

Traceback (most recent call last):
line 111, in SearchWithId
print("result"+text)
TypeError: can only concatenate str (not "Query") to str

Would Any One Know Why?

This Is Sqlalchemy

[unkempt prism]

See in the docs https://docs.sqlalchemy.org/en/14/orm/tutorial.html#querying that its either want to be looping or get first or all

[tepid ferry]

Try
,,, py
print("result"+str(text))
''"
in your code's line 111

[celest rain]

heyo, what would you guys say is the best database to use?

[torn sphinx]

Help me

[icy stream]

it depends what you're doing really;
for a small project/an introduction to databases something like SQLite is good
for storing json-like data something like mongodb is suitable, though doesn't scale well if you're using it outside of the circumstances it makes sense
for larger projects industry level databases like MySQL, MariaDB, and PostreSQL are good choices, as they have a lot of features and throughput

[unkempt prism]

I tried this it printed the sql query.

[gentle warren]

unable to create database file , I have been following many youtube tutorials on Flask, but everytime I am getting an error in creating database. (ubuntu==20,python ==3.8,Flask 2.0.1, Flask-SQLAlchemy 2.5.1)

`from flask import Flask ,render_template
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

app = Flask(name)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:////todo.db"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
db = SQLAlchemy(app)

class Todo(db.Model):
sno = db.Column(db.Integer,primary_key=True)
title = db.Column(db.String(200),nullable=False)
desc = db.Column(db.String(500),nullable=False)
date_created = db.Column(db.DateTime,nullable=False,default =datetime.utcnow)

def __repr__(self) -> str:
    return f"{self.sno} {self.title}"

u/app.route("/")
def HomePage():
return render_template("index.html")

if name == "main":
app.run(debug=True)`

• to initiate the database i am opening python console and

db.create_all( )
.db file is not being created and the error i am getting

return self.dbapi.connect(*cargs, **cparams) sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to open database file (Background on this error at: http://sqlalche.me/e/14/e3q8)

[unkempt prism]

What do you call this python file?

[gentle warren]

app.py

[grim vault]

"sqlite:////todo.db" Thats one slash to much. You try to open /todo.db and don't have permission for the root path I guess.

[unkempt prism]

so you

from app import db
db.create_all()

[gentle warren]

yes

[gentle warren]

i tried

app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///todo.db"

getting error
return self.dbapi.connect(*cargs, **cparams) sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to open database file (Background on this error at: http://sqlalche.me/e/14/e3q8)

[grim vault]

Have you tried an absolute path where you know to have write permission. I don't know where your app will try to create the db file. eg (for linux) "sqlite:////tmp/todo.db"

[gentle warren]

let me try

i tried
"sqlite:////tmp/todo.db"
no error erupted but .db file is not created

[zealous vale]

How do you select all rows with a certain column true in SqlAlchemy ORM?

I am doing ```python
db.query(Table)
.filter(Table.column == True_())
.all()

Which works but I dont know. I just feel there might be better syntax

This also seems to work ```python
db.query(Table)
.filter(Table.column == 'true')
.all()

[dusk dagger]

Hello, have you try sqlalchemy async session ?

I submit a discussion but no one answer it yet.
https://github.com/sqlalchemy/sqlalchemy/discussions/6630

The following code is a demo of how I select a row and update it.
But I do think it is not straightforward for me. (Although it works.)

Is this the right to way to update a row?
Since select query and update statement are executed seperately, how to make sure both work on the same row?

And I found I don't need to db.resfresh that object, it will return the updated one, and I don't know why ...
( It may be purposely designed this way?)

Hope someone can help explain this.

async def modify(
        self,
        db: AsyncSession,
        user_id: str,
        payload: Union[UserProfile, UserPersonProfile],
    ) -> UserProfileModel:
        """modify user profile partially"""

       # Normally when we try to update a row, we will find out if this row exists
        query = sa.select(UserProfileModel).where(UserProfileModel.id == user_id)
        result = await db.execute(query)
        profile = result.scalars().first()
        
        # if not, raise exception
        if not profile:
            raise HTTPException(400, detail=["profile not found"])
        
        # here is my question
        stmt = (
            sa.update(UserProfileModel)
            .where(UserProfileModel.id == user_id)
            .values(**payload.dict())
        )
        result = await db.execute(stmt)
        
        # why I don't need this?
        # db.refresh(profile)

        return profile

[unkempt prism]

What OS are you on

ls -lat /tmp/todo.db
-rw-r--r--  1 d  wheel  16384 14 Jun 20:11 /tmp/todo.db

[gentle warren]

ubuntu 20

[pale cipher]

hello i need help about mysql database for datatable

in datatable <DEMO> this type of name not showing

[rustic geyser]

```py
@client.command()
async def configure(ctx, channel: discord.TextChannel):
    db = sqlite3.connect('database.sqlite')
    cursor = db.cursor()

    cursor.execute("SELECT channel_id FROM database WHERE guild_id = ?", (ctx.guild.id,))
    result = cursor.fetchone()
    if result is None:
        sql = ("INSERT INTO database(guild_id, channel_id) VALUES(?,?)")
        val = (ctx.guild.id, channel.id)
    elif result is not None:
        sql = ("UPDATE database SET channel_id ? WHERE guild_id = ?")
        val = (channel.id, ctx.guild.id)
    cursor.execute(sql,val)
    db.commit()
    cursor.close()
    db.close()

    #server_id = ctx.guild.id
    #ai_chat_servers[server_id] = channel.id
    await ctx.send(f'AI Chat is now set to {channel}')


@client.event
async def on_message(message):

    await client.process_commands(message)

    if client.user == message.author:
        return

    if message.content is None:
        return

    db = sqlite3.connect('database.sqlite')
    cursor = db.cursor()
    cursor.execute("SELECT channel_id FROM database WHERE guild_id = ?", (message.guild.id,))
    result = cursor.fetchone()
    if result is None:
        return

    if message.channel.id == int(result[0]):
        response = await rs.get_ai_response(message=message.content)
        await message.reply(response.message)

Help me if anyone knows database
I expected the bot to change the ai chat channel when i use *configure command again if the channel is ALREADY SET```

ill paste if u want^^

[gaunt surge]

Hello, how to update a nested element if exists (or insert if doesn't exists)? with pymongo

find = {"accountId": accountId}
        post = {"$set": {"aux": 
                                    {"loadouts": {f"{loadoutTag}": {
                                    "value1": value1, 
                                      "value2": value2, 
                                        "value3": value3, 
                                        "value4": value4, 
     }}
}}}

        doExist = collection.find(find)
        if doExist:
            collection.update(find, post, upsert=True)
            msg = "loadout saved"
            return msg

database looks something like this:

accountId: "12341234124124"
aux:
  daily: true

but when I run the code, daily will disappear and replaced with the new data I want to insert.

[grim vault]

You are missing the equal sign after channel_id in:

sql = ("UPDATE database SET channel_id ? WHERE guild_id = ?")```

[rustic geyser]

ohhhhhhhhhhh

TYSM

u always help me

lets see if it works then

works

tysm

[jade swan]

The balance in this code gets updated :

new_bal = data["balance"] - price
users.update_one(data, {"$set": {"balance": new_bal}})
    data1 = users.find_one({"_id": user_id})
    users.update_one(data1, {"$set": {"level": new_lvl}})

While here it does not :

new_bal = data["balance"] - price

            users.update_one(data, {"$set": {"balance": new_bal}})
            data1 = users.find_one({"_id": user_id})
            users.update_one(data1, {"$set": {"employees": ems}})

I don't get any errors and when I tried to add a print function under the second one that prints "updated bal" it prints, while it does not update. I also made it return after it updates the bal, it passes it and does not update it. While also the employees get updated.

[pallid shard]

how too add reason in json but not using append'

users[str(member.id)]["author"] += author``` This not work

o i fix it

how to save time in json

{"687597629387177994": {"lista": 1, "reason": "Nie podano powodu", "author": 687597629387177994, "czas": ```

users[str(member.id)]["czas"] = ctx.message.created_at ```

@client.command()
async def blacklist(ctx,member: discord.Member=None ,*, reason="Nie podano powodu"):
    await open_blacklist(member)


    author = ctx.author.id
    author = int(author)




    users = await get_blacklist_data()


    users[str(member.id)]["reason"] = reason
    users[str(member.id)]["author"] = author
    users[str(member.id)]["lista"] += 1
    users[str(member.id)]["czas"] = ctx.message.created_at 

    
    print(users)

    reasonV2 = users[str(member.id)]["reason"]
    author = users[str(member.id)]["author"]
    lista = users[str(member.id)]["lista"]
    emer = discord.Embed(title="Ten użytkownik już jest na liście!!",description=f"",colour=discord.Color.dark_red(),timestamp= datetime.datetime.now() -datetime.timedelta(hours=2))
    emer.add_field(name="**Administrator:**",value=author,inline=False)
    emer.add_field(name=f"**Powód:**", value=reasonV2,inline=False)
    emer.set_author(name=member,icon_url= member.avatar_url)
    

    if lista>1:
        await ctx.send(embed=emer)
        return

    with open("blacklist.json",'w') as f:
        json.dump(users,f)




async def open_blacklist(member: discord.Member):





    users = await get_blacklist_data()
    print(users)
    if str(member.id) in users:
        return False
    else:
        print(users)

        users[member.id] = {}
        users[member.id]["lista"] = 0
        users[member.id]["reason"] = {}
        users[member.id]["author"] = {}
        users[member.id]["czas"] = {}
        
    
    with open("blacklist.json", "w") as jsonfile:
        json.dump(users, jsonfile)

async def get_blacklist_data():
    with open('blacklist.json','r') as f:
        users = json.load(f)

    return users```

all code

[wise goblet]

thanks. it looks like I am having purely OLTP systems... even if I am using auto commit queries instead of transactions.

so... no point for me to have not 3d form normalized data

[harsh pulsar]

show what options you are using to connect to the server

[grim vault]

I was thinking of a cube (3d form) until I realised the missing r ;)

[little pumice]

hi

hello, what is the datatype of emails like: [e-mail], in sqlite3

[grim vault]

TEXT

[little pumice]

i tested that

not working

Traceback (most recent call last):
File "C:\Users\CHAMSSO\PycharmProjects\SQLite3\main.py", line 6, in <module>
c.execute("INSERT INTO userinfo VALUES(admin, admin, [e-mail], FALSE, 15)")
sqlite3.OperationalError: near "@<gmail": syntax error

[grim vault]

Well, use binding variables then it will work.

[little pumice]

!codeblock

import sqlite3

conn = sqlite3.connect('database.db')
c = conn.cursor()

c.execute("""CREATE TABLE IF NOT EXISTS userinfo(username TEXT,password TEXT,email TEXT,banned TEXT,points INT)""")
conn.commit()
c.close()
conn.close()

print('Finished')

this is my code of creating database

[grim vault]

looks good, your insert is bogus.

[little pumice]

and how to fix it

[grim vault]

show code

[little pumice]

import sqlite3

conn = sqlite3.connect('database.db')
c = conn.cursor()

c.execute("INSERT INTO userinfo VALUES(admin, admin, [e-mail], FALSE, 15)")
conn.commit()
c.close()
conn.close()

print('Finished')

oh my email

[grim vault]

c.execute("INSERT INTO userinfo VALUES(?,?,?,?,?)", ("admin", "OBIuQm%swqaS7W!m", "[e-mail]", FALSE, 15))```

[little pumice]

ok lemme test

oh

that worked

ty

[grim vault]

Always use ? binding for data values.

[little pumice]

ok this a good hint

[grim vault]

That's one of the MAJOR rules.

[little pumice]

@grim vault

for example i have a login form

the user typed : admin

how can i check for the password of 'admin'

[grim vault]

I'm not touching that. I will only say that you should not save the plain password in the database. I'm sure there are some blogs/tutorials about security somewhere.

[little pumice]

i got it

with some tricks

from my mind 😂

!code

[delicate field]

Here's how to format Python code on Discord:

```py
print('Hello world!')
```

These are backticks, not quotes. Check this out if you can't find the backtick key.

[little pumice]

import sqlite3

conn = sqlite3.connect('database.db')
c = conn.cursor()


c.execute("SELECT * FROM userinfo ORDER BY points LIMIT 0, 1000000")
result = c.fetchall()
username = input()

for piece in result:
    if username in piece:
        print(piece[2])
    else:
        None



conn.commit()
c.close()
conn.close()

print('Finished')

[harsh pulsar]

import sqlite3

username = input()

conn = sqlite3.connect('database.db')
try:
    curs = conn.cursor()
    curs.execute(
        "SELECT * FROM userinfo WHERE username = ? ORDER BY points LIMIT 0, 1000000",
        (username,)
    )
    for row in curs:
        print(row[2])
finally:
    conn.close()

print('Finished')

@little pumice what about something like this?

[smoky tartan]

this is my statistics code which prints the success rates for my KNN code. it works great- without line 4 that is. it works in reasonable time, it prints numbers which make sense and in general it works well. However with line 4 the code still works in reasonable time but does not print anything- it doesnt give an error or anything but just doesnt print anything. I tried all my funcs (KNN, stats without normalisation and the normalisation itself) and they all work well. Any ideas why?

i'm convinced the error is related to line 4 because without it everything works well.

[little pumice]

thx

[harsh pulsar]

@smoky tartan this might be better for #data-science-and-ml

[smoky tartan]

thanks, i'll send it there

sorry

[harsh pulsar]

also please post your code as text, not a screenshot

!code

[delicate field]

Here's how to format Python code on Discord:

```py
print('Hello world!')
```

These are backticks, not quotes. Check this out if you can't find the backtick key.

[torn sphinx]

in MySQL, is it normal that transaction rollback doesn't rollback truncate of a table?

[harsh pulsar]

is that the correct server name @torn sphinx ?

[torn sphinx]

yes

[harsh pulsar]

@torn sphinx did you try "Get instance name from Configuration Manger" and "Verify - the instance is running" ?
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/troubleshoot-connecting-to-the-sql-server-database-engine?view=sql-server-ver15

[torn sphinx]

also not helped

hey, when i run my discord.py bot it gives this error

https://meme.com/Java#1500||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​|| https://kurwa.club/u/Xqc3p.png

i think its a mongo thing

my connection string is @siriuscuster.9zcgd.mongodb.net/sirius?retryWrites=true&w=majority

https://meme.com/Java#1500||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​||||​|| https://kurwa.club/u/OpVXr.png

this is all the names and stuff, did i format the connection thing properly or is it a different reason to why its not working

[brazen charm]

Postgres

[sudden ivy]

mysql

[oak carbon]

i have a schema called data and i have 2 tables in data (guild, user)
so what i have to do is i have to fetch all table records from data where user_id = 12345 in the tables, how can i do it?

[river ice]

Not sure , which sql db are you using? @oak carbon

[oak carbon]

postgres

[river ice]

sorry for no reply . I haven't used Postgres schemas . But this question sohuld get more attention in a help channel #❓｜how-to-get-help I'm interested to know aswell

And haven't used schemas in general . not sure how they differ and i cant get into it right now

[jaunty galleon]

users_data = await conn.fetch('''SELECT * FROM table_name WHERE user_id = ($1)''', 12345)```
That?

[oak carbon]

no like i want to get all table data not from an specific one

[jaunty galleon]

All table data?

just SELECT * FROM table_name

[oak carbon]

no wait let me explain you @jaunty galleon

i have a schema called data and imagine there are 2 tables in that schema user, guild and i want to fetch all the records from user and guild tables where user_id=12345 with only 1 query

[grim vault]

select * from user, guild where user.user_id = guild.user_id and user.user_id = 12345```

You might get double entries.

[oak carbon]

but there are many tables

so i have to write it manually? the query

[grim vault]

Why exactly do you need that. Also if there are multiple entries in one table you'll get the product.

[oak carbon]

yh i want to filter that only to get all data from all the tables in the schema where user_id=12345

[river ice]

#help-bread

about that isseu

[wise goblet]

MariaDB would be a better choice than MySql
PostgreSQL would be a better choice than MariaDB

[uneven stream]

wts the sqlite datatype for python datetime object?

[wise goblet]

the easiest choice would be storing in regular charfield.
with storing as..

[delicate field]

@wise goblet :white_check_mark: Your eval job has completed with return code 0.

001 | 2021-06-15T08:07:27.405342
002 | <class 'str'>
003 | 2021-06-15 08:07:27.405342
004 | <class 'datetime.datetime'>

[uneven stream]

why is it different each time it prints?

[wise goblet]

!e

import datetime
normal = datetime.datetime.utcnow()
print(normal)
print(type(normal))
chared = normal.isoformat()
print(chared)
print(type(chared))
dechared = datetime.datetime.fromisoformat(chared)
print(dechared)
print(type(dechared))

[delicate field]

@wise goblet :white_check_mark: Your eval job has completed with return code 0.

001 | 2021-06-15 08:09:55.957817
002 | <class 'datetime.datetime'>
003 | 2021-06-15T08:09:55.957817
004 | <class 'str'>
005 | 2021-06-15 08:09:55.957817
006 | <class 'datetime.datetime'>

[wise goblet]

first field shows you as date time

then after it got turned to str to be stored

and then I showed you how to turn back to datetime from str

[uneven stream]

why is the T added?

[wise goblet]

I don't see T

ah, I got it

this is Isoformat

how datetime is represented in its... sort of jsonified way

[uneven stream]

o

so I store it as```sql
datetime TEXT NOT NULL

[wise goblet]

whatever, regular charfield with restriction 32 chars should be enough

[uneven stream]

o

thanks!

[uneven stream]

uhh, I need to order datetime, would it work?

[wise goblet]

well, it should not be a problem

just order by as regular field

[uneven stream]

wdym how would it order datetime by string?

[wise goblet]

2021-06-15T08:09:55.957817

it is already in an order of YEAR, MONTH, DAY, HOUR, MONUTES, SECONDS

ordering as string, you order by date time

[uneven stream]

lemme see

thanks!

[faint blade]

I think it's TIMESTAMP?

At least for PostgreSQL you can do for example datetime TIMESTAMP NOT NULL DEFAULT (NOW() AS TIME ZONE 'utc')

[calm prawn]

in sqlite3 how do I search by a single name?
I mean if I have a row named NAME and theres a value called Bartick Maiti. How do I do a query that if I write Bartick it will give the result

or if I do Maiti it will also give the result

[uneven stream]

o

[proven arrow]

Sqlite doesn’t have a type for dates or datetime

[uneven stream]

thanks!

[uneven stream]

it worked right now

[proven arrow]

Because it uses a dynamic type system.

You can put any random letters as the data type and it would accept it

[uneven stream]

?

lemme see

[proven arrow]

But there is no storage class for it

[faint blade]

Ah, see 2.2 https://www.sqlite.org/datatype3.html

[uneven stream]

oh

[proven arrow]

You can use LIKE pattern matching

[faint blade]

So you could use INTEGER and then https://docs.python.org/3/library/datetime.html#datetime.datetime.fromtimestamp

[calm prawn]

the problem I am facing is. I don't know if its in starting or in middle.
So when i do %Bartick% it gives me the desire output but if I do %B% it also return me the same result which I don't want. Is there a way to remove that

or do I have to loop though checking the spellings?

[proven arrow]

Then use regex

[calm prawn]

regex? How

I mean how to use regex in query?

[proven arrow]

Or if you don’t want people searching for words with a few letters then only make the query after x amount of characters.

[uneven stream]

    time = dt.datetime.fromtimestamp(resp[3])
TypeError: an integer is required (got type str)
```?
how do I convert that to datetime?

[grim vault]

WHERE name LIKE '%Bartick%' will find it anywhere in the string. % is the * in SQL-LIKE.

[faint blade]

Well for whatever reason resp[3] is a string. Can you print it?

[uneven stream]

its what I inserted into the table as datetime object

[grim vault]

datetime.fromtimestamp() needs the seconds since the unixepoch.

[uneven stream]

2021-06-15 09:30:58.884291

[proven arrow]

They already have this

[grim vault]

Ah, didn't see that post.

[faint blade]

In that case, use https://docs.python.org/3/library/datetime.html#datetime.datetime.strptime

[uneven stream]

do I use datetime.strptime?

oh

[faint blade]

Yes

[uneven stream]

thanks!

[grim vault]

You can use multible conditions? where name like '%B%' and name not like '%Bartick%'

[vestal pine]

.

[ripe dove]

pandas question: How do I write foo to row 12 of the column whose row 1 value is "bar"?

[modest ledge]

How do we use order by in sql with the value of two columns combined

[gaunt garden]

Anyone have any good resources for learning mongoengine. Im trying to do a commissioned bot and the client needs data stored in two different collections the issue im having is although i found the docs there haven't been many great resources that i can find that work well for beginners with MongoDB.

What im trying to learn:

• How to set a default connection (don't know why this has been an issue, i defined the DB in the DATABASE_URI)

• switch collections properly, for different document insertions/queries.

• overall making a helper script to make inserting and querying data multiple times across the bot easier.

Thanks in advance for any help/resources!

[proven arrow]

Since order by is last in logic, you can just do ORDER BY (first_column + second_column)

[modest ledge]

ye i figured it myself later lmao

thanks tho

[torn sphinx]

Is postgres good?

[jaunty galleon]

Yes

For async version, I would recommend using asyncpg

[sharp lily]

hello guys ,how are u?

can u help me to find a data for a project of social network analysis

ans sorry for my bad English

[harsh pulsar]

the database name in the database uri has to do with user authentication, it's not the database you're connecting to. you can put a variable in your code with the names of the default db and collection

[gaunt garden]

Ah, alright. Thanks

[harsh pulsar]

this might be more on-topic in #data-science-and-ml . as for data, https://networkdata.ics.uci.edu/resources.php https://sites.google.com/site/ucinetsoftware/datasets

[sharp lily]

thanks u my friend

[tropic dagger]

Hello, I'm trying to use vanilla SQLAlchemy (no Flask extension or anything) and I was wondering how you could create table like these:

from sqlalchemy.orm import relationship
class User(Base):
    __tablename__ = 'user_account'

    id = Column(Integer, primary_key=True)
    name = Column(String(30))
    fullname = Column(String)

    addresses = relationship("Address", back_populates="user")

    def __repr__(self):
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

class Address(Base):
    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('user_account.id'))

    user = relationship("User", back_populates="addresses")

    def __repr__(self):
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"

in a single row in the database. I understood somewhere along the way you needed a session to modify these, but for the creation itself, I have no idea.

[harsh pulsar]

what do you mean by "in a single row"? rows are inside tables

[tropic dagger]

What I mean by that is creating every table in one command

[harsh pulsar]

Base.metadata.create_all(engine)?

[tropic dagger]

It worked. Ty!

[stray fulcrum]

Does anyone know how you would back-up a MONGODB database?

Every now and then

[harsh pulsar]

@stray fulcrum mongoexport is one option

[thorny field]

is JSON a good database for a bot on multiple server?

[cerulean jackal]

depends on amount of info. I have a bot that only writes/reads info about which server has which channel assigned for statistics notifications (which get automatically posted from time to time). For that small amount of data, json fits perfectly.

But if you need many entries from many servers - you may look onto sqlite

speakin of devil

[thorny field]

ok, i am using sqlite now, how do i add a new row? do i run SQL code in cursor.execute()?

[cerulean jackal]

ye, basically

https://www.pythoncentral.io/introduction-to-sqlite-in-python/ this was kinda good entry point article

(I used it by myself)

[thorny field]

btw is there a software that runs your bot 24/7 with a sqlite database?

[cerulean jackal]

wdym "software that runs bot"?

[thorny field]

hosting service(github, heroku)

Do python variables work in cursor.execute()?
Code:

user_id = str(ctx.message.author.id)
    guild_id = str(ctx.message.guild.id)
    cursor.execute('''
        INSERT INTO userdata (author,money,mango,megas,ele,soul,givida,pm,wm,cd,gs,f,guild)
        VALUES(str(user_id),4000,0,0,0,0,0,0,0,0,0,0,str(guild_id));
        ''')

[cerulean jackal]

better to ask on discord.py (if we are talking discord bots) discord about it.

I've used heroku, but it restarts daily (thus some saving-related things should be written accordingly) and only runs like 23 days per month, unless you attach your credit card. I've heard about people using free amazon aws, but didnt try it by myself. Personally I've found it easiest to just assign some space on digitalocean vps, in case you already pay for something like that

[cerulean jackal]

dont do that

[thorny field]

then what do i do?

[thorny field]

uhh soo for my case what is the new code?

[cerulean jackal]

replace "VALUES(str(user_id), 4000, blablabla" with "VALUES(?, ?,?)" (one ? for each variable). Then put actual variables after you close quotes

[thorny field]

wdym?

[cerulean jackal]

user_id = str(ctx.message.author.id)
    guild_id = str(ctx.message.guild.id)
    cursor.execute(
    '''INSERT INTO userdata (author,money,mango,megas,ele,soul,givida,pm,wm,cd,gs,f,guild)
    VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)''', (str(user_id),4000,0,0,0,0,0,0,0,0,0,0,str(guild_id))
)

something among these lines

because if you will insert python strings directly - receiving some malicious sql command as string will execute it

[thorny field]

OK THX!!!!!!!!

@cerulean jackal srry for the ping, but is there a way to check if a certan value is in the database already?

[thorny field]

Can someone help me in #help-ramen ????

[jaunty galleon]

Anyone knows what data I need to put in asyncpg.create_pool to run it locally?

I forgot

[harsh pulsar]

@jaunty galleon whatever the local address of the postgres server is, often (but not always) something like localhost:5432

[tropic dagger]

Has anyone tried to use SQlAlchemy's fields LargeBinary and PickleType? I wanted to know if I could store images in dbs under the form of NP arrays I could pickle up

[harsh pulsar]

i'm not sure pickling a numpy array is the most efficient way to store it @tropic dagger

[jaunty galleon]

I think I also need username and database?(whic ate the same?)

[tropic dagger]

Even when the imgs are under 200x200 px?

I'm not sure what's the best strategy here if the img is kinda small: keeping it in a file system and the db is a way to reference it or store everything in the db

[harsh pulsar]

the real problem with pickle specifically is that you an have problems unpickling across python versions

among others

[tropic dagger]

oh crap... unless everything runs on Docker...

[harsh pulsar]

you could np.save to a BytesIO then write the bytes to the db

[jaunty galleon]

What if my host don't supply it? But they do supply SUPERUSER PASSWORD, POSTGRES DATABASE, POSTGRES USER and DATABASE PASSWORD

[harsh pulsar]

host? if it's not on your computer, it's not "local"

[jaunty galleon]

But it doesn't run on a service

Like elephantsql

[harsh pulsar]

what do you mean? either it's running on your computer or someone else's

[thorny field]

How can I see if a specific piece of data is in the sqlite database?

[tropic dagger]

like a table?

[jaunty galleon]

It runs on the host's

[thorny field]

yes

[jaunty galleon]

But from what I got from them, is it possible to connect that way?

[tropic dagger]

sqlite> .tables in the sqlite console

[thorny field]

wait

[tropic dagger]

With sqlite3 you'll probably need to execute ".tables" with your cursor

[thorny field]

@bot.command()
async def register(ctx):
    user_id = str(ctx.message.author.id)
    guild_id = str(ctx.message.guild.id)
    cursor.execute(
        '''INSERT INTO main(author,money,mango,megas,ele,soul,givida,pm,wm,cd,gs,f,guild)
        VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)''', (str(user_id),4000,0,0,0,0,0,0,0,0,0,0,str(guild_id))
    )
    db.commit()
    """
    r = open('user.csv', 'r')
    content = r.read()
    if (user_id not in content): 
        with open('user.csv', 'a', newline='') as a: 
            a.write(user_id + "," + "4000" + "," + "0,0,0,0,0,0,0,0,0,0" + "," + guild_id + "\n")
        await ctx.send("Account successfully register and saved!")
    else:
        await ctx.send("You are already registered.")
    r.close()
    """
    await ctx.send("Account successfully register and saved!")

I want the same thing in the comments where I worked with CSV file except with the SQLite database. Can someone help me convert the code in the comment?

[harsh pulsar]

note: """ is not a comment. it's just a big string, which happens to do nothing

[thorny field]

i did before but i want the code inside """ to work with my SQLite databse

[tropic dagger]

Do you have to use the cursor or can you do something else to gain time? Like using pandas's to_sql function.

[thorny field]

doesn't matter

[tropic dagger]

Because what you can do is opening your CV with pandas, then save the dataframe to your SQLite db

It adds an intermediate step but it gets the job done

[thorny field]

my friend helped me already but thx!

[robust fjord]

What's the recommended type for storing dates and times in PostgreSQL?

All the values in the column are going to use the same time zone

[jaunty galleon]

You mean to store datetime objects?

[robust fjord]

Yes

[jaunty galleon]

I personally use timestamp without time zone

And than I can do something like:

all_data = await conn.fetch('''SELECT * FROM time WHERE ($1) > saved_datetime_object''', datetime.datetime.utcnow())```

[robust fjord]

Thank you!

[jaunty galleon]

Np

I hope this is correct, I use it for my discord bot

[harsh pulsar]

no reason to not use time zone imo, but that's just me

[potent mural]

can anyone help me with my database assignment?

i have created the database i just gotta enter some queries to retrieve specific data ive done about half of them got around 9 left

and im struggling with these

[bold nest]

Question related to sqlite3. I have a fairly big database (30.000) records. I want to publish this database online and make it search able like the application db browser does. Does anyone knows such opensource web application?

[next sun]

can anyone help me with psycopg2 module to save data into my database table using paramaterized queries?

I am getting a weird error

LINE 20:             temp,
                     ^```

I don't have temp column at all in my table....

[river ice]

not sure what "application db browser" does but i see databases are commonly hosted to the public via APIs ... querying an API is sometimes just querying a database , with rate limits. Youtube for example .. Flask is a good place to start for an API , and then production release its better to move to a different "api hosting library" other than flask. I see there are lots of suggestions around. And the API would be hosted on the internet with a hosting company like DigitalOcean, AWS ... or Heroku . Companies that are meant to host APIs' (instead of websites)

[river ice]

I see there is probably something off with the query but i can't see the code . Suggesting with a next issue.. to post the code and/or open a help channel #❓｜how-to-get-help and post the channel here for us to merge to

[ripe matrix]

Hello, just to be clear, I can't use placeholders as column names when I'm making a query using my discord bot?

[jaunty galleon]

No you can't

[storm minnow]

#help-broccoli i need some help with selenium, would be very nice if someone can take a look please
Thank you

[brave bridge]

You can't. Why do you want that?

[hybrid verge]

You can't gic that because of github

[ripe matrix]

I need to search a specific column based on user input

[brave bridge]

Right, but why do you want that?

Can you show your code, perhaps?

[ripe matrix]

Because that's the purpose of my bot haha

async def raid(self, ctx: commands.Context, a: str, *, b: str):
    cc = await self.bot.db.fetchval("SELECT $1 FROM tablename WHERE hero_ex = $2;", a, b)
    await ctx.send(f"{cc}")

[brave bridge]

async def raid(self, ctx: commands.Context, a: str, *, b: str):
    if row not in {"list", "of", "allowed", "columns"}:
        # raise an error
    row = await self.bot.db.fetchone("SELECT * FROM tablename WHERE hero_ex = $2;", a, b)
    await ctx.send(row["a"])

[ripe matrix]

I see, let me try that

[ripe matrix]

It says "AttributeError: 'Pool' object has no attribute 'fetchone'"

I'm using asyncpg/python if that matters

[brave bridge]

i don't remember how to do it with asyncpg. What do you use to fetch a row?

should be fetchrow

[ripe matrix]

This is now my current code

async def raid(self, ctx: commands.Context, a: str, *, b: str):
  list = ...
  if a in list:
  cc = await self.bot.db.fetchrow("SELECT * FROM tablename WHERE hero_ex ILIKE $2;", a, b)
  await ctx.send(cc["a"])

It said "IndeterminateDatatypeError: could not determine data type of parameter $1"

So I changed the $2 to $1 and removed a

[harsh pulsar]

@ripe matrix you don't use the pool to make queries, you have to acquire a connection from the pool

[ripe matrix]

But then it said KeyError: 'a' so I just removed the quote from cc["a"] then it worked

[harsh pulsar]

unless it lets you directly make queries using the pool, where it automatically acquires and releases a connection?

[ripe matrix]

I am not sure but I put my pool in the main .py then I use self.(poolname) to make a query if I'm in cogs, wasn't sure if I needed to do anything else

It's all good now, thank you :)

[cerulean jackal]

similar to question above, except about sqlite. Have database with file info. I want to save file creation time with timezone, so it will be possible to delete it after X amount of time passes (say, 12 hours), even if server has been moved to other country in between operations. What s the best approach for it?

I've head sqlite has built-in datetime function, but I cant find info about timezone insertion anywhere. Should I rely on python's datetime instead or?

[faint blade]

You do have one issue in this. You have await ctx.send(cc["a"]) instead of using the parameter a: await ctx.send(cc[a])

[faint blade]

SQLite does not have any form of datetime type, it has functions built-into its SQL though as you mention. I recommend saving the timestamp and then creating Python datetime's with it.

https://docs.python.org/3/library/datetime.html#datetime.datetime.utcfromtimestamp

[ripe matrix]

Yup, I changed it already :)

[faint blade]

https://docs.python.org/3/library/datetime.html#datetime.datetime.timestamp

So use the latter to convert it to a timestamp (remember to use UTC datetimes from utcnow())

And the former to get the datetime from the timestamp inside SQLite

[tropic dagger]

Can I save any Python object to bytes and/or binary in a database, then retrieve it later?

I'd like to save an instance of this type of object: https://github.com/sgillies/affine

[harsh pulsar]

using pickle, yes, but it won't necessarily be retrievable in any other python interpreter, other than the one you saved it with

[tropic dagger]

I'd like to avoid Pickle if I can if there's incompatibilities between versions

[harsh pulsar]

it's much, much better to try to serialize the data somehow

it looks like Affine is just a subclass of namedtuple

https://github.com/sgillies/affine/blob/master/affine/__init__.py#L116-L117

[delicate field]

affine/__init__.py lines 116 to 117

class Affine(
        namedtuple('Affine', ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'))):```

[harsh pulsar]

there are also loads and dumps functions that appear to dump the data to a string

[cerulean jackal]

named tuple is like key with multiple values. Make table for these or something

[harsh pulsar]

so you can dump the data into a string and then save it as text or binary blob

[tropic dagger]

Yeah that was my main strategy just in case. before appending to the object I'll use pickle.dumps on my object, then store it

[harsh pulsar]

i mean these https://github.com/sgillies/affine/blob/master/affine/__init__.py#L595-L623

but, because these elements just look like numbers, you can save the affine object as a list/array/whatever of those 6 numbers

a throughi

you could use the to_gdal/from_gdal methods too

[tropic dagger]

This one looks better. It's easier to stringify this one

[harsh pulsar]

that's what i'm saying, don't even need to stringify it unless you have to

first of all, it's already just a tuple, so it's iterable as-is and behaves entirely like a tuple

it's just 6 numbers internally, with a bunch of methods

what database are you using @tropic dagger ?

[tropic dagger]

PostGIS

It's an extension of Postgres with WKT geo-objects

[slim acorn]

Anyone working with the alembic without using flask and other tooling here?

Alembic and postgresql upgrades without using anything like flask migrate.

It usually works but it currently give me some errors.

[harsh pulsar]

yeah ive seen postgis

[next sun]

hello everyone

[harsh pulsar]

in this case i don't think you need a WKT, you can just use an array

[next sun]

has anyone ever used upsert feature on postgres?

even in mysql will do

but postgres would be actually better

[harsh pulsar]

@tropic dagger

create table affine_transformations (
  id           serial primary key,
  coordinates  real array[6],
)

[tropic dagger]

can we store arrays just like that with SQLAlchemy's orm?

[harsh pulsar]

i'm not sure, probably. because these are tuples internally, you can just call list() on them and get the 6 numbers in a list

does sqlalchemy provide some kind of "adapter" functionality? where you can serialize and deserialize between basic python objects and these "interesting" data types

[faint blade]

I think I have once, hold on.

[next sun]

that's great

so

I am a bit stuck here...

[tropic dagger]

I've only seen pickle and large binary

[next sun]

basically, I am using parameterized query

you know that you call execute on cursor

and pass in the prepared statement and the tuple of values

right?

[harsh pulsar]

https://docs.sqlalchemy.org/en/14/core/custom_types.html maybe something in here

[next sun]

all is working fine until I try to use the upsert feature

[faint blade]

It's something like: ```sql
INSERT INTO my_table (
my_pk, val1
) VALUES (
1, 123
) ON conflict my_pk -- You can also have a constraint here or a WHERE
DO UPDATE my_table
SET val1 = 123
WHERE my_pk = 1;

[next sun]

yes

@faint blade I got that

but my problem is a bit more complex

let me show you

        insert into "FootballData" (
            temp_c,
            feelslike,
            uvindex
        ) VALUES (
            %s, %s, %s
        )
        ON CONFLICT ON CONSTRAINT footballdata_un 
        DO UPDATE SET (
            temp_c,
            feelslike,
            uvindex
        ) = (%s, %s, %s)
        """```

                    box = []

                    for value in dictionary.values():
                        print(value)
                        box.append(value)
                     
                    # cursor.execute("insert into people values (%s, %s)", (who, age))
                    tuple_of_parameter_values = tuple(value for value in box)
                    cur.execute(statement, tuple_of_parameter_values)```

but I am getting the following error:

[faint blade]

I see now, count the numbers of %s you have

[next sun]

the funny thing is that when I run the query in the database, it works fine

[faint blade]

SQLite doesn't understand that the 1st and 4th are the same one

[next sun]

what??

this is not SQLite

it's postgres

[brave bridge]

@next sun The query is expecting 6 values, you're providing 3.

[next sun]

anyway, in the database, I replace the placehorders for actual values

but it works

6?????

[faint blade]

Ah right sorry, was it Psycog2?

[next sun]

how come????

[faint blade]

I told?!!?!?!?!?!?

[next sun]

Yes, it is

[brave bridge]

@next sun You have 6 %s's in your query.

[next sun]

Psycog2

let me see

[faint blade]

Postgres doesn't understand that your 4th %s is meant to be the same as the 1st one

[next sun]

hang on a second

but one is for updating right?

or it doesn't matter?

in such case, how could I fix that?

[brave bridge]

It doesn't matter, each %s corresponds to a single value in the tuple you provide

You can use named parameters if you don't want to repeat them:

insert into "FootballData" (
    temp_c,
    feelslike,
    uvindex
) VALUES (
    %(temp_c)s, %(feelslike)s, %(uvindex)s
)
ON CONFLICT ON CONSTRAINT footballdata_un 
DO UPDATE SET (
    temp_c,
    feelslike,
    uvindex
) = (%(temp_c)s, %(feelslike)s, %(uvindex)s)

In that case, you'll have to provide a dict instead of a tuple.

[faint blade]

Huh, didn't know that was a thing. Can you not just use indexes?

(1, 2, 3)

[next sun]

I tried the following:

        insert into "FootballData" (
            temp_c,
            feelslike,
            uvindex
        ) VALUES (
            %s, %s, %s
        )
        ON CONFLICT ON CONSTRAINT footballdata_un 
        DO UPDATE SET (
            temp_c,
            feelslike,
            uvindex
        ) = ({tuple_of_parameter_values})
        """```

but it doesn't work

[brave bridge]

You shouldn't use string formatting for making queries

[brave bridge]

This is probably the most convenient solution

[modest ledge]

TooManyConnectionsError: sorry, too many clients already
why am i getting this when i only used pools

[faint blade]

No it's not string formatting, it's https://www.python.org/dev/peps/pep-0249/

https://www.python.org/dev/peps/pep-0249/#paramstyle

[brave bridge]

I'm talking about the last snippet with ({tuple_of_parameter_values})

[faint blade]

Ah, yes I see now.

[faint blade]

Are you opening multiple pools?

[modest ledge]

perhaps?

[next sun]

hang on a second. but the values are going to be the same anyway

[modest ledge]

they're opened when needed and released when done

[next sun]

is it going to work?

provide a dict?

[faint blade]

You don't open multiple pools. You open one and then acquire multiple connections from it

[brave bridge]

There's no way to make it refer twice to the same value with %s.

[harsh pulsar]

@tropic dagger based on the docs, it looks like you can do this:

import sqlalchemy.types as types
from sqlalchemy import Column
from sqlalchemy.orm import declarative_base

class AffineType(types.TypeDecorator):
    """Converts Affine objects to arrays."""
    impl = types.ARRAY(types.Float, 6)
    cache_ok = True

    def process_bind_param(self, value, dialect):
        return tuple(value)

    def process_result_value(self, value, dialect):
        return Affine(*value)

Base = declarative_base()

class Transformation(Base):
    __tablename__ = 'transformations'
    transformation = Column(AffineType)

[modest ledge]

well i opened 1 pool in each scripts

[faint blade]

You can do cur.execute(statement, tuple_of_parameter_values * 2)

[brave bridge]

...or that, yes

but it's a bit of a hack 👀

[modest ledge]

used cogs for a discord bot so for each cog i made a pool

[next sun]

• 2???

what????

[faint blade]

Yes!!!!?!?!?

[brave bridge]

It depends on the particular library. In the link you sent https://www.python.org/dev/peps/pep-0249/#paramstyle, there is a style that allows using an index.

[next sun]

I am super lost.

ok

[brave bridge]

!e

print(("foo", 1) * 3)

[delicate field]

@brave bridge :white_check_mark: Your eval job has completed with return code 0.

('foo', 1, 'foo', 1, 'foo', 1)

[next sun]

I will try the solution

[faint blade]

Because you want to use each %s twice. So

[harsh pulsar]

it might be clearer to write

statement = "..."
values = (a, b, c)
parameters = values + values
cur.execute(statement, parameters)

[faint blade]

That's bad. Open one pool for the whole bot!

[grim vault]

Why not use the EXCLUDED.colname which does use the same value as given to the INSERT?

[next sun]

so, if I go with this solution, I don't to make the tuple as a dict

[modest ledge]

idk how to lmao i cant access a bot variable inside a cog

[next sun]

right?

[modest ledge]

says its not defined

[tropic dagger]

My strategy was to declare a String field and do this: transformation = ", ".join([str(arg) for arg in ds.transform.to_gdal()])

[harsh pulsar]

yuck, why would you do that when you can just use an array

parsing bad

[brave bridge]

yeah lol, that's what you should use @next sun

[grim vault]

insert into "FootballData" (
    temp_c,
    feelslike,
    uvindex
) VALUES (
    %(temp_c)s, %(feelslike)s, %(uvindex)s
)
ON CONFLICT ON CONSTRAINT footballdata_un 
DO UPDATE SET (
    temp_c,
    feelslike,
    uvindex
) = (EXCLUDED.temp_c, EXCLUDED.feelslike, EXCLUDED.uvindex)```

And don't you need a where clause in the update?

[next sun]

humm

[faint blade]

You can, if you give the cog the bot. ```py
class MyCog(commands.Cog):
def init(self, bot):
self.bot = bot

... # Somewhere else

bot.add_cog(MyCog(bot)) # Pass bot into the cog

You also have `ctx.bot` if it ever is needed.

[next sun]

I see

that might work even better

cool.

[grim vault]

What's the footballdata_un constrain? That should be your where.

[next sun]

Thank you so much

[tropic dagger]

I had no idea how I should insert those in a DB

[next sun]

that is the constraint I have in one of the columns that makes sure there is no duplicates

[modest ledge]

i tried but for some reason it says not defined

[grim vault]

Yes, but you need to use that in the WHERE clause of your DO UPDATE. You don't need it, ok.

ON CONFLICT DO UPDATE updates the existing row that conflicts ...

[next sun]

really?

how come it works when I run the query in the database?

and even it says you can do that in the documentation

[faint blade]

Please always provide a traceback, it is simply not helpful to me to get "Yeah I think I did that but it didn't work". I am trying to help you here 🙂

[harsh pulsar]

i literally just showed you how to set it up in sqlalchemy. postgres also has its own syntax for arrays

[grim vault]

I do not know enough about postgres, maybe it does this by itself. It does.

[modest ledge]

okay wait lol imma try once again if it doesnt work ill show you traceback

[tropic dagger]

Oh nvm I thought it was harder than that. So types.ARRAY(types.Float, 6)'s declaration works like a higher-level programming language for arrays?

[next sun]

yes. I even tested it in my database

all working fine now guys

[next sun]

And thanks a lot for the Excluded. That is exactly what I needed

[tropic dagger]

Ok it seems to work so far

[brave bridge]

With aiosqlite, is there any way to set row_factory?

1. If I don't await on the connection, I can't set row_factory because there's no active connection yet
2. If I do await on the connection, I can't use it in an async with statement, because it will start a thread twice.

[cerulean python]

Does with sqlite3.connect() commit on __exit__ ??

If you just close your database connection without calling commit() first, your changes will be lost!
Docs say no :/

[hexed estuary]

hmm, I feel like I do it all the time

[hexed estuary]

https://docs.python.org/3/library/sqlite3.html#using-the-connection-as-a-context-manager

it does automatically commit transactions

are you maybe reading about something else?

[cerulean python]

That line was next to close()

[torn sphinx]

Hello help me

[hexed estuary]

Well, __exit__ of connection objects both commits and closes.

(or rollbacks and closes if the exit is due to an exception)

[cerulean python]

good to know

[brave bridge]

https://github.com/omnilib/aiosqlite/issues/118 if anyone can help 🙂

[torn sphinx]

Hi help me

[cerulean python]

If you need help with databases, you only need to ask your question here to get help. If you need help with something else, please read #❓｜how-to-get-help

[torn sphinx]

Not works although Name is in that table

[harsh pulsar]

__exit__ is called when a with block ends. so yes, that's what with does when you use it on a sqlite3 connection

[cerulean python]

The question was, does it commit

I know how context managers work

[harsh pulsar]

@torn sphinx the table is called HumanResources.Department

[harsh pulsar]

i believe that's all the context manager does

e.g. it doesn't close the connection, it just commits

[torn sphinx]

yes

[harsh pulsar]

you wrote HumanResources.name, did you mean Department.name?

[cerulean python]

it most definitely closes the connection. sqlite is IO based

[harsh pulsar]

with doesn't mean it has to close anything

[harsh pulsar]

https://docs.python.org/3/library/sqlite3.html#using-the-connection-as-a-context-manager

Connection objects can be used as context managers that automatically commit or rollback transactions.
with does not .close() , it only commits

it's unfortunately not stated clearly in the docs, but it is stated clearly in the example code:

# Connection object used as context manager only commits or rollbacks transactions,
# so the connection object should be closed manually
con.close()

[harsh pulsar]

can you post this as text, not as a screenshot? it's hard to read this way

[cerulean python]

I might be thinking about aiosqlite then or idk. Super weird for sqlite3 to not do that

[harsh pulsar]

i agree, but this is an old library

things were weirder and worse in the past

[prime meteor]

Happy programmer noise

[torn sphinx]

Msg 8120, Level 16, State 1, Line 1
Column 'HumanResources.Department.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[harsh pulsar]

yes, Name does not appear in the GROUP BY clause, so you must specify how to aggregate it

[torn sphinx]

While using AdventureWorks2017 as data source, make single table import ETL package into newly created database.
ETL has to succeed no matter how many times you will execute it.
Use stage table and “Execute SQL Task” for data merge (avoid using “Lookup task”).

I have restored that database. SO what next?

?

[velvet arrow]

getting sort of a basic issue here was wondering if i could get some quick help

just trying to use sqlite3 but got this issue:
conn = sqlite3.connect('database.db')
NameError: name 'sqlite3' is not defined

[brave bridge]

Can you show the whole code?

Did you import sqlite3?

[velvet arrow]

i have sqlite3 installed on my computer (although for whatever reason i couldnt get the command line thing to work)

yeah i did one sec

oh wait no im an idiot i imported sqlite

nvm

[open tusk]

what channel do i type in

[torn sphinx]

Hackers

[open tusk]

hackers channel?

[granite latch]

what is the difference between closure and attribute closure??

[torn sphinx]

hey, how can i prevent this from happening:

sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00 (Background on this erro
r at: http://sqlalche.me/e/14/3o7r)

im using sqlalchemy flask

[pure sleet]

that url has a suggested fix

and it explains why it occurs in the first place

[torn sphinx]

im using flask-sqlalchemy, so i think that engine stuff isnt working for me

[pure sleet]

flask sqlalchemy is an extension of sqlalchemy

[torn sphinx]

yeah, but im not doing any sort of engine = create_engine("mysql://u:p@host/db", pool_size=10, max_overflow=20), how can i change the pool_size and max_overflow

[pure sleet]

you can add a dictionary to the flask config:

SQLALCHEMY_ENGINE_OPTIONS = {
    "pool_pre_ping": True,
    "pool_recycle": 300,
 }

for example

it's explained here:
https://flask-sqlalchemy.palletsprojects.com/en/2.x/api/#configuration

[torn sphinx]

thanks! im gonna check it out

[thorny field]

So I am coding a discord bot and I am switching from a CSV file to a SQLite database.
CSV File:

user,money,mango,megas,ele,soul,givida,pm,wm,cd,gs,f,guild
761977941881258077,4000.0,0,0,0,0,0,0,0,0,0,0,815077804482756610

I have a command that returns how much money the user has.
Code:

# Money command
@bot.command()
async def money(ctx):
    user_id = str(ctx.message.author.id)

    user_data = open("user.csv", "r")
    checking = open("user.csv", "r")
    check = checking.read()

    if (user_id in check):
        user_array = list(csv.reader(user_data))
        for i in range(len(user_array)): 
            for j in range(len(user_array[i])): 
                if user_array[i][j] == user_id:
                    user_row = i
        
        embed=discord.Embed(
            title="Money",
            description="You currently have $" + str(user_array[user_row][1]) + " dollars.",
            color=discord.Color.green()
        )

        await ctx.send(embed=embed)
    elif (user_id not in check):
        await ctx.send("You need to be registered to use this command.")

I need help to make the code work with the SQLite database.

Database:

[thorny field]

@harsh pulsar can u help me?

[river ice]

#help-carrot message #help-carrot

[river ice]

don't ping the man if it has been more than an hour since you talked to him

[thorny field]

Ok srry

[lone island]

I need a tutorial to setup a postgresql database for my discord bot , i dont know much about databases , any suggestins ?

[wise goblet]

googling f"{Linux Operational System and version} install postgresql"
googling f"Create database and user in postgresql"

if you will be choosing Linux OS to have postgresql, I would recommend Ubuntu 20.04.
quite beginner friendly system

Additional suggestions... installing Ubuntu 20.04 Desktop and trying it all there.

If you are in windows... you can manage with learning how to manage postgresql in windows I guess for starters
https://www.postgresql.org/download/windows/
But this skill would not be super useful, since we deploy stuff to linux servers usually

[glossy perch]

    async def channel(self, ctx, channel:discord.TextChannel):
        if ctx.message.author.guild_permissions.manage_messages:
            db = sqlite3.connect('main.sqlite')
            cursor = db.cursor()
            cursor.execute(f"SELECT channel_id FROM main WHERE guild_id = {ctx.guild.id}")
            result = cursor.fetchone()
            if result is None:
                sql = ("INSERT INTO main(guild_id, channel_id) VALUES(?,?)")
                val = (ctx.guild.id, channel.id)
                emb = discord.Embed(description=f":approve: {ctx.author.mention}: **Welcome channel** has been set to {channel.mention}", color = 0x2ecc71)
                await ctx.send(embed=emb)
            elif result is not None:
                sql = ("UPDATE main SET channel_id = ? WHERE guild_id = ?")
                val = (channel.id, ctx.guild.id)
                emb = discord.Embed(description=f":approve: {ctx.author.mention}: **Welcome channel** has been updated to {channel.mention}", color = 0x2ecc71)
                await ctx.send(embed=emb)
            cursor.execute(sql, val)
            db.commit()
            cursor.close()
            db.close()``` so i have this welcome channel command , how would i make it where u can remove it

like

,welcome channelremove

and it removes it

[lone island]

ty

[proven arrow]

Use delete statement.
delete from main where guild_id = ?

[wise goblet]

u a welcome.
do you know basics of working with SQL language and/or ORM?

[glossy perch]

    async def channel(self, ctx, channel:discord.TextChannel):
        if ctx.message.author.guild_permissions.manage_messages:
            db = sqlite3.connect('main.sqlite')
            cursor = db.cursor()
            cursor.execute(f"SELECT channel_id FROM main WHERE guild_id = {ctx.guild.id}")
            result = cursor.fetchone()
            if result is None:
                sql = ("Delete FROM main WHERE guild_id = {ctx.guild.id}")
                val = (ctx.guild.id, channel.id)
                emb = discord.Embed(description=f":approve: {ctx.author.mention}: **Welcome channel** has been deleted", color = 0x2ecc71)
                await ctx.send(embed=emb)
            cursor.execute(sql, val)
            db.commit()
            cursor.close()
            db.close()```

@proven arrow so that?

[proven arrow]

No

[glossy perch]

then how

[proven arrow]

Your parameters are more than it expects

[glossy perch]

    async def channel(self, ctx, channel:discord.TextChannel):
        if ctx.message.author.guild_permissions.manage_messages:
            db = sqlite3.connect('main.sqlite')
            cursor = db.cursor()
            cursor.execute(f"SELECT channel_id FROM main WHERE guild_id = {ctx.guild.id}")
            result = cursor.fetchone()
            if result is None:
                sql = ("Delete FROM main WHERE guild_id = {ctx.guild.id}")
                val = (ctx.guild.id, channel.id)
                emb = discord.Embed(description=f":approve: {ctx.author.mention}: **Welcome channel** has been deleted", color = 0x2ecc71)
                await ctx.send(embed=emb)
            cursor.execute(sql, val)
            db.commit()
            cursor.close()
            db.close()```

?

[proven arrow]

Your still passing 2 values to the query. You should only pass what the query expects.

[glossy perch]

so should i remove

cursor.execute(f"SELECT channel_id FROM main WHERE guild_id = {ctx.guild.id}")

[lone island]

uhh not really , i've been using JSON till now

[wise goblet]

this is a funny book for beginners

[wise goblet]

https://sqlbolt.com/
is nice to walk through interactive lessons

[lone island]

lol kk , thanks

[glossy perch]

    async def channel(self, ctx, channel:discord.TextChannel):
        if ctx.message.author.guild_permissions.manage_messages:
            db = sqlite3.connect('main.sqlite')
            result = cursor.fetchone()
            if result is None:
                sql = ("Delete FROM main WHERE guild_id = {ctx.guild.id}")
                val = (ctx.guild.id, channel.id)
                emb = discord.Embed(description=f":approve: {ctx.author.mention}: **Welcome channel** has been deleted", color = 0x2ecc71)
                await ctx.send(embed=emb)
            cursor.execute(sql, val)
            db.commit()
            cursor.close()
            db.close()```

is this correct

[median wave]

try it

and also why @welcome.command

is that what you named your commands?

[glossy perch]

its a group command

,settings welcome channel gsdgsd