databases

[velvet ridge]

There is smth very much wrong on my database.. when ever the loop is ran, it sends the "Your reminder has arrived" even tho there can be like ~2 minutes left, why's that?
The loop code that checks database every 30 seconds: https://paste.pythondiscord.com/elaripoceg.py
The database table looks like this when created ```py
await db.execute("CREATE TABLE IF NOT EXISTS reminders (guild_id INTEGER, user_id INTEGER, channel_id INTEGER, message_id INTEGER, expire INTEGER)")

Date examples ```py
current time = 2021-12-25 16:35:10.451783
date in database = 2021-12-25 14:39:13.258142+00:00

[stark sparrow]

firebase

[grim vault]

Have you tried with the timezone? current_time = datetime.datetime.now(datetime.timezone.utc)

[velvet ridge]

Let me give it a try

[velvet ridge]

it worked!

thx alot, I did not even think about the timezone thing

[grim vault]

I'm also not 100% sure but .fetchall() will not return None, only an empty list [] if no data is found.

[velvet ridge]

I will need to find a way to check if list is empy

[grim vault]

len(data) == 0

[velvet ridge]

oh..

thx! I'll add that there as well

next time I will try to remember the timezone in there

[orchid ember]

should i learn MySQL or should i stick to SQLITE

[brave bridge]

depends on what you want to make

[cosmic seal]

Thank you.

[torn sphinx]

hello i have a question
im trying to make a column true using boolean

create table server_config
(
    guild int not null
        constraint server_config_pk
            primary key,
    mod_role int,
    log_channel int,
    muted_role int,
    ban_appeal_link text,
    thin_ice_role int,
    birthday_category int,
    booster_roles bool default 1 not null,
    booster_role_hoist int,
    bulk_log_channel int
);```
but whenever i check if its true it comes back as false

[orchid ember]

mysql?

[harsh pulsar]

how are you checking?

and yes, what database?

[torn sphinx]

heloo @harsh pulsar

[torn sphinx]

sqlite3

[torn sphinx]

sqlite3

[orchid ember]

Bool exists in sqlite3?

[torn sphinx]

yeah

[grim vault]

No, https://www.sqlite.org/datatype3.html#boolean_datatype

[blissful basalt]

Do i need to know async to learn about databases? and which type of database should i learn?

[brave bridge]

You don't need to know about async/await to use a database.

Many of the popular database engines use the SQL language, so this: https://sqlbolt.com/ should get you started

When you get familiar with the basics of SQL, you can use Python's sqlite3 module. The official docs are pretty good: https://docs.python.org/3/library/sqlite3.html

[blissful basalt]

whats the difference between the different types of sql? Im completely new to this

[brave bridge]

wdym by 'different types of SQL'?

[blissful basalt]

There are many popular SQL databases including SQLite, MySQL, Postgres, Oracle and Microsoft SQL Server

i read that on the link

like what are they and whats the difference between them?

[brave bridge]

Those are just different database engines. They use the same language, SQL, to make queries and such. (Each adds some custom extensions to it, though)

[blissful basalt]

so im not going to accidentally start learning something very narrow?

[brave bridge]

the database engine that you need will depend on what kind of app you're making

[blissful basalt]

wdym?

[brave bridge]

Netflix doesn't use SQLite. But an internal app with 100 users doesn't need Cassandra or even PostgreSQL.

[blissful basalt]

im guessing Cassandra and Postgresql are more powerful?

and i dont need that so thats why i can use sqlite?

[brave bridge]

Some engines are just faster. For example, PostgreSQL is just generally faster than SQLite for a variety of reasons. It also supports replication (when multiple machines store data together). On the other hand, PostgreSQL requires you to install it as a separate program and run it in its own process.

Some engines are more specialized. For example, Cassandra is better if you have lots of writes, but it offers weaker data consistency, IIRC

[blissful basalt]

sorry for a ton of questions but what does data consistency and IIRC mean?

[brave bridge]

IIRC means "if I remember correctly" 🙂

[blissful basalt]

oh i thought it was a term that had something to do with databases lol

[brave bridge]

Have you made web applications or something like that before?

[blissful basalt]

nope, im learning in my free time so i have no clue if im doing things in the correct order or not

[brave bridge]

what projects have you done?

[blissful basalt]

just a rock paper scissors game, a basic calculator and half of a pygame project

is it too early to start with databases?

[brave bridge]

it's fine, I'm just thinking of how to explain this

[blissful basalt]

aha

[brave bridge]

@blissful basalt
Imagine that you have 8 processes incrementing a counter (stored in some kind of shared database).
This is what each process does (pseudocode): ```py
def process(self, period):
while True:
previous_value = counter.read()
new_value = previous_value + 1
counter.write(new_value)
self.counter += 1
time.sleep(period)

1. Process A reads the number 41 from the database
2. Process B reads the number 41 from the database
3. Process A computes the new value to be 42
4. Process B computes the new value to be 42
5. Process B stores the value 42 in the database
6. Process A stores the value 42 in the database
Whereas the correct value at this point should've been 43

So you end up in an inconsistent state. State is inconsistent when some important property about it is broken.
In this case, process1.counter + process2.counter + ... + process8.counter should be equal to the shared counter after the processes finish. And this is what you would expect. But here, the state can go out of sync

[blissful basalt]

ah i think i watched a video about something similar. It was about threading and how you would have to lock something to prevent this from happening?

[brave bridge]

that's one way to do it, yes. ```py
def process(self, period):
while True:
with counter.lock():
previous_value = counter.read()
new_value = previous_value + 1
counter.write(new_value)
self.counter += 1
time.sleep(period)

[fading patrol]

If you're just starting out with a personal project, SQLite is definitely the way to go. Most of what you learn with that will transfer to Postgres or anything more powerful when you need it. SQLite is simpler to start learning with and the main limitation is just speed and scale

[blissful basalt]

ah cool, I think i understand the base of it a little better now 🙂 Ty both

[sinful rivet]

how to check is "something" in the column "name" in "table_name"?

[torn sphinx]

"""
SELECT * FROM table_name
WHERE name = SOMETHING
"""
# and you can use fetchone/fetchall to get the data , naming it as  data

if data : print("it exists")
else : print("does not")```

[sinful rivet]

if data?

[torn sphinx]

read the comment

[sinful rivet]

not understand

[torn sphinx]

do you understand what the query did?

[sinful rivet]

sadly nope, new to sqlite

[torn sphinx]

Understandable , basically it just SELECTed every row in which the condition name= something was True

get this?

[sinful rivet]

that, yes

[torn sphinx]

now , the cursor has selected the rows , you can use
cursor.fetchone() to get one of the data , as a tuple
cursor.fetchall() will get all such queries , as a list of tuples

and if none of the rows meet the condition , it just returns None

is this clear?

[sinful rivet]

ah i have a question but i'm not sure hoe to say it in text xd

[torn sphinx]

you can try to

[sinful rivet]

ok so

[torn sphinx]

or just gimme an example/condition

[sinful rivet]

is there nothing like

"cursor.detect (or something)"

E

[torn sphinx]

as far as I know , no

selecting is the only method of collecting data

[sinful rivet]

then from dis

i just understand that fetch is to selecting data, i ever meet that "none" condition, but not sure how it works

[sinful rivet]

....?

[sinful rivet]

ok, still not understand
how to check is "something" in the column "name" in "table_name"

[austere portal]

What's the name for $n query parameters?

[sinful rivet]

ok, still not understand
how to check is "something" in the column "name" in "table_name"

[slender atlas]

Something like this?

SELECT EXISTS(SELECT 1 FROM table_name WHERE name = "something")

It will fetch 1 if "something" was found in that column in that table or 0 otherwise

[thorny remnant]

I'd just SELECT * then use result.fetchone()

[trim lintel]

Exists is faster in this case

[sick belfry]

layer = get_channel_layer()
  async_to_sync(layer.group_send)("webapp",
     {"type": "dataSend","category": "imu",
      "data":[sess.id, imu_cnt, imu_time, ax, ay, az, wx, wy, wz, batt, rssi]})

anyone seen websocket/channels run slow when sending data very fast? (every 15ms this gets sent)

instead of the consumer sending the data, it waits until it gets like 10 messages to send and sends them all at once...

     async def dataSend(self, event):
        send_json = json.dumps({"type":"dataSend","category":event["category"], 
                                "data": event["data"]
                                })
        await self.send(text_data=send_json)

[blissful basalt]

If im watching a beginner tutorial about sql will it be somewhat similar when i work with databases in python?

[sick belfry]

depends. many newer packages i.e.~DJANGO handle all sql commands for you

so you don't need to know sql at all

just a general understanding of put get and db setup... but newer systems use a model based DB

[blissful basalt]

what about when ive seen people import sqlite which is a dbms right?

[sick belfry]

i don't know. I use packages that handle all of that

Django manages the db if you use sql

postgres is a little more advanced, but it still handles it

[blissful basalt]

isnt Django something you use to build websites? Im new to this

[sick belfry]

hmm I guess so. I am just using django as an exmaple. Ibuild for web

but python and django are local. So i use them for local apps too

to manage my db

[blissful basalt]

hm im still kinda confused

[fading patrol]

Not all databases use SQL, but if you plan to use Python with any databases that do, it's very worth learning the basics. It shouldn't take more than a few weeks at most to get comfortable writing basic queries.

Regarding the point about Django, it includes what's called an ORM feature. That allows you to interact with a SQL database using pure Python instead of actually writing SQL queries. SQL Alchemy is another ORM which works on its own (Django itself is a web framework as you mentioned, not just an ORM)

In general with Python its better to use an ORM when you can, but learn SQL a little first before you try to use one

[blissful basalt]

Hm ok so ill learn the sql basics first then see where i go from there

[sick belfry]

great answer. I definitely have become reliant on the ORM features and forgot most of my SQL background. I do need to use sql queries often directly to the DB when troubleshooting. So it is important to know.

[blissful basalt]

Wdym when u say it allows me to interract with a sql database using pure python? That I can write it directly in my program?

[vale heart]

I have three tables in sqlalchemy. Table1 owns Table2 and now I want Table3 to own Table2 as well
In Table1 I have the following line:
table_child = db.relationship('Table2', backref='owner')
In Table2 I have the following line:
table_child = db.relationship('Table2', backref='owner')
In Table3 I have the following line:
owner_id = db.Column(db.Integer, db.ForeignKey('table1.id'))
but that only establishes a relationship between Table1 and Table3
how do add a foreignkey for Table2 as well?

[fading patrol]

Yes, you can write queries directly in your program in Python not SQL. Here's a relevant tutorial https://realpython.com/python-sqlite-sqlalchemy/

[blissful basalt]

Ah thanks :)

[brave bridge]

I don't think using an ORM is always "better"

[fading patrol]

I said it is "in general" not "always". Not sure what situations you have in mind but the main caveat I'm aware of is that ORM queries can be slower

[thorny anchor]

also you have to learn it

[fleet ibex]

interesting read dowcet.. not gonna ping ya.. but im finding it useful..

[stoic tide]

I've never met an ORM I liked.

[sinful rivet]

how to add a new row?

[earnest blade]

Hello im searching some one who can do machine learning and neural netowrk

pls contac me

[elder smelt]

hi, can someone tell me how to merge these rows. I've added an example

[grizzled wadi]

What's your opinion on https://github.com/RobertCraigie/prisma-client-py ?

I didn't really like ORMs until I made this because I hated having to figure out how to write complex queries but as everything gets auto-completed for you it's incredibly easy to learn and use

You of course still have the drawback that not everything you can do in SQL you can do with Prisma but the majority of standard queries should be doable and you can always fallback to raw SQL if you need to

The worst aspect of this IMO is the performance loss however I will be improving performance in the future

[velvet ridge]

what is serial PRIMARY KEY is postgres db?

[brazen charm]

the equivalent of an auto incrementing integer

a serial is just INTEGER AUTOINCREMENT eqv in something like mysql

[velvet ridge]

so it's a value that increases?

[brazen charm]

well it's a calculated field

i.e when you insert a row, the last value is incremented by 1 and returned

its garenteed to always be unique

[velvet ridge]

        CREATE TABLE IF NOT EXISTS infractions(
            id serial PRIMARY KEY,
            user integer
            apply time
            expire time
            active bool
            reason text
        )
``` so every time I insert smth, the id will increase?

[brazen charm]

yes

[velvet ridge]

that's good

thx alot!

does postgres autocommit?

