databases

[chrome mango]

ill try a await cur.commit()

see if that helps

oof, ill go look at the doc

[paper flower]

conn.commit

[chrome mango]

ahh, thank you

yup that works

wierd how the docs dont really mention it that much

[simple grove]

date_add( ) ?

SELECT DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 2 HOUR)```

like this ?

that will select one value, the current time + 2 hrs

yeah close to that

wait those two statements ran together

see how it goes right from CREATE COLUMN .... to SELECT DATE_ADD

with no semicolon / line break

can you do

select 1;

you're in mysql-cli right

ohh

it doesnt need the SELECT in this case

get rid of that

i didnt understand this is what you're doing

i thought you typed in the alter and everything

you know what to do?

where it has SELECT DATE_ADD...

just make it

DATE_ADD...

ok

what does it say now

hmm it shows last_updated as INT

ok my bad

you are not allowed to do this in the default expression

sadly

the best you can do is make your trigger or insert set the column

ok

what do you get tryong to add the trigger

ok

so all you would add is the middle line right

and you would change datetime_field

to the actual name of your column

so

SET NEW.last_updated = NOW() + INTERVAL 2 HOUR;```

thats the text you would add i believe

timestamp

oh

you see where it has `` `last_updated`

for some reason it seems to be not liking that empty pair of quote

can you cancel and try again

oh

well ping me once you're able to do that and try again , if you want

oh ok

hmm

it seems like now its a permission issue

any thought on that?

what about this
https://stackoverflow.com/a/29077372

maybe the database isn't selected? 🤔

i have no idea bro, sorry

looks fine to me

[jade wing]

sounds like a xy-problem to me
isn't what you really want just date and time in your local time zone from the database?
can you run the following query?

SELECT @@version @@GLOBAL.time_zone, @@SESSION.time_zone

i haven't used mysql workbench for many years, is don't even remember what it looks like or how to use it

are you running the database at heroku?
what timezone are you seeing in the database and what timezone do you want it to be in instead?

[torn sphinx]

@jade wing hello i made my code more dynamic, i made a function that find the start and end of all the blocks and i put them in a list and now it can read all sizes of the XL file here's the code if you wanna see
https://paste.pythondiscord.com/adomexujux
i think you did ask for me to send you the result

i am also here to ask for some insight since no one in the help channel helped i showed you before the XL file but here it is again
https://cdn.discordapp.com/attachments/342318764227821568/979884164205641808/unknown.png

i wanted to know if i can add an If statement in those "creneau" to recognize those blocks you see

[craggy sage]