also that there is time can I still have table with value time? ```sql
CREATE TABLE IF NOT EXISTS messages(
user integer,
time time,
)

or will the name confuse the db

[unreal tartan]

use double quotes I think. "time" time,

but then you have to use the quotes in select queries and stuff too. I'd try to avoid it if not necessary.

[swift ridge]

hey guys, a quick question in FD,
is AB -> C the same as BA -> C?

[hidden dune]

Have any python developers tried IRIS from InterSystems. It is an OO development environment with Sql access. Data is stored in multidimensional sparse arrays which is an incredibly fast storage architecture. It has its own scripting language, ObjectScript and has gateways to Java,. NET, a host of adapters to various third party systems, native support for hl7 and FHIR but best of all they have native bindings to Python R and Julia and embedded support for Python which is likely to overtake ObjectScript as the primary scripting language. It runs on all najor platforms including Pi. You can down load trial or community kits in either Contrainers or as native installs. If you want to know more either contact me or the InterSystems Developer Group (mention my name in 😎)

[coarse vault]

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword"
)

print(mydb)

when running this code, i am getting this error
ConnectionRefusedError: [WinError 10061] No connection could be made because the target machine actively refused it

Does anyone know a fix for this error?

[stoic tide]

Funny you should bring it up! I was looking at trying it before AoC started and took all my time. (I have significant experience with SQL Server, and thought I might try ot help out the SQL Serer support.) I guess you're the Robert mentioned in the README.md ?

[stoic tide]

That error means that the machine you tried to connect to refused the connection. That might sound obvious, but it's important in that it's dfferent than the machine accepting the connection, then deciding the username or password (or something else) was wrong with the connection request.

You should make sure that your MySQL server is running and set up to accept remote connections; and that there's no firewall or other security software in the way.

[coarse vault]

How am i supposed to set up mysql server

and make it accept remote connections

[grizzled wadi]

Yes I am and I'm more than happy to help if you do try it out!

I actually haven't tested it with SQL Server so please let me know if you run into any issues, it should work well as all the actual database connections and querying are handled directly by Prisma

[stoic tide]

Almost any MySQL tutorial will give instructions on setting it up. What you'll need to do depends on which operating system you're using.

[coarse vault]

aight thanks
any good tutorials you know?

[stoic tide]

Will do. Is there an easy way to get in touch with you? This server is very busy and noisy -- difficult for any kind of serious convesation.

[stoic tide]

https://dev.mysql.com/doc/refman/8.0/en/tutorial.html

[stoic tide]

https://www.mysqltutorial.org/install-mysql/

[grizzled wadi]

Feel free to DM me or create a discussion on the repository

[stoic tide]

https://realpython.com/python-mysql/

[dusty steppe]

How do I turn a txt file to a csv?
The different fields are separated by tab instead of commas
So when I open it in excel, all the values get stored in one column

[stoic tide]

You'll have to read the file, parse it, and emit it in the desired format.

[stoic tide]

Maybe, if the file format in't so far off, you can do this directly in Excel with the "Text to Data" command.

Excel also has an "Import" feature.

[hidden dune]

Open Excel, select Data, select from File, specify the .txt file, excel will try and determine the delimiter but if it can't then you get the option to specify the delimiter where upon excel will read in the file contents correctly. Make sure that your columns are correctly data typed, eg if you see ####### that means the column width is too small. If you see 1+e6 or words to that effect then change the data type of the column to numeric and remember to specify how many decimal places if any you want. Once you have done all of this you can then save the file as type .csv and it will prompt you for the delimiter If I remember correctly). You may also want to save the .xslx form of the file so you can come back and make changes without having to reimport the data.

[dusty steppe]

Done this, it works, thank you

[dusty steppe]

I've done it now, but thank you very much

[vale heart]

Is it a good idea to store images in a SQL-like database? In my current project, I have a backend server in Python and we get a lot of images from an app and, currently, we just store them in a file system.

The added advantage of this is that we are able to view them easily but everything else related to the images (each image has some numerical values associated with it to describe items in it) is stored in our SQL database.

So, should I start storing images in a SQL database as well, or is it only recommended for a small number of images and not a large number of images (we get, on average 1000 images per user per day, maybe even more in the coming days).

I did some reading on this and most people definitely advise against this. I wanted to know what people in this channel think.

[stoic tide]

It depends on what you're going to do with the images, and how large they are, and how many, and how you access them.

here is a relevant research paper (though, it's getting a bit old): https://www.microsoft.com/en-us/research/publication/to-blob-or-not-to-blob-large-object-storage-in-a-database-or-a-filesystem/

[vale heart]

each image goes through an object detection algorithm and then some more complex computer vision algorithms. Basically, the images are processed continuously as the algorithm runs, so they would need to be read again and again.

[stoic tide]

I don't know how often or quickly "again and again" is, but that sounds like a case for using an object store or a file system.

What is the path for accessing the objects now? Is your app web based? Do the tools get the data straight from the DB, or ... ? Would HTTP be better for acesssing the images? You don't say much about your overall architecture.

[vale heart]

right now, we get images sent from an android app via retrofit. Our backend server (using python-flask) then stores them in a filesystem. We store a string reference to it (full address of the image on the server) which is then used to load the image when data related to it is being processed

[stoic tide]

OK. And why are you considering storing the images in the database? What problem are you hoping to solve?

[vale heart]

I was just wondering whether it is a good idea to do that? My idea was to have some consistency with the numerical data since each image is related to some numerical data that is stored in an SQL table (hence, a relationship between the two could mean maintaining a new table with the images?)

[stoic tide]

I think what you have is fine; I can't imagine what would get better if you moved the images into the database.

As you describe it, it sounds like you've got a relationship between the metadata for the image and the file path to the image's location on whatever storage system you're using. And that's fine; nothing wrong with it.

[fading patrol]

I'm a noob to SQL but have consistently been told something like this is preferable to storing images in a DB. I don't know the details of what can go wrong, but I've had several teachers emphasize it's a bad idea

[trim lintel]

It is not that its a bad idea. It is about finding the right solution to solve your problem. Both approaches have benefits.

[stoic tide]

Just wondering -- did you read that paper I linked?

[coarse vault]

in MySQL, whenever i try starting or stopping my server, the workbench stops responding
any fixes for that?

[stoic tide]

Did you see my previous responses to you? I just wanted to make sure you saw them, since there was no acknolwedgement.

[coarse vault]

oh sry....yes i did
i realized i forgot to install the MySQL onto my computer and set up the whole thing
thanks

[stoic tide]

Great. That's certainly a requisite step.

[coarse vault]

haha yea

[stoic tide]

You can't use MySQL it you don't have MySQL.

[coarse vault]

https://tenor.com/bapDD.gif

Omar, do you know how to start and stop a MySQL server in CMD

cuz workbench causing headaches for me

[stoic tide]

Does "CMD" mean the Windows terminal?

If so, no. I usually use MySQL in Linux.

In Windows, I'd use the Services applet.

[coarse vault]

Ah ok

[coarse vault]

Yea

[coarse vault]

Ok

[agile heath]

what does:
postgresql server at *** rejected ssl upgrade mean?
i'm using asyncpg to connect to a psql server

[stoic tide]

It means that you tried to connect to postgresql indicating that your client wanted to use SSL, but the server isn't configured to use SSL.

The source code in question is here, you can review it: https://github.com/MagicStack/asyncpg/blob/master/asyncpg/connect_utils.py

[agile heath]

what are some fixes?

[agile heath]

I've look at this.

[stoic tide]

Get the server correctly configured to use SSL, if you need it.

If you don't need it, don't request SSL in your connection.

[agile heath]

alright

[coarse vault]

Figured it out
i was looking for the wrong service 😂

Their are like 15 million of them

[stoic tide]

There shouldn't be quite that many.

[steel rover]

if i have a few documents, and i want to get the document with the highest value in a particular field, how can i do that using pymongo?

[gray patrol]

Help help help... how do you config mongodb for rest api? I edites the config file but it doesnt seem to be working.. my client is just showing “it looks like you are trying to access MongoDb over HTTP on the native driver port”

[crisp spade]

Does anyone know how you can insert a large dictionary inside a mysql cell?

like its characters exceed 10000, this is why I don't use varchar

[brazen salmon]

Do I store the image in a database or do i stroe in disk and have the file path to it in my database whats the best option and why?

[fading patrol]

Sounds like you need another database. This is what NoSQl DBs like Mongo are good for. I know Postgres has a JSONB data type which can be handy in a pinch but I'm not sure if MySQL has something similar

[fading patrol]

There was just a lengthy discussion about this if you search or scroll. Unless your images are very small you probably don't want them in your database itself, just URLs

[crisp spade]

Is nosql compactible with python?

also is it easier to use than postgres, postgres gave me a freaking headache last time I tried it

[fading patrol]

You can definitely use MongoDB with Python. I haven't done it myself but it seems pretty popular and worth looking into

[crisp spade]

Thank you

[hidden dune]

In IRIS we store the blobs in the database as character or binary streams. TRhere are two advantages, firstly there is no performance lag between exporting a file into an HTTP POST or GET for example and secondly when you backup your database you are backing yup your blobs as well. Thirdly, in the case of IRIS whuch supports superb unstructured text capabilities with support for deterministic and probabalistic search functionalty using amingst others the levenstein algoythm, where you can specu=ify distance and blocking factors, the unstructured data has already been pulled into memory (or at least a pre-compiled semantic and entity association map has been created and is held in memory which makes for very fast unstructured text searches.

[brazen salmon]

Ohhh thats a coincidence! Apparently I have the exact same question as the OP, who said he needs to store imagles for computer vision process that he gets from an app endpoint. Thx for the referral.

[swift ridge]

hey guys, a quick question in FD,
is AB -> C the same as BA -> C?

FD = functional dependency

[velvet ridge]

Does postgres DB auto commit?

[fading patrol]

Are you using psycopg2? If so I don't think so

Actually yeah I guess it can https://stackoverflow.com/questions/57761557/is-there-any-reason-not-to-use-autocommit

[velvet ridge]

I use asyncpg, I never saw any ways to commit so I thought it will automatically commit them without the need of any sort of .commit()

But as I read that article you linked it seems like I might need to enable it so changes will be permanent

[fading patrol]

That article is not about asyncpg. I'm not familiar with that but I glanced at the docs and didn't see anything about needing to commit

I think it makes queries directly

[velvet ridge]

That is quite nice

So I don't have to worry about commit stuff, thx alot 😄

[tacit plank]

Can anyone give me an idea on how do I get rid of the using temporary here by creating index?

Thanks in advance

[tacit plank]

Here is the creation code:

Image enter image description here enter image description here

I am trying to figuring out a way to get rid of using temporary by creating index.

My SQL code:

EXPLAIN SELECT SQL_NO_CACHE COUNT(*) AS NUMBER, b.deptName,b.id
FROM t_emp AS a
LEFT JOIN t_dept AS b
ON b.id = a.deptid
WHERE a.age > 40 AND a.empno <> 0
GROUP BY a.deptid
HAVING NUMBER >= 2

Here is the creating of the table:

CREATE TABLE t_dept(
id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
address VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE t_emp(
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) DEFAULT NULL,
age INT(3) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
empno INT NOT NULL,
PRIMARY KEY (id),
KEY idx_dept_id (deptId)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t_dept(deptName,address) VALUES
('HR','NY'),
('IT','CA'),
('DEV', 'SC');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES
('JACK',90,1,100001),
('DAVID',50,1,100002),
('MIKE',24,1,100003),
('JOE', 90,1, 100001);

[glossy orchid]

I'm kinda new to SQL can someone help me with a Select & Join statement
I have three tables, id is unique in B and C.

A            B            C
name, id     id, ID2      name, id

So maybe

A            B            C
name, id     id, ID2      name, id 
tim, 1       1 , X         tim , 3
tim, 2       2,  X         tina, 1 
paul, 2      3,  Y         tina, 2 
paul, 3      
            
              

what I want are the counts grouped by name of table A and C, in there end with a

name CountA    Count B

tina   0       2
tim    2       1
paul   2       0

Lastly I would like to filter it by ID2

Okay got it done 🙂
by
Select * from
(Select Count From ...) as CountedA
Left Join
(Select Count From ...) As CountedB
ON CountedA.id=CountedB.id

[acoustic thunder]

for mongo db, im trying to insert a document into an array, how could i do this, i have DataBase.db8.insert_one(params1) but i want it to not just insert into the colelction but to the array.

[inner sentinel]

hmmm, is there such thing as a mock postgres database for testing or local development that doesn't require a db?

[charred delta]

if you speak about testing, there is pytest, which has something

[paper flower]

It depends on what you need, if you're using orm and you don't use postgres specific features then you could use in memory sqlite db

[inner sentinel]

ooo how would I do that

[paper flower]

Otherwise probably run your db locally or in docker

First tell me what orm (if any) you're using

[inner sentinel]

haven't picked one yet

was likely going to use either sql alchemy or tortoise-orm, since it needs to have an asyncio engine

[paper flower]

SQLAlchemy has asyncio support 🤔 I personally don't really like orms that use active record pattern (django, tortoise ...)

[inner sentinel]

active record pattern?

[inner sentinel]

yes, with 2.0 syntax for asyncpg iirc as of rn

[paper flower]

Yep, in active record a model is also responsible for making db queries:

user = User()
user.username = "Username"
user.save()

[inner sentinel]

ahhh, so changes are made locally and then saved

probably sounds easier than the other way

[paper flower]

But when using something like C# Entity Framework or SQLAlchemy that use data mapper and unit of work patterns that code would look something like this:

user = User()
user.username = "Username"
with session.begin():
    session.add(user)
session.refresh(user)  # If you need to use i afterwards

Issue is that your models are not always 1:1 representation of your table schema/row, using data mapper you can map your models however you like to any class(es)

@inner sentinel TLDR: IMO SQLAlchemy is more feature-rich but would be harder to use than say tortoise orm

[inner sentinel]

yeah I'll probably just use tortoise orm as this is mainly to provide a config for my application

[paper flower]

Why do you need an orm for providing a configuration?

[inner sentinel]

probably could just use sql directly but I don't know it yet ¯\_(ツ)_/¯

it's config/"cache" and other variables, plus I can then use it to store other data

[fading patrol]

You're going to try using an ORM without knowing SQL? I guess it's possible but I wouldn't recommend it. You're pretty likely to run into problems you don't understand. Learning basic SQL is easier then really understanding how an ORM works honestly

[inner sentinel]

I have somewhat of a basis but I'll get there ¯\_(ツ)_/¯

[coral yarrow]

I'm having a bit of a PyMongo problem

The error: https://paste.disnake.dev/?id=1640741607777674&language=python

The code in question:

    @commands.slash_command(description="Close a poll.")
    async def close_poll(self, inter: disnake.ApplicationCommandInteraction, title: str):
        poll_to_close = polls.find_one({"title": title})
        message = PollsCog.bot.get_message(poll_to_close["message_id"])
        await message.edit(view=None)
        polls.delete_one({"title": title})
        await inter.send("Poll closed.")

    @close_poll.autocomplete("title")
    def autocomplete_title(self, inter: disnake.ApplicationCommandInteraction, user_input: str):
        guild_polls = polls.find({"guild_id": inter.guild.id})
        open_polls = []
        for i in guild_polls:
            open_polls.append(guild_polls["title"])
        return [i for i in open_polls if user_input in i]```

From

  File "/home/the1calc/modernbots/dropbot/src/polls.py", line 168, in autocomplete_title
    open_polls.append(guild_polls["title"])
  File "/home/the1calc/.local/lib/python3.8/site-packages/pymongo/cursor.py", line 646, in __getitem__
    self.__check_okay_to_chain()
  File "/home/the1calc/.local/lib/python3.8/site-packages/pymongo/cursor.py", line 423, in __check_okay_to_chain
    raise InvalidOperation("cannot set options after executing query")
pymongo.errors.InvalidOperation: cannot set options after executing query

it seems like appending to a local list is causing problems in the database?

which doesn't really make sense

[severe coral]

Yeah the abstractions can work both ways it offers flexibilty in mapping but it could confuse you when debugging ...Document please

[paper flower]

You'd still use 1:1 mapping of your tables to models, using declarative extension for example, but sqlalchemy is still more powerful than say django orm

If you know sql sqlalchemy would be easier to use imo

[ruby flint]

Hey guys, so my web application is already responding pretty fast in production server. In a local environment, the query time is quite slow because I'm far from the Microsoft SQL server. But, is there any point in optimizing the query time even when the application is querying data pretty fast? I would say on average that the response time is approximately less than 300ms

[steel rover]

im using pymongo to make logging for my discord bot. the first case in the server registers fine, but how do i retrieve the latest case? if there are multiple documents in the collection all belonging to the same server, how do i retrieve the document where the "Case Number" Value is the highest?

[burnt turret]

if "case number" are just increasing integers, you can sort by descending order of case number and limit it to 1 document

[steel rover]

yeah but you cant sort dictionaries in python, can you?

[burnt turret]

no you sort in the query

coll.find().sort("Case Number", pymongo.DESCENDING).limit(1)

[steel rover]

oh

[burnt turret]

(you can sort dictionaries too fwiw)

[steel rover]

i'll try that

[wheat umbra]

What's a better way to store abstract items in database? Like some item may have something others don't

[trim lintel]

Abstract into a table and Create a relationship

For example an online shopping site. Not all registered users may have orders, so for this you will have a users table and orders table

[wheat umbra]

I know about that kind of relationship but what I want to do is basically inheritance

[trim lintel]

Please be more specific and give example of what your use case is. There are many ways to do inheritance or abstract something.

[wheat umbra]

yes I am trying to find a good example

[trim lintel]

Are you learning about inheritance or you have a problem already where you need to use inheritance?

[wheat umbra]

I guess both? I am using sqlalchemy orm.

I am literally confused with their inheritance system. So I really need to understand the logic

[fringe tiger]

I'm trying to restore 12.3 postgres database by using 12.9 but it errors because of version. I tried to google how to run multiple versions at the same time but all I found is for different major versions, dunno if I can run 12 twice.

also having troubles installing specific minor version (12.3) running https://www.postgresql.org/download/linux/ubuntu/ then sudo apt install postgresql-12.3 sais it cant bee found, looks like they only have 8.2 8.3 8.4 9.0 9.1 9.2 9.3 9.4 9.5 9.6 10 11 12 13 14 15

[wheat umbra]

class Item:
    id = None
    name = None
    price = None

class Apple(Item):
    id = 1
    name = "Apple"
    price = 5
class Orange(Item):
    id = 2
    name = "Orange"
    price = 5
class Car(Item):
    id = 3
    name = "Something"
    model = "0"
    price = 5
    
class Basket(Item):
    id = 4
    name = "Basket"
    price = 100
    items = [Apple,Orange]

the "Car" is the real problem. because car need an extra field named "model". also Basket can store items

python can easily add new object properties but a db cannot

I mean I can create a relation table for car models. but what if Every item has many different fields

[trim lintel]

You can create a table for it. That is fine.

[wheat umbra]

for every different fields?

[trim lintel]

It depends how different the properties will be. There are different inheritance types which you can see here. https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database

Or you can always opt in for the EAV model which gives you flexibility but lacks data integrity

[wheat umbra]

I think I got some cool ideas from this. Thanks for the help.

[topaz glen]

guys could i get some consultation? im planning to chose my db for my project. i have a flask backend and a js frontend. should i chose a js based db engine or use the flask backend for db. there are alot of databases for js and seeing how i have a js frontend, should i use js databases like graphql or other popular dbs

[torn sphinx]

Hi, I was wondering if i could write this query better? it takes approximately 10 seconds as of right now which is err a bit slow... sql select distinct product as CUR_PRODUCT, count(*) as total, (SELECT count(*) FROM `ffs-3d-viewer-analytics-api-prod` WHERE os = 'iOS' AND product = CUR_PRODUCT AND month(date) = 09 AND customer = 'Armani') AS iOS, (SELECT count(*) FROM `ffs-3d-viewer-analytics-api-prod` WHERE os = 'Android' AND product = CUR_PRODUCT AND month(date) = 09 AND customer = 'Armani') AS Android, (SELECT count(*) FROM `ffs-3d-viewer-analytics-api-prod` WHERE os = 'Windows' AND product = CUR_PRODUCT AND month(date) = 09 AND customer = 'Armani') AS Windows, (SELECT count(*) FROM `ffs-3d-viewer-analytics-api-prod` WHERE os = 'Mac OS' AND product = CUR_PRODUCT AND month(date) = 09 AND customer = 'Armani') AS MacOS from `ffs-3d-viewer-analytics-api-prod` WHERE month(date) = 09 AND customer = 'Armani' GROUP BY product ORDER BY product

[grim vault]

for sqlite, postgresql:

SELECT product AS CUR_PRODUCT,
       COUNT(*) AS total,
       COUNT(*) FILTER (WHERE os = 'iOS') AS iOS,
       COUNT(*) FILTER (WHERE os = 'Android') AS Android,
       COUNT(*) FILTER (WHERE os = 'Windows') AS Windows,
       COUNT(*) FILTER (WHERE os = 'Mac OS') AS MacOS
 FROM ffs-3d-viewer-analytics-api-prod
WHERE month(date) = 9
  AND customer = 'Armani'
GROUP BY product
ORDER BY product

or if that's not working:

SELECT product AS CUR_PRODUCT
     , COUNT(*) AS total
     , SUM(CASE WHEN os = 'iOS' THEN 1 ELSE 0 END) AS iOS
     , SUM(CASE WHEN os = 'Android' THEN 1 ELSE 0 END) AS Android
     , SUM(CASE WHEN os = 'Windows' THEN 1 ELSE 0 END) AS Windows
     , SUM(CASE WHEN os = 'Mac OS' THEN 1 ELSE 0 END) AS MacOS
 FROM ffs-3d-viewer-analytics-api-prod
WHERE month(date) = 9
  AND customer = 'Armani'
GROUP BY product
ORDER BY product

No DISTINCT needed for a GROUP BY

[torn sphinx]

this works! but i noticed if total is 0 the row isnt returned

is there a way to fix that?

[grim vault]

No, no product no row to select?

join with the product table maybe.

[torn sphinx]

well, is there a way to get it return zero rows still in total? i remember there was a way

its only one table unfortunately

So basically ALL products and if any results

[grim vault]

Maybe

SELECT B.product AS CUR_PRODUCT,
...
 FROM ffs-3d-viewer-analytics-api-prod AS A,
     (SELECT DISTINCT product FROM ffs-3d-viewer-analytics-api-prod
-- add some where maybe
) AS B
WHERE month(A.date) = 9
  AND A.customer = 'Armani'
  AND A.product = B.product
GROUP BY 1
ORDER BY 1```

[sleek oyster]

Hey guys I am trying to insert a list into a postgre column

def set_column():
    column_list = []
    connection = psycopg2.connect("postgres://postgres:banana_2@localhost:5432/postgres")
    connection.autocommit = True

    crs = connection.cursor()

    crs.execute("SELECT * FROM raw LIMIT 200")

    records = crs.fetchall()
    print(records)

    for record in records:
        try:
            animal = record[1].get("animal")
            breed = animal['breed']['breed_component']
            print(breed)

            column_list.append(breed)
        except Exception as e:
                pass
        finally:
            return column_list



def insert_to_column():

    connection = psycopg2.connect("postgres://postgres:banana_2@localhost:5432/postgres")
    connection.autocommit = True
    crs = connection.cursor()

    insert_q = """
     INSERT INTO data_warehouse (breed)
       SELECT j FROM unnest((%s)::text[]) AS j;
    """
    data = set_column()
    data = str(data)
    for d in data:
        crs.execute(insert_q, d)


insert_to_column()

But i get this error

psycopg2.errors.InvalidTextRepresentation: malformed array literal: "["
LINE 3:        SELECT j FROM unnest(('[')::text[]) AS j;
                                     ^
DETAIL:  "[" must introduce explicitly-specified array dimensions.

[torn sphinx]

select B.product, count(*) as total,
                 count(CASE WHEN type = 'VIEW_AR_PRODUCT' then 1 END) AS AR,
                 count(CASE WHEN os = 'iOS' then 1 END) AS iOS,
                 count(CASE WHEN os = 'Android' then 1 END) AS Android,
                 count(CASE WHEN os = 'Windows' then 1 END) AS Windows,
                 count(CASE WHEN os = 'Mac OS' then 1 END) AS MacOS
from `ffs-3d-viewer-analytics-api-prod` AS A,
     (SELECT DISTINCT product FROM `ffs-3d-viewer-analytics-api-prod`) AS B
WHERE month(date) = 09 AND A.customer = 'Armani' AND A.product = B.product
GROUP BY 1 ORDER BY 1``` trying this, it still returns only 29 rows (29 are the rows with at least one row)

[grim vault]

Yeah, it's a inner join thinking about it, so only existing products will be counted.

[torn sphinx]

so i guess ill have to fetch the entire list client side and then fill up w the data i get from this query

[grim vault]

Don't know what happens if you make A an outer join.

like

select B.product, count(*) as total,
                 count(CASE WHEN type = 'VIEW_AR_PRODUCT' then 1 END) AS AR,
                 count(CASE WHEN os = 'iOS' then 1 END) AS iOS,
                 count(CASE WHEN os = 'Android' then 1 END) AS Android,
                 count(CASE WHEN os = 'Windows' then 1 END) AS Windows,
                 count(CASE WHEN os = 'Mac OS' then 1 END) AS MacOS