i have a postgres db with a user bot, and my vps has the same user. when either log in with that user and run psql or sudo -u bot psql and then run ALTER USER bot WITH PASSWORD 'ABC123"' (yes the password has " at the end) I don't get a response.

[unkempt prism]

Do you finish your sql statement with an ;

[craggy sage]

ahh im very stupid

[keen minnow]

there is no such thing

[icy oyster]

to add a comment in SQL I just add COMMENT <my comment> ?
Example

CREATE TABLE IF NOT EXISTS personal (
            date DATE COMMENT Hello how are you, 
            time TEXT COMMENT Hello how are you, 
            trade_id INT COMMENT Hello how are you
            )

[brave bridge]

Seems like an Oracle-specific thing. Why do you want this?

COMMENT attaches some metadata to a column

If you want an inline comment like with # in Python, you can use -- your comment here

[icy oyster]

I want the user that will read the SQL to read the comment

I dont want the coder to read it

[icy oyster]

I mean if he does or not, its not what I'm looking for

I mean maybe I wanna use something that doesnt exist you know 🤣

[brave bridge]

What do you mean by "the user that will read the SQL"?

[icy oyster]

the UI

[brave bridge]

and is "the coder" different?

[icy oyster]

when you place the cursor over the column

[brave bridge]

ah

But you want to prevent programmers who don't use a GUI from reading the comment?

[icy oyster]

For example I place the cursor above date time and a text shows up that says "this is the date where... etc etc"

[icy oyster]

nono I mean

[brave bridge]

What database are you using?

[icy oyster]

I'm the """programmer"""

sqlite3

I just want the user that will read the SQL to read the comment

for example there it says TEXT COMMENT

seems wrong 🤣

as if the datatype is TEXT COMMENT 👀

and my code says in tps, TEXT COMMENT "This is the comment"

[brave bridge]

Seems like SQLite doesn't have this feature.

[icy oyster]

so yeah I suppose COMMENT doesnt exist in sqlite

yeah

idk I thought I read that once

[brave bridge]

It does exist in Oracle's database system

But it's not standard SQL.

Maybe this will be useful: SQLite preserves comments in the data definition language. https://stackoverflow.com/a/7426289/10295729
Maybe your GUI can read those comments?

[icy oyster]

Gonna go through it but that seems to be what I need.

[warm yacht]

is ```sql
DESC <table_name>;

the same as ```sql
SHOW COLUMNS FROM <table_name>;

?

[sonic hinge]

def delete_MongoDB(key):
   db = cluster["UserData"]
   collection = db["UserData"]
   collection.delete_one({"Key": key})

y is this function not working? it doe nt error, it simply doesnt remove the item form the database? im using pymongo

[torn sphinx]

In MongoDB, should I model messages in a channel (like Discord) as embedded documents in the channel model or as a list of references? My understanding is that because there's a document size limit and I want to allow an arbitrary number of messages in the channel, they have to be referenced.

Or not a list of references in the channel model, but a reference to the channel in each message model

[celest spindle]

Hello guys, could anyone give me some help on understanding how I can insert a document in a mongodb schema if a parameter (eg message_id) (which all documents in the collection have) is unique (other docs message_id have a different one)?

(I tried searching in the internet but I didn't find what I wanted actually)

[sturdy birch]

i got error while setting up mysql server in ubuntu. anyone who can solve this is requested to help!

[chrome mango]

does anyone know how to prevent sql injection with aiomysql? i tried:

    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute(
                sql="INSERT INTO log_bans (member_id, guild_id, moderator_id, reason) VALUES (%s, %s, %s, %s)",
                params=(member_id, guild_id, moderator_id, reason,))

but i get the error TypeError: Cursor.execute() got an unexpected keyword argument 'sql'

[brave bridge]

Sounds like it doesn't accept keyword arguments

Use positional arguments then

[chrome mango]

is there a way to do it then? its based on pymysql so i assume its supported somehow

[brave bridge]

await cur.execute(
    "INSERT INTO log_bans (member_id, guild_id, moderator_id, reason) VALUES (%s, %s, %s, %s)",
    (member_id, guild_id, moderator_id, reason)
)

[chrome mango]

Oh, right

il give that a whirl, thank you

[bleak bough]

How would i get a row by the second largest i.e.

SELECT FROM table WHERE something = ? AND value = the second largest from the previous where results```

or would it just be better to sort after fetching all

[storm mauve]

one way to use it might be window functions, something like ... select where rank() = 2 ..., but doing it in python after fetching sorted by might not be too bad of an idea

[bleak bough]

alright, might as well do it after because there wont be that much data

[brave bridge]

@bleak bough Alternatively, you can first select the top two then from that select the lowest:
https://www.db-fiddle.com/f/984muSK3HXunHf6Kzxcmp/0

[bleak bough]

thanks

[mossy parcel]

anyone know if writing a python list to dynamo will convert to StringSet?

trying to fit data into dynamo and looks like an older php app just writes a php array in without anything special but the field data type is StringSet

[opaque wave]

hey again guys, got a question. What is the best way to get a sum of some columns off a table, some other columns as they are in order to join them with another table?
e.g. get sum(col1), sum(col2) grouped by user from table2, country+city from table2 and join these with table1 on user

I tried getting the sum cols and self join with table2 then join with table1
using partition by user for sums and getting the other cols I need from table2 and join with table1

but both seemed quite slow
am i better of just joining each one with table1 separately? although this sounds stupid

also I want to get the first instance of a col by user from table 2, but I guess it's easier if that's done separately

[sly pivot]

Is this table normalised

[torn sphinx]

hi, i've an api and db managing users. I was wondering about the best way to automatically check if user's subscription is inactive based on a start + duration time. I could achieve that by checking user's subscription every time an user get retrieved through the api, however i think doing it directly from the db would be a better option, but i don't really know how i could achieve that. If someone could can give some clues, thanks !

[crystal vapor]

  File "c:\Users\thoma\Desktop\discord server bot\discord.py-bot\bot\__init__.py", line 207, in <module>
    bot = Bot()
  File "c:\Users\thoma\Desktop\discord server bot\discord.py-bot\bot\__init__.py", line 54, in __init__ 
    db.autosave(self.scheduler)
AttributeError: module 'db.db' has no attribute 'autosave'```

[timid tree]

Hi, I just switched from sqlite to postgresql and to connect it to my python code it needs some kind of username and stuff. Due to some circumstances I can only use postgresql's command line tool instead of pg admin and stuff. Can anyone please explain me how to get those connection details in terminal.

NOTE: I haven't even configured any settings if there are for postgresql, I've just used initdb and pg_ctl ... start

Please mention when replying, thanks for your time

[bleak bough]

how do i do this "SELECT link, title FROM submissions WHERE id = ? AND id is not in table2 WHERE link = link from first table select"

i think i mangled what i was asking

I'm trying to do this:

c.execute("SELECT link, title FROM submissions")
for link in await c.fetchall():
    c.execute("SELECT id FROM table2 WHERE link = ?", (link,))
    data = await c.fetchall()
    if data is None or id not in data:
        # this is the one i want
        break```

but without having to fetch all of the data from submissions

• also if the link isn't in table2

[paper flower]

Maybe something like

select link, title 
from submissions where id = ?
and id not in (select id from table2 where table2.link = submissions.link)

[bleak bough]

would i need a placeholder for the second id?

and id = ? not in (select id from table2 where table2.link = submissions.link)

[paper flower]

you only need one placeholder here

[bleak bough]

ill give it a try and see, thanks doctor

[paper flower]

You might want to check this SO question, there are other solutions too
https://stackoverflow.com/questions/12048633/sql-query-to-find-record-with-id-not-in-another-table

[bleak bough]

wait is this going to work if the link is not in table2

ooh i could just try except

[wraith shell]

Has anyone tried implementing a mixin in SQLAlchemy through Events API to soft delete when delete occurs on that mixin subclassed model? Is that even possible? I am hoping to keep the record and not change the interface for the devlopers. They should go on with session.delete but as long as mixin is there, the record would get soft deleted only.

[torn sphinx]

I need help with query select, so I've two columns, I want to change, users can search with 2 columns "namapopuler" or "namailimiah" and search using "Case-Insensitive"

def getSpeciesData(name):
    connection = connect_db()
    cursor = connection.cursor()
    # Just change the column using "where"
    cursor.execute('SELECT * FROM hewankusayang WHERE namailmiah = %s', name)
    result = cursor.fetchone()
    connection.close()
    return result```

[torn sphinx]

Fix using this

f"SELECT * FROM hewankusayang WHERE namapopuler LIKE '%{name}%' OR namailmiah LIKE '%{name}%'"```

if someone can improve this, please tag me 😄

[ionic latch]

What syntax can you use if you want the least amount in a database

https://gyazo.com/71956017d9081230b9e3f3a7c1d8893b

[paper flower]

There's an example on how to make soft deletes in sqlalchemy documentation but it doesn't cover soft deletes using session.delete 🤔 You might want to look into event listeners here

https://docs.sqlalchemy.org/en/14/_modules/examples/extending_query/filter_public.html

[warm wraith]

how to connect my bot to mongodb ?

[wraith shell]

I have used global filters on Query object in the past but that comes later when soft delete has already happened. I have seen one or two implementation of soft deletes and those are basically CRUD Manager classes with item.delete method with type check and doing update if its using mixin. I was hoping to use something global which can intercept using before_delete mapper event and cancel that event after inspecting the object, then I could send the update statement to the database to set it to soft delete. Here's one implementation, but again its not on the session. https://github.com/vitaly-efremov/sqla-softdelete/blob/master/sqla_softdelete/soft_delete_mixin.py

[ionic latch]

https://gyazo.com/78780cbc7061cfb1503ab492a6abac4b

does this mean i have to make a function and a class within the function?

[torn sphinx]

does someone know error? i am trying to connect with with mysql, its also first_name in mysql

pls tag if someone know

[bleak bough]

SELECT link, title, id, prompt_type FROM submissions
WHERE id NOT IN (SELECT id FROM votes WHERE votes.link = submissions.link)
ORDER BY future_time DESC```
how do i use a placeholder for id in line 2

id = ?

[jade wing]

you don't need one if you use that sub-select

[bleak bough]

it's not the same id from submissions

[jade wing]

what do you mean?

[bleak bough]

well imagine i write it like this

SELECT data FROM submissions
WHERE id = ? NOT IN (SELECT id FROM votes WHERE votes.link = submissions.link)
ORDER BY future_time DESC```

[jade wing]

that is not a valid sql statement, instead describe what you want to do with the whole query

[bleak bough]

i want to get data from submissions where a variable i set is not in votes where the submission and votes link are equal

[jade wing]

oh, try something like:

SELECT data FROM submissions
WHERE id = ? AND id NOT IN (SELECT id FROM votes WHERE votes.link = submissions.link)
ORDER BY future_time DESC 

[bleak bough]

ahhh okay that makes sense, thanks

wait no

that would only select the data from submissions where the id = ? right?

i want to go through all the data

like i want to do this without an f string

SELECT link, title, id, prompt_type FROM submissions
WHERE {variable} NOT IN (SELECT id FROM votes WHERE votes.link = submissions.link)
ORDER BY future_time DESC```

[jade wing]

you can't just stick a variable or value in there, which column do you want to match against?

[bleak bough]

from (SELECT id FROM votes WHERE votes.link = submissions.link) if you imagine that turns into a list of ids

[jade wing]

yeah, i'm with you so far, is it those id:s you want to pick a certain id out of?
why would you then need the select statement at all, is it just to confirm that such a record exists with that link in both tables?

[bleak bough]

first table is submissions with unique links, second one is a one to many relationship of people who have interacted with that submission

i only want them to be able to interact once, so when they're in the second table i need to know

so i dont show them the same thing twice

surely this would work

SELECT link, title, id, prompt_type FROM submissions
WHERE id = ? NOT IN (SELECT id FROM votes WHERE votes.link = submissions.link)
ORDER BY future_time DESC```

[warm wraith]

how to connect my bot to mongodb ?

[fair girder]

what's the best practice to prevent duplicate records when joining many tables with one table? Something like:

SELECT SUMS-FROM-ALL-TABLES
FROM TABLE1
LEFT JOIN TABLE2 ON X = Y AND X2 = Y2
LEFT JOIN TABLE3 ON X = Y AND X2 = Y2
LEFT JOIN TABLE3 ON X = Y AND X2 = Y2
LEFT JOIN TABLE4 ON X = Y AND X2 = Y2
WHERE X
GROUP BY ID```

do you just need to select and group in between joins?

[jade wing]

aha, I get it now, i'm not around a computer now but i'll get back to you on that as soon as i am if no one has beat me to the punch by then 😉

[bleak bough]

if anyone knows how to do this in one sql query i'd be grateful, or if it's even better to do it in one.

await c.execute("SELECT link, title, id, prompt_type FROM submissions ORDER BY future_time DESC")
for submission in await c.fetchall():
    link, title, id, prompt_type = submission
    await c.execute("SELECT * FROM votes WHERE link = ? AND id = ?", (link, interaction.author.id))
    if await c.fetchone() is None:
        break```

[storm mauve]

so you are looking for the latest submission that the user did not vote for?

perhaps use a left outer join followed by a filter for where ... = null?

[bleak bough]

yeah

[bleak bough]

SELECT A.link, title, A.id, A.prompt_type
FROM submissions A
LEFT JOIN votes B
    ON A.link = B.link
WHERE B.id != ?
ORDER BY future_time DESC```
doesn't appear to be keeping data from submissions when there's no matching link in votes ![7739monkathink](https://cdn.discordapp.com/emojis/960314142030442566.webp?size=128 "7739monkathink") getting none

[grim vault]

SELECT link, title, id, prompt_type
  FROM submissions
 WHERE NOT EXISTS (SELECT 'X'
                     FROM votes
                    WHERE votes.link = submissions.link
                      AND votes.id = ?
                  )
 ORDER BY future_time DESC

or

SELECT link, title, id, prompt_type
  FROM submissions
 WHERE link NOT IN (SELECT link FROM votes WHERE votes.id = ?)
 ORDER BY future_time DESC

[bleak bough]

first one seems to be working

thanks berndulas

[arctic furnace]

Is anyone here familiar with CTE in SQL

I am having trouble using CTE in a join

[jade wing]

just state the problem as detailed as you can and see if someone answers

[arctic furnace]

How do I paste code like above

To get it formated correctly

[jade wing]

!code but with sql if it's just sql

[delicate field]

Here's how to format Python code on Discord:

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

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

[jade wing]

!paste if it's too long

[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.

[tight junco]

can i use sqlite for a data that might need about 1.5k rows and 10 columns?

[nocturne bay]

yes, that is why they invented databases like sqlite

[chrome temple]

I heard that creating a new connection for each request is considered as a bad practice, because for relatively large bots that’ll just connect and disconnect your bot dozens of time. How do I make a method to create the connection only when needed, and return it as much as you need (and then also closing it when the bot is shut down) with aiosqlite?

[sly garden]

this is very out of context

you want to make a botvar, or create an async init (this gets called when the bot is initialized)
so
bot.db would be how to call the db connection every time
this is initialzed in the async init with
self.db = await <your connection>

[coral briar]

Can someone help me in #help-carrot please.

[coral briar]

Anyone.

[shy night]

how to connect my bot to mongodb ?

can someone help me

[nova forge]

Can we store emojis in varchar field?

[zealous spire]

emojis are just fairly high unicode points (e.g. 🥴 is \u1F974), so if your database implementation can store utf-8, it can probably store emojis, but it may require customization specific to the database system

apparently mysql, for example, doesn't work for utf-8 above the BMP (i.e. above \uFFFF), so you have to enable utfmb4 mode. this is supposedly going to become the standard in the future (or has it already?), but i don't use mysql so i dunno details

[nova forge]

Well its using utf8mb

[nova forge]

Will that work?

[zealous spire]

this should work, yes

[nova forge]

Aha Ill try and I dont need to convert the emoji to the unicode right?

[zealous spire]

this person didn't, and it may provide another option you might need if yours doesn't work: https://stackoverflow.com/a/39465494

[nova forge]

Umm ill try to insert emojis

[nova forge]

It works

[zealous spire]

well that's good

[nova forge]

Aha thanks!

[tidal lark]

Hello, I am trying to understand how to model a **collection of the same entity within an entity **-- the API response for the endpoint I am requesting returns a Transaction object which has a parameter inner_transactions which is a < Transaction > array

[jade wing]

in relational database systems this is typically modeled with a one-to-many relation between two tables

[earnest cape]

getting this error while trying to insert values into a table

sqlite3.OperationalError: table lookup has 4 columns but 3 values were supplied

the first column is id PRIMARY KEY AUTO INCREMENT, so shouldn't passing a tuple with 3 values (excluding id) work?

[topaz sluice]

Hello!
in a sqlite table deleting a row messes the order of rowId
what's the easy way to attain the order back after deletion...

[jade wing]

what sql statement are you trying to run, i'm guessing it'ssome kind of insert statement, can you paste it here in the channel?

[jade wing]

rowid is not a result or entry counter, it's an ever increasing id that is not meant to be reused

[topaz sluice]

yes, that's the issue

[earnest cape]

class Lookup:
    def __init__(self):
        self.con = sqlite3.connect(r'database\test.db')
        self.cur = self.con.cursor()

        self.create_table()
        self.insert(("Bob", "Blue",  170))

    def create_table(self):
        self.cur.execute("""CREATE TABLE IF NOT EXISTS lookup(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        character TEXT,
        type TEXT,
        number INTEGER
        )""")

    def insert(self, item):
        self.cur.execute("""INSERT OR IGNORE INTO lookup VALUES(?,?,?)""", item)
        self.con.commit()

[jade wing]

but that is how it's designed to work, if you want it in some other way you need to build something else yourself

[topaz sluice]

add another ? at values

[jade wing]

no, that's not the issue here for @earnest cape

[earnest cape]

i get this instead

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 4, and there are 3 supplied.

[jade wing]

change this line to this:

        self.cur.execute("""INSERT OR IGNORE INTO lookup (character, type, number) VALUES(?, ?, ?)""", item)
```you need to specify all the columns that you want to operate on when your not using all of them

[tidal lark]

So there could be two DDLs, one to create a transaction table and another to create an inner_transaction table, though they would have all the same fields defined as they are the same entity

[earnest cape]

thank you it worked! i understand now

[jade wing]

it's also preferable to specify all columns in every query, that way the code will be more robust if the table definition changes, for example the order of the columns or a new column might be added between some of the old columns

[earnest cape]

👍 will do

[jade wing]

in that case you can reuse the same table for both and do a self join, just have a column with the parent_id of the transaction that it belongs to and let it be null if it's the top most entry in that tree

[jade wing]

is it a real problem for you if all the rowids are not consecutive or is it just that you think it feels wrong or wasteful?
if it's for some kind of presentation to an end user i would solve the numbering for that in another way in my code (but not in sql)

[topaz sluice]

Actually I solved the numbering in my code externally.
But i was curious if there's a easier way using sql

[jade wing]

yeah, that's what i would do too if it's a row counter for presentation to an end user and then have the real rowid as a hidden field for each row if they can for example be selected for operations on the selected rows

[dull coyote]

Hello I'm having the following error:

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Calendar.calendar_events - there are multiple foreign key paths linking the tables.  Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

I'm trying to join two tables by two foreign keys pointing to different fields like that:

class TableA(Base):
    __tablename__ = "tbl_tableA"
    
    id = Column(INTEGER(10, unsigned=True), primary_key = True, nullable=False)        
    owner = Column(String(length=32), nullable=False, unique=True, index = True)
    survey_id = Column(String(length=48) , nullable = True, unique = True)
    
    #One to one relationships
    objectA = relationship("SurveyTemplate", back_populates = "objectB", uselist = False)



class TableB(Base):
    __tablename__ = "tbl_tableB"

    id = Column(INTEGER(10, unsigned=True), primary_key=True, nullable=False)
    owner = Column(String(length=32), ForeignKey("tbl_tableA.owner", ondelete='CASCADE'), nullable=False, index = True, unique = True)
    tpl_id = Column(String(length=32), ForeignKey("tbl_tableA.survey_id", ondelete='CASCADE'), nullable=False, index = True, unique = True)
    
    #Bidirectional
    objectB= relationship("TableA", back_populates = "objectA")

What I'm doing wrong?

[strong karma]

Anybody know where I can get/scrape a list of all restaurants in a zip code, preferably a JSON with addr + phone

I found one place but in order to get the list I have to schedule a meeting with them and potentially pay. Looking to avoid that

[brave bridge]

depends on the country, I suppose

It makes total sense to me that this undergoes payment and screening. After all, that's private information of a lot of businesses.

[strong karma]

To be fair, it's generally found on Google, trip advisor, etc, so I wouldn't consider info like a business phone number/address/name/cuisine as private. Just hoping I don't have to scrape google lol

[dull coyote]

Why anybody answer me on this server? Im missing something?
Im acting bad?

If its like that, anybody Who explains me this situation.

This is the second question I ask, and no answers

[jade wing]

maybe nobody that is reading right now knows 🤷
i for example don't know sqlalchemy at all and generally doesn't care much for ORMs

[dull coyote]

Thanks for replying anyway. ❤️

[jade wing]

the person in here that i know is very good with sqlachemy hasn't been active at all on the server for almost 1,5 days
but i bet there is more people that knows it around here, but maybe they aren't active right now either

[torn sphinx]

I wanted to ask does anyone know how I can import the name of a text data into my responses

[jade wing]

what kind of responses?
and what do you mean with "name of a text data"?

[torn sphinx]

nvm

I was able to fix the problem myself

sry

[fair girder]

is something like this possible in sql server?

FROM TABLE1
LEFT JOIN TABLE2
ON Y
WHERE X'
GROUP BY X.Y')
LEFT JOIN TABLE3
....)```

in words, do the first join and then do consecutive joins with other tables

or maybe better question, can I have the where and groupby clauses in the first join if kther joina follow?

[ionic latch]

What can i do so that when people type the first charater of a name it should return the whole word? So if i type 'Exotic' it should return 'Exotic Liquids'

I tried using LIKE operation but then i must type a specific character, but i cannot do that since its the user who puts in character in the paramenter not me

[vast epoch]

Possible

[cosmic cloak]

what is the newest and best way to use databases in python? I noticed many things like MySQL, PyQt, SQLite, NoSQL and so on

I'm looking for something simple that can create objects of classes that I have and access their variables and methods

like I'm looking to store data

and modify it

[storm mauve]

PyQt is a UI framework, not a database

[cosmic cloak]

yeah right, just gave examples though I didn't look into them

[storm mauve]

and 90%+ of the time, the "best" way to do something will not be the newest

[cosmic cloak]

I only looked into MySQL and SQLite

right

[storm mauve]

you might want to look into SQLAlchemy if you want to stick with a python mindset

[cosmic cloak]

how does it come in handy?

[storm mauve]

MySQL, SQLite, PostgreSQL and others are relational database systems which require for you to use the SQL language/syntax

SQLAlchemy is an ORM (object relational mapping) which translates python statements into queries for them instead of having you write them yourself

[cosmic cloak]

I'm familiar with SQL language/syntax

SQLAlchmey seems very nice

ORM sounds amazing

I will definitely take a look into that now. Thanks!

[storm mauve]

keep in mind that you still need to structure your data in specific ways though

[cosmic cloak]

right

thanks a lot

[storm mauve]

sqlite vs mysql vs nosql etc depends on the scale of the project and which kind of data, but for if it's just a local project that fits well in SQL, SQLite should be fine as a backend

[cosmic cloak]

yeah

alright thanks a lot, didn't know that actually

[proper yacht]

my db is connecting 2 times idk why..

[topaz sluice]

getting this error

[jade wing]

specify the column names in your query and you'll be fine

[jade wing]

!code is better then screenshots if you want to get help with your code and output (including errors), then we can copy/paste and give you new code instead of having to type it all of an image (we are not OCR machines)

[delicate field]

Here's how to format Python code on Discord:

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

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

[topaz sluice]

Thankyou!

[jade wing]

this time i'll pretend to be an OCR program anyway...
this is the code your looking for:

c.execute("INSERT INTO notes (title, content) VALUES (:title, :content)", { 'title': note.title, 'content': note.content })

[topaz sluice]

issue solved !

[dull coyote]

Solved adding a primaryjoin argument

[timid tree]

can any one explain me whats wrong

    @commands.Cog.listener()
    async def on_guild_join(self, guild: discord.Guild) -> None:
        users = [(user.id, guild.id) for user in guild.members if user.bot is None]
        async with self.bot.db.acquire() as conn:
            async with conn.transaction():
                await conn.execute(
                    "INSERT INTO guilds(id) VALUES($1)", guild.id
                )
                await conn.executemany(
                    "INSERT INTO economy(member, guild) VALUES($1, $2)", users
                )

Its is supposed to add the guild to guilds table and the members in the guild to economy table when it joins the guild, but idk why the second query never runs, the first execute runs fine.

[grim vault]

Did you check that users is not an empty list? I'm just guessing but maybe it should be ... if user.bot is False] or .. if not user.bot]

[fair girder]

can I get some help with parentheses? I have a query that looks like this:

SELECT a.[SOME COLUMNS], SUM(TABLE2.COL), SUM(TABLE3.COL)
FROM
(
SELECT a.[SOME COLUMNS]
FROM TABLE1
LEFT JOIN TABLE2
ON TABLE1.COLS = TABLE2.COLS
WHERE
GROUP BY TABLE1.COLS
) a LEFT JOIN   TABLE3
                ON TABLE1.COLS = TABLE3.COLS
                GROUP BY TABLE1.COLS + TABLE3.COL

I was to keep joining tables e.g.

LEFT JOIN TABLE4
ON TABLE1.COLS = TABLE4.COLS
GROUP BY TABLE1.COLS + TABLE3.COL + TABLE4.COL

but I tried some parentheses combinations and get syntax errors

[jade wing]

which database are you using?
when using "group by" you list multiple columns with , in between them not +
and you move the group by statement down, you don't use multiple of them unless they are for separate selects such with a sub-selects

[fair girder]

I've used the plus symbol just to communicate the logic of the query
there is an initial join (table1 with table2) grouped by tabel1 cols, then the next join needs to be grouped by the previous columns + one column of table3. I need multiple group by's because otherwise I get duplicate data

that's why table4's join also needs to be grouped by the previous cols + a new column in table4

as far as database is concerned I'm not really sure

so it's more like nested joins if that makes sense

need to join the next table to the joined previous table

[jade wing]

capabilities and syntax can differ somewhat depending on the sql dialect that the database in question

[fair girder]

i would guess t-sql but i'm not too sure

if I continue with another left join after what I've pasted before, I get a syntax error on 'LEFT JOIN'. If I use parentheses on the previous join, I get a syntax error on 'ON' of the TABLE3 join

[jade wing]

you mean Microsoft SQL Server?

[fair girder]

yes

i can only get it to work with another SELECT FROM at the start

but that seems stupid

[jade wing]

your "where" clause can't be left empty like that (as it is in your sub-select), either remove the where clause or complete it

[fair girder]

that's just a simplistic example

the where is not empty

or is it inevitable to use another select from for every other table i join?

[jade wing]

one more thing, the columns [SOME COLUMNS] must appear in the group by clause unless used in an aggregate function in the select (at least as a rule of thumb, but there are some exceptions)

[fair girder]

yes they are

as I said that's a simplistic representation, but captures the logic -- there is no error in any clause

what do you think about the latest bit I posted

is it inevitable but super dumb or is it fine by sql standards

[jade wing]

that it's probably unnecessary to do everything as sub-selects like that, but ot do depend on the circumstance, and you probably don't want alias all the sub-selects with the same alias ("a" in your case)

[fair girder]

don't worry about the aliases

initially I've done this whole thing differently but I was told to do it with this logic

the issue is I can't use another join unless I use another select

unless I miss something syntactically

[jade wing]

can't you do something like this instead?

SELECT t1.col1, SUM(t1.col2), SUM(t2.col2), SUM(t3.col2), SUM(t4.col2)
FROM table1 t1
LEFT JOIN table2 t2 ON t1.col1 = t2.col1
LEFT JOIN table3 t3 ON t1.col1 = t3.col1
LEFT JOIN table4 t4 ON t1.col1 = t4.col1
GROUP BY t1.col1;

[fair girder]

no, it creates duplicates

in short: I need to get for each unique user of table1, the sum of a column from every other table

x6 times

each other table has a unique column that I need to get the sum of

so these need to be grouped by on every join

[dire temple]

psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block

?

[jade wing]

you would need to provide some code as well

[dire temple]

async def countdown():
    cursor.execute("UPDATE userdatas SET treasure_counter = treasure_counter - '00:00:01' WHERE treasure_counter > '00:00:00';")
    cursor.execute("UPDATE userdatas SET is_tcomp = True WHERE treasure_counter = '00:00:00';")
    db.commit()

whlie this code working

async def treasure_boolean_changer(user: discord.Member, boolean):
    cursor.execute('UPDATE userdatas SET is_tcomp = %s WHERE id = %s;', (boolean, str(user.id)))
    db.commit()

i use this code

[dire temple]

it gives me this error

sorry my eng isn't good

[jade wing]

it's fine, don't worry about it as long as you can get your point across 🙂

[dire temple]

thx

[jade wing]

so, the first code block works, but not the last code block?

[dire temple]

first one works every second

again again

while it working 1 time per second

when i run second code

it gives me the error

[jade wing]

i'm wondering where you get your cursor from and what state it's in when the treasure_boolean_changer function gets called and if that can be a source of your error 🤔

[dire temple]

import psycopg2
import discord

db = psycopg2.connect(dbname='', user='', password='', host='')
cursor = db.cursor()


    
async def countdown():
    cursor.execute("UPDATE userdatas SET treasure_counter = treasure_counter - '00:00:01' WHERE treasure_counter > '00:00:00';")
    cursor.execute("UPDATE userdatas SET is_tcomp = True WHERE treasure_counter = '00:00:00';")
    db.commit()
    
async def treasure_boolean_changer(user: discord.Member, boolean):
    cursor.execute('UPDATE userdatas SET is_tcomp = %s WHERE id = %s;', (boolean, str(user.id)))
    db.commit()
   

cursor cames from here

[jade wing]

sometimes there's problems when reusing cursors
and remember that the functions aren't executed in the order as they appear in the source code
also, as bots are asynchronous you probably want to use an asynchronous database driver/connector such as asyncpg instead of psycopg2 and do await for the database operations

[storm mauve]

You should store when it will be completed and check if {now} has passed that time, updating all rows every second is a very bad idea

[dire temple]

i searched more and i am trying make a new coneection for loop i am hoping it will work

[jade wing]

you don't need a new connection, just a new cursor, if that's even your problem

[dire temple]

can i create a multiple cursor on one connection?

[jade wing]

yes, you can 🙂
and it's much better doing it that way as every connection takes up much more resources on the database server

[dire temple]

Thx

It works

[torn sphinx]

{"550322163190857729": 1763, "730670083319529525": 200 ........}

global reports
try:
    with open(config["db_r_path"]) as f:
        reports = json.load(f)
except FileNotFoundError:
    print("Could not load reports.json")
    reports = {}

def _r_save():
    with open(config["db_r_path"], 'w') as f:
        json.dump(reports, f)

Basically discord ids with values in a json file.
How do i get like a top 10 highest of these

[jade wing]

json is not a database and is very unreliable if you try to use it as such for storage of data

[torn sphinx]

ik its bad but where do i post it if not here

[jade wing]

either #python-discussion (can be very noisy at times) or clam a help channel using #❓｜how-to-get-help

[torn sphinx]

k

[timid tree]

ok ill check

[outer imp]

I want the code to show the number of people you have added to the database
If I have 5 people, I want them to tell me

[jade wing]

!code if it's short enough

[delicate field]

Here's how to format Python code on Discord:

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

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

[jade wing]

!paste if it's too long for the other method

[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.

[outer imp]

import sqlite3
from termcolor import colored
Admin_name = ["Mohamed","Ziad","Mazen","Mona","Haneen","Fatma","Kareem","Yossef","Abdalla","Mohand","Moneer","Hend"]
Admin_age = [18,19,20,21,22,23,24,25,26,27,28,29]
Admin_gender = ["Male","Male","Male","Female","Female","Female","Male","Male","Male","Male","Male","Female"]
Admin_country = ["Egypt","Sudan","Saudi Arabia","Tunisia","Tunisia","Somalia","Algeria","Morocco","Iraq","Syria","Yemen","Libya"]

Admins_info = sqlite3.connect("Admins.db")

Admins_info.execute("create table if not exists Admins(user_id integer,name text,age integer,gender text,country text) ")

cr = Admins_info.cursor()

cr.execute("select * from Admins")

def USER_NAME():
for x in cr.fetchall():
user_id = colored(x[0],color="yellow")
name = colored(x[1],color="red")
age = colored(x[2],color="red")
country = colored(x[4],color="red")
print(f"{user_id}:{name} your age: {age} your country => {country}")

USER_NAME()

Admins_info.commit()
Admins_info.close()

[jade wing]

and you want to know how many entries are in the Admins table using an sql query or just count them while your fetching the entries from the database and print them?

[outer imp]

Yes, this is what I want to just compute it while fetching the fetch from the database and printing it?

[outer imp]

thank you I found the solution

[bleak bough]

SELECT submissions.link COUNT(votes.link) as Count
FROM submissions
WHERE id = ?
LEFT JOIN votes ON submissions.link = votes.link
ORDER BY Count DESC```
where have i messed this one up ![7739monkathink](https://cdn.discordapp.com/emojis/960314142030442566.webp?size=128 "7739monkathink")

sqlite3.OperationalError: near "(": syntax error error if you need it

added a comma next to the first value next to select, now i have an error near left

nevermind, the where just needed to be after join

[chrome mango]

is there meant to be no space after COUNT?

[jade wing]

nice, finding solutions your self (or with just a little bit of guidance) is a good way to learn instead of getting them served 👍
i was unfortunately a bit busy earlier, but i would have suggested either use something like (as a simple example):

def USER_NAME():
    i = 0
    for x in cr.fetchall():
        i += 1
        user_id = colored(x[0],color="yellow")
        name = colored(x[1],color="red")
        age = colored(x[2],color="red")
        country = colored(x[4],color="red")
        print(f"{i}: {user_id}:{name} your age: {age} your country => {country}")
    print(f"Total number of users: {i}")
```or with how many entries/rows you have in the database with SQL:
```python
def user_count():
    cr = Admin_info.cursor()
    cr.execute("select count(1) from Admins")
    i = cr.fetchone()[0]
    cr.close()
    print(f"Number of users: {i}")

user_count()

[outer imp]

Thank you very much for your interest, your method is easier than mine🥰

[jade wing]

just wanted to show two different ways to go about it
and you don't need to print the line number for each line in the first example, that was only to show that it was an option with that method

[outer imp]

Thank You very much

[jade wing]

here's a few more techniques you might find useful (and i'm only create a database in memory instead of on file as this is just an example and i didn't mean to keep it around):

#!/usr/bin/env python3

import sqlite3
from contextlib import closing

admins = [
        (1001, "Mohamed", 18, "Male", "Egypt"),
        (1002, "Ziad", 19, "Male", "Sudan"),
        (1003, "Mazen", 20, "Male", "Saudi Arabia"),
        (1004, "Mona", 21, "Female", "Tunisia"),
]

def user_count():
    with closing(db.cursor()) as cur:
        cur.execute("""select count(1) from Admins""")
        i = cur.fetchone()[0]
    print(f"Users in table: {i}")

db = sqlite3.connect(":memory:")
db.row_factory = sqlite3.Row    # enable dictionary style access to columns in results
with db:    # will commit() on success or rollback() on failure when leaving this block of code (indentation)
    db.execute("""create table if not exists Admins (user_id integer, name text, age integer, gender text, country text)""")
    db.executemany("""insert into Admins (user_id, name, age, gender, country) values (?, ?, ?, ?, ?)""", admins)

with closing(db.cursor()) as cur:
    i = 0
    cur.execute("""select * from Admins""")
    for row in cur:
        i += 1
        # column names in the dictionary are case-insensitive
        # and can still be accessed by index as the last column in this example
        print(f"""{i}: {row["name"]} is a {row["aGE"]} years old {row["GENDER"].lower()} from {row[4]}.""")
    print(f'Total: {i}')

user_count()

db.close()

[remote latch]

if not cursor.execute("FROM ? GET ?",database, id):```
it's giving me the error "execute expected at most 2 arguments, got 3" where's the third argument given?

trying to check if a data entry with the id is in the database

[jade wing]

which database library/driver/connector are you using?
id is your third argument to .execute() as your SQL statement is the first
depending on the library you use to reach the database you might have to write it as:

if not cursor.execute("FROM ? GET ?", (database, id)):

[remote latch]

sqlite3

[jade wing]

then the above syntax with the parentheses around all the parameters for the placeholders is the one you need to use and if you only have on parameter you still need to have them and a coma within them to make it work and make python understand that it should really remain a tuple

[brazen socket]

yo, i wanna access my table but why is it erroring this?

asyncpg.exceptions.UndefinedTableError: relation "feedback" does not exist

...
await bot.db.execute("INSERT INTO feedback (prompt, completion, feedback) VALUES ($1, $2, $3)", self.prompt, "|".join(self.splitanswer), "great")
...

heres a piece of code i used

[paper flower]

Your table doesn't exist?

[brazen socket]

it is saying it doesnt exist even though it is

[paper flower]

Are you sure you connected to the right db?

hm 🤔

which library you're using?

[acoustic cave]

Hey, when I try using sqlalchemy with cockroachdb it gives me this error sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:cockroachdbalthough I have my sqlalchemy-cockroachdb connector installed, any ideas on fixing it ?

[brazen socket]

psycopg2

[paper flower]

You sure? It's not async

[brazen socket]

i just moved to psycopg2 after i sent the code thought it would fix it

but nop

[paper flower]

Maybe it's in a different schema?

[narrow saffron]

Hey, I have a table with foreign key fields, when drawing an entity relationship diagram for this, do I connect these two separate entities with an arbitrary relationship? Or is there way to add an entity as an attribute to another entity.

[mild merlin]

hey i have a problem in my sql code. ```python
mycursor.execute("SELECT activecodes FROM license")
myresult = mycursor.fetchall()
for x in myresult:
if key == x:
print("Good!")

The **key** does exist in *activecodes* but it doesnt print Good!. What should I do? Feel free to mention me. Thank you for your time!

[wanton sundial]

I need some help with a mysql select

"The person search must return the name and position, and can be filtered by the name of the person.
person"

Script:
/* Effettivo_logico: */
DROP DATABASE IF EXISTS Effettivo;
CREATE DATABASE Effettivo;
USE Effettivo;

DROP TABLE IF EXISTS Pessoa CASCADE;
CREATE TABLE Pessoa (
CODG_PESSOA INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
DESC_NOME VARCHAR(255) NOT NULL,
DATA_NASCIMENTO DATE,
CODG_CARGO INT
);

DROP TABLE IF EXISTS Cargo CASCADE;
CREATE TABLE Cargo (
CODG_CARGO INT PRIMARY KEY NOT NULL,
DESC_CARGO VARCHAR(255) NOT NULL
);

ALTER TABLE Pessoa ADD CONSTRAINT FK_Pessoa_2
FOREIGN KEY (CODG_CARGO)
REFERENCES Cargo (CODG_CARGO)
ON DELETE SET NULL;

INSERT INTO Cargo (CODG_CARGO, DESC_CARGO) VALUES
(1, "Gerente de tecnologia"),
(2, "Estagiario");

INSERT INTO Pessoa (DESC_NOME, DATA_NASCIMENTO, CODG_CARGO) VALUES
("Sergio Almeida Costa", "1992-01-01", 1),
("Henrique Dalmagro", "2004-09-28", NULL),
("Rafael Barros", "2004-11-11", NULL);

[grim vault]

fetchall() will return a list of tuples, so x will be a tuple (with one entry because you only select one column). You need if key == x[0]: to check the value of the selected column.

[mild merlin]

thanks. I now have this code ```python
if key == x[0]:
hwid = subprocess.check_output('wmic csproduct get uuid').decode().split('\n')[1].strip()
sql = "INSERT INTO users (username, password, usedkey, hwid) VALUES (%s, %s, %s, %s)"
val = (username, password, key, hwid)
mycursor.execute(sql, val)
mydb.commit()

but i get this error

File "C:\Users\Δημήτρης Κολιόπουλος\Desktop\multitool_opensource\multitool.py", line 54, in <module>
main()
File "C:\Users\Δημήτρης Κολιόπουλος\Desktop\multitool_opensource\multitool.py", line 51, in main
register()
File "C:\Users\Δημήτρης Κολιόπουλος\Desktop\multitool_opensource\multitool.py", line 37, in register
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 665, in prepare_for_mysql
result = self._cmysql.convert_to_mysql(*params)
_mysql_connector.MySQLInterfaceError: Python type tuple cannot be converted

[grim vault]

print(val) to check which one of the arguments is a tuple?

[mild merlin]

the key

okay i fixed it thank youu!!!

[twilit marlin]

I am working on the backend for a crypto exchange. We have a new requirement wherein we need to display the volume (total money that flowed through the service) for different time ranges (last hour, day, month etc). I was wondering what would be the best way to approach it?

• Write normal database queries, which will be recalculated every time we make a request; I fear that this would become very DB heavy
• Write a cron job that would save the volumes over a specific time period, and run this every hour
• A combination of both these, the first request will trigger a calculation, and these stats will be saved to the database; subsequent requests will just display these stats. Recalculations will be made when the requested time range changes.

What are your thoughts?

[sharp marsh]

I'm trying to create TTL index in MongoDB to delete documents after one minutes, but It's not working. pls help me with this

[shrewd basalt]

hi all if a person login into a site ,it checks whether the email and password is in db. how we can do it can any write a query bcoz usally we have a million rows in db. if we use IN CLAUSE it takes time .we have any better way

[brave bridge]

What database are you using?

Can you show your schema?

[shrewd basalt]

postgres

i am just asking in general

[brave bridge]

Usually this is accomplished with an "index"

[shrewd basalt]

any code for reference (flask)

[brave bridge]

flask doesn't access the database in any way

do you mean SQLAlchemy?

[shrewd basalt]

yes

[brave bridge]

If you go to a (physical) library and want to find a book by the author, you don't search through every single book in the library. Instead, you first go the letter you're interested in and then do a binary search by the author name.

[shrewd basalt]

oh

[brave bridge]

This should probably help:
https://en.wikipedia.org/wiki/Database_index
https://www.postgresql.org/docs/current/indexes.html
I haven't used SQLAlchemy, but this seems like what you want:
https://docs.sqlalchemy.org/en/14/core/constraints.html

[shrewd basalt]

ok thanks

[west hill]

anyone recommend any good MySQL tutorials especially one with good joins and group bys

[outer imp]

I want after I delete a person to return the order from the beginning of the number 1

[next grove]

for tutorials , mysqltutorial.com is the best place to learn from , and after that you can solve problems at hackerranks website , i felt they were very challenging for me more than in the other places

[outer imp]

I want after I delete a person to return the order from the beginning of the number 1 ```py
import sqlite3
names = sqlite3.connect("name.db")
cr = names.cursor()
cr.execute("create table if not exists names(user_id integer,name text)")

cr.execute("insert into names(user_id,name)Values(1,'Mohamed')")

cr.execute("insert into names(user_id,name)Values(2,'Kareem')")

cr.execute("insert into names(user_id,name)Values(3,'Mohseen')")

cr.execute("insert into names(user_id,name)Values(4,'Mariam')")

cr.execute("insert into names(user_id,name)Values(5,'Maher')")

def unzip_name():
cr.execute("select * from names")
for unzip in cr.fetchall():

    print(unzip)

unzip_name()
#=====================
#== result ===========
#=====================

(1, 'Mohamed')

(2, 'Kareem')

(3, 'Mohseen')

(4, 'Mariam')

(5, 'Maher')

#=======================
cr.execute("select * from names")
cr.execute("delete from names where name = 'Mohamed'")
print("#"*50)
unzip_name()
#=====================
#== After result =====
#=====================

(2, 'Kareem')

(3, 'Mohseen')

(4, 'Mariam')

(5, 'Maher')

names.commit()
names.close()```

[stoic finch]

im looking to constantly scraoe data from a website and store their updated values inside a database to grab at a later time to avoid their server delays, what would the best sort of database be to use for this? im currently using MonogDB for a database with my discord bot

[paper flower]

Nope, that's how sequential id's work

There's no reason to regenerate vacant ids

[paper flower]

Depends on data i'd say

I personally prefer postgres but i see no reason why mongodb might be a bad choice here

[wicked tapir]

Hi

I have an assignment in which i have to find a data set and relate it to a business problem then create visualizations of it on any visualization tool

can anyone help

[inland raft]

I have a database with over 100,000 rows. Occasionally I need to find a few specific things and running a linear search algorithm just takes way too much time. What can you guys suggest

[inland raft]

@wicked tapir there are plenty of sample data sheets you can find out git hub. Pick any one of them and maybe see if it has any problems in terms of working in the real world. then just create some charts and visual/appealing stuff and compile it all into one portfolio kinda thing and submit

[paper flower]

What your query looks like?

[neat breach]

I'm getting an error while inserting some values into my database. This is what the error says: sqlite3.OperationalError: near ")": syntax error

this is the line its giving the error: self.cursor.execute("""

[west hill]

send ur code

[neat breach]

im just gonna copy paste it

its not a lot

import sqlite3
import random

class User():

    def __init__(self, age, nickname, id_number):
        self.age = age
        self.nickname = nickname
        self.id_number = id_number
        self.connection = sqlite3.connect('game_database.db')
        self.cursor = self.connection.cursor()  

    def add_user(self):
        self.cursor.execute("""
        INSERT INTO user VALUES (
        {}, 
        '{}',
        {},
        )
        """.format(self.age, self.nickname, self.id_number))

        self.connection.commit()


connection = sqlite3.connect('game_database.db')
cursor = connection.cursor()  

age = 0
nickname = 'abcdefghijklmnopqrstuvwxyz'
id_number = ''

if input('Do you want to create a new account? (y/n)').lower() == 'y':
    age = input('Enter your age: ')
    if int(age) <= 12:
        print('You are too young.\nGoodbye.') 
        quit()

    else:
        while len(nickname) >= 16:
            nickname = input('Enter your nickname: ')
            if len(nickname) >= 16:
                print('Nickname must be less than 16 characters.')
            else:

                try:    
                    cursor.execute("""
                    SELECT * FROM user
                    WHERE nickname = {}
                    """.format(nickname))
                    if cursor.fetchone() != nickname:
                        break
                    else:
                        print('Nickname already exists.')
        
                except sqlite3.OperationalError:

                    while len(id_number) < 10:
                        id_number += str(random.randint(1, 9))

                    new_user = User(age, nickname, int(id_number))
                    new_user.add_user()
else:
    print('Goodbye.')
    quit()

cursor.execute("SELECT * FROM user")
results = cursor.fetchall()
print(results)

the error is in add_user

this is my first time working with databases fyi

[west hill]

why not use f strings

and it's """).format

u missed a )

[neat breach]

oh

i used f strings

import sqlite3
import random

class User():

    def __init__(self, age, nickname, id_number):
        self.age = age
        self.nickname = nickname
        self.id_number = id_number
        self.connection = sqlite3.connect('game_database.db')
        self.cursor = self.connection.cursor()  

    def add_user(self):
        self.cursor.execute(f"""
        INSERT INTO user VALUES (
        {self.age}, 
        '{self.nickname}',
        {self.id_number},
        )
        """)

        self.connection.commit()


connection = sqlite3.connect('game_database.db')
cursor = connection.cursor()  

age = 0
nickname = 'abcdefghijklmnopqrstuvwxyz'
id_number = ''

if input('Do you want to create a new account? (y/n)').lower() == 'y':
    age = input('Enter your age: ')
    if int(age) <= 12:
        print('You are too young.\nGoodbye.') 
        quit()

    else:
        while len(nickname) >= 16:
            nickname = input('Enter your nickname: ')
            if len(nickname) >= 16:
                print('Nickname must be less than 16 characters.')
            else:

                try:    
                    cursor.execute(f"""
                    SELECT * FROM user
                    WHERE nickname = {nickname}
                    """)
                    if cursor.fetchone() != nickname:
                        break
                    else:
                        print('Nickname already exists.')
        
                except sqlite3.OperationalError:

                    while len(id_number) < 10:
                        id_number += str(random.randint(1, 9))

                    new_user = User(age, nickname, int(id_number))
                    new_user.add_user()
else:
    print('Goodbye.')
    quit()

cursor.execute("SELECT * FROM user")
results = cursor.fetchall()
print(results)

[west hill]

seems fine now

[neat breach]

but i get the same errro

  File "c:\Users\Ayden\Downloads\Python\folders\add_new_person\main.py", line 46, in <module>
    cursor.execute(f"""
sqlite3.OperationalError: no such column: nickname

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "c:\Users\Ayden\Downloads\Python\folders\add_new_person\main.py", line 61, in <module>
    new_user.add_user()
  File "c:\Users\Ayden\Downloads\Python\folders\add_new_person\main.py", line 14, in add_user
    self.cursor.execute(f"""
sqlite3.OperationalError: near ")": syntax 
error```

[west hill]

is multi line strings allowed?

[neat breach]

why shouldnt it be?

[west hill]

try with just one " on each end

[neat breach]

ok

it worked

thanks for the help

[west hill]

np

[neat breach]

but that wasnt the error

i had an extra ,

look python def add_user(self): self.cursor.execute(f""" INSERT INTO user VALUES ( {self.age}, '{self.nickname}', {self.id_number}, ) """)

after {self.id_number}

that coma is extra

and that is what was causing the erro

[west hill]

oh yeah missed that

[neat breach]

thanks anyway

[west hill]

np

[brave bridge]

!sql-fstring

[delicate field]

SQL & f-strings
Don't use f-strings (f"") or other forms of "string interpolation" (%, +, .format) to inject data into a SQL query. It is an endless source of bugs and syntax errors. Additionally, in user-facing applications, it presents a major security risk via SQL injection.

Your database library should support "query parameters". A query parameter is a placeholder that you put in the SQL query. When the query is executed, you provide data to the database library, and the library inserts the data into the query for you, safely.

For example, the sqlite3 package supports using ? as a placeholder:

query = "SELECT * FROM stocks WHERE symbol = ?;"
params = ("RHAT",)
db.execute(query, params)

Note: Different database libraries support different placeholder styles, e.g. %s and $1. Consult your library's documentation for details.

See Also
• Extended Example with SQLite (search for "Instead, use the DB-API's parameter substitution")
• PEP-249 - A specification of how database libraries in Python should work

[brave bridge]

@neat breach don't use f-strings to substitute parameters

also @west hill

[west hill]

why

[west hill]

oh i see

[dull dust]

I have a table named post1 and when I tried to drop it, it is just stuck there, not returning the "1 row affected" statement

I suspect that the table is so long that it is taking a while. Could that be the cause?

I did have a blob column which stored an image in a string/bytes format

[dull dust]

The details are in #help-potato

[fiery tinsel]

Hey everyone! Has someone just found any workaround for this issue with sqladmin? https://github.com/aminalaee/sqladmin/issues/160 I’m just thinking to use prisma ORM along with prisma studio for better support for this kind of issues with admin interfaces

[brave bridge]

If you have a question, ask it:
https://www.pythondiscord.com/pages/guides/pydis-guides/asking-good-questions/#q-is-anyone-here-good-at-flask-pygame-pycharm

unless you are just wondering if it's in use by anyone

[sleek tiger]

why do we need foregin keys

i can create two tables

and make an coulm for table2_elements_id

without making it foregin

[brave tree]

you're correct that you dont technically need them

[sleek tiger]

@brave tree then why do people use it
there must be a reason

[brave tree]

but defining them as what they are can bring benefits

1. the database server knows it's a foreign key, and hence can index and do optimisation stuff
2. it can help the database enforce data integrity rules (wont let you insert a foreign key that doesnt exist)
3. lets you chain updates automatically (eg, delete all related items if the main item gets deleted.)

was typing

[sleek tiger]

thanks mate it clears it all

[shrewd basalt]

How to use format in where caluse in pyscopg2

[paper flower]

@inland raft Hello?

Let me know if you still need help

[torn sphinx]

I need concurrency support

#help-pear

[sleek tiger]

hey how to create and auto incrementing primary key id

like i want to insert new row in table with lastId+1

like without providing id

[paper flower]

Depends on the database

but generally id integer primary key should work

[celest oasis]

Does:
x = rows or columns?

myArray[x, y] == myArray[row, column]?

[paper flower]

It's not related to databases but it depends on internal notation/structure

Might be (x, y), might be (y, x)

[sleek tiger]

thanks,
may you tell which is good practice :userId with dict {'userId': userId}
or ? with dict (userId) while inserting values

[celest oasis]

Ok okay. Thank you.

[paper flower]

Both are good, i think using named params is more readable

[sleek tiger]

oh cool i am using that

[next raft]

Hello, I've been using postgres with sql alchemy ORM for python3, I wanted to know if there is a way we can update a row if a duplicate key already exists.

PS : The direct function (on_duplicate_key_update) exists in mySQL but not postgres

[paper flower]

Depends on how you want to perform your update 🤔
On conflict do update exists in postgres too, but you can't use it with orm, only with sqlalchemy core

[next raft]

we have on_conflict_do_update , but thats doesnt work when there are duplicate keys

[paper flower]

You want to update row on conflict (duplicate key), on conflict do update does what you want 😅

[next raft]

that is what i thought initially, but if it encounters duplicate primary key, it still throws the sql integrity exception

[wraith agate]

how to get an output ....
displaying The employers name in the field of salesman & their commission ?
like in first column it will be the list of names of employees as salesman
And in second column it will be their comission

[next raft]

even i was wondering why that was happening and what purpose on conflict do update serves if im still getting the sql integrity error

[paper flower]

Maybe you're not using it correctly? You shouldn't get integrity error here really

[next raft]

stmt = insert(myTable,bind=engine).values(dicts) #dicts -> a list of dictionaries
            for dict in dicts:
                update_stmt = stmt.on_conflict_do_update(
                        index_elements=myTable.primary_key,
                        set_=dict
                    )
                update_stmt.bind = engine
                conn.execute(update_stmt)

this is how im using it

[paper flower]

I think you're overriding on_conflict_do_update here

[next raft]

oh

[paper flower]

You can't use it if you're inserting multiple rows (in case you want them to have different on conflict ... statements)

[next raft]

but im executing the query in the loop itself

so how is it overriding?

[paper flower]

https://docs.sqlalchemy.org/en/14/core/tutorial.html#executing-multiple-statements
I think one statement can't have multiple on conflict ... blocks, you can create multiple statements and execute them via executemany

[next raft]

oh but in each iteration, im creating a new update_stmt right? is that what is causing the issue?

[paper flower]

Hm, it seems like it doesn't support sending multiple statements at the same time 🤔

[paper flower]

Well, yeah, and your on conflict ... gets replaces with new one

[next raft]

correct, but the replacement is after the first on conflict is executed right?

[paper flower]

You're trying to insert all the records in single query, i think you should just put statement creation into your for loop

[next raft]

so you're saying that this replacement is what is causing the issue?

i see, lemme try that

[paper flower]

for dict in dicts:
    stmt = insert(myTable, bind=engine).values(dict)  # dicts -> a list of dictionaries
    update_stmt = stmt.on_conflict_do_update(
        index_elements=myTable.primary_key,
        set_=dict
    )
    update_stmt.bind = engine
    conn.execute(update_stmt)

[next raft]

ooooh wow

that works like a charm

thanks alot

i've been stuck here since quite sometime, your help is appreciated alot, thank you once again

[paper flower]

np, i also wonder if there's a way to send multiple statements at the same time

[tranquil zinc]

using ms access and pyodbc

[paper flower]

remove comma at the end of line 8

[paper flower]

I think you should be able to rewrite it this way, but you may need to pass all your columns to set_ parameter

stmt = insert(myTable).values(dicts)
stmt = stmt.on_conflict_do_update(
    index_elements=myTable.primary_key,
    set_=stmt.excluded
)

https://docs.sqlalchemy.org/en/14/dialects/postgresql.html?highlight=on_conflict#updating-using-the-excluded-insert-values

[next raft]

ooh that looks promising, will try now

[paper flower]

As i said you might have to explicitly pass your columns:

stmt = stmt.on_conflict_do_update(
    index_elements=myTable.primary_key,
    set_={
        "some_column": stmt.excluded.some_column,
    }
)

[torn sphinx]

hey, i have a mysql db table with this structure, how can i select the SUM of all gusses of all user_ids . I tried "SELECT user_id, SUM(gusses) FROM table" but that raised an error -

#1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'IZWpEIo7jc.guesses.user_id'; this is incompatible with sql_mode=only_full_group_by

should i add GROUP BY user_id?

[paper flower]

Yep, if you want to select guesses for all servers

[torn sphinx]

oh yeah, that fixed the issue, i should have focused on the 'In aggregated query without GROUP BY' part of error

[young snow]

const Sequelize = require('sequelize');
const db = require('../config/DBConfig');

// Create sets table in MySQL Database
const Set = db.define('set',
    {
        setName: { type: Sequelize.STRING(50) },
        setItem: { type: Sequelize.SET('a', 'b') },
        setPrice: { type: Sequelize.DECIMAL(5,2) }
    });
    
module.exports = Set;

when i ran the program, it shows "TypeError: Sequelize.SET is not a function". I've searched the data type for MySQL that stores multiple values in an attribute which was SET but apparently node.js does not recognise SET as a data type. What are the other data types that can store multiple values in an attribute?

[white elm]

Does anyone know what this means?

Traceback (most recent call last):
  File "C:\Users\user\OneDrive\Dokument\Paid Services Assistance\index.py", line 12, in <module>
    conn.execute('''CREATE TABLE users
sqlite3.OperationalError: near ")": syntax error

tried to make a table but it gave me that error

[neat breach]

i figured that out

[neat breach]

What should i use instead of f strings?

[brave bridge]

Read the embed above my message

[white elm]

do anyone know

[white elm]

what the error in this could be

i might just be dumb

if i dont get what this means

[next raft]

hm this doesnt sound very convinient to me, i'd rather go for a simple loop 😁

[neat breach]

try checking if you put an extra comment insude of ur code

[white elm]

maybe i have

[neat breach]

i got the same error because i put an extra coma

[white elm]

oh i see

hmm tbh

i cant see any extra commas

[neat breach]

neither did i

but it was worth a shot

[white elm]

yeah

well this is weird

[neat breach]

google the error

[white elm]

idk maybe the code is messed up due to the spaces

yeah

wait do you really need to use "'''"

maybe that would be it

[neat breach]

im pretty sure its """ """ not ''' '''

[neat breach]

try using double quotes instead of single quotes

[white elm]

yeah

i just used ""

query = "CREATE TABLE users (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, SECRET INT NOT NULL, GUEST CHAR(125), QUEUE INT NOT NULL, ACTIVE BOOLEAN NOT NULL CHECK);"
conn.execute(query)

tried doing this

but it still gave the same error

[white elm]

it says syntax error near ")"

which i dont really get

[neat breach]

what is the ; for?

[white elm]

well i saw a guide on how to use it and that ";" was displayed

[neat breach]

try without it

it might work

might

[white elm]

oh okay

nah still same error lol

[sleek tiger]

hey there is nothing called BOOLEAN

you have to use INT

and for CHAR its VARCHAR() i guess

[white elm]

yeah i noticed that

[grim vault]

Your CHECK need an expression what to check.

[white elm]

i removed that one before

[grim vault]

If I remove the CHECK from your statement it just works.

CREATE TABLE users (
    ID INT PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    SECRET INT NOT NULL,
    GUEST CHAR(125),
    QUEUE INT NOT NULL,
    ACTIVE BOOLEAN NOT NULL
);

This works for me with sqlite 3.35.5

[grim vault]

^^

[white elm]

yeah

[white elm]

i actually just changed CHECK to DEFAULT(true)

if that would work

[grim vault]

It should work, yes (no parenthesis needed, so just DEFAULT TRUE)

[white elm]

oh

lol

i used the parenthesis

when i created the table

is that any big difference?

[grim vault]

No, you need the parenthesis if it's an expression, for just a literal value it's not needed but also works.

[white elm]

oh okay i see

[white elm]

btw when i insert stuff. For example if i want to insert 3 things when it has 4 things. Can i just skip the last value?

if you get me

[grim vault]

You'll need to define which columns you want to insert.

[white elm]

INSERT INTO users (id,username,secret,guest,queue) \ VALUES (1, 'test', 12234, 'None', 0);

if you see in this code

the last value is active

but i dont want to change the value of it in this insert code

[grim vault]

That's ok. If the column is not used in the insert statement it will get the default value.

[white elm]

oh okay isee

i see*

so this should work then

[grim vault]

The backslash is too much?

[white elm]

is it?

oh isn't this the way you insert?

[grim vault]

INSERT INTO users (id,username,secret,guest,queue) VALUES (1, 'test', 12234, 'None', 0);

[white elm]

oh lol

[grim vault]

The backslash might be from some editor for expanding a one liner into a multi liner?

[white elm]

oh ig

because i saw this code in a stackoverflow comment

so i just tried it out

but i never executed the code

so ima try this new line rn

[grim vault]

In your table there is no username it's called NAME

[white elm]

oh yeah

sorry it's because i changed it again haha

btw how can you remove the inserted things later on?

is it like drop?

[grim vault]

It's DELETE and I would suggest some SQL tutorial, see pins.

[white elm]

oh okay

i'll look it up

[white elm]

i found out how to use it now and made added select. One thing that i wonder is if it is possible to make a select statement that looks for another value in table content there it has found the secret.

conn.execute(f"""SELECT * FROM users WHERE secret = '{embed.footer}'""", e)

Here's what the systems looks for in the beginning

But i would like to make something there it also will look for the username in that same table content there it first found the secret

[grim vault]

!sql-fstring

[delicate field]

SQL & f-strings
Don't use f-strings (f"") or other forms of "string interpolation" (%, +, .format) to inject data into a SQL query. It is an endless source of bugs and syntax errors. Additionally, in user-facing applications, it presents a major security risk via SQL injection.

Your database library should support "query parameters". A query parameter is a placeholder that you put in the SQL query. When the query is executed, you provide data to the database library, and the library inserts the data into the query for you, safely.

For example, the sqlite3 package supports using ? as a placeholder:

query = "SELECT * FROM stocks WHERE symbol = ?;"
params = ("RHAT",)
db.execute(query, params)

Note: Different database libraries support different placeholder styles, e.g. %s and $1. Consult your library's documentation for details.

See Also
• Extended Example with SQLite (search for "Instead, use the DB-API's parameter substitution")
• PEP-249 - A specification of how database libraries in Python should work

[grim vault]

You can use more than one column in an where clause, like:

conn.execute("SELECT * FROM users WHERE username = ? AND secret = ?", (user.name, embed.footer))```

[white elm]

oh

[white elm]

so as the "embed.footer" is the footer of a discord message embed. Im looking for the secret id that is displayed in the discord embed footer

so with this. Should i do like secret = '{embed.footer}'

[grim vault]

No, see above (SQL & f-strings)

[white elm]

oh okay

[grim vault]

You should use placeholders and parameters.

[white elm]

oh okay

btw how can i continue this to see if both of those values have been found

in the same spot

or what it's called

because it's the username im specifically trying to get

through the secret id

[grim vault]

After executing the statement you fetch the result and check it.

[white elm]

oh okay

yeah i did use result = conn.fetchone() and then if result:

since the system has no idea what the username from the beginning was

im tryna locate the username by detecting the secret id to check it with embed.footer

and then somehow get the username by using the secret id

[forest moat]

Can someone help me? I'm newer to databases and I'm struggling with understanding how to link tables together for an API that I'm creating for a card game. Trying to use SQLAlchemy with Postgress.

So I have a Card table that has a couple different columns, one of them being Type. In the card game, there are 5 different card types, so I want to have my own table with those types listed, then have the Card table link to the Type table.

Each card type has a different amount of attributes as well. So a Character card might have 3 attributes (so I'd create 3 columns in the Character Table for each attribute), while an Attack card might have 5. This is where I'm really struggling with figuring out how to link things together, because I need the Card table to somehow access the data from one of those type tables based on its card_id and its type.

For whatever reason, my brain just is drawing a blank when I am trying to figure out how to do it.

[coral briar]

Can someone please try to help me here: https://stackoverflow.com/questions/72595178/checking-the-value-of-a-json-file-with-a-discord-bot-command?noredirect=1#comment128237895_72595178

[white elm]

do anyone know how the SQLite extension by alexcvzz works?

like how do i view the stored data?

[white elm]

do you have any clue?

if you have used this

[grim vault]

If you're talking about the sqlite extension, no, I've no clue.

[white elm]

oh okay

yeah im basically lost right here haha

this is all i see atleast

[white elm]

wait what lol last time i checked the records. There was no data stored at all

conn.execute(f"""INSERT INTO users (id,username,secret,guest,queue) VALUES ({count}, '{ctx.message.author}', {sellerid}, 'None', 0);""")

this code seems to not do anything, i get no errors either

[icy oyster]

I'll consider json a database in my case? 😄

jsonString = json.dumps(json_data)
jsonFile = open(f"{user_id}_{symbol}_{derivates_or_inverse}.json", "w")
jsonFile.write(jsonString)
jsonFile.close()

if json_data is posted as application/json, do I need to go through json.dumps() ? or just jsonFile.write(json_data) ?
Thanks in advance

[white elm]

i would actually say that mysql is better for databases

it's just easier to manage

[icy oyster]

but in this specific case I'll use json

Just for this specific thing

[white elm]

oh i see

[icy oyster]

or wait maybe it's better to use sql

I just dont know that much SQL :/

[white elm]

it's actually very easy

import sqlite3

and then

[cyan crypt]

where is the help channel

[icy oyster]

I use sql for a thing in this script I'm coding

but well

[cyan crypt]

for py

[icy oyster]

in this server

👍

[cyan crypt]

yes where

[icy oyster]

idk

[cyan crypt]

which channel

[icy oyster]

seek and destroy

[white elm]

the available help channels

[grim vault]

#❓｜how-to-get-help

[white elm]

anyway

[white elm]

just create a file called database.db

[icy oyster]

what if its .sql?

[white elm]

and then

conn = sqlite3.connect('database.db')
print("Database successfully connected!")

[icy oyster]

same thing right?

[white elm]

the "paid-services" is just the name of my database

well it's actually some difference

[icy oyster]

I went through all of that, but there are some thing I dont know and I'm a bit lazy, I should go through it now 😛

For example

[white elm]

.sql is for scripts
.sqlite is for sqlite databases
.db works as .sqlite i believe

it's very easy

just create a file with the extension .db

[white elm]

and with the import of sqlite3 you continue by writing this

[icy oyster]

If there is a column named test_chuti, and there is a value in that column that is chuti_noob, how can I find out if chuti noob exists in the column?

[white elm]

something like SELECT * FROM table WHERE test_chuti = ?

[icy oyster]

cause I'm trying to build a database that will store data from users on certain tickers (cryptocurrencies e.g: BTCUSDT), if user_id and ticker are the same, that means the user already setted that up, and so I'll need to delete that data to overwrite it with the new one

idk if that makes sense

so I should figure out if user_id and symbol exist in the same X row, and if it does, that should be deleted and replaced with this new data (I'll manage this new data)

and what I mean by replace is basically deleting the old data 😄

[white elm]

i think there is something called REPLACE

in the sql language

you might have to look that one up

[white elm]

SELECT REPLACE('SQL Tutorial', 'T', 'M');

https://www.w3schools.com/SQL/func_sqlserver_replace.asp

i found this

idk if this helps

[icy oyster]

and after that something that deletes the X row right?

[white elm]

i believe so

[icy oyster]

do you know if that exists? it should but we never know, at least I dont 🤣

[white elm]

you use the conn variable to write conn.execute("""SELECT REPLACE""")

[white elm]

wait are you specifically looking for how to completely delete a value in a column

or just replace old data with new data?

like some kind of a password reset system

[icy oyster]

yeah kinda

I mean idc if the whole X column is deleted

I can easily add a new one

[white elm]

hmm

im tryna understand this replace thing

i dont know that much about sql but i would guess that you could just remove the value from the column

and just insert a new value

that works too

[cyan crypt]

#python-discussion

[white elm]

?

[white elm]

like ik the basic "INSERT", "CREATE", "DELETE" and so

but im not familiar with REPLACE

[icy oyster]

yeah I think DELETE is the way to go

idk how I can identify the X row I wanna delete though 😄

[white elm]

oh

that is with "SELECT

"*

[white elm]

this

select the table name of your database

and write the column

that you want to check

[icy oyster]

cur.execute(f'''SELECT * FROM table WHERE user_id = ? (and symbol?);''', f"({user_id}, {symbol})"

something like this?

cause I cannot use f-strings with sql

so I should use this format I guess?

[white elm]

wait

cant you use f string within sql code in

python?

if so then i get why my damn database would not receive a new record

lol..

[delicate field]

SQL & f-strings
Don't use f-strings (f"") or other forms of "string interpolation" (%, +, .format) to inject data into a SQL query. It is an endless source of bugs and syntax errors. Additionally, in user-facing applications, it presents a major security risk via SQL injection.

Your database library should support "query parameters". A query parameter is a placeholder that you put in the SQL query. When the query is executed, you provide data to the database library, and the library inserts the data into the query for you, safely.

For example, the sqlite3 package supports using ? as a placeholder:

query = "SELECT * FROM stocks WHERE symbol = ?;"
params = ("RHAT",)
db.execute(query, params)

Note: Different database libraries support different placeholder styles, e.g. %s and $1. Consult your library's documentation for details.

See Also
• Extended Example with SQLite (search for "Instead, use the DB-API's parameter substitution")
• PEP-249 - A specification of how database libraries in Python should work

[icy oyster]

for example you cannot do this

table = my_table
f"SELECT * FROM {table} WHERE"

idk why, but you cant 🤣

[white elm]

damn i get why i never received an error in my system lol

conn.execute(f"""INSERT INTO users (id,username,secret,guest,queue) VALUES ({count}, '{ctx.message.author}', {sellerid}, 'None', 0);""")

this is what i did

[icy oyster]

wait

this is what u need

[white elm]

now i have to figure out how to do it without f strings

[icy oyster]

INSERT INTO table (column1, column2, column3) VALUES (?, ?, ?);''', (f"{date}", time, trade_id)

this is an example

            cur.execute(f'''INSERT INTO disdev
            (date, time, trade_id, pair, side, entry_price, tps, final_tp, sl, leverage, wallet_balance, usdt_order_value, order_value)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);''', (date, time, trade_id, symbol, side, order_price, tps, final_tp, sl, globals().get(f"{symbol}_Leverage"), wallet_balance, stake_value, usdt_order_value, order_value, long_trade_number.lower(), short_trade_number.lower())

``` this is what I have lmao

and it works 🤷‍♂️

[white elm]

oh lol

thanks for making me realise how dumb i am 😂

i literally tried to understand why i never received new data

[icy oyster]

same happened to me

[white elm]

wait i dont even need to use f strings then

i could just use normal clean variables

right?

[icy oyster]

paste code

[white elm]

conn.execute("""INSERT INTO users (id,username,secret,guest,queue) VALUES (?, ?, ?, ?, ?);""", (f"{count}, '{ctx.message.author}', {sellerid}, 'None', 0);")

its a bit messed up rn

since i just tried fix it

[icy oyster]

ahh yeah

no need of fstrings mb

[white elm]

how could i do this without f strings?

sorry im just getting lost rn haha

[icy oyster]

so my thing would be something like

'''SELECT * FROM table WHERE test_chuti = ?;''', (test_chuti_overwrite)

conn.execute(f'''INSERT INTO users (id,username,secret,guest,queue) VALUES (?, ?, ?, ?, ?);''', (count, ctx.message.author, sellerid, "None", 0)

[white elm]

ooh i see

conn.execute("""INSERT INTO users (id,username,secret,guest,queue) VALUES (?, ?, ?, ?, ?);""", (count, ctx.message.author, sellerid, 'None', 0))

[icy oyster]

bruh discord crashed?

[white elm]

this right?

[icy oyster]

went down for 10 secs

[white elm]

did it?

[icy oyster]

idk its now back online for me

[white elm]

lol ik discord is bad rn

my discord client has crashed multiple times

their performance has been strangely bad recently

[icy oyster]

i think yeah, try that

[white elm]

yeah ill give it a try

[icy oyster]

do u know if this is what I have to do?

[white elm]

yeah i would say that you try this too lol

[icy oyster]

🤣

[white elm]

btw

i got an error

Error binding parameter 1 - probably unsupported type.

do you know what this could be

i got this error

after testing the code

[white elm]

wait parameter 1 is the first column right?

like the "id" column

[icy oyster]

yeah

it goes in order

i think param 1 is like index 1

so 2nd item

idk at a 100% though

[white elm]

could it be that the "count" variable is displaying 0?

im trying to make an id for each data

so like 1, 2, 3 etc

but i might have messed it all up

[icy oyster]

uhhh

idk 🤣

[white elm]

wait i think it was the ctx.message.author

lol

[icy oyster]

never worked with d.py and sql

[white elm]

because i noticed that the username column

is not supporting "#"

ig

[icy oyster]

https://tenor.com/view/seriously-gif-23861497

[white elm]

lol

i tried adding .name

to the variable

so i'll see how this goes

[icy oyster]

lemme know what happens

[white elm]

yup that worked lmao

[icy oyster]

what does this return?

curu.execute('''SELECT * FROM userconfig WHERE identifier = ?;''', (column_name,))

[icy oyster]

great

[white elm]

wait

[icy oyster]

I'd like to do something like
if column exists:
delete column

[white elm]

isn't there a special type you can set to a column so you can use all symbols and letters

[icy oyster]

but idk how to do that in sql

[white elm]

because that would be better lol

[icy oyster]

cant u just set it as TEXT?

[white elm]

oh yeah thats true

[icy oyster]

columns in sql are X axis or Y axis?

[white elm]

uhm lol

[icy oyster]

I guess rows are vertical

and columns horizontal?

idk

[white elm]

lol ig

btw

im lost rn

no new data

has been added

and idk why

[icy oyster]

try: except:?

[white elm]

yeah

no error displayed lol

from that

[icy oyster]

https://imgur.com/a/9qgkp5v

curu.execute('''SELECT * FROM table WHERE identifier = ?;''', (column_name,))
curu.execute('''DROP COLUMN yellow_circle;''') ---> will this delete the yellow circle and the red ones also? 
``` do u know this?

[icy oyster]

ummm

can u paste the code in here? maybe I can help

!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.

[white elm]

https://paste.pythondiscord.com/ejoguleqim

here

this is the whole discord bot command

it does include the conn.execute

which is the one that does not seem to do anything lol

it should be stored in here

but it looks like it is 0 records

and the thing is that it looks like in the terminal that the code worked

since i got a error saying that the "id" cant take another value with the same unique code

or wait

[icy oyster]

idk

[white elm]

what do you use to view your data

[icy oyster]

cant find an error

https://inloop.github.io/sqlite-viewer/#

[white elm]

yeah exactly!

thats the same thing i use

wtf

and i cant see the records lol

[rigid yacht]

+-+-+--+-

[rotund geyser]

Anyone able to provide an easy fix to the table cannot be null problem ?

[obtuse vortex]

i am trying to INSERT INTO table an sql query select * from that_table; as a string but its not working does anyone have some suggestions ?

[next raft]

can u show the exact query you're trying to use?

[obtuse vortex]

insert into game(name, sql) values('fetch_users', 'select * from users where name is not 'HARRY' limit 10;') @next raft

[grim vault]

if you use single quotes inside the string use double quotes outside:
insert into game(name, sql) values('fetch_users', "select * from users where name is not 'HARRY' limit 10;")

[obtuse vortex]

i tried that. it doesnt work in postgresql

[grim vault]

is not is also not SQL, use <>

What does not work?

It should work in python, if you mean as direcrt SQL just double the quotes to escape them:
insert into game(name, sql) values('fetch_users', 'select * from users where name <> ''HARRY'' limit 10;')

[obtuse vortex]

let me try this

it worked thanks a lot!!!!

i am very happy @grim vault

@next raft thanks to you too for your time and effort!

[next raft]

I wanted to perform on_conflict_do_update with constraint as a foreign key in sqlalchemy postgres python3, but that throws error for the obvious reason that foreign key is not unique, so I tried adding unique constraint to the conflicting column, that didnt work as well, I also tried making the foriegn key as the primary key , but even that doesnt seem to work, so does anyone have a work around for this situation?

stmt = insert(myTable,bind=engine).values(dict)
update_stmt = stmt.on_conflict_do_update(                                        constraint=myTable.c.foreign_key_column,set_=dict)
conn.execute(update_stmt)``` this is what i actually want to do

[placid coral]

What id database?

[remote latch]

cursor.execute('''DELETE FROM server WHERE id = ?;''', (id,))
cursor.execute('''SELECT * FROM server WHERE id = ?;''', (id,))
exists = cursor.fetchall
if not exists:```
why does it always say it exists when I deleted the record beforehand

[icy oyster]

why an .sql-journal file appeared when I executed my script?

[icy oyster]

Something like this happened to me, use a time.sleep in between

since python is quicker than SQL

[grim vault]

You are missing the parentheses cursor.fetchall()

[remote latch]

time.sleep(1) between the delete and select?

[deep maple]

Hi, I have a bot discord with 10k servers and it has to query a database for each message, do you know how I can do it or what I can use to make mysql perform well enough, even if it grows and the number of queries increases. I thought about a cache system but I don't know how to implement it with mysql connector, I don't want to have to write a whole cache system for each request but I don't know how I could convert the sql requests into a python function to implement it all, here is an example of what I'm trying:

[icy oyster]

I think yeah?

[remote latch]

chid = cursor.execute('''SELECT commandschannel FROM server WHERE id = ?''' (message.channel.id,)).fetchone()```
anyone know why it's giving me the error "TypeError: 'str' object is not callable"

[fading patrol]

Is this sqlite3 or what?

[remote latch]

yes it's sqlite3

[fading patrol]

I haven't used it much but should that maybe be:

chid = cursor.execute('''SELECT commandschannel FROM server WHERE id = ?''' % message.channel.id).fetchone()

I'm just going off the docs here: https://docs.python.org/3/library/sqlite3.html

[grim vault]

You're missing a , after the sql statement, and mybe split it up.

cursor.execute("SELECT commandschannel FROM server WHERE id = ?", (message.channel.id,))
chid = cursor.fetchone()

[remote latch]

ah got it

[fair girder]

is there a way to get the first value of a column in sql and use that in group by afterwards?

[tawny nebula]

Has there anyone been playing with repository patterns with Python?

I read about it from the Designing data intensive applications and apparently it's somewhat popular in Java but seems quite rarely used in Python

[torn sphinx]

so i've inserted this into my python TinyDB https://paste.pythondiscord.com/emolorewoj

and i wanna know how i can query and give dimanche and Lundi to a variable called result

[vernal sinew]

If I want to search a mongo database for a certain int and if that int is found return true, is there a faster way then just doing find?

[flat pewter]

What's the best way to do interactive/dynamic SQL queries in some kind of function / class ?

I am using Dash with some checkboxes. Once you clicked on your boxes you fire your query against the database.
You can also add some additional filters like provide a list of another kpi you want to filter.

Right now I am doing.

base_sql = ''' select x from y where z'''
query = base_sql

optional_sql_1 = ''' AND something'''
optional_sql_2 = ''' AND something_else'''

if optional_1:
query = query + optional_sql_1

if optional_2:
query = query + optional_sql_2

etc..

Not gonna lie. It's getting pretty ugly. I have to do the same thing for params since I am going to bind them.
I am using (?,?).. notation which is also interactive depending on what checkboxes you clicked. Help is much appreciated 🙂

[warped plank]

https://www.dolthub.com/blog/2022-06-14-announcing-museums-bounty/

we run dataset bounties and we just launched a new one. this time we're building a dataset on museum collections. let me know if you have questions! this is self-promo ish btw, but i think also relevant

[tawny nebula]

sounds you might have a use for SQL expressions

SQLAlchemy has those. You could construct the queries using those instead of doing your own string manipulation (which is dangerous)

You basically ask user for input and you construct the query based on that?

[oak elbow]

Would this be the correct section to ask about multi-index dataframes?

I needed help to create one, I'd be iterating through a for loop where I'm returned a dictionary and each dictionary has a unique ID and I would want that unique ID to serve as the secondary index. The primary index is a timestamp.

[flat pewter]

The user is fairly restricted. I don't think it's dangerous just ugly. The user can click most stuff with checkboxes. Think like: customer_type (with checkboxes) [x] VIP, [x] Standard, [x] LowValue... etc. and the optional parts is stuff like specific store ids. in the end you get all customers with your filters. cursor.execute(query, params). It is not possible to inject something (I think..)

I'll look into the expressions. I figure they provide like objects and do the sql part internally?

[flat pewter]

the checkboxes are also kinda annoying to handle. the query looks like: '''WHERE customer_type in ({costomer_type_placeholder})'''.format(costomer_type_placeholder=costomer_type_placeholder) ............ costomer_type_placeholder = ', '.join(['?']*len(customer_type_checkbox_elements)

So if you only click VIP, you will get costomer_type_placeholder = (?) where ? translates to "VIP"

[torn sphinx]

hello i need help with database for warnings discord bot

[spring wave]

aaa

a

a

[placid coral]

How to add database?

[versed quiver]

Just released a new package to provide a clean, typed interface for those of us looking to use raw SQL.
https://www.reddit.com/r/Python/comments/vc86y2/bring_your_own_query_introducing_mayim_the_not/

[white elm]

Does anyone know why i cant see any new records in my database? From what i can see in my terminal output, there was no errors of the executed line of code. But when i check my database, it's no new records added.

conn.execute("""INSERT INTO users (username,secret,guest,queue) VALUES (?, ?, ?, ?);""", (ctx.message.author.name, sellerid, 'null', 0))

Idk i might have written something in the code that made the database not upload new data

here's how i've made the fields

[unkempt prism]

Is autocommit on? Otherwise you will likely need to have another line with:

conn.commit()

[white elm]

oh

i could just try to add that line

under conn.execute

to see

[white elm]

yeah, that was the thing that was needed. Im just pretty stupid haha

it works as it should now

[white elm]

btw is there some kind of an "SELECT" feature that allowed you to get the value of a column in the same place there you already found a specific value in another column

allows*

nvm found out how

[wise goblet]

when we run some query it is later optimized to run more efficiently

how to turn this feature off for debugging purposes

I was to run it unefficiently

[paper flower]

I think it depends on the database?

[wise goblet]

postgresql?

[paper flower]

Postgresql caches a lot of things, maybe you could do something like discard plans?

Otherwise stopping postgres and clearing cache is the only way i could think of

[boreal zenith]

For using mongodb with Flask which package is good, flask-mongoengine or mongoengine. I don't see any updates to flask version of the package in the last 2 years, So confused whether its gonna get me into troubles or not!!

[cobalt fiber]

hello, i seem to hav trouble. whenever someone claims a for a 2nd time daily, they should be given credits and today's date should be updated in the db but... everytime, the db records the date as 2003, 2002 or 2001

heres the partial code

current_time = datetime.date.today()
tomorrow = (current_time + datetime.timedelta(days=1))

try: #check if this user has a claimed date recorded
  claimed = db.execute(f"SELECT claimed_date FROM bankData WHERE user_id = {ctx.user.id}").fetchone()
except:
 pass

if claimed == None: #first time claimer
  db.execute("INSERT OR IGNORE INTO bankData (user_id, amount, claimed_date) VALUES (?,?,?)", (ctx.user.id, 100, current_time))
  db.commit()
  #hello user, you claimed. come back tomorrow

elif (datetime.date.today() - datetime.date.fromisoformat(str(claimed[0]))).days > 0: #not claiming for first time
  db.execute(f"UPDATE bankData SET amount = amount + 100, claimed_date = {current_time} WHERE user_id = {ctx.user.id}")
  db.commit()
  #hello user, you claimed. come back tomorrow```

user_id is int, amount is int and claimed_date is varchar(255)

[grim vault]

That's because you are using f-strings

!sql-fstring

[delicate field]

SQL & f-strings
Don't use f-strings (f"") or other forms of "string interpolation" (%, +, .format) to inject data into a SQL query. It is an endless source of bugs and syntax errors. Additionally, in user-facing applications, it presents a major security risk via SQL injection.

Your database library should support "query parameters". A query parameter is a placeholder that you put in the SQL query. When the query is executed, you provide data to the database library, and the library inserts the data into the query for you, safely.

For example, the sqlite3 package supports using ? as a placeholder:

query = "SELECT * FROM stocks WHERE symbol = ?;"
params = ("RHAT",)
db.execute(query, params)

Note: Different database libraries support different placeholder styles, e.g. %s and $1. Consult your library's documentation for details.

See Also
• Extended Example with SQLite (search for "Instead, use the DB-API's parameter substitution")
• PEP-249 - A specification of how database libraries in Python should work

[cobalt fiber]

alright, ill giv it a try

[grim vault]

f"UPDATE bankData SET amount = amount + 100, claimed_date = {current_time} WHERE user_id = {ctx.user.id}"
-> "UPDATE bankData SET amount = amount + 100, claimed_date = 2022-06-15 WHERE user_id = 1234"
-> "UPDATE bankData SET amount = amount + 100, claimed_date = 2001 WHERE user_id = 1234"

[cobalt fiber]

tysm! it worked. i was getting headaches bcoz of this

[brave bridge]

Hi @oblique nebula, this is a good time to read our #rules. Please don't spam requests for paid work.

[oblique hamlet]

async def set_server_setting(ID: int, setting: str, value):
  async with aiosqlite.connect(database) as db:
    await db.execute(f"UPDATE guilds SET {setting} = ? WHERE guild_id = {ID}", (value,))
    await db.commit()

async def main():
  await set_server_setting(0, "filter_words", {"a", "b", "c"})
  server_settings   = await get_server_setting(0)
  print(server_settings['filter_words'])
``` using aiosqlite, anyone know why I aam getting `sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.`?

[remote latch]

anyone know why my sqlite3 database doesn't save between sessions of running the code despite me using the commit function

do I have to close the connection every time I change smthn in the database for it to matter??

[paper flower]

Sets are not supported in sqlite

[paper flower]

I'm not sure, can you share your code?

[remote latch]

 cursor.execute('''UPDATE server SET commandschannel=? WHERE id == ?''',(message.channel_mentions[0].id,message.guild.id))
 conn.commit()```
it'll save temporarily but wont save when I stop the code then run again

using vscode btw

[paper flower]

How do you create your connection?

Would be great if you can create a minimal reproducible example

[umbral delta]

Is there any way to get Output from the Database and store in a variable?

    result = mycursor.fetchall()

    # We are getting the number out from a [(1,)] to 1
    for data in result:
        id_user = data[0]
        id_discord = data[1]```

I found this way, but is not the best one.

[grim vault]

mycursor.execute("SELECT ID_User FROM User WHERE ID_Discord = ?", (id_account_user,))
result = mycursor.fetchone()
if result is None:
    print(f"{id_account_user}: not found")
else:
    id_user = result[0]

# id_discord == id_account_user
# must be the same because of ... WHERE ID_Discord = id_account_user

[umbral delta]

It returns [] not None

Is it the same??

[grim vault]

No, [] is an empty list, so I guess you were using fetchall() instead of fetchone()

I only use fetchall() if I expect more than one result, but my guess was that there only can be zero or one user to select, so I used fetchone() which will return None or a tuple of the columns selected (in this case a tuple of one column).

[torn sphinx]

raise errors.ProgrammingError(
mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement can anyone help?