from  (SELECT DISTINCT product FROM `ffs-3d-viewer-analytics-api-prod`) AS B
outer join `ffs-3d-viewer-analytics-api-prod` AS A ON A.product = B.product
WHERE month(date) = 09 AND A.customer = 'Armani'
GROUP BY 1 ORDER BY 1```

[torn sphinx]

i can try that

it doesnt seem to allow that at all

sql error

[slim juniper]

Any good tutorial for sqlalchemy?

[fading patrol]

Lots... I would suggest searching bases on what specifically you want to do with it

[wheat umbra]

if you are trying to use orm then stick with the original docs.

[shut mantle]

Curious if anyone does any sort of yearly "archiving" of alembic migrations in their projects? I know in Rails-land it can be a common practice to move all the migrations out to an archive subdirectory and use one of the autogenerated schema files as the basis for the "first" migration post-archival but so far I haven't been able to turn up anything like that in SQLAlchemy/Alembic. Just curious how others are managing their ever-growing migrations folders

[brazen charm]

not that im aware of. 😅 I will admit we have a folder of alembic migrations of nearly 500 migration files

[shut mantle]

Yeah it's not a burning need here, my versions folder is still small. I've just worked on Rails apps where they did yearly archivals so it's on my mind currently. It's nice that the autogenerated schema files are idempotent and can be manipulated into Rails migration format pretty easily.

it'd be cool if there was an alembic command to cough up an idempotent migration of the current schema of whatever DB you point it at

[brazen charm]

yeah i thought there would have been a command to merge it all into one

but couldnt find anything

[raw saffron]

alembic revision --autogenerate when pointed at a database

[shut mantle]

O_o

interesting, although at first glance it seems like it's just a way to "true up" the current state of the DB vs whatever the latest ran migration is. I'll do more reading but is there a way to get it to cough up a migration of the entire schema?

[raw saffron]

https://alembic.sqlalchemy.org/en/latest/autogenerate.html

[shut mantle]

thanks, I'll keep digging to see if it can do what I want

[brazen charm]

<@&831776746206265384> Random linky ^^

[wheat umbra]

check #c-extensions too

[west fjord]

Hello, can anyone help in this question?
"Write SQL DDL statements, which will transform the existing database schema, as initially defined within properties.sql, in such a way that any attribute-based generalization/specialization hierarchies are now expressed by explicit relations. You should choose a solution with the east impact possible to the complexity of queries as of task A.2 above."
SELECT prop_type.prop_type, COUNT()
FROM prop_type, prop_for_rent
WHERE prop_type.prop_type = prop_for_rent.prop_type
GROUP BY prop_type.prop_type;

SELECT prop_type, prop_rent_pm, COUNT()
FROM prop_for_rent
GROUP BY prop_type, prop_rent_pm;

SELECT prop_rent_pm, prop_type, COUNT()
FROM prop_for_rent
GROUP BY prop_rent_pm, prop_type;

SELECT rent_pm, COUNT () as "No. of leases per monthly rental amount"
FROM lease
GROUP BY rent_pm;

SELECT lease.rent_pm, COUNT(), t2."SUM"
FROM lease, (SELECT SUM (t1."No. of leases per monthly rental amount") as "SUM"
FROM (
SELECT rent_pm, COUNT() as "No. of leases per monthly rental amount"
FROM lease
GROUP BY lease_no, rent_pm) t1) t2
GROUP BY lease.rent_pm, t2."SUM";

SELECT lease.payment_method, lease.rent_pm, COUNT() as "No. of leases per payment method", t2."SUM" as "Overall total no. of leases"
FROM lease, (SELECT SUM (t1."No. of leases per monthly rental amount") as "SUM"
FROM (
SELECT rent_pm, COUNT() as "No. of leases per monthly rental amount"
FROM lease
GROUP BY lease_no, rent_pm) t1) t2
GROUP BY lease.payment_method, lease.rent_pm, t2."SUM";

SELECT lease_no, rent_pm, payment_method
FROM lease
GROUP BY lease_no, rent_pm, payment_method;
These are the queries

[wheat umbra]

!paste

[delicate field]

Pasting large amounts of code

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

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

[west fjord]

https://paste.pythondiscord.com/bawoyufaze.py

[blissful basalt]

If im just learning sql do i need a text editor for it or can i just use the command line thingie for it?

[torn sphinx]

if you can read it in the cmdline that's fine, personally I like to use stuff like the mySQL workbench so it's easier to read and see what I'm doing

[blissful basalt]

whats the mySQL workbench?

[torn sphinx]

just a GUI to write mySQL commands in

like SSMS in T-SQL

[blissful basalt]

Where can i download it?

[torn sphinx]

official mySQL website https://dev.mysql.com/downloads/workbench/

[blissful basalt]

thanks

[blissful basalt]

i have to sign up to some shit and there are obligatory fields that i need to fill in like workplace and company name etc and i dont work lmao

what do i do

[torn sphinx]

as long as it doesn't ask payment info, I just fill in mumbo-jumbo at those things

professional astronaut, number [phone] etc.

[blissful basalt]

aha lmao ok

thanks lol

[torn sphinx]

sure

fake it until we make it 😄

[blissful basalt]

yeah haha

[fading patrol]

Dbeaver is a nice free tool that works with many databases

[blissful basalt]

Ive already downloaded the workbench and im getting frustrated with the complicated setups so i think ill try this one for a bit first

ty tho

[shut mantle]

if you're on macOS and screwing around with mySQL/MariaDB I would recommend Sequel Pro: https://sequelpro.com/

for postgres I use Postico: https://eggerapps.at/postico/

[trim lintel]

😅😅

[lucid crane]

Are there any websites I can use to learn aiosqlite?

[storm mauve]

aiosqlite is just sqlite3 but async
sqlite3's syntax is just a variation of SQL
so you may want to start with some basic/generic SQL tutorial, then look into SQLite specific resources, then look at aiosqlite's documentation

[lucid crane]

Alright

Would this be a good choice for my first database?

I'm looking for something async

[abstract pivot]

Consider looking at sqlbolt for learning sql

[abstract pivot]

Yes

[abstract pivot]

Like what was mentioned, aiosqlite is an async wrapper around the standard sqlite3 library

Afaik most databases have an async wrapper/driver for them

E.g. asyncpg for Postgres

[little grail]

Is there something obvious that I'm doing wrong here? https://gist.github.com/Cypheriel/b86c644e1c7bd3ae6a9528559dfbd5c0

output ends up being something like

post = Posts(id=0, title='foo', body='bar', publish_date=datetime.datetime(2021, 12, 29, 21, 35, 40, 659192))
/home/cypheriel/.cache/pypoetry/virtualenvs/fastapi_sample-dWGrA7jw-py3.10/lib/python3.10/site-packages/sqlmodel/orm/session.py:60: SAWarning: Class SelectOfScalar will not make use of SQL compilation caching as it does not set the 'inherit_cache' attribute to ``True``.  This can have significant performance implications including some performance degradations in comparison to prior SQLAlchemy versions.  Set this attribute to True if this object can make use of the cache key generated by the superclass.  Alternatively, this attribute may be set to False which will disable this warning. (Background on this error at: https://sqlalche.me/e/14/cprf)
  results = super().execute(
post2 = None

which... is not at all what I'm expecting

perhaps it is that my table for posts is not accurate with the model, but... I have no idea what I'm doing

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR NOT NULL,
  body TEXT NOT NULL,
  publish_date TIMESTAMP
);

is what I'd used to create the table

[little grail]

oh, I solved it... Turns out my problem was using await session.commit() outside of the context manager

[pliant spire]

Anyone know how I can find a database or datastore for a program?
Trying to create an unofficial api for something
I guess this is more RE stuff

[torn sphinx]

a database you could make yourself, https://www.mysqltutorial.org/mysql-create-database/

what is RE stuff ?

[simple slate]

is it more efficient to use ORDER BY to sort results as needed or to use .sort/pandas?

[fading patrol]

That's a Google question https://stackoverflow.com/questions/48876556/sort-values-vs-order-by-and-when-and-why-should-i-use-which/48876660

[crisp spade]

how do you install mongodb on mac? I tried homebrew but it never works

this keeps on appearing

how do I fix this?

[torn sphinx]

java

developer

[whole perch]

i use pymongo and i used a localhost database before but now i created an online database but i cant really access it. I changed the connection url but i get the error if not str(message.author.id) in somedata.find_one({"_id":ObjectId("61cdd866c416abf63c1a5314")})["blockeduserid"]: TypeError: 'NoneType' object is not subscriptableBut if i connect with mongodb compass everything is there

and this is my other relevant code py myclient = pymongo.MongoClient(host=os.environ["mongourl"]) RedBugBot = myclient["RedBugBot"] linkedchannels = RedBugBot["linkedchannels"] somedata=RedBugBot["somedata"]

[lofty quiver]

find_one couldn't find document

[whole perch]

ik but it exists

[lofty quiver]

are you sure that it is exists?

[whole perch]

yes

[lofty quiver]

try to find without filter. If it finds, then something with your filter

[whole perch]

ok

TypeError: 'NoneType' object is not subscriptable

[whole perch]

it still doesnt work

[lofty quiver]

idk then

[whole perch]

ok

[hasty hinge]

Is it more recommendable to use a cursor per query or a cursor per connection?

I'm having some problems with mysql-connector-python. After some random time the script losts connection to the MySQL server during a query, I tried handling the exception and create a new connection to the database but now I'm getting a weakly-referenced object no longer exists" error.

[brazen charm]

new cursor per query

cursors are designed to be cheap to create, connections are not

[vale heart]

how do I clone a row in sqlalchemy? I want to change some values in the column for a given row and then add those changes as apart of a new row to my table instead of just updating.

[trim lintel]

Fetch row you want to duplicate and use those values to create a new one.

[vale heart]

how do I do it in sqlalchemy?

[simple grove]

I prefer cursor per connection

[brazen charm]

ig print out what (await.....).all() is

[torn sphinx]

generally in orms a method like all() will give you a collection/array of items even if a single match was found.

[rare valve]

#help-cookie

[delicate field]

@safe swan Please don't try to ping @everyone or @here. Your message has been removed. If you believe this was a mistake, please let staff know!

[topaz dawn]

Does anyone know how to separate chatterbot inputs from responses in ListTrainer? My bot keeps responding with messages that it's supposed to receive, not send back.

[stark sparrow]

Can't we do ```py
collection.find()

[native haven]

can you maybe share a bit more of your code

[stark sparrow]

sure

    @commands.Cog.listener()
    async def on_message(self, message):
        if not message == self.client.afk_msg:
            mongo_url = "mongo_url"
            cluster = AsyncIOMotorClient(mongo_url)
            database = cluster["database"]
            collection = database["afk"]
            embed = discord.Embed(timestamp=message.created_at, description=f"Welcome back {message.author.mention}! I have removed your AFK!", color=0x00ffff)
            results = collection.find({"_id": message.author.id})
            for result in results:
                collection.delete_one(result)
                await message.channel.send(embed=embed)

the error is from that for result in results line

[native haven]

how about coding it to use delete_many. but I think you don't need to put it in a for loop since you're using _id which iirc can't have two or more of the same id in the collection.

[stark sparrow]

i tried to remobe the for loop

didnt work

started spamming

as it is on_message

[stark sparrow]

i used without the for loop, it will continue removing me from afk

multiple times

delete_many still gives same error

[burnt turret]

the error is because you need an async for instead of for to loop through the async cursor that motor provides (https://motor.readthedocs.io/en/stable/tutorial-asyncio.html#querying-for-more-than-one-document)
but as chof64 said you should probably rewrite that query to a single delete

doing just await collection.delete_many({"_id": message.author.id}) should be effectively the same 🤔

[simple slate]

whats the proper way to store multiple pandas dfs in the same db?

I cant figure out how to write to different tables without using different files

[slender atlas]

You can create a new table in the same database file for each data frame.

[simple slate]

so would doing this work properly?

def _get_dfs(self) -> None:
  connected = False
  while not connected:
    try:
      con = sqlite3.connect("file:cache.db?mode=r, uri=True")
      connected = True

    except sqlite3.OperationalError:
      self._save_dfs()

  self.scores_df = pd.read_sql("SELECT * FROM scores", con, parse_dates=["created_at"])
  self._beatmaps_df = pd.read_sql("SELECT * FROM beatmaps", con, parse_dates=["last_updated"])
  self._beatmapsets_df = pd.real_sql("SELECT * FROM beatmapsets", con)
  # ranks_df = pd.real_sql("SELECT * FROM ranks", con, parse_dates=[column name of dates])

def _save_dfs(self) -> None:
  con = sqlite3.connect("file:cache.db")

  self.scores_df.to_sql("scores", con, if_exists="append")
  self._beatmaps_db.to_sql("beatmaps", con, if_exists="append")
  self._beatmapsets_df.to_sql("beatmapsets", con, if_exists="append")

[shy bane]

I'm planning to use 2 phase commit for MongoDB, can someone share the advantages and disadvantages for the same. Usecase: Ecommerce system

[bleak timber]

hello basically this is my code it checks if there is a new free game every fixed interval then adds it to a db but some games keep getting added can u help its part of discord bot

async def on_ready(self):
        if not self.is_started:  # Prevents the next lines from executing more than once when reconnecting
            self.load_resources()
            await self.change_presence(status=discord.Status.online,  # Changes status to "online"
                                       activity=discord.Game("!k help")  # Changes activity (playing)
                                       )
            logger.info(f"Kairo bot now online")
            self.is_started = True

            self.main_loop = True  # Changes It to True so the main loop can start
            logger.info(f"Main service was started")

            while self.main_loop:
                for i in self.MODULES:
                    if not self.data_config[f"{i.MODULE_ID}_status"]:  # Prevents games from getting added to the db
                        continue                                       # when a module isn't enabled
                    free_games = i.get_free_games()
                    # If there is at least one element this will put It in a list
                    free_games = [free_games] if isinstance(free_games, Game) else free_games
                    if free_games:
                        try:  # Checks if module author specified a threshold
                            i.THRESHOLD
                        except AttributeError:
                            i.THRESHOLD = 6
                        free_games = db.check_database(f"{i.MODULE_ID.upper()}_TABLE", free_games, int(i.THRESHOLD))
                        for j in free_games:
                            channel = self.get_channel(926357675162349588)
                            await channel.send(embed = self.generate_message(j.name, j.link))

                await asyncio.sleep(300)```

[quasi panther]

Hello everybody,
I have a question about MongoDB v5.0. Since, v4.4 mongod restart forced to rebuild indexes even if there's no corrupted one. I have checked the forums and docs if I could bypass that. But couldn't find anything on that.
Is there any work around for that? I am sure I am probably missing something out.
TIA.

[onyx nimbus]

Do you mean that you're getting duplicates? If that's the case, you could solve this by setting a candidate key in the table to UNIQUE, and set it to ignore queries that cause conflicts.

[fleet ibex]

about to loose my mind, out of nowhere one of my sqlite3 scripts has stopped working.. I had a backup script, ran it.. also nothing..no errors, the files it pulls from are still present, nothing has changed...
restarted vsc, killed the db recreated it and still data isnt being inserted from 1 script, the others work fine tho..
I routinely delete the tables and re-create them while testing, so I've been doing this for awhile and now it just decides to not work ?!?! .. rawr!

[proven arrow]

what is not working?

[fleet ibex]

it is not working correct.

[proven arrow]

so what does that mean exactly?

[fleet ibex]

I just compared it with my alpha version, line for line nothing changed.. and the alpha file isnt working either.... it means it's not inserting anything into the db, when it was 30 mins ago..

[proven arrow]

Did you try adding any logging to see what parts of the code are reached or check your error logs for your application?

[fleet ibex]

I have not begun using any of the actual logging stuff, but I inserted a print statement to verify the data it's suppose to insert it's getting from csv files is there.

holy crap.. how did this happen.. i fixed it.. but both files use a temp :inmemory: db.. and both files where as ":memory" .. and not ":memory:' ...

I had not loaded the alpha in a week or so, and working when i put it to bed... this is so strange.. but omfg.. was about to literally start crying.

backing up all my files now, that was just to weird

[proven arrow]

You should consider adding logging to you codebase if this is important

And there is no way to know why what happened without seeing the codebase.

[fleet ibex]

if, I am creating a dataframe at the start of my script, when I pass it to a function, do I need to return it if I make changes to it, or will it be updating itself as it goes from function to function?

I dont know if the the changes stay with it, where as an sql statement needs to be commited.

[true cosmos]

why don't you use dictionaries instead? and at the end if you need the info in sql just send a commend and update the whole database...

[fleet ibex]

I havent learned dict's .. maybe it would be easier, i onno.

[true cosmos]

yes, I wish someone have told me that when I was learning I could have save me hundreds of wasted time with sql

[fleet ibex]

sort of attempting to write a janky harry potter sorting hat, using and keeping track to the counts of -1 here is +1 there in a cloned table as a df.. plan was to also be able do some math in the df and then send it back to sql.

[true cosmos]

nowadays, I didn't even use sql anymore, just dictionaries and save the info in txt... I work for myself, so I don't have to deal with security so I can save my data in txt files.

[severe coral]

Learn dicts

[fleet ibex]

yes, its on my list to do.. learn dicts..

[delicate field]

@brittle rivet Please don't try to ping @everyone or @here. Your message has been removed. If you believe this was a mistake, please let staff know!

[jolly urchin]

!paste

[delicate field]

Pasting large amounts of code

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

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

[jolly urchin]

Though i am having data in the table, still i am getting empty lists:
https://paste.pythondiscord.com/sakulukaja.py

pls help me

[grim vault]

def select_all():
    connection = sqlite3.connect(DB_NAME)
    cursor = connection.cursor()
    connection.execute("""
    SELECT * FROM "Seat"
    """)
    result = cursor.fetchall()
    connection.close()
    return result

You execute the select on connection instead of cursor

[fading patrol]

!d sqlite3

[delicate field]

sqlite3

Source code: Lib/sqlite3/

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

The sqlite3 module was written by Gerhard Häring. It provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249, and requires SQLite 3.7.15 or newer.

[brave bridge]

what do you mean?

[fading patrol]

^^^ Start here, if that's what you mean

[brave bridge]

Do you want to use existing software to store data? Or do you want to implement your own database management system in Python?

[hidden plume]

i am using the database mango db. how can i delete specific stuff in a collection. the code should delete in the collection from the author the share and the stock property. this could does not work. can someone help me please

find = collection.find_one({'_id': ctx.author.id})
       if find[f'shares: {arg}'] == 0:
           collection.delete_one({'_id': ctx.author.id}, {f'stock: {arg}': arg, f'shares: {arg}': 0})
           embed = discord.Embed(title=f'You have 0 shares of {arg}')
           await ctx.send(embed=embed)

this is the error

[brave bridge]

What do you mean by "a database"?

What do you actually want to do?

[sinful drift]

a list could be used as a database, you can just store things in a list.

[brave bridge]

yep

it all depends on your application

Sometimes it's fine to store files in the filesystem, sometimes even in memory.

In other situation you'll need a relational database management system (like SQLite3 or PostgreSQL) or a really fast in-memory cache (like Redis)

[misty eagle]

hi

[hidden plume]

can someone help please?

[true swift]

In my opinion, you must to use a dictionary as a Python database. But a list is ok, too :b

[bright hound]

does anyone know how to use pandas read_parquet() and make it include the partition column in the table it returns?

there is something in spark I think where you can provide a 'base path' argument and it will adjust the schema, but I can't find anything for pandas

[potent citrus]

Hello, anyone has an idea of how to make a QR on a file and that the QR is linked to the file on the cloud?

[jolly urchin]

See there are two types of databases SQL and NoSQL. In SQL you need to know SQL Code using some third party libraries like sqlite3 and in NoSQL you don't have to write SQL schemas. For ex. MongoDB which uses json at the base level

Hope this help you🙂

[verbal lava]

what is this: Command raised an exception: AttributeError: aenter sqlite3

[whole perch]

import pymongo
from bson import ObjectId
myclient = pymongo.MongoClient(os.environ["mongourl"],port=27017)
RedBugBot = myclient["RedBugBot"]
linkedchannels = RedBugBot["linkedchannels"]
somedata=RedBugBot["somedata"]
print(somedata.find_one({"_id":ObjectId("61d191f56c7061e409ed16d6")}))```i dont know why somedata.find_one returns None but linkedchannel.find_one works

ping me please if you have a solution

[verbal lava]

What does aenter mean in sqlite?

[crisp spade]

from __future__ import print_function
import mysql.connector
from mysql.connector import errorcode

from datetime import date, datetime, timedelta

cnx = mysql.connector.connect(user='', password="", host="",
                              database='')
cursor = cnx.cursor()

tomorrow = datetime.now().date() + timedelta(days=1)


add_account = ("INSERT INTO accounts "
               "(user_ign, user_password, user_email, user_status, user_inventory, user_wallet) "
               "VALUES (%s, %s, %s, %s, %s, %s)")

data_account = ("test2", "test2", "[e-mail]", "User", '{"Apple":1, "Bomb":100}', 10000)

cursor.execute(add_account, data_account)

P.S. when i use a sql script to add values, it works fine

I tried this but the result when i look in workbench...

[crisp spade]

depends on what type of database you want to make. A database could be a simple json file or dictionary all the way to a full postgres server

if you don't want to experiment with servers yet, I suggest starting with JSON or something like sqlite3

both are easy to understand and efficent to use - only problem is they're store locally, which means you cannot connect to it on a different computer

if you want to make an online server, start with mysql, mongodb or postgres

[fading patrol]

1. put your file on the cloud platform of your choice 2) generate a QR code for the link (even Chrome can do this)

Off topic though

[long echo]

I am wondering whether I should migrate everything (during runtime too?) into an SQL database, or something similar. So that the data would be integrated better, and I would have easier time coding and managing this.

Context :
I have 3 databases.
• Database 1 is used for storing users' information. (json)
• Database 2 holds all possible misspelled users names. (json)
• Database 3 is dynamic, it is given before runtime. It holds lots of data related to a user, sorted by their names. (excel)

More Context :
The program simply goes through each of the databases.
Converts the misspelled names/aliases to the offical ones.
Using the these names, it parses the related information - and allocates the given data to the correct place.

Final Thoughts :
Is there any better way to go about it? Maybe even change the format of the database I am given (as in, how its sorted, and how it stores the data)? Something els?

[plush glen]

Can anyone help me set a user database up where i can do !removediscord (userid) and then it removes them from my database i dont have a database yet tho

[elfin bay]

Am I missing something?

import sqlite3
from disnake.ext import commands

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

class Currency(commands.Cog):
    def __init__(self, bot):
        self.bot = bot

    @commands.Cog.listener()
    async def on_ready(self):
        c.execute(
            """CREATE TABLE IF NOT EXISTS currency (
            user_id text, wallet int, bank int)
            """)
        conn.commit()
        print(f"{self.__class__.__name__} Cog has been loaded \n-----")
    
    # Commands
    @commands.command(description="Check your balance", aliases=["bal"])
    async def balance(self, ctx):
        user_id_str = str(ctx.author.id)
        print(user_id_str)
        c.execute("SELECT * from currency WHERE user_id = (?)", user_id_str)
        data = c.fetchall()
        
        if not data:
            c.execute("INSERT INTO currency VALUES (?,?,?)", (user_id_str, 0, 100))
            conn.commit()
        await ctx.reply(f"Wallet: {data[1]}\nBank: {data[2]}")

Error:

File "C:\Users\khale\Desktop\currencyBot\bot\cogs\currency.py", line 27, in balance
    c.execute("SELECT * from currency WHERE user_id = (?)", user_id_str)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 18 supplied

[brave bridge]

It should be c.execute("...", (user_id_str,))

sqlite3 was interpreting that string as a container for 18 strings

[elfin bay]

oh ok

thank you!

[lofty finch]

Hi, I'm currently learning about Spark. I want to ask, is Spark basically a customizable MapReduce ? Because from what I understand we can basically customize the spark to be like map-reduce-transform-map-reduce rather than just map-reduce ?

[surreal meadow]

1. SQL
2. SQLlight
3. postgrade
4. SQLalchemy

Someone told me that this is the best path of learning Data base languages any thing else to add or remove?

like MongoDB or thers

[agile heath]

why does typing pg_restore not output anything and no errors
im running this on linux and postgresql

[hollow hawk]

if i have an sqlite db with the info like the following. how can i import the data as a df with the index as the option_id and then the curr_price and curr_price_time as columns?

[fading patrol]

That makes sense. I wouldn't waste more then a week or two on step one, just learn the basics of SQL queries quickly and dive in to SQLite.

MongoDB is NoSQl so pretty different, you could probably learn it at any stage (although I have yet to learn it myself to be honest, so take that with a grain of salt)

[abstract parcel]

SQL is a good starting point if you want to learn about databases, like in relational database systems, when SQL is the native query language.

If you define databases as relational databases, and want to learn that, SQLite is one of the simplest and probably a decent place to start. SQLite operates on a single database file and is not as powerful and performant as Postgres, MySQL etc. which is actually a full database engine.

SQL (language / syntax) varies a bit between the different solutions like MySQL, Postgres etc. For instance you will have a lot more capabilities / functionality using SQL against Postgres and MySQL than SQLite. I would recommend SQLite only for smaller projects, when you only need to structure your relational / schema-based data locally for a small server or client application or so. Otherwise I would preferred Postgres or similar.

After understanding the basics of SQL, I would look at ORM (object-relational mapping), because it will make you life a lot easier, since you can work with databases and data models as objects and in a more abstract way.

If you want to learn about other types of database-like solutions, like NoSQL, document storage, key-value stores etc., I would recommend to take a look at:

• Redis
• Elasticsearch / OpenSearch
• InfluxDB

[fading patrol]

Have you tried this? https://pandas.pydata.org/pandas-docs/version/0.15.0/generated/pandas.read_sql.html

[abstract parcel]

I’ve used MongoDB for a few projects several years ago, when Elasticsearch were initially released. I quickly moved to Elasticsearch because it performed much better and was in my opinion more powerful and better to work with. But that’s almost a decade ago, and Mongo may have evolved into something better since then. Elasticsearch has really been my first choice on document storage / search engine for a lot of use cases ever since ☺️

[surreal meadow]

thank you for the clarification!

[plush glen]

@abstract parcel Could u help me with xampp sql?

[finite parrot]

Hey! I'm using MongoDB

Which way will be better to store members in guilds (its for economy)?

{
  "_id": GuildID,
  "members": {
    str(Member_1ID): dict(INFO1),
    str(Member_2ID): dict(INFO2)
    #etc...
  }
}

or

{
  "_id": f"{GuildID}{MemberID}",
  #and some info here
}

Ping me, when you reply, pls

[harsh pulsar]

how about this?

[
  {"_id": {"guild": GuildID, "member": Member_1ID}, **INFO1},
  {"_id": {"guild": GuildID, "member": Member_2ID}, **INFO2},
]

[jolly urchin]

Your most welcome

[night charm]

I know very little about SQL. I have a program that can send data into a single table through ODBC into MSSQL. There are about 20 fields for each row, and about 100 rows arrive every second all day. One field has a kind of ID, and there are about 500 unique IDs. I would like to be able to run some kind of statistical analysis essentially realtime throughout the day on a selection of the unique IDs. Ideally I would like to be able to pick an ID and then pull multiples days/weeks/months of data specific to this ID, run an analysis, pick a new ID, run an analysis, and then loop this forever, running it as fast as I can. Do you have any recommendations for how to do this efficiently in terms of how I should store the data? Again, I know very little about SQL. Is this something that indices would solve? Or maybe it would make sense to somehow filter this giant dataset into different tables for each unique ID?

[finite parrot]

I won’t be able to get member

[honest raven]

I'm not exactly sure about your problem and schema. But isn't it straightforward to select the data fields you want, specifying the ID using WHERE clause?

[night charm]

I figured that it would be really slow if I had tons of rows in the table. 100 rows per second of new data means potentially tens of millions of rows after a few weeks

And hundreds of millions of rows down the line. If I only want to grab data for a single day/week/month for a particular ID, would it be difficult to do that even if I were using indices?

[honest raven]

If designed and implemented well, I guess using sql index will speed up the process.

[night charm]

Does that approach slow down a lot if the table gets bigger?

I just found this random quote from stackoverflow: "Query performance depends on indexes. Without them the server has to scan the entire 4B rows. Partitioning is a data management feature, not a performance feature. If you want to search by date, index the date columns."

Assuming he's right, I guess I shouldn't worry about the size of the table

"Partitioning is never done for query performance. With partitioning the performance will always be worse, the best you can hope for is no big regression, but never improvement."

Some other relevant quote

[fleet ibex]

maybe look into SELECT TOP .. and define whatever.. group by, etc ?

[brazen charm]

depends on the indexes

speed of insertion generally decreases as the number of row increases.

for fetches, if you don't have a index the db is able to use then yes, it will slow to a crawl pretty quite

[night charm]

Hmm. Thanks. I guess I'll just try to add indices for the column with one of the ~500 unique ID's and then also for the date and/or datetime

And then maybe things will stay fast even after the table grows to be humongous

[honest raven]

If you plan to do some statistic analysis everyday, if not every second, you can probably doing it incrementally.

I mean, you don't have to start from the very beginning of the data to present each time.

Like, if you know the average number from the beginning of the time to yesterday, and you get today's data, it's not hard to calculate the average without doing everything on all the data again.

Well, your use case will involve much deeper statistics than avg, I'm just saying that as an example.

[night charm]

Yeah, I get what you are saying. But what does that mean in terms of the SQL side? Leave everything in one table going back to the very beginning and then just have indexing help me efficiently get the new chunk?

[prime kelp]

return _op_msg_uncompressed(
bson.errors.InvalidDocument: cannot encode object: FolderModel(id=ObjectId('61d2f2982fa8e9156ce07120'), name='/', file_urls=None, folders=None, create_epoch=None, last_edit_epoch=None), of type: <class 'src.cogs.ide.dialogs.navigated_saved.FolderModel'>

class FolderModel(Model, ABC):
    name: str
    file_urls: Optional[List[str]]
    folders: Optional[List[str]]
    create_epoch: Optional[int]
    last_edit_epoch: Optional[float] = None

from odmantic import Model

any ideas?

[torn sphinx]

sqlite3 is awesome!

[brave bridge]

it is

[celest sleet]

am trying to sucessfully execute an sqlinject but i cant do it 😄

so this is my supposedly vuln py async def List_By_Name(Name): conn = await db_connect() c = await conn.cursor() sql = """SELECT * FROM Users WHERE Name='%s'""" % Name #await c.execute(sql) await c.executescript(sql) fetched = await c.fetchall() await conn.close() return fetched

it dosnt even fetch normal data but it reacts on '; etc

still whats the point if it even cant fetch normal...

and if i use await c.execute(sql) it fetches normal, but if use ; char it sais You can only execute one statement at a time.

but this one is intresting, c.executescript(sql) but then i cant even fetch normal, and i dont know why

it only returns blank list

[lofty summit]

am I blind or there is no plural of find_one_and_update for mongodb (pymongo)?

[brave bridge]

What columns are there in the Users table?

You could try ' OR Name = 'Alice' OR Name = 'Bob as the name

[celest sleet]

its just 2 columns ID, Name

@brave bridgebut its weird that it dosnt even fetch a correct name

with "c.executescript(sql)"

[brave bridge]

Why do you want to execute SQL injection on this?

[celest sleet]

just to learn

nvr done sqlinjection with python

[brave bridge]

What library are you using?

[celest sleet]

aiosqlite

i mean it should be able cause i am passing in unsanitized strings

or is aiosqlite so secure?

i saw on the web, sqlite one used and he was succefull doing a sqlinject with c.execute()

maybe its diffrent with aioslite

@brave bridgewhats weird is that c.executescript, returns always empty list. no matter what if legit or not.

c.execute() i dont get further cause it refuse accept 2 executions

[brave bridge]

Seems like executescript doesn't return any data 🤷

You need to use execute if you want to retrieve data

[celest sleet]

oh that would explain then

but execute gives an error as soon it sees ;

[brave bridge]

Here's an example from the docs:

async with aiosqlite.connect(...) as db:
    await db.execute("INSERT INTO some_table ...")
    await db.commit()

    async with db.execute("SELECT * FROM some_table") as cursor:
        async for row in cursor:
            ...

[brave bridge]

You can only use one statement in an execute

what do you want to do?

[celest sleet]

hm thats not how iv done it tho

Name = await db_hack.List_By_Name(Name)

where i pass string

[brave bridge]

Can you show the whole code?

[celest sleet]

async def List_By_Name(Name):
    conn = await db_connect()
    c = await conn.cursor()
    sql = """SELECT * FROM Users WHERE Name='%s'""" % Name
    #await c.execute(sql)
    await c.executescript(sql)
    fetched = await c.fetchall()
    await conn.close()
    return fetched

[brave bridge]

What does db_connect do?

generally you should be using aiosqlite with async with blocks

[celest sleet]

async def db_connect():
    conn = await aiosqlite.connect(str(DBPATH))
    conn.row_factory = aiosqlite.Row
    return conn

hm but i have no issue with handle db this way tho

it can read n insert delete etc

[brave bridge]

you will get issues if there's an exception between opening the connection and closing it

[celest sleet]

hm ok i will rewrite the sql with your blocks and see if any diffrence

[brave bridge]

that shouldn't make any difference, I'm just saying

let me see

[celest sleet]

what i thought

but yeah your right

[brave bridge]

Have you tried using execute instead of executescript?

[celest sleet]

yeah then i cant even do ';

[brave bridge]

?

Can you show the code with execute that doesn't work?

[celest sleet]

its same just change c.executescript to c.execute

oh here we go

now we coming somewhere

i did urs with leaving ; out

it accepted that asd in there

so that basicly is an sqlinjection

but i guess i cant like ; drop table Users

etc

but i can succesfully do injection and select what ever member by id i want

.hack n ' OR ID='1' OR Name = 'Bob

@brave bridgethanks for clearing up, so basicly it was that i was so into ; that is what needs to be left out.

am so stupid

[brave bridge]

Are you making some kind of CTF?

[celest sleet]

nah just learning

to drop table it needs to be an other execution with .commit()

[celest sleet]

could be an CTF tho but wasnt my mission just to learn nvr done sqli with python only with php

now am gonna play with .commit() and insert and get it to drop table

[ornate kayak]

Is it possible to do two queries to a database at once? I'm not sure how to word it, but I have 4 tables, one of them being a join table for two others (Heroes, Roles, Affiliations and Heroes_Affiliations). I can get results between Heroes and Roles using the foreign key but want to know if its possible to include the join table in the same query as well? Or would it just be better to put the two separate results together in my own code?

Actually, I think I'm going to just do two different queries. Sorry

[harsh pulsar]

what do you mean "include the join table"? joins happen across rows, so you can include any column from any table in the join

you can concatenate two queries with union but that doesn't sound like what you really want

it would help if you provided sample data, e.g. with https://www.db-fiddle.com/

[ornate kayak]

https://www.db-fiddle.com/f/hLmwVo2cQhM5b1LbmYqfrz/1 Hopefully this is the correct link. I'm just trying to learn more about databases and wanted to know if these two steps could be done as one.

[ornate kayak]

Sorry, I just tried a third query and got back what I was looking for. Typing out the example for you helped me see what I was getting confused on. Thanks for the website though, its really helpful

[harsh pulsar]

happy to hear it! this happens a lot, the process of formulating an answerable question leads to an answer to the question

[torn sphinx]

how do you make a table using sqlite3 on replit? apparently i need to make one for the level system i want to make

is there a good video on how to use sqlite3 for discord bots?

[sinful rivet]

how to insert a list into a row

[sinful rivet]

bruh replit

[brave bridge]

Depends on what DBMS you're using, and what you're going to be doing with that list.

[brave bridge]

repl.it is fine for what it is 🤷

[stark sparrow]

can we add empty lists in google firebase?

        data = {
                "wallet_amt": 100,
                "bank_amt": 0,
                "bank_space": 1000,
                "inventory": [],
                "current_job": "no_job"
        }

it is adding everything else in the database instead of the inventory list

not useful ig

what can i use instead of lists and tuples?

[torn sluice]

So does anyone know how I would go about writing a python script that makes a request to a web server that writes to a database for a login system? I dont want the client to directly connect to the database

[fathom star]

easy, you can use express.js if you want to use javascript, or you can use something like flask for the webserver

the python script that actually sends the request can use the requests library or aiohttp if you want asynchronous

the backend can connect to the database however you like depending on the database

i.e the sqlite library for sqlite databases

[night charm]

I have a lot of data being sent to one table in a MSSQL database and don't seem to be able to change this fact. I want to automatically send all new rows based on a condition or two to other tables immediately upon arrival. Should I be using triggers for this?

[sinful rivet]

sqlite, wanna save guild members

[sinful rivet]

replit suc for d-bots

[paper flower]

Depending on what you want to store here you could just store your items in separate table

[paper flower]

Use foreignkey + separate table 🙂

I think storing a lot of elements in a single list in db wouldn't be a good idea

[woeful torrent]

I'm a bit confused about sqlalchemy ORM best practices. From what I understand, sqlalchemy sessions manage transactions internally - but what does that imply for commits? Is it fine if I commit after every insertion or do I have to manually count insertions and only commit every N insertions for better performance?

[paper flower]

I believe if you have an active transaction and begin new one data would only be sent to the database when outer transaction closes 🤔

with session.begin():
    with session.begin_nested():
        session.add(user)

[woeful torrent]

hmm so do I actually have to manually begin new sessions?

[paper flower]

You can create a context manager that would do that for you 😉

[woeful torrent]

Basically my use case is getting large amounts of metadata (multiple gb) and inserting them into a local DB in a single loop

[paper flower]

I'm not sure about performance in this case but you might want to chunk your data and commit as you go

[woeful torrent]

OK, so I would have to manually chunk my data. How do I figure out what a good chunk size is?

[paper flower]

Not sure 😅

[woeful torrent]

For some reason it's really difficult to find any definitive information about this anywhere

[paper flower]

Yep, because it's really specific use-case, i don't think chunk size would matter as long as it satisfies your memory constraints

e.g. 100-500mb

[woeful torrent]

I guess I'll just have to try and profile it

[paper flower]

You might want to use this or some other techniques mentioned in sqla documentation
https://docs.sqlalchemy.org/en/14/orm/contextual.html?highlight=bulk#sqlalchemy.orm.scoping.scoped_session.bulk_save_objects

But i think standard way of committing should be fine too

[sinful rivet]

wud

[paper flower]

I have no idea on what you want to store, could you tell me?

[frigid flicker]

Hey guys I have flask app with postgresql database I have created databases and table buy when I try to run my app I get this error

Traceback (most recent call last):
  File "/home/dhairya/PD2/app/user_service/models.py", line 79, in add_user
    db.engine.execute(query)
  File "/home/dhairya/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2075, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/home/dhairya/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 942, in execute
    return self._execute_text(object, multiparams, params)
  File "/home/dhairya/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1104, in _execute_text
    statement, parameters
  File "/home/dhairya/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/home/dhairya/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/home/dhairya/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/dhairya/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/dhairya/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/dhairya/.local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "member" does not exist
LINE 1: INSERT INTO Member VALUES('admin','$5$rounds=535000$aBkJYfG5...

 [SQL: "INSERT INTO Member VALUES('admin','$5$rounds=535000$aBkJYfG5qT7KTpsS$Ox','Admin','Admin','admin@admin','admin',True) ON CONFLICT (Username) WHERE Username='admin' DO NOTHING;"] (Background on this error at: http://sqlalche.me/e/f405)

can anyone help me

I have this member table

[torn sphinx]

Like what the other guy says, it’s fine for what it is, that’s just your opinion :/

[tulip cloak]

best video to learn Json ?

[woeful torrent]

If you just mean the json format read this page and you'll know all there is to it: https://www.json.org/json-en.html

[unborn kettle]

Hi, when I try to run this python code it gives me this error, can you help me? I'm trying to connect to a database on Oracle SQL developer. Thanks

import cx_Oracle 
cx_Oracle.init_oracle_client(lib_dir=r"C:\Users\35191\Desktop\opt\instantclient_21_3")

con = cx_Oracle.connect('abd1/abd1@localhost:1521/orcl')
print (con.version)
con.close()

Error: cx_Oracle.DatabaseError: ORA-12541: TNS:no listener

[swift ridge]

guys if I know a certain field of record, how do I take its fname and lname?

[sage lily]

Do u mean sélect ?

[narrow granite]

Is it possible to access a mongodb from different languages e.g i link a name with a birthdate and in python and then i want to acess that data with a program written in java

On reply pls ping

[paper flower]

Don't see why not

[storm mauve]

yeah, as long as you have a "driver" that can connect to it, the Database can be accessed by any language - that applies for just about all of them (maybe except sqlite)

[paper flower]

Why not sqlite though? 🤔

[storm mauve]

I'm not sure if it is supported as widely as others like Mongo or Postgres

[paper flower]

It is commonly used in client-side applications, so it should be

[storm mauve]

yeah, checking the website now, it is public domain, open source and claims to be cross-platform - so probably supported
ignore the fact that cross-platform has nothing to do with the languages which support it

[paper flower]

😅 java should have a client for sqlite

[narrow granite]

Thx

that makes my life easy

[hard totem]

hi here! i have have flask app with sqlite database and need help updating a database column with enum type.
below my alembic migration script as well as the error showing my logs:

rom alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'aa7fb94eee6b'
down_revision = 'db5a451270ef'
branch_labels = None
depends_on = None


def upgrade():

    current_state_type = sa.Enum('uninstalled', 'installed','broken', name='current_state_type')
    current_state_type.create(op.get_bind())
    op.add_column('installed_contents', sa.Column('new_current_state', current_state_type , nullable=True))
    op.execute('update installed_contents set new_current_state = CAST(CAST(current_state as text) as current_state_type)')
    op.alter_column('installed_contents', 'current_state', new_column_name='old_current_state')
    op.alter_column('installed_contents', 'new_current_state', new_column_name='current_state')
    op.drop_column('installed_contents', 'old_current_state')
    op.get_bind()
    op.execute('DROP TYPE current_state_type')
    op.alter_column('installed_contents', 'current_state', nullable=False)

def downgrade():
    current_state_type = sa.Enum('uninstalled', 'installed', name='current_state_type')
    current_state_type.create(op.get_bind())
    op.add_column('installed_contents', sa.Column('old_current_state', current_state_type , nullable=True))
    op.execute("update installed_contents set current_state = 'uninstalled'")
    op.execute("update installed_contents set old_current_state = CAST(CAST(current_state as text) as current_state_type)")
    op.alter_column('installed_contents', 'current_state', new_column_name='new_current_state')
    op.alter_column('installed_contents', 'old_current_state', new_column_name='current_state')
    op.drop_column('installed_contents', 'new_current_state')
    op.get_bind()
    op.execute('DROP TYPE current_state_type')
    op.alter_column('installed_contents', 'current_state', nullable=False)

my logs:
UserWarning: Skipping unsupported ALTER for creation of implicit constraintPlease refer to the batch mode feature which allows for SQLite migrations using a copy-and-move strategy.
util.warn(
Traceback (most recent call last):
File "/home/desir/Documents/lasttest/olip-api/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "/home/desir/Documents/lasttest/olip-api/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: error in table installed_contents after drop column: no such column: old_current_state

[acoustic thunder]

is there a way in mongodb, to insert a named array

like name the array going ionto the colelction instead of being like Object 1

[paper flower]

TBH Using enums with alembic + postgres was too much for me so i switched to using string (not-native) enums

Enum(native_enum=False) iirc, sqlalchemy would use strings instead

[sinful rivet]

but how to do it and why

[sinful rivet]

oh, next message

[paper flower]

Why? It's easy to query it. How? First tell me what you want to store 😅

[sinful rivet]

:l a guild's members

[paper flower]

Would it be just guild members or some additional info too?

[sinful rivet]

just a guild member in a row

[paper flower]

What's the purpose of that really?
You probably could just get list of guild members using discord api

[sinful rivet]

bru

not server guild, like a game guild :l

[paper flower]

Ah, i see 😅
Is there something like users table?

[sinful rivet]

uh, wdym :v

[paper flower]

You're storing id's of users that are in a guild, there should be a table that stores these users too, right?

[sinful rivet]

yes

[woeful torrent]

Is there a way with sqlalchemy to auto-increment a column that isn't a primary key?

[paper flower]

You can create two tables and define relationship between them using secondary table

create table users (
    id integer primary key,
    username varchar not null
);


create table guilds (
    id integer primary key,
    name varchar not null
);


create table guilds__membership (
    user_id integer,
    guild_id integer,
    foreign key(user_id) references users(id),
    foreign key (guild_id) references guilds(id),
    primary key (user_id, guild_id)
);

insert into users (username) values ("Doctor");
insert into guilds (name) values ("A Guild");
insert into guilds__membership (user_id, guild_id) values (1, 1);

/* Would select all users and their guilds, would be easier if you use an ORM */
select users.*, guilds.* from users
join guilds__membership gm on gm.user_id == users.id
join guilds on gm.guild_id == guilds.id;

[paper flower]

Yep! https://docs.sqlalchemy.org/en/14/core/metadata.html#sqlalchemy.schema.Column.params.autoincrement

[woeful torrent]

I just get the column set to null whether I set "auto" or True

Also that page seems to suggest that using auto-increment on a non-primary key isn't possible...

[paper flower]

🤔 Maybe it doesn't work that way after all. What DBMS are you using?

[woeful torrent]

I'm testing with sqlite

which doesn't support incrementing compound primary keys

but having a local, auto-incrementing column would be extremely useful

[paper flower]

Also why do you need an autoincrement on non-pk column?

[woeful torrent]

basically I'm retrieving a database of articles and tags

The tags have global IDs

[paper flower]

mhm 🤔

[woeful torrent]

however, since I'm only collecting a subset I also need local IDs that are auto-incrementing

so I need the remote IDs for verifying integrity but the local ones for other purposes

[paper flower]

Why exactly you need local ids here?

[woeful torrent]

it's a bit hard to explain - basically the local IDs need to be between 0 and the total size of the subset I keep track of

since I need them as indices into densely packed matrices eventually

[paper flower]

I really don't understand why you need some local id to keep track of your tags when your tags already have ids

[woeful torrent]

the remote IDs are in a larger range with gaps

which is an issue for the end goal of my application

I guess at this point I'll just have to increment that manually and insert without using auto-increment

I already tried making the local ID my primary key and leave the global one as just an integer with a unique constraint

[paper flower]

Why exactly you need these id's densely packed?

[woeful torrent]

but that leads to a whole range of other issues

[woeful torrent]

Basically if I want to relate tags to indices into matrices or higher dimensional tensors I need a direct mapping to an index in the range 0 to N - where N is the size of my local subset of tags

but that's not a database issue

it's just a requirement from the task I'm using the database for ultimately

[paper flower]

Is it related to ml?

[woeful torrent]

yes

[paper flower]

Perhaps you could create a second column that would use autoincrement? 🤔

That still could lead to issues if you say delete tags

[woeful torrent]

That was what I was trying to do but isn't supported apparently

[paper flower]

I don't think sqlite supports autoincrement on non-primary keys

[sinful rivet]

;-; not understand

[paper flower]

sql could be hard to understand 😅
Do you understand these two tables?

create table users (
    id integer primary key,
    username varchar not null
);


create table guilds (
    id integer primary key,
    name varchar not null
);

[sinful rivet]

varchar no

[paper flower]

Postgres supports serial on non primary key columns

create table tags(
    id serial primary key,
    local_id serial unique
);

insert into tags (id) values (42);
insert into tags (id) values (43);
insert into tags (id) values (44);

select * from tags;

id     local_id
42     1
43     2
44     3

[paper flower]

varchar is just a text

Like str in python

[sinful rivet]

oh ok

[sinful rivet]

and what is primary key

ik integer

[woeful torrent]

hmm that's good to know, I'll keep it in mind

[paper flower]

It's a primary key that i used to identify a specific row in your database, discord users have id's for example

[sinful rivet]

wut

[paper flower]

If you have two or more users you have to differentiate between them, you could use some sort of identifier to do that

[sinful rivet]

oh ok

[paper flower]

primary key guarantees that these values would be unique and you could reference them from other tables

[woeful torrent]

Now that I removed that field for the time being I noticed a much more severe issues though.
I have a many-to-many relationship between articles and tags like this:

    tags = orm.relationship(
        "Tag",
        secondary=Table(
            "tag_relations",
            Base.metadata,
            Column("tag_id", ForeignKey("tag.id"), nullable=False, primary_key=True),
            Column("article_id", ForeignKey("article.id"), nullable=False, primary_key=True),
        ),
        backref="articles"
    )

The issues is, whenever I try to add an Article object which has tags that already exists, it tries to insert that existing Tag again and raises an exception. If I try to merge the tags first and then add them to the articles, it raises and exception because it tries to add the Article again

I'm not sure what I'm doing wrong here

[sinful rivet]

hmmmm

so i must wipe my database again?

[paper flower]

If you don't have any valuable data in it 😅

[paper flower]

You don't need nullable with primary_key, also i didn't run into any issues with adding duplicate entries into relationship (yet) 😅

Is Tag persistent before you're trying to add it to Article?

[woeful torrent]

This is what I've tried:

    def add_article(self, article: Article, tags: List[Tag]) -> None:
        for tag in tags:
            self._session.merge(tag)
        self._session.add(article)
        article.tags = tags
        self._session.commit()

With this I run into the issue that the article is added more than once

[paper flower]

You mean tag is added more than once?

[woeful torrent]

No, the article

[paper flower]

🤔

[woeful torrent]

If I remove that for loop then the tags are added more than once

[paper flower]

What error are you getting exactly?

[woeful torrent]

sqlite3.IntegrityError: UNIQUE constraint failed: article.id

If I use this more intuitive approach:

        image.tags = tags
        self._session.add(image)
        self._session.commit()

I get this error instead: sqlite3.IntegrityError: UNIQUE constraint failed: tag.id
Here I get that it's caused by the add also trying to add each tag again whether it exists or not - I just couldn't find a solution that only inserts the ones that aren't there

The merge approach was my attempt at that which just ended up inverting the issue

[paper flower]

Maybe has something to do with https://docs.sqlalchemy.org/en/14/orm/relationship_api.html#sqlalchemy.orm.relationship.params.active_history ?

Since you're replacing reference to whole tags list

[woeful torrent]

I honestly don't understand what that does

[paper flower]

Me neither 🙂

[woeful torrent]

I guess I'll have to look into it more tomorrow...

Seems like the ORM is actually making this much harder rather than easier...

[paper flower]

You probably should try to save your tags first 😉

[digital jolt]

hi

im new

[sinful rivet]

oh right, why my bot always wipe a table in my database when reruned

[sinful rivet]

ok, what u wanna ask

[sinful rivet]

hi new, i'm dad!

[sinful rivet]

someone?

[brave bridge]

can you show your code?

[sinful rivet]

database https://paste.pythondiscord.com/sapocejuma.sql
main https://paste.pythondiscord.com/gisocobaye.py

[brave bridge]

You never commit the changes you do to the schema

So when a cursor is destroyed, all the changes are gone.

[sinful rivet]

oh i see, ok ty

[digital jolt]

lol

[foggy walrus]

Hello guys! So im trying to solve a following problem. I have came up with this codeSELECT STUDENT_ID SUM(MARKS) FROM MARKS WHERE MARKS>=500 ORDER BY MARKS DESC GROUP BY STUDENT_ID; However, the output is You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY STUDENT_ID' at line 3 What could be the issue?

I changed my code to this SELECT STUDENT_ID, SUM(MARKS) FROM MARKS WHERE MARKS>=500 GROUP BY STUDENT_ID ORDER BY MARKS DESC; and i get the following error Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'MARKS.MARKS' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[grim vault]

If you want to order by the sum you'll need to refer to the column by number or give it an alias:

SELECT STUDENT_ID, SUM(MARKS)
    FROM MARKS WHERE MARKS>=500
    GROUP BY STUDENT_ID
    ORDER BY 2 DESC;
-- or
SELECT STUDENT_ID, SUM(MARKS) AS "MARKS_SUM"
    FROM MARKS WHERE MARKS>=500
    GROUP BY STUDENT_ID
    ORDER BY MARKS_SUM DESC;

And you only sum marks >= 500, so if a student has one 500 mark and one 70 mark, the sum will still be 500.

[narrow granite]

With MongoDB motor asyncio driver, can i update to values at once with await collection.update_one({'key': 'value'}, {'$set': {'key': 'value'}}) ?
e.g with await collection.update_one({'key': 'value'}, {'$set': {'key': 'value'}{'key2': 'value2'}}) or sth different

if you can, how?

thx for every response

On reply pls ping

[burnt turret]

https://docs.mongodb.com/manual/reference/method/db.collection.update/#use-update-operator-expressions---inc-and--set- there's an example under this where it shows the right syntax

[narrow granite]

thx

[ornate kayak]

When I use array_agg to query in Postgres, it sometimes duplicates the response. Is there a specific cause for this?

[hallow inlet]

hello i'm having some trouble with MySQL

why is this query not working

https://pastebin.com/Hvsz1AGR

can't see any syntax errors

i'm running it on python

[crimson sun]

Are there any asyncio-compatible MySQL adapters that natively support prepared statements (not just parameters like aiomysql supports)

[paper flower]

sqlalchemy has prepared statements, maybe you could use orm isntead of using a dbapi library directly? 🤔

It's client side though 😅

[crimson sun]

Yeah, so it's parameters with escaping I'm guessing?

[paper flower]

Ofc

[crimson sun]

If that's the only way it's the only way, in which case aiomysql will work fine, was just trying to be super safe™

Thanks!

[paper flower]

Well, i think aiomysql should escape provided parameters

It wouldn't make sense not to do that

[crimson sun]

Yeah, it does, so I'll just use that. I'm doing so little SQL that having a full ORM is a bit overkill

[paper flower]

sql is hard to maintain imo, plus mapping your data into python classes instead of parsing returned tuples is better

[past current]

Guys a quick question. A company have branches right. Now I have a table called Address and one called Branch. What would be the relationship between them? I'm a bit stuck here.

[lost plaza]

hello, i wanna search a data by an id but the id was fullnumbers and got error
bson.errors.InvalidId: '839288460308774982' is not a valid ObjectId, it must be a 12-byte input or a 24-character hex string
code :

postid = "839288460308774982"
prefixis = colname.collection.find_one({"_id" : ObjectId(postid)})
print(prefixis)```
how i can solve this?

[unique cradle]

https://thecybercode.com

[opal epoch]

hi guys

{% extends "learning_logs/base.html" %}
could someone explain what this line does?

[fading patrol]

Presumably one to one: one branch per address and one address per branch

[opal epoch]

got it nvm

pls help

<form action="{% url 'learning_logs:edit_entry' entry.id %}" method='post'> {% csrf_token %} {{ form.as_p }}

i don't understand this

i am confused

someone pls hlep

help

dun

nvm

[torn sphinx]

name = input('name: ')
hwid = input('hwid: ')
sql = "INSERT INTO customers (name, hwid) VALUES (%s,%s)"
val = (f"{name} , {hwid}")
mycursor.execute(sql, val)
mydb.commit()

error:

  File "C:\Users\Δημήτρης Κολιόπουλος\Desktop\spoofer\hyper.py", line 31, in <module>
    mycursor.execute(sql, val)
  File "D:\coding_things\pythonV2\lib\site-packages\mysql\connector\cursor_cext.py", line 257, in execute
    prepared = self._cnx.prepare_for_mysql(params)
  File "D:\coding_things\pythonV2\lib\site-packages\mysql\connector\connection_cext.py", line 679, in prepare_for_mysql
    raise ValueError("Could not process parameters")
ValueError: Could not process parameters

[torn sphinx]

can someone help?

[raw saffron]

change to

val = (name,hwid)

[torn sphinx]

i got this error now

Traceback (most recent call last):
  File "C:\Users\Δημήτρης Κολιόπουλος\Desktop\spoofer\hyper.py", line 31, in <module>
    mycursor.execute(sql, val)
  File "D:\coding_things\pythonV2\lib\site-packages\mysql\connector\cursor_cext.py", line 269, in execute
    result = self._cnx.cmd_query(stmt, raw=self._raw,
  File "D:\coding_things\pythonV2\lib\site-packages\mysql\connector\connection_cext.py", line 521, in cmd_query
    raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'hwid' in 'field list'

ohhh my bad

i have made a type error

lemme ses

see

okay it works! it was just a type error of mine

okay i have another problem. Because i am kinda new to mysql thing, i now want to see if the hwid (from the sql) matches the hwid from my pc and if it is correct, only then will the program run. (i have the code for the hwid authentication, i just want to get the "hwid" column from mysql to check if tha hwid is correct

[raw saffron]

heading off now, but you can just fetch it like this:

name = 'testcustomer'
sql = "SELECT hwid FROM customers WHERE name = ?"
result = cursor.execute(sql, (name))

[torn sphinx]

so i changed my code to

[frosty stone]

Yo.
I have a sqlite3 database (python buildtin) and it does always give me results like that:

[(123123, "hello", "bananas")]

Problem: I cant use this.
Like: i[1] doesn't work.
e.g: I want to read the "bananas" key.

[torn sphinx]

name = 'testcustomer'
sql = "SELECT hwid FROM buyers WHERE name = ?"
result = mycursor.execute(sql, (name))

and i get this error

 Command raised an exception: ValueError: Could not process parameters

[brave bridge]

!e
[(123123, "hello", "bananas")] is a list with one element, and that element is a tuple. If for some reason you're sure that it has one element, you can do: ```py
rows = [(123123, "hello", "bananas")]
print(rows[0])
print(rows[0][2])

[delicate field]

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

001 | (123123, 'hello', 'bananas')
002 | bananas

[frosty stone]

Thanks!

[torn sphinx]

hi. here is my code

import subprocess, requests, time, os

#Strings
hardwareid = subprocess.check_output('wmic csproduct get uuid').decode().split('\n')[1].strip()
site = requests.get('https://pastebin.com/wWi4Jas8%27%27)


#Actual Authentication System
try:
    if hardwareid in site.text:
        pass
    else:
        print('[ERROR] HWID NOT in database')
        print('[HWID:' + hardwareid + ']') 
        time.sleep(5)
        os._exit()
except:
    print('[ERROR] FAILED to connect to database')
    time.sleep(5) 
    os._exit()

and it works just fine! But i want it to change it to get the HWID from an sql databse which this one

name of the table = buyers & columns = name and hwid

and i want to take only tha hwid from above and try matching it with the one that my pc has (which works only if i manage to get the hwid code from the sql). Any ideas??

[torn sphinx]

could you also help me solve my problem if you can?

[fleet ibex]

try (name,) @torn sphinx

[torn sphinx]

where ?

[fleet ibex]

in your result =

[torn sphinx]

doesnt work, i have tried it

[fleet ibex]

doh, .. try..
mycursor.execute (sql)
result = mycursor.fetchall()

that may not work tho.. you need db.execute . depending on your db connection you made

looking at older posts,
mydb.execute(sql)

anyways gl @torn sphinx perhaps do a intro tutorial :)

[torn sphinx]

okay thanks for ur help!

i will try

actually i figured out how to get the hwid from the sql. Basically i changed the code to ```python
sql = "SELECT hwid FROM buyers WHERE hwid = hwid"
mycursor.execute (sql)
site = mycursor.fetchall()

and i also printed the result. But although my hwid code is in the sql it doesnt recognize it. here is tha cmd

[('53408763450896453089-',), ('6ED4D949-CAA6-C819-A85E-2CF05D2A9524',)]
[ERROR] HWID NOT in database
[HWID:6ED4D949-CAA6-C819-A85E-2CF05D2A9524]

[balmy folio]

excuse my ignorance with regards to databases, but if my table (with 5 data entries in it) does not have anything with a primary key of 1, is there a way to repopulate an object with pk=1?

[vapid mist]

does anyone know how to completely remove postgresql from their vps? im getting a bunch of errors and cant connect to nothing and when i uninstall/ re install its like all configs are saved

[livid moss]

how to return null if no Data found in postgres?
example select * from _id where id = 1

[wanton spindle]

someone wanna help me set up a otp spoof bot? want a private channel on tele thats not so damn expensive... lol

[torn sphinx]

can someone with psql im on ubuntu 20.04

root@kaihlmfao:~# psql
psql: error: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?```

i keep getting this error

[tawny sail]

what does service postgresql status give ?@torn sphinx

also, which database migration tool do you guys use in python? other than the django one, for postgreSQL

I am using aerich along with tortoiseORM for now, but just wonder if there are any better options

[torn sphinx]

root@kaihlmfao:~/ryuk# service postgresql status
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Fri 2022-01-07 05:36:33 CET; 1h 33min ago
   Main PID: 27232 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 1066)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Jan 07 05:36:33 kaihlmfao systemd[1]: Starting PostgreSQL RDBMS...
Jan 07 05:36:33 kaihlmfao systemd[1]: Finished PostgreSQL RDBMS.```

[tawny sail]

looks like it exited

run sudo systemctl restart postgresql and see what happens

run the status command again

or you can also run sudo systemctl status postgresql

[lost plaza]

um did someone using mongodb? i need help

[torn sphinx]

hello guys! I have this code ```python
import subprocess, requests, time, os

hardwareid = subprocess.check_output('wmic csproduct get uuid').decode().split('\n')[1].strip()
sql = "SELECT hwid FROM buyers WHERE hwid = hwid"
mycursor.execute (sql)
hwid = mycursor.fetchall()
print(hwid)

try:
if hardwareid in hwid:
pass
else:
print('[ERROR] HWID NOT in database')
print('[HWID:' + hardwareid + ']')
time.sleep(5)
os._exit()
except:
print('[ERROR] FAILED to connect to database')
time.sleep(5)
os._exit()

I have put my hwid in the sql database, and when i run the program it print it in the cmd, but it says that it is no correct. I think it doesnt read it right. What do you think? pla ping me

[frosty stone]

Hello! I'm using a sqlite3 database and I want to update the data from a table.This is my code:

DATABASE_LOCALDATE_CURSOR.execute("UPDATE {} SET id1={} AND string2='{}' WHERE id={}".format(tablename,id1,string2,unique_id))
DATABASE_LOCALDATE_CURSOR.commit()

But it does nothing :/

[torn sphinx]

is there any reason why id1 is written without '' and id2 is written with '' ?

[frosty stone]

id2 is a string

[torn sphinx]

hello guys! I have this code ```python
import subprocess, requests, time, os

hardwareid = subprocess.check_output('wmic csproduct get uuid').decode().split('\n')[1].strip()
sql = "SELECT hwid FROM buyers WHERE hwid = hwid"
mycursor.execute (sql)
hwid = mycursor.fetchall()
print(hwid)

try:
if hardwareid in hwid:
pass
else:
print('[ERROR] HWID NOT in database')
print('[HWID:' + hardwareid + ']')
time.sleep(5)
os._exit()
except:
print('[ERROR] FAILED to connect to database')
time.sleep(5)
os._exit()

I have put my hwid in the sql database, and when i run the program it print it in the cmd, but it says that it is no correct. I think it doesnt read it right. What do you think? pla ping me

[fleet ibex]

1st, your try block is for results.. which wont tell you if the db connection failed, but that the result you were looking for isnt there.. could lead to unneeded debuggin later.
2nd, what says no correct.. and error or your code at which line?
@torn sphinx

[frosty stone]

I found the error!
DATABASE_LOCALDATE_CURSOR.execute("UPDATE {} SET id1={} AND string2='{}' WHERE id={}".format(tablename,id1,string2,unique_id))
didn't work and didn't give me the error. (no response)
I had to replace the AND to a ,

[gleaming jewel]

i wanted to learn sql , anyone has any good resources to learn?

[fleet ibex]

which sql?

[gleaming jewel]

postgres

[fleet ibex]

https://www.techonthenet.com/postgresql/index.php

i like it because it has all the sql dbs there to look at

[gleaming jewel]

thanks man

[lost plaza]

did anyone know how to fix error:
Dictionary entries must contain key/value pairs
code:
dbcol.update_one({"_id":(idis),{"$set":{"prefix":(prefixis)}}})

[torn sphinx]

key "_id" has value (idis) so far so good

after that you have a key of

{"$set":{"prefix":(prefixis)}}
``` that has no matching value

it's also not allowed to have a dictionary as a dictionary key by the way, so even if you added a value you would get:

idis = "Articuno"
prefixis = "Zapdos"
AloneInSkyDictionary = {"_id":(idis),{"$set":{"prefix":(prefixis)}}: "Moltres"}

TypeError: unhashable type: 'dict'

@lost plaza I think you can fix it with this info, if not let me know

hint: though {"$set":{"prefix":(prefixis)}} cannot be a key, it can be a ....e

[torn sphinx]

it gives the error that the hwid was not found. But i have inserted it in my sql and it also prints it before it says that tha hwid is not in the database. It just doesnt read it properly. Here is the cmd error

[fading patrol]

What OS? If it uses apt you can try to purge the package

[lost plaza]

uh i still cant get it, im new at it tho...

[torn sphinx]

oh that's okay, the answer was value
In other words, this is the correct version fo your code

idis = "Articuno"
prefixis = "Zapdos"
AloneInSkyDictionary = {"_id":(idis),"Moltres": {"$set":{"prefix":(prefixis)}}}
print("Success")

[lost plaza]

hmmm for what is the "Moltres" ?

[torn sphinx]

In summary now you have a dictionary with two keys.
The 1st key has a String value
The 2nd key has a Dictionary value

[lost plaza]

oh so it need a string first?

[torn sphinx]

so you have equal amounts of keys and values 😉

[lost plaza]

wait so does the string will change the docs or wht?

[torn sphinx]

The values of a dictionary can be of any type, but the keys must be of an immutable data type such as strings, numbers, or tuples.

[lost plaza]

wait"

[torn sphinx]

oh just to be clear, I don't know what dbcol.update_one is (also because you only sent that bit)

what I told you explains the error

Dictionary entries must contain key/value pairs
```  nothing more

[lost plaza]

uh

[torn sphinx]

It's up to you now to use this knowledge to fix your method call

[lost plaza]

so dbcol is just mongodb things to set the dbname and collection

[west furnace]

@lost plaza you're using mongodb?

[lost plaza]

yes i am

[west furnace]

isn't that the library where you can do a.b.c.d.e.f.g.... infinitely and fails super late?

[lost plaza]

im not pro at it

[lost plaza]

wut?

[west furnace]

iirc, like....

some objects, you can get any attribute and then any attribute again

and so on

[lost plaza]

idk bruh im stressed cuz this

but if u know what u do it will be usefull u know

[lost plaza]

given error update_one() missing 1 required positional argument: 'update'

ah i get the working code

[lost plaza]

tq for ur help

[torn sphinx]

you're welcome

[barren crypt]

Hi

Can anyone here help me with some django questions?

[torn sphinx]

depends on the question, what do you want to know ?

[barren crypt]

My question is: Is there any way to annotate a foreign key to a queryset and then access the foreign model data via the annotated value?

I mean sth like:
a = city.objects\ .annotate(dog_fk = F('house__dog'))\ .values('dog__age')

But i know that doesnt work

[torn sphinx]

hellow guys! I have this code:

import subprocess, requests, time, os
hardwareid = subprocess.check_output('wmic csproduct get uuid').decode().split('\n')[1].strip()

sql_select_Query = "SELECT hwid FROM buyers"
cursor = mydb.cursor()
cursor.execute(sql_select_Query)
# get all records
records = cursor.fetchall()

try:
    for row in records:
        hwids = row[0]
        print(hwids)
        if hardwareid in hwids:
            pass
    else:
        print('[ERROR] HWID NOT in database')
        print('[HWID:' + hardwareid + ']') 
        time.sleep(5)
        os._exit()
except:
    print('[ERROR] FAILED to connect to database')
    time.sleep(5) 
    os._exit()

Here is my problem: When i run it, despite my hwid is in the sql it says that it is not found. Here is the error from cmd

53408763450896453089-
6ED4D949-CAA6-C819-A85E-2CF05D2A9524
[ERROR] HWID NOT in database
[HWID:6ED4D949-CAA6-C819-A85E-2CF05D2A9524]
[ERROR] FAILED to connect to database

This is printed in the cmd. As you can, when I print the hwids before check them, you can see it prints 2. The first is just random numbers but the second one is the correct one. How can i fixe it? Plz tag me ❤️

[grim vault]

Why not just:

import subprocess, requests, time, os
hardwareid = subprocess.check_output('wmic csproduct get uuid').decode().split('\n')[1].strip()

hwid_stmt = "SELECT COUNT(*) FROM buyers WHERE hwid = %s"
cursor = mydb.cursor()
cursor.execute(hwid_stmt, (hardwareid,))
hwid_count = cursor.fetchone()[0]

if hwid_count == 0:
    print('[ERROR] HWID NOT in database')
    print(f'[HWID:{hardwareid}]') 
    time.sleep(5)
    os._exit()

[sage arch]

Anyone use cx_Oracle and got around the CLOB issues where when you close the db connection you cant access the CLOB data? How did you get around it. Currently I save to a csv but seems a bit akward

[grim pier]

Hey im using MySQL Within my code, do i need to constantly keep opening the connection or can i leave it open?

Everytime i update anything on the DB im using py sql = await connect_to_sql_db() async with sql.cursor() as cursor:

[fading patrol]

You can generally leave the connection open

Close and reopen cursor on same connection

[grim pier]

@fading patrol I had a little play around cant seem to get it working, i tried making sql a global variable. is this the correct way?

[cobalt forge]

quick and quite a dumb mongodb question why is my data not inserting (ignore the disc bot code)

profile = {
            "userId":ctx.message.author.id,
            "userTag":ctx.message.author,
            "balance":0
        }
collection.insert(profile)

it inserted before i added balance, but now after adding it it doesn't insert, anyone know why?

[fading patrol]

Haven't worked with MySQL much, is this maybe the same issue you're having? https://stackoverflow.com/questions/19440055/correct-way-of-keeping-mysql-connection

[past current]

Hey guys, I need help for designing a notification system. This is what I'm trying to accomplish, 1 user can trigger a notification. There can be 1 or multiple users who gets notified by the event. And I need to keep track of whether or not someone has seen the notification. Also there will be different types of notification? Could someone sincerely try to help me?

[sleek oyster]

guys need some help

connection = psycopg2.connect("postgres://postgres:banana_2@localhost:5432/postgres")
connection.autocommit = True

crs = connection.cursor()

crs.execute("SELECT * FROM raw LIMIT 2000")

records = crs.fetchall()



def age():
    for record in records:
        try:
            animal_data = record[1].get("animal")
            age = animal_data["age"]
            print(age)
            min = age.get("min")
            print(min)
            crs.execute(f"""
            INSERT INTO age_table(unique_aer_id_number, min, unit, qualifier)
            VALUES (%s, %s, %s, %s)
            ON CONFLICT DO NOTHING
            """), (record[0], age.get("min"), age.get("unit"), age.get("qualifier"))
        except Exception as E:
            print("Error: {}".format(E))

I am trying to insert this data to a table

LINE 3:             VALUES (%s, %s, %s, %s)```

I get this error

it's not the data types, everything is a string

any idea what could it be?

[fleet ibex]

you didnt pass in records to your function..

assuming its processed in another that is

[sleek oyster]

@fleet ibex

the records are there, i just didn't include them 😛

[fleet ibex]

sorry was a stab in the dark, I'm unfamiliar with the .get in queries

[fading patrol]

Off topic and haven't tried it but maybe this will help: https://github.com/liiight/notifiers

[visual gust]

p sure pg uses $X formatting

eg $1, $2 etc

ah nope psycopg uses %s formatting

[sleek oyster]

it was the """)

another dude figured it out 😄

i put the parenthesis in the wrong place

oopsie

[barren minnow]

Hey, does anyone know where I can get help on MySQL?
I can't find a discord server for MySQL

[fading patrol]

Try asking right here, or Stack Exchange or Reddit or IRC or Slack https://www.reddit.com/r/mysql/comments/o6dj8k/discord_group_for_mysql/?utm_medium=android_app&utm_source=share

[barren minnow]

Thanks 🙂
I was hoping that i wouldnt have to use a non-discord platform 😅 but i guess i'll just bite the bullet

[fading patrol]

Again you can try asking here. The Coding Den also has a database channel

[barren minnow]

I guess I'll ask here, and if no one can help, then I'll go to The Coding Den

I'm using Visual Studio Code.
I'm trying to learn MySQL (using the tutorial from Fireship).
I'm getting this error ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

[stiff gale]

hey for some reason when i run this little bit of code:

cursor.execute("SELECT * FROM vods WHERE id=%s", (vod_id,))
entries = cursor.rowcount
result = cursor.fetchone()

The rowcount = 0 even when it does find a result in my select query?

i am very confused

[fading patrol]

Can you share the tutorial link and/or the relevant details about how they have you trying to connect?

[barren minnow]

This is the tutorial https://youtu.be/Cz3WcZLRaWc
The problem I'm having is basically at 6:45
I think I'm doing everything they're doing in the video, but I get that error
When I run the create table users block, I get that error.
I've tested the connection like he does in the previous step, and it connects fine

[fading patrol]

Love the $30 pillow LOL.

Does it have you check your client version and does it say what it should be

[barren minnow]

I'll rewatch it and check

He seems to be using 8.0.23, whereas I'm using 8.0.27.
But it doesn't seem to be a 8.0.23 specific tutorial.

maybe im using the wrong driver

I think I'm using the correct driver
https://marketplace.visualstudio.com/publishers/mtxr
I'm using their SQLtools, and their MySQL driver

🤔 I have a more detailed error message
Request connection/GetChildrenForTreeItemRequest failed with message: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

The only thing I can think of is that the service i have is named MySQL80.1, because i had created a previous MySQL80, and i didn't realize it wasnt deleted properly when i reinstalled MySQL

could that be the cause?

I don't think it should be, since VS code is telling me it's connecting to the correct server

[unborn kettle]

Does anyone have knowledge about cx_oracle here?

[delicate field]

@torn sphinx Please don't try to ping @everyone or @here. Your message has been removed. If you believe this was a mistake, please let staff know!

[vapid mist]

how can i index it correctly to get just the id/int

[brave bridge]

You don't get a single record back, you get a list

so query_result[0]["log_channel"]

or use the fetchval method

[vapid mist]

thank you 🙏

[fiery anvil]

SQLAlchemy is harder than I thought it would be, maybe I’m not good enough at SQL yet but the documentation wording is hard, despite the code being pretty easy to follow

I’m used to Java docs where the fields and types were easier to see ig

[pure sleet]

what's hard to understand.

?