databases

[dreamy pollen]

hi does anyone know how to crud with files(pdfs) in mongodb using mongoose

[leaden beacon]

Write a code that makes a "person" class and use it to find their attributes and behaviours for example attribute can be- age, name, skin color and behaviour can be skills, accent, mindset

[fleet ibex]

J1m, its a tuple

do, print(type(myresults))

[mild merlin]

yo. I have this command, but when i use it 2 times i get this error ```python
mycursor.execute(f"SELECT gen FROM customers WHERE name = '{user.name}'")
myresult = mycursor.fetchall()
for x in myresult:
print(x[0])
result = int(x[0]) - 1
print(result)
sql = f"UPDATE customers SET gen = '{result}' WHERE name = '{user.name}'"
mycursor.execute(sql)
mydb.commit()
mycursor.execute(f"SELECT gen FROM customers WHERE name = '{user.name}'")
emb=nextcord.Embed(title="Ainta Gen", description=f"Your account is: {acc}\n**Gens left:** {result}", color=color, timestamp=datetime.now())
await channel.send(embed=emb)

File "C:\Users\Δημήτρης Κολιόπουλος\Desktop\ainta\main.py", line 158, in gen
cursor = mydb.cursor()
File "D:\coding_things\pythonV2\lib\site-packages\mysql\connector\connection_cext.py", line 569, in cursor
self.handle_unread_result(prepared)
File "D:\coding_things\pythonV2\lib\site-packages\mysql\connector\connection_cext.py", line 785, in handle_unread_result
raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found

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

Traceback (most recent call last):
File "D:\coding_things\pythonV2\lib\site-packages\nextcord\ext\commands\bot.py", line 1055, in invoke
await ctx.command.invoke(ctx)
File "D:\coding_things\pythonV2\lib\site-packages\nextcord\ext\commands\core.py", line 933, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "D:\coding_things\pythonV2\lib\site-packages\nextcord\ext\commands\core.py", line 177, in wrapped
raise CommandInvokeError(exc) from exc
nextcord.ext.commands.errors.CommandInvokeError: Command raised an exception: InternalError: Unread result found

[devout olive]

when to use Inner join or left join

like theoretically what should I know

in terms of degree and cardinality situations between tables

[raw saffron]

use inner join if you want records that are linked across both tables, omitting records that have no shared key

left join keeps all records from one table, adding nulls if they have no shared key with data from the second table.

[raw saffron]

Little bobby tables would like a word

[mild merlin]

?

[grim vault]

It means: don't use f-strings for values. What if the user name has an ' in it? (harmless but would result in an syntax error)
The error is most likely because you never fetched the last select you executed. Which you don't even need because you know the value of gen, you just did an update of the column.

[grim vault]

maybe something like this:

        mycursor.execute("SELECT gen FROM customers WHERE name = %s", (user.name,))
        row = mycursor.fetchone()
        if row is not None:
            print(f"selected gen for {user.name}: {row[0]}")
            new_gen = int(row[0]) - 1
            print(f"gen for update: {new_gen}")
            upd_stmt = "UPDATE customers SET gen = %s WHERE name = %s"
            mycursor.execute(upd_stmt, (new_gen, user.name))
            mydb.commit()
            emb=nextcord.Embed(title="Ainta Gen", description=f"**__Your account is:__** `{acc}`\n**__Gens left:__** `{new_gen}`", color=color, timestamp=datetime.now())
        else:
            # maybe an insert here?
            emb=nextcord.Embed(title="Ainta Gen", description=f"**__Your account is:__** `{acc}`\n**__You don't have any Gens__**", color=color, timestamp=datetime.now())
        await channel.send(embed=emb)```

[mild merlin]

lemme try

it works tyy

[grim vault]

Another thing: You sure the user.name is unique?

[mild merlin]

yes

[mild merlin]

i also have one more question. I want to make it like if gen (from line 1 that we select it), if this number is 0, i want to give a different output. How can i?

[grim vault]

A simple if should do? You have the value in new_gen.

Or in int(row[0]) if you mean the selected value. If the column in the database is of type integer you don't need the int().

[rigid mica]

    @commands.command()
    async def logs(self,ctx, channel : discord.TextChannel = None):
        if channel is not None:
            log = await db.execute('''UPDATE setup SET logs = $1 WHERE guild = $2''', channel.id,ctx.guild.id)
            loggings1 = await db.fetchrow('''SELECT logs FROM setup WHERE guild = $1''', ctx.guild.id)
            print(loggings1)
            loggings = loggings1["logs"]
            global logs
            logs = self.bot.get_channel(loggings) or await self.bot.fetch_channel(loggings)
            await logs.send('Channel configured as logging channel!')```

it sends all logs where the bot is in to my main server for some reason...

ping me if u reply please.

[light olive]

@client.command()
async def getuser(ctx, id):
    discord_id = int(id)
    finder = {
        "_id" : discord_id,
        "genshin_id" : { "$gt": "1" },
        "ltoken" : { "$gt": "a" },
        "nltuid" : { "$gt": "1" }
    }
    for i in mycol.find(finder):
        dis_id = i["_id"]
        genshin_id = i["genshin_id"]
        ltoken = i["ltoken"]
        nltuid = i["nltuid"]
    msg = f'dis_id = {dis_id}\ngenshin_id = {genshin_id}\nltoken = {ltoken}\nnltuid = {nltuid}'
    await ctx.send(msg)

I got this error while using the command :
UnboundLocalError: local variable 'dis_id' referenced before assignment

[brave bridge]

What is the code supposed to do? Can you explain it line by line?

[light olive]

it just find those objects
dis_id
genshin_id
ltoken
nltuid
from mongodb
the data stored like this

{
  "_id": "132",
  "genshin_id": "148",
  "ltoken": "1393",
  "nltuid": "2839"
}

[brave bridge]

So if there are two objects:

{ "_id": "132", "genshin_id": "148", "ltoken": "1393", "nltuid": "2839"}
{ "_id": "132", "genshin_id": "149", "ltoken": "1394", "nltuid": "2840"}

What should the code do?

[light olive]

There is no two object of the same 🙂
And I will fix this when I complete the code

[brave bridge]

So why do you have a for loop?

A for loop is for when you want to do something with many things

!e
Now to get to your issue, this is how you can get this error in a different way:

def f():
    things = []
    for thing in things:
        stuff = thing
    print(stuff)

f()

[delicate field]

@brave bridge :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 7, in <module>
003 |   File "<string>", line 5, in f
004 | UnboundLocalError: local variable 'stuff' referenced before assignment

[brave bridge]

Do you see why it's happening here?

[light olive]

the code use for loop in one thing?

[brave bridge]

hm?

My code fails because things is empty, so the loop body never executes.

[light olive]

Oh

[brave bridge]

So your query doesn't return any items. The loop body executes zero times, hence the error.

I don't know anything about mongodb, but it seems like you're searching with an integer ID, but the database uses string IDs.

In any case, you should use the method that fetches the first searched things, and then think about what to do if it is not found.

[light olive]

Okay thx

[quiet nebula]

if i need to use a dictionary in my bot, (dict is very small, around 10-15 pairs) should i use a dtbase, .json file, or a python dict?

[nova cove]

database would get good practice in, even if it’s small

[brave bridge]

What do you want to want to do with that dict? And where does it come from?

[quiet nebula]

I want to read values every time my bot connects to a voice channel, like {guild_id: voice_channel_to_join}

[brave bridge]

So for every server, you want to store the voice channel to join, right?

[brave bridge]

Does this dict ever change?

[quiet nebula]

yes

one dict should have all the server(it only have 3 server so far lol) and a channel for each server, very small

[quiet nebula]

yes

[brave bridge]

when?

and how?

[quiet nebula]

when users run a command to change the default channel the bot should join when online

so like, dict[guild_id] = new_channel_id

[brave bridge]

Does this information need to persist between runs? Or is it okay if the bot loses it after it restarts?

[quiet nebula]

it should persist after closing and opening

well, only because its more user friendly

otherwise i would just use a py dict lol

[brave bridge]

And how many guilds do you expect your bot to join, eventually?

[quiet nebula]

1-2 more

so around 4 at best (im not that popular)

[brave bridge]

Here's an idea: have a folder, like voice_channels/. In this folder, have a JSON file for each guild you're in, like 43985159135167136410.json.

[quiet nebula]

oh, sounds nice

i'll try that

[paper flower]

For small scale json files would do, you could migrate to sql later if you have to

[lucid hearth]

i am using mongoengine to connect to my remote mongodb atlas cluster, i dont know if i need to update python but im getting this timeout errer, it does not seem to be connecting to the database bc it is not creating the db at all.

pymongo.errors.ServerSelectionTimeoutError: website-shard-00-01.crlxw.mongodb.net:27017: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: certificate has expired...

[storm mauve]

try checking if your computer has any available updates

(your device, not python)

[lucid hearth]

how do i do that?

[storm mauve]

if windows, just press the windows key then enter Check for updates

[lucid hearth]

it says im up to date but its also a windows 10, do i need to change to a windows 11?

[storm mauve]

shouldn't have to

[lucid hearth]

my other project that uses pretty much the same code can easily connect to it

but its on a differenct cluster

connects to a different cluster

[limber dragon]

why mssql and postgre sql dont support CREATE ASSERTION. any idea ?

[dense barn]

await self.bot.pg_db.execute("UPDATE drawgoal SET $1 = $1+1 WHERE user_id = $2",self.rarity_emojis3[f'{emoji}'],message.interaction.user.id)
``` can i do something like this?

[hasty hinge]

Is it normal that my commits don't show up instantly in MySQL?
Like, I usually if I make a select just after the commit, it won't show up on the first select, but in the second one it does.

I'm not sure if it has to do with some cache, but I really don't have idea of what could cause this

I'm using MySQL 8.0.24 on the server, and I'm using AioMySQL on my code, and I'm making one connection/pool per run

[hasty hinge]

🤦‍♂️
Well, I was testing and testing and found that autocommit fixes this, so I searched some more and looks like my problem was that I wasn't commiting my SELECT queries, letting this here in case anyone else is having the same issue:

[ionic smelt]

            await db.execute('''CREATE TABLE if not exists Config (
                joinlogs INT,
                leavelogs INT,
                entrance INT
            )''')
```not sure why this only create the column `joinlogs`, the rest didnt get created

[lunar pier]

Hello! Does this looks like normal database or something won't work?

[torn sphinx]

Hey does anyone here know how to create a datasheet and link it to a login page or any sor of registration page

I need help with a project me and my friends are working with

Pls dm me personally if any one is willing to help

[nova cove]

Looks like a relational database.

[rigid mica]

    @commands.command()
    async def logs(self,ctx, channel : discord.TextChannel = None):
        if channel is not None:
            log = await db.execute('''UPDATE setup SET logs = $1 WHERE guild = $2''', channel.id,ctx.guild.id)
            loggings1 = await db.fetchrow('''SELECT logs FROM setup WHERE guild = $1''', ctx.guild.id)
            print(loggings1)
            loggings = loggings1["logs"]
            global logs
            logs = self.bot.get_channel(loggings) or await self.bot.fetch_channel(loggings)
            await logs.send('Channel configured as logging channel!')```

for some reason I keep getting this error:

Traceback (most recent call last):
File "C:\Users\bert\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\client.py", line 351, in _run_event
await coro(*args, **kwargs)
File "c:\Users\bert\Downloads\discord bots\Bertie's discord bots\cogs\moderation.py", line 136, in on_message_edit
if logs is None:
NameError: name 'logs' is not defined

    @commands.Cog.listener()
    async def on_message_edit(self,message_before, message_after):
        if logs is None:
            return
        if message_before.content.startswith("!play"):
            return
        if message_before.content.startswith("https"):
            return
        if 'youtube' in message_before.content:
            return
        else:
            embed = discord.Embed(title=f"{message_before.author.name} edited a message",description="",url = message_after.jump_url, color=0x3498db)
            embed.add_field(name='Before', value=message_before.content,
                            inline=True)
            embed.add_field(name='After', value=message_after.content,
                            inline=True)
            await logs.send(embed=embed)```

what I'm trying to do: people can set a login channel, where the logs get sent in. If there is no channel in the database, return

[nova cove]

!or

[delicate field]

When checking if something is equal to one thing or another, you might think that this is possible:

if favorite_fruit == 'grapefruit' or 'lemon':
    print("That's a weird favorite fruit to have.")

While this makes sense in English, it may not behave the way you would expect. In Python, you should have complete instructions on both sides of the logical operator.

So, if you want to check if something is equal to one thing or another, there are two common ways:

# Like this...
if favorite_fruit == 'grapefruit' or favorite_fruit == 'lemon':
    print("That's a weird favorite fruit to have.")

# ...or like this.
if favorite_fruit in ('grapefruit', 'lemon'):
    print("That's a weird favorite fruit to have.")

[rigid mica]

How do I select from multiple values? @nova cove

for example where guild = guild.id and member = member.id

[nova cove]

SELECT guildid, memberid FROM table WHERE guildid = $1 AND memberid = $2

?

[rigid mica]

ah ok

@nova cove py @commands.command() @commands.has_permissions(manage_roles = True) async def warn(self,ctx, member : discord.Member = None, *, reason = None): loggings1 = await self.bot.db.fetchrow('''SELECT logs FROM setup WHERE guild = $1''', ctx.guild.id) loggings = loggings1["logs"] global logs logs = self.bot.get_channel(loggings) or await self.bot.fetch_channel(loggings) if member is None: embed = discord.Embed( title = '**Error**', description = 'Please provide a valid member to warn!', color = (0x3498db) ) await ctx.send(embed=embed) else: if reason is None: await db.execute('''INSERT INTO warnings (guild,member, reasons, moderator) VALUES ($1, $2, $3, $4)''',ctx.guild.id, member.id, 'unknown reason', ctx.author.id ) await db.execute('''UPDATE warnings SET warns = warns + 1 WHERE member = $1 AND guild = $2''', member.id, ctx.guild.id) await ctx.send(f'{member} got warned by {ctx.author}, reason: ``unknown reason``') else: await db.execute('''INSERT INTO warnings (guild,member, reasons, moderator) VALUES ($1, $2, $3)''', ctx.guild.id,member.id, reason, ctx.author.id ) await db.execute('''UPDATE warnings SET warns = warns + 1 WHERE member = $1 AND guild = $2''', member.id, ctx.guild.id) await ctx.send(f'{member} got warned by {ctx.author}, reason: ``{reason}``') if logs is None: return await logs.send(f'{member} got warned by {ctx.author}, reason: ``{reason}``')

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: PostgresSyntaxError: INSERT has more target columns than expressions

[nova cove]

there are 4 values in INSERT INTO warnings () first parentheses

but you only use 3 placeholders in VALUES ()

[rigid mica]

ooh smh

[nova cove]

in the else statement

[rigid mica]

mb

couldve known that

[rigid mica]

does anyone have some time to fix my last issue with my bot?

[jagged holly]

Hi

Is their a svg library that allows me to update svg id or titles

I have a floor plan

Exported from Visio to svg but o need to change each office or desk square with an username from a employee list

I will use this svg in okviz synaptic panel visual for@power bi

Which I will join employee username to svg metadata employee name

For interactive dashboard

[fading patrol]

Did you try https://github.com/btel/svg_utils

[jagged holly]

Right now I have to@do it manually

https://okviz.com/synoptic-panel/

Let try

https://synoptic.design/ their tool while good has me manually clicking each box and then typing a name

And it does. Or save what you did

Only export to svg

Thanks

[midnight veldt]

Hello guys im using motor.pymongo. Do you know how to make the requests more faster

?

[rigid mica]

#help-carrot someone please.

[river matrix]

Hey, does anyone know how to run SQL through Jupyter?

[raven trail]

Anyone here with experience using/implementing Redis, specifically Redis OM in Python?

[fading patrol]

https://12ft.io/proxy?q=https://towardsdatascience.com/heres-how-to-run-sql-in-jupyter-notebooks-f26eb90f3259

[random arch]

is it ok to use Google Sheets as a database? its for a small project

[nova cove]

uhh it would prolly be fine but why not just use like SQLite

[brave bridge]

yeah, SQLite will be way faster

although depends on the needs

[keen gust]

i have a test bot . and it i does some sql operations on on_message
rn it handles 1 msg per guild per second
assuming the bot is in 1000 guilds now , it will handle 1000 msgs per second (assumption only)
so if it does sql operations maybe i get ratelimited (from either discord or from sql server)
so i need some suggestion..
is it ok to continue in such situation
or i should make bot.variables & put sql data in it and using task.loop i sync data from bot.variable to sql db every x hours
or u guys have any perfect suggestion .. ?

[proven arrow]

How does doing sql operations get your bot rate limited? Sql inserts go into the db, not to discord.

You should also specify the database you have and what kinds of indexes on the table you are inserting into have.

For most databases 1000 inserts per second is nothing. It can handle way more. But the actual value depends on your hardware, table structure. So best you run some performance tests.

[keen gust]

@proven arrow
amazonaws db
postgres
heroku host
asyncpg driver

[proven arrow]

You may run into limits with number of connections you have, it depends how the inserts are done. If this is going to be an issue then you can queue inserts and perform them in batches.

[keen minnow]

DBs can handle multiple thousands of operations per second.
Things to consider:

• what can happen if your machine is unexpectedly shutdown/rebooted prior to syncing the data?
• Either you reduce the number of queries, scale up or scale out

[keen gust]

ok that means i should not worry
btw thanks for the generous info and help

[proven arrow]

No you should. You have not tested it. If you are receiving 1000 messages per second and requesting a connection from the database, you may hit the limit like I said. So you should test with your current codebase first, before putting it live.

[keen minnow]

In general when it comes to performance:

• don't assume anything
• validate your hypothesis
• make sure you focus on the right thing

[keen gust]

ok , i will do it tonight..👍

[keen gust]

ok i will keep in mind...👍

[keen minnow]

also you can't improve what you can't measure.
So setup some monitoring and telemetry

[keen gust]

ok i m also gonna try doing a test upon bot.varriable VS db in sense of execution speed performance

[nimble lava]

hello i want to do pooling to my mysql but it says mysql.connector has no attribute pooling

i installed mysql.connector from pip

[raven trail]

Inspect the package and see if it is using a different alias for the method you want, if not, you may have the wrong package

[mild merlin]

Hello. I have this code, but i want to make it like when row is 0, to give different output.```python
mycursor.execute("SELECT gen FROM customers WHERE name = %s", (user.name,))
row = mycursor.fetchone()
print(f"selected gen for {user.name}: {row[0]}")
new_gen = int(row[0]) - 1
print(f"gen for update: {new_gen}")
upd_stmt = "UPDATE customers SET gen = %s WHERE name = %s"
mycursor.execute(upd_stmt, (new_gen, user.name))
mydb.commit()
lines.remove(random_item_from_list)
with open('rockstars.txt','w') as f:
f.writelines(lines)
emb=nextcord.Embed(title="Ainta Gen", description=f"Your account is: {random_item_from_list}\n**Gens left:** {new_gen}", color=color, timestamp=datetime.now())
await user.send(embed=emb)

[mild merlin]

??

[torn sphinx]

Is it possible to do SQL queries in Python in a more elegant way?

[fading patrol]

Yes, you can use an ORM like SQL Alchemy

[torn sphinx]

Is it a mapping SQL to Python objects?

Or you can use a custom language instead operating SQL as strings?

[fading patrol]

Exactly

[torn sphinx]

@fading patrolDo you like Python very much?

[fading patrol]

You can say that, yes

[torn sphinx]

@fading patroldo you have experience in any other language(s)?

@fading patrolloving a language without having experience with other languages sounds suspicious.

[proven arrow]

Are you not able to use a simple if check ?

[mild merlin]

i've tried using if row = "0": ... but doesnt work

[proven arrow]

= is for assignment

[shadow flicker]

hi

[torn sphinx]

@proven arrowAre you a real pilot of air hansa league member?

@shadow flickerHello Subham Shaurma

[proven arrow]

For German cargo previously.

[shadow flicker]

Hello bro

@torn sphinx Would u pls teach me python

[torn sphinx]

Why not? But I can teach just Java. Python i don't know.

[torn sphinx]

A cargo pilot doesn't count.

[shadow flicker]

@torn sphinx Thankuu so much

[proven arrow]

https://tenor.com/view/arthur-treachers-what-a-pity-oh-dear-what-a-shame-sarcastic-gif-19632319

[shadow flicker]

@torn sphinx Bro would u like to be my friend in discord

[torn sphinx]

Of course! Always.

[shadow flicker]

i have sent u request

join my c++ chanell also

[torn sphinx]

I accepted your kind request for our friendship!

[torn sphinx]

I can't see your suffering!

[torn sphinx]

really cargo pilots don't count. if you fall and die just you and some useless goods are wasted

[proven arrow]

Ok. Whatever makes you happy. 🙂

[torn sphinx]

Beer makes me happy. That not so.

[proven arrow]

Are you even old enough to drink? 😅

[torn sphinx]

I am already 14 if you meant that

it doesn't mean I am 14

[proven arrow]

Sounds like it

[torn sphinx]

oh it hurts.

you downgraded me. i am just drunk. i am not stupid.

[proven arrow]

Ok not sure what your intention here is, but I need to go pick the kids up. Bye.

[torn sphinx]

good luck1

kids are the main importance

your DNA replications. they are the most important things in the world

[gusty mulch]

Hi I'm getting this error when I try to write some data to my db PostgresSyntaxError: syntax error at or near ")" and this is the full traceback sql ERROR:cogs.error_handler:PostgresSyntaxError: syntax error at or near ")" (In suggest) Traceback: File "/usr/local/lib/python3.8/dist-packages/discord/ext/commands/core.py", line 85, in wrapped ret = await coro(*args, **kwargs) File "/home/modmail/cogs/general.py", line 323, in suggest suggestions = await self.bot.get_sug(post.id, ctx.guild.id) File "/home/modmail/classes/bot.py", line 80, in get_sug res = await conn.execute("INSERT INTO suggestions (post, guild, member, original, message) VALUES ($1, $2, $3, $4, $5,) RETURNING *", File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 320, in execute _, status, _ = await self._execute( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1659, in _execute result, _ = await self.__execute( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1684, in __execute return await self._do_execute( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1719, in _do_execute stmt = await self._get_statement( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 398, in _get_statement statement = await self._protocol.prepare( File "asyncpg/protocol/protocol.pyx", line 168, in preparecould I be missing a ) somewhere, I can send more of my code if needed

[burnt turret]

you have a trailing comma in the VALUES

[gusty mulch]

okay thanks, what about this error PostgresSyntaxError: syntax error at or near "suggestions" with traceback sql ERROR:cogs.error_handler:PostgresSyntaxError: syntax error at or near "suggestions" (In suggest) Traceback: File "/usr/local/lib/python3.8/dist-packages/discord/ext/commands/core.py", line 85, in wrapped ret = await coro(*args, **kwargs) File "/home/modmail/cogs/general.py", line 330, in suggest await conn.execute("INSERT suggestions SET member = $1, original = $2, message = $3 where post = $4 and guild = $5", ctx.author.id, ctx.message.id, suggestion_mesage, post.id, ctx.guild.id) File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 320, in execute _, status, _ = await self._execute( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1659, in _execute result, _ = await self.__execute( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1684, in __execute return await self._do_execute( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1719, in _do_execute stmt = await self._get_statement( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 398, in _get_statement statement = await self._protocol.prepare( File "asyncpg/protocol/protocol.pyx", line 168, in prepare

[burnt turret]

INSERT INTO suggestions ...

you're missing the INTO before the table name

uh actually wait

what do you want to do with that query?

basically the rest of the query (except for the INSERT at the beginning) looks like a standard UPDATE query

[gusty mulch]

I thought it might be missing into but i wasn't sure.
as for what I want to do with that query I want to write all of the values to the db, I would use UPDATE as I have for all of the others but since one of my columns is a serial number it doesn't seem to like UPDATE being used which is why I'm using insert

[burnt turret]

do you want to insert a new row into the database, or do you want to change an existing row?

[gusty mulch]

insert a new row

which would mean i would need insert right?

[burnt turret]

right

can you try writing the correct insert query?

the syntax for an INSERT goes something like INSERT INTO <table name> (column1, column2, ...) VALUES(value1, value, ...)

[gusty mulch]

well I have this in my bot.py file which handles my table connectionspy async def get_sug(self, post, guild): async with self.pool.acquire() as conn: res = await conn.fetchrow( "SELECT * FROM suggestions WHERE post=$1 and guild = $2", post, guild) if not res: res = await conn.execute( "INSERT INTO suggestions (post, guild, member, original, message) VALUES ($1, $2, $3, $4, $5) RETURNING *", post, guild, None, None, None)

[torn sphinx]

why will anyone use MYsql and stuffs if he can do this in vs code?

is there any benifit in writing in command line rather then vs code

yes

oh thats means it doesn't matter much

[midnight veldt]

Hello, has someone worked with mongodb?

I need a help in it

[fading patrol]

You want an audit trigger, here's how it works on Postgres https://wiki.postgresql.org/wiki/Audit_trigger

[gusty mulch]

So I think I'm doing something wrong in how I'm writing my data to my db bc I keep getting this errorsql DataError: invalid input for query argument $1: 'T' (an integer is required (got type str))with this tracebacksql ERROR:cogs.error_handler:DataError: invalid input for query argument $1: 'T' (an integer is required (got type str)) (In suggest) Traceback: File "/usr/local/lib/python3.8/dist-packages/discord/ext/commands/core.py", line 85, in wrapped ret = await coro(*args, **kwargs) File "/home/modmail/cogs/general.py", line 331, in suggest await conn.execute("UPDATE data SET sugcount=$1 WHERE guild=$2", suggestions[5], ctx.guild.id,) File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 320, in execute _, status, _ = await self._execute( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1659, in _execute result, _ = await self.__execute( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1684, in __execute return await self._do_execute( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1734, in _do_execute result = await executor(stmt, timeout) File "asyncpg/protocol/protocol.pyx", line 183, in bind_execute File "asyncpg/protocol/prepared_stmt.pyx", line 197, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msgand this is my suggestion tables sqlsql REATE TABLE IF NOT EXISTS public.suggestions ( post bigint NOT NULL, guild bigint NOT NULL, member bigint, original bigint, message text COLLATE pg_catalog."default", suggestion integer NOT NULL DEFAULT nextval('suggestions_suggestion_seq'::regclass), CONSTRAINT suggestions_pkey PRIMARY KEY (post, guild) )

[pure sleet]

is suggestions[5] a string?

if so the error is pointing that that is the issue

convert it to an int, that's what your database is expecting

[jolly fog]

Any good guides for Postgres ?

[gusty mulch]

well suggestions[5] should be a int, like the sql shema above sayssql suggestion integer NOT NULL DEFAULT nextval('suggestions_suggestion_seq'::regclass),but maybe i'm supposed to be calling suggestions[6]? i thought calling stuff from db's was indexed from zero, i.e. column 1 of the table would be accessed as suggestions[0]

[pure sleet]

i don't know what the suggestions array contains in your python code, but as i said above, the problem probably lies there, so double check if it's actually an int or string in python

[gusty mulch]

well adding py if suggestions[5].isdigit(): await ctx.reply("Suggestions[5] is `INT`") else: await ctx.reply("Suggestions[5] is not `INT`") if suggestions[6].isdigit(): await ctx.reply("Suggestions[6] is `INT`") else: await ctx.reply("Suggestions[6] is not `INT`")tells me Suggestions[5] is not INT and Suggestions[6] is not INT when i'm pretty sure at least one of them should be int

[pure sleet]

so it's a string

[gusty mulch]

but one of them should be int not string according to my sql schema

[pure sleet]

so idk what could be causing that, but if it's a string of nothing but numbers, as i said, convert to an int first

bruh

where is suggestions getting data from?

let's start there

the array i mean

[gusty mulch]

this tablesql CREATE TABLE IF NOT EXISTS public.suggestions ( post bigint NOT NULL, guild bigint NOT NULL, member bigint, original bigint, message text COLLATE pg_catalog."default", suggestion integer NOT NULL DEFAULT nextval('suggestions_suggestion_seq'::regclass), CONSTRAINT suggestions_pkey PRIMARY KEY (post, guild) )which creates the array herepy suggestions = await self.bot.get_sug(post.id, ctx.guild.id)using this functionpy async def get_sug(self, post, guild): async with self.pool.acquire() as conn: res = await conn.fetchrow( "SELECT * FROM suggestions WHERE post=$1 and guild=$2", post, guild) if not res: res = await conn.execute( "INSERT INTO suggestions (post, guild, member, original, message) VALUES ($1, $2, $3, $4, $5) RETURNING *", post, guild, None, None, None) return res

[pure sleet]

print suggestions [5] and tell what you see

[gusty mulch]

it prints this, I added some extra text to the output so i could identify itpy Printing output of Suggestions[5]: T Printing output of Suggestions[6]:

[pure sleet]

can you print suggestions?

[gusty mulch]

the entire array?

[pure sleet]

yes

[gusty mulch]

it just outputs Output of suggestions array: INSERT 0 1, guessing somethings screwy with my python sql statements then

[pure sleet]

hhhm

so it's a string

which I'm assuming it's not returning anything from the fetch command it seems

[gusty mulch]

hmm, weird

[gusty mulch]

so does my sql and python look wonky? bc i assume it is but i'm just not seeing it

[pure sleet]

you function does two things, basically, it executes a select statement, and an insert statement

what's happening here is that, it executes the insert statement if the select query returns nothing

that's the fault in your logic,

it's basically deciding to execute the insert if your select returns nothing etc

at all times

[grim vault]

You'll need to fetchrow() the insert after the exeution to get the data.

I don't have postgres to test, but try to also use conn.fetchrow(...) for the insert, not conn.execute()

[gusty mulch]

so you mean somejting like this?py async def get_sug(self, post, guild): async with self.pool.acquire() as conn: res = await conn.fetchrow( "SELECT * FROM suggestions WHERE post=$1 and guild=$2", post, guild) if not res: res = await conn.fetchrow( "INSERT INTO suggestions (post, guild, member, original, message) VALUES ($1, $2, $3, $4, $5) RETURNING *", post, guild, None, None, None)bc that seems to be working, it keeps throwing errors on the bits I added to troubleshoot with, this being one of thempy ERROR:cogs.error_handler:AttributeError: 'int' object has no attribute 'isdigit' (In suggest) Traceback: File "/usr/local/lib/python3.8/dist-packages/discord/ext/commands/core.py", line 85, in wrapped ret = await coro(*args, **kwargs) File "/home/modmail/cogs/general.py", line 327, in suggest if suggestions[5].isdigit(): it's not like I need that if statement i'm just a bit confused on why a integer doesn't have a isdigit() attribute

[proven arrow]

Because it’s a method of string

[gusty mulch]

oh, well that makes sense but shouldn't it then execute my else statementpy if suggestions[5].isdigit(): await ctx.reply("Suggestions[5] is `INT`") else: await ctx.reply("Suggestions[5] is not `INT`")

[proven arrow]

No because there’s an error, so it doesn’t even reach there

[gusty mulch]

oh okay that makes sense

[cobalt fossil]

Useful

[fluid glen]

async def testgc(ctx):
    id = ctx.author.id
    for r in cur.execute("SELECT EXISTS(SELECT 1 FROM bbgc WHERE playerid = (?)", (id,)):
        if r == 1:
            print('Table exists.')
        else:
            print("table does not exist")```

With this bit of code, I'm getting the error: ```for r in cur.execute("SELECT EXISTS(SELECT 1 FROM bbgc WHERE playerid = (?)", (id,)):
sqlite3.OperationalError: incomplete input```
what could be the issue?

this is with sqlite3

[grim vault]

You are missing a closing ) in the sql string.

[fluid glen]

where would that go

[grim vault]

either remove the opening ( before the ? (it's not needed) or add anoter one after the last.

"SELECT EXISTS(SELECT 1 FROM bbgc WHERE playerid = ?)"
"SELECT EXISTS(SELECT 1 FROM bbgc WHERE playerid = (?))"

[fluid glen]

ohh thank you

[grim vault]

Also: r in this context is a tuple, so if r == 1: will always be False. You need if r[0] == 1:

And there is only one result possible, no for loop needed. A simple fetchone() will do.

[patent grotto]

Hey guys,

I'm adding to a database from Python and some of the fields need to be turned into their own table.

The problem is that in the original data my script gets is not unique and so my table auto generates a primary key. When I'm then looking to create linked records in the linked table, I don't know what the record ID is in the primary table (as it was auto generated by the database).

Can anyone help me to think of a decent way out of this? I suppose I could move the creation of the primary key from the database to Python, checking the last ID and adding one etc but that seems an unnecessary and hacky change?

Even if I were to re pull the primary table afterwards, as the python data doesn't have a key I'm not sure how I would find the correct records without using all fields and the date as a sort of composite key which feels even worse 😦

The other way I was thinking of doing it was to get the current last ID and then calculate what the ID should be. This table shouldn't very often be modified by anything other than this process but if it isz, while the process is working, that could break this solution.

[pure sleet]

are you using an ORM or raw sql to execute queries?

[patent grotto]

I'll admit that I don't know what these two things are but I'm using CX_Oracle and using the executemany functionality

[pure sleet]

are you your self writing queries like "INSERT INTO ..." etc?

or is this done for you

[patent grotto]

So I'm using SQL strings like:

SQL = "insert into ccops.CMS_ISSUE_KEY" + Test_String +\
            " (ISSUE, ISSUE_ID) VALUES (:1, :2)"

[pure sleet]

ok, cool

so, you can get the last auto generated primary key

[patent grotto]

Yes

[pure sleet]

yea gimme a sec

[pure sleet]

what database are you using?

there's like a LAST_INSERT_ID() function in most sql databases

might have to verify with yours

so you can use a select statement like

SELECT LAST_INSERT_ID();

and it will return the last auto incremented id

[patent grotto]

the problem is that I'm inserting about 1.6K records into the primary table at a time (with a single executemany command)

It's Oracle

[pure sleet]

i mean, you can create a procedure that does this for you

but else, idk

and again, check the equivalent of that function for your database

[patent grotto]

I wonder if the best way would be to first get what ID we're up to, assume that my first (of 1.6K) will be one more than this and keep adding and then check my prediction at the end and only if this is validated; go ahead and insert to the secondary table the predicted forign keys?

It would be a nighmare to match these records up though if it ever wasn't validated :/

[pure sleet]

in what situation do you need to make 1.6k concurrent inserts?

[patent grotto]

I don't really get what your suggested though as these are seperate tables?

[patent grotto]

This process uploads a csv file up to a database table

[pure sleet]

no, last_insert_id is a function that gets the last auto generated key

again, im not sure if it's valid for oracle as i've never used that

but this is the main idea

[patent grotto]

I still don't get what your suggesting though. they're different tables :/

[pure sleet]

what are you trying to do?

[patent grotto]

Is it possible for us to try voice chat as it's diffucult to explain over text?

[pure sleet]

sure

wait, no my wifi won't let me

it wont connect

[patent grotto]

Fair enough - I will try to explain better over text

Ok, so I currently have an automation which uploads a CSV file of around 1.6K records into a primary table. It does this as one insert statement. I now need to take some of these fields (touchpoint 1, touchpoint 2, touchpoint 3, etc), remove from the primary table and create a touchpoint table with touchpoint number, value and ID from the primary table

[pure sleet]

what do you mean by some?

is this some sort of filter?

[nimble bear]

Is it normal for pymongo to take 24 seconds to retrieve 12k documents (1.2mb) from my database

[patent grotto]

Not really, it's more like I'm transposing some of the feilds (the touchpoint fields) and turning them into their own table. So insead of the primary table having fields touchpoint 1, touchpoint 2, touchpoint 3; the touchpoint table has the primary table's ID as a forign key, touch point number (1, 2 or 3) and what value this is.

[pure sleet]

so it's a relationship between the two tables?

[patent grotto]

Yes

[pure sleet]

but you need the id from the first table to insert into the second one?

[patent grotto]

I know nothing about mongo db but this feels in computer and esspeically Python time. It might be faster if you use somthing like Rust, C## or C

[patent grotto]

Yes

[pure sleet]

ok, again

[nimble bear]

oh i see, I only know python and nodejs lol

[pure sleet]

to my previous point

[nimble bear]

Its a shame it might be like that lol

[pure sleet]

last_insert_id, will get the last auto generated id

that is a function,

it get's the last auto generated id

since your primary key is auto generated

[patent grotto]

12K docs is a lot of docs and Python is one of the slowest programming languages around, that's why when people are dealing with really big data, they tend not to use Python

[nimble bear]

Yeah, maybe ill take a look at other options then

[pure sleet]

instead of just inserting everything, and then worrying about problem 2 later, you can loop through each of your records and insert, then use that function, get the id, then insert in your second table

that's what i was going to suggest to you

[patent grotto]

Could do. I could be talking nonsence beucase I really don't know mongo db, it's possible you can speed this up using Python but if you want really fast while still being intuative, try Rust maybe

[nimble bear]

Yeah rust could be a good option, does mean learning a whole new language though

[patent grotto]

Could be, NodeJS might also be faster - you could try that to see if it's purley a Python slow thing or not

[nimble bear]

Yeah Ill see if node is any faster, thanks for the help 🙂

[patent grotto]

Would it not be better to generate the primary key in Python and then lopping every commit indevually?

[pure sleet]

how is python going to know which key to generate?

[patent grotto]

get the last record before staring and then start at the number above that and keep incramenting

[pure sleet]

still a loop no?

when you insert into the second table

[patent grotto]

to create the records yes (which it is now) but it woulnd't be a loop of inserts, just a single insert and commit

[pure sleet]

can you show me how this single insert works?

as an example etc

if i have a bunch of data for example

[patent grotto]

sure, one mo

[sterile pelican]

Hello I want to make it so when the data of user, member or guild is updated, the data_last_used changes to the current timestamp. I tried using triggers but this didn't work obviously. Also I am afraid it may cause infinite recursion

CREATE FUNCTION update_last_used() RETURNS trigger AS $$
    BEGIN
        IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') THEN
            IF TG_TABLE_NAME = 'members' THEN
                UPDATE members SET data_last_used = CURRENT_TIMESTAMP WHERE id = NEW.id AND guild_id = NEW.guild_id;
            ELSE
                UPDATE TG_TABLE_NAME SET data_last_used = CURRENT_TIMESTAMP WHERE id = NEW.id;
            END IF;
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ulu_trigger_users
BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_last_used();

CREATE TRIGGER ulu_trigger_members
BEFORE UPDATE ON members FOR EACH ROW EXECUTE FUNCTION update_last_used();

CREATE TRIGGER ulu_trigger_guilds
BEFORE UPDATE ON guilds FOR EACH ROW EXECUTE FUNCTION update_last_used();```
Maybe there's an easier way to do this (I also need it to update on select, but triggers don't support that lol)

[patent grotto]

So you're using CX_Oracle's executemany() function and you have your SQL, let's say like I had before:

SQL = "insert into ccops.CMS_ISSUE_KEY" + Test_String +\
            " (ISSUE, ISSUE_ID) VALUES (:1, :2)"

Then you just define your data table and insert your values like so:

Data = [['TestIssue1', 1], ['TestIssue2', 2]]

executemany(SQL, Data)

Does that make sence @pure sleet how in the above example has one two records but could equaly have thousands of records and would be a single insert statment?

[nimble bear]

In nodejs it takes 41s to get the amount of documents in the database which is almost double which isnt ideal

[patent grotto]

ahh... 😦

[pure sleet]

i see, executemany is a function from the driver im guessing

[patent grotto]

That is strange because I just googled it and confirmed that node is generally much faster than Python - maybe not for database stuff. Sorry about that

[patent grotto]

Yea, I think so.

I can tell you that where as inserting about 10K records will take about 30 seconds with a loop; it less than a second using execute many

[pure sleet]

yea, i see

[nimble bear]

Its all good, was worth a try

[patent grotto]

Thanks for helping me explore my options @pure sleet. I hope that executemany() thing is useful for you. I suspect that most databases will have a similar function

[pure sleet]

yeah, i just never had a situation where i needed something like it

but yeah, say you can select the last and first id's, how would you generate data for the inbetween records?

for your second table that is

[patent grotto]

Probably worth it even for smaller number of inserts, if your doing multiple inserts/updates etc at once, I think it's just more efficient then a loop, probably won't really notice it below 100 though

[pure sleet]

i mean i never needed to do more than one insert at a time for my use cases etc

[patent grotto]

So if instead of having the database auto incrament my primary key, I think I'm going to get Python do it. So I'm going to check the last ID number, start with one more than that and keep incramenting in Python. That way I always know what my the ID is of the record I'm on and can use it for the secondary table insert

[patent grotto]

Fair enough. I work for an energy company so we deal with relatively large data sets

[pure sleet]

oh right, you can just grab the last data in your db and check the primary key to get your starting point then

sounds like a plan to me

unless im missing something

[patent grotto]

Yes. If the highest primary key value is 1000, I start with 1001 and work my way up and I always know what my ID number is.

It means chaning how my database table is defined in the database and my test table, which is a pain but I think it's the least bad way of doing it

Gonna call it guys. Thanks again for your help @pure sleet

[knotty wharf]

I am having a bad time inserting list of item on mySQL database using python connector into mariaDB
(index 0 of item = A should go with index 0 of day = '2222-09-01' and so on) how can I proceed ?

diseases = ['A', 'B', 'C']
diagnostics = ['2222-09-01', '2111-09-02', '2000-09-02']
cursor.execute("""INSERT INTO _SubjectDisease (subject_id, disease, diagnostic)
VALUES (?, ?, ?) """,
(SUB_ID, diseases, diagnostics)
)```

Oh and by the way this is the error I get

[grim vault]

zip it :)

diseases = ['A', 'B', 'C']
diagnostics = ['2222-09-01', '2111-09-02', '2000-09-02']
sql_stmt = "INSERT INTO _SubjectDisease(subject_id, disease, diagnostic) VALUES (?, ?, ?)"
for disease, diagnostic in zip(diseases, diagnostics):
    cursor.execute(sql_stmt, (SUB_ID, disease, diagnostic))

[dry crag]

Hey doc, one more question to our conversation few days ago. What's the advantage of using SQLAlchemy core over psycopg2 database driver? Aren't these 2 different solutions for exactly same thing?

[fading patrol]

The basic difference is that if you use a ORM like SQL Alchemy, you don't need to work in two languages at once (SQL + Python). It allows you to use pure Python instead. For complex projects that can be much easier to manage

[dry crag]

This I understand. As I learn about DBs, I want to start from basics and implement some SQL queries. In our previous conversation, Doctor said that I can still use SQLAlchemy for that, just without using it's ORM. To my very limited knowledge, that basically what I would achieve using for example driver like psycopg

Do I understand this correctly?

[fading patrol]

Looking back at your earliest question here, I agree that if you're not very familiar with basic SQL, learn that first before you try to work with SQL Alchemy. The best way to do that is to leave Python out of it, just use Postgres directly. You can do that on the command line, in PGadmin, in the IDE of your choice. Just get a basic grasp of how doing CRUD with basic SQL queries works

[maiden dagger]

HI

HRU

[dry crag]

All right, so having some basic understanding of SQL queries, I'll just follow with using a simple database driver like psycopg to query the DB in my app, and can later follow up with ORM when I feel more comfortable

[dry crag]

I'm trying to import psycopg2 into my app's virtualenv. With psycopg2-binary installed on my machine, it imports psycopg2 into my code nicely. But when I install psycopg2-binary in my virtualenv, and uninstall it from main environment, it cannot find the psycopg2 module. Does anyone have explanation to this?

Without psycopg2 installed on my machine, but only in the virtualenv - i have this error:

(FinanceApp_env) zaizu:~/Git/FinanceApp$ /bin/python3.10 /home/zaizu/Git/FinanceApp/FinanceApp/FinanceApp.py
Traceback (most recent call last):
  File "/home/zaizu/Git/FinanceApp/FinanceApp/FinanceApp.py", line 8, in <module>
    import psycopg2
  File "/usr/lib/python3/dist-packages/psycopg2/__init__.py", line 51, in <module>
    from psycopg2._psycopg import (                     # noqa
ModuleNotFoundError: No module named 'psycopg2._psycopg'
(FinanceApp_env) zaizu:~/Git/FinanceApp$ pip list
Package           Version
----------------- -------
binary            1.0.0
mypy              0.931
mypy-extensions   0.4.3
pip               22.0
psycopg2-binary   2.9.3
setuptools        58.1.0
style             1.1.0
tk                0.1.0
tomli             2.0.0
typing_extensions 4.0.1
(FinanceApp_env) zaizu:~/Git/FinanceApp$ 

With psycopg2 installed in my main environment:

(FinanceApp_env) zaizu:~/Git/FinanceApp$ /bin/python3.10 /home/zaizu/Git/FinanceApp/FinanceApp/FinanceApp.py
Traceback (most recent call last):
  File "/home/zaizu/Git/FinanceApp/FinanceApp/FinanceApp.py", line 306, in <module>
    main()
  File "/home/zaizu/Git/FinanceApp/FinanceApp/FinanceApp.py", line 294, in main
    table = TransactionRepo()
  File "/home/zaizu/Git/FinanceApp/FinanceApp/FinanceApp.py", line 97, in __init__
    conn = psycopg2.connect("dbname=financeapp user=zaizu")
  File "/home/zaizu/.local/lib/python3.10/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

[sterile pelican]

How do I create a rule that updates on update but without recursion

CREATE RULE "test" AS ON UPDATE TO users DO ALSO UPDATE users SET data_last_used = CURRENT_TIMESTAMP WHERE id = NEW.id;```
Also if possible make it work on select

[fleet ibex]

https://www.sqlitetutorial.net/sqlite-trigger/ this?

[sterile pelican]

I am using postgres

[fleet ibex]

ohh.. no exp with postgres

[sterile pelican]

:(

[fleet ibex]

postgres also has triggers tho

[sterile pelican]

Yeah but they are complicated

And it will cause recursion anyway

[knotty wharf]

thank you for your help, I really appreciate !

[keen gust]

@proven arrow@keen minnow
does this happen with every1 ?
in my case..
when bot is run without cogs .. no. of connections are 1
but when bot is run including cogs ... no. of connections are > 10
(i used asyncpg.create db pool in main.py file and i have more than 10 cogs)

[keen minnow]

I think you got the wrong person to ping

[keen gust]

no , u both had helped me ... so i tagged right ! 🙂

[keen gust]

.

[keen minnow]

alright maybe.
So what's the question?

[keen gust]

why each cog increases no. of connections to the db ? when i have connected to db in main.py file

[keen minnow]

no idea what's a cog

[oak swan]

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "noman" does not exist

can someone help me regarding this error

[keen minnow]

if by cogs, you mean some kind of credit or db size, it would make sense that the more you pay, the more connections they make available?

[torn sphinx]

After i get this py result = connection.execute(query).fetchall()
How would I get my chosen column data out into a new variable?
As it is now it prints the entire database.

[keen gust]

cog is a py file that is used to divide commands according to their catagories using class commands.cog

btw i have a limit of 20 connections ... whenever i use a command from a cog that executes an sql statement ... no of connections increases by 1

[proven arrow]

Please show what the query looks like.

[proven arrow]

Are the connections being released back to the pool?

[torn sphinx]

query = sqlalchemy.select([customer_info]).where(customer_info.columns.text_id == '12345')```

This prints out the entire thing. I want one value, from a specific column, assigned to a variable as a string.

[proven arrow]

What do you mean the entire thing? I am not familiar with these orms.

[torn sphinx]

The entire database?

[proven arrow]

Lol 😂

[torn sphinx]

https://tenor.com/view/what-seriously-confused-what-the-heck-shocked-gif-15984626

[proven arrow]

How many rows are you expecting?

[torn sphinx]

I only got one row to test with, but I want one row, one column, one specific value

Im so bad explaining

Say I got 3 columns, 1, 2 and 3. And I got 1 row, with one value on each column. I want to know how to make a query that gets that value from column 1, row 1

[keen gust]

yes , when i use a command from a cog that execute an sql query ... connection increases by 1 and after command execution conn decreases by 1 .. i have only 20 conn limit 😦

[proven arrow]

No I understand, I am just not familiar with sql alchemy. Have never used it.

[torn sphinx]

Yay, I found out an easy way. py value = result["column_1"] print(value)

this works for me

[proven arrow]

Yes that’s how it’s supposed to work.

[keen gust]

so if i use all commands within the main.py file ( and not in cog files ) , no. of connection will remain 1 ?

[proven arrow]

No. It depends if you request a connection from the database. Pool connections are just database connections.

If you request one it will go down, until you give it back.

[keen gust]

ok i got it thanks 🙂

[mild merlin]

hello. I have this code, but despite gensleft are 0 it still doesnt print error and it does the code i have in else function. While i want it when gensleft = 0, it gives just an error. ```
mycursor.execute(f"SELECT gen FROM customers WHERE name = %s", (user.name,))
xd = mycursor.fetchone()
gensleft = int(xd[0])
print(gensleft)
if gensleft == "0":
print("error")
else:

plz ping me

[mild merlin]

??

[proven arrow]

Your comparison is integrr vs string

[candid vault]

why does my api-auth/login page return this upon success

[proven arrow]

This wouldn’t be a database issue unless this is a cms type application where you load the url/page data from the database

[candid vault]

well the goal is to get to return the token

[nova cove]

#web-development

[candid vault]

this is database stuff

im using django

[mild merlin]

                xd = mycursor.fetchone()
                gensleft = int(xd[0])
                lolo = int(gensleft)
```like this?

or str?

[proven arrow]

Do you understand what was wrong?

[mild merlin]

not really

[proven arrow]

A number and string are different types. If you compare the both to be equal it will always be false. This is what you are doing currently.

[tight isle]

so I have a sqlite3 database and I have a TEXT object with a value of py b'WnGN4PFOqifRWrot4lKz_6e4CggHD4sooK-Cosa77H4=' but when I return it I have it in quotes, is that because of r.text (requests), or is it because its a TEXT value?

[harsh pulsar]

what do you mean that you have it in quotes? as in, there are literal " characters inside the string?

[soft gorge]

Is ON DUPLICATE KEY a thing for sqlite3?

[grim vault]

No, SQLite uses ON CONFLICT

[tight isle]

When I return the value and print it, it is literally "b'somebytes'"

[grim vault]

So, you used str() on y byte object for the insert.

[gusty mulch]

bad_words = (set(data[28] + words))
TypeError: unsupported operand type(s) for +: 'NoneType' and 'list'```I'm assuming it's saying `NoneType` for ```py
data = await self.bot.get_data(448405740797952010 if ctx.guild.id None else ctx.guild.id)```because the column of `data[28]` is a empty `text[]` type, not sure how to fix it

[grim vault]

In this case data[28] is None and words is a list. You are doing None + ["a", "list"] which doesn't work.

[gusty mulch]

would this fix the error?py bad_words = words if data[28] is None else set(data[28] + words)

[grim vault]

Nearly: set(words)

[gusty mulch]

sopy bad_words = set(words) if data[28] is None else set(data[28] + words)

[grim vault]

bad_words = set(data[28] or [] + words) should also work.

[gusty mulch]

thanks

[tight isle]

How can I "unstring" it now?, or can I just return a non string value from sql

[soft gorge]

Where can I learn ON CONFLICT

[fading patrol]

From the SQLite docs: https://www.sqlite.org/lang_conflict.html

[soft gorge]

How would I do this if I'm working with multiple variables and columns?

I don't see much information on that

[harsh pulsar]

what bernudas said. running str() on a bytes object does not convert it to a string. it produces a stringified representation of the python object, which includes the literal b'' stuff. the proper way to convert between bytes and text (i.e. str) is to use the bytes.decode() method with your desired encoding, eg. (b'WnGN4PFOqifRWrot4lKz_6e4CggHD4sooK-Cosa77H4=').encode('ascii')

remember that in python strings are "strings of unicode codepoints", not "strings of bytes" as they are in some other languages

[tight isle]

Oh right im using str(key) to send it, if I send it as ascii will it work then? Also pretty sure you meant .decode("ascii")?

[harsh pulsar]

yes, i meant decode. good catch

ascii would work, since this looks like base64 or something similar

use an encoding that makes sense for the text data that you have

e.g. if this is utf-8, then use .decode('utf-8')

i am pretty sure that utf-8 is actually the default (so it should work the same as ascii, since it's a superset of ascii, but writing 'ascii' will flag any non-ascii text as an error)

im not sure if the default might vary in different locales

!d bytes.decode

[delicate field]

bytes.decode

bytes.decode(encoding='utf-8', errors='strict')``````py

bytearray.decode(encoding='utf-8', errors='strict')```
Return a string decoded from the given bytes. Default encoding is `'utf-8'`. *errors* may be given to set a different error handling scheme. The default for *errors* is `'strict'`, meaning that encoding errors raise a [`UnicodeError`](https://docs.python.org/3/library/exceptions.html#UnicodeError "UnicodeError"). Other possible values are `'ignore'`, `'replace'` and any other name registered via [`codecs.register_error()`](https://docs.python.org/3/library/codecs.html#codecs.register_error "codecs.register_error"), see section [Error Handlers](https://docs.python.org/3/library/codecs.html#error-handlers). For a list of possible encodings, see section [Standard Encodings](https://docs.python.org/3/library/codecs.html#standard-encodings).

By default, the *errors* argument is not checked for best performances, but only used at the first decoding error. Enable the [Python Development Mode](https://docs.python.org/3/library/devmode.html#devmode), or use a [debug build](https://docs.python.org/3/using/configure.html#debug-build) to check *errors*.

[harsh pulsar]

aha, so the default is just utf-8 everywhere

[tight isle]

Yeah thats base64

Well ill check out if it works in like an hour

Thanks for the help eitherway

[harsh pulsar]

!e ```python
data = b'asdf1234'
print(repr(str(data)))
print(repr(data.decode()))

[delicate field]

@harsh pulsar :white_check_mark: Your eval job has completed with return code 0.

001 | "b'asdf1234'"
002 | 'asdf1234'

[harsh pulsar]

@tight isle ☝️

[tight isle]

its still a bit weird, now I got b'"bytes"'

okay so now I have the string but it isnt bytes

im just missing a b

I think thats either because r.text outputs a string or because the database does something wrong

I had to use

key = r.text
key = key[1:-1]
f = Fernet(key.encode())```

[gloomy rivet]

Can anyone explain how this syntax works?

ON CONFLICT(x) DO UPDATE SET y=excluded.y```
im guessing x is my unique ID columns name but if i want to update the entire row what do I type after DO UPDATE?

[grim vault]

You just specify all columns you want to update in the SET part of the update.

INSERT INTO t1 SELECT * FROM t2 WHERE true
ON CONFLICT(x) DO UPDATE SET col1=excluded.col1,col2=excluded.col2,col3=excluded.col3,...

[gloomy rivet]

so if i got 100 columns i need to type all col1=excluded.col1,...

untill 100?

hmm

[grim vault]

Yes, but if you have 100 columns you may want to rethink your db design.

I'm not sure but you can try: ... DO UPDATE SET t1.* = excluded.* (I don't think it will work, btw).

[gloomy rivet]

actually is the syntax correct? i typed all the .excluded already but im getting an operationalerror: no such table: main.Inserted

[grim vault]

Show code

[abstract pivot]

what does the schema parameter do for asyncpg.Connection.set_type_codec? how is public different from pg_catalog?

[gloomy rivet]

cant show since its in my work computer
basically
'''INSERT INTO t1(column1, column2, column3...) SELECT * from t2 WHERE true ON CONFLICT(ID) DO UPDATE SET column1 =excluded.col1 ....columnx = excluded.columnx'''

is it because im running on python sqlite3?

[brazen charm]

I dont think thats a valid SQLite query

[gloomy rivet]

do u have a valid sqlite query to update t1 from t2?

[grim vault]

!e

import sqlite3

conn = sqlite3.connect(":memory:")
curs = conn.cursor()

curs.execute("""CREATE TABLE test_one
    (
      t1_id integer primary key,
      t1_col1 integer,
      t1_col2 text
    )"""
)
curs.execute("""INSERT INTO test_one VALUES
  (1, 101, 'one_one'),
  (2, 102, 'one_two'),
  (3, 103, 'one_three')"""
)

curs.execute("""CREATE TABLE test_two
(
  t2_id integer primary key,
  t2_col1 integer,
  t2_col2 text
)""")
curs.execute("""INSERT INTO test_two VALUES
  (1, 201, 'two_one'),
  (2, 202, 'two_two')"""
)
conn.commit()

print(curs.execute("select * from test_one").fetchall())
print(curs.execute("select * from test_two").fetchall())

curs.execute("""
    INSERT INTO test_one SELECT * from test_two WHERE true
    ON CONFLICT(t1_id) DO UPDATE SET t1_col1=excluded.t1_col1, t1_col2=excluded.t1_col2
""")
conn.commit()

print(curs.execute("select * from test_one").fetchall())
print(curs.execute("select * from test_two").fetchall())

[delicate field]

@grim vault :white_check_mark: Your eval job has completed with return code 0.

001 | [(1, 101, 'one_one'), (2, 102, 'one_two'), (3, 103, 'one_three')]
002 | [(1, 201, 'two_one'), (2, 202, 'two_two')]
003 | [(1, 201, 'two_one'), (2, 202, 'two_two'), (3, 103, 'one_three')]
004 | [(1, 201, 'two_one'), (2, 202, 'two_two')]

[gloomy rivet]

thanks!

[river juniper]

help!!
i m having prblm in importing mysql.connector in pycharm & it is showing no module is present there what should i do ?

[river juniper]

Can u help?

[river juniper]

@grim vault can u help, pls

[grim vault]

Sorry, I don't use pycharm, so I have no idea. Have you installed the mysql.connector? What's the actual error message you get?

[river juniper]

no module named 'mysql'

is the actual error msg

[grim vault]

Sounds like you havn't installed it.

[river juniper]

how should i?

pls sry but im new to it

[grim vault]

I normally use pip in a command line: python -m pip install -U mysql-connector-python

[river juniper]

ok I'll try to run the same line

[river juniper]

ty for ur time and sry i think i should reinstall my compiler as every module is now showing the same error

[scenic gorge]

hello

i'm try to run my linked list

but actually it dosen't work

[fluid lava]

Is there way to create a sql "view" using pandas, as you can do with regular tables?

[raw saffron]

what functionality would you want from that, which isn't supplied by reading tables?

[harsh pulsar]

what is Fernet? what is r?

[harsh pulsar]

pandas doesn't support tables that query for data on-demand, if that's what you're asking. otherwise, a "view" into data stored in a separate database is pretty much just a skeleton of the column names; in order to access the data, you have to copy the data into the memory of your python application.

it's also possible that you're asking about using pandas to operate directly on data in a rdbms table, essentially compiling pandas operations to sql. i'm not aware of any library that does this, although it might be a nice idea. this is kind of what pyspark and dask do, though. but those are different tools from general pandas + sql

[nova cove]

!pypi cryptography

[delicate field]

cryptography v36.0.1

cryptography is a package which provides cryptographic recipes and primitives to Python developers.

[harsh pulsar]

fair enough. but what does this have to do with the database

[nova cove]

>>> from cryptography.fernet import Fernet
>>> key = Fernet.generate_key()
>>> f = Fernet(key)
>>> token = f.encrypt(b"my deep dark secret")
>>> token
b'...'
>>> f.decrypt(token)
b'my deep dark secret'

[nova cove]

idk

maybe he is trying to encrypt some data and put it into the db

[meager kernel]

can anyone teach me how to use repl databases? I've tried looking for tutorials, but they are kinda confusing

[harsh pulsar]

ah, makes sense

that seems reasonable given how the data looks

[nova cove]

mhmm

[harsh pulsar]

it sounds like multiple layers of incorrect str() usage

like they are calling str() erroneously before encrypting

[nova cove]

i would hash instead of encrypting tbh

especially if its some user auth system where sensitive info is being put into the database

[harsh pulsar]

don't some encryption schemes also hash the data along the way? i think e.g. bcrypt does that

[nova cove]

maybe but encryption is 2-way so the data can still be decrypted

idk how encryption+hashing works together doh

[harsh pulsar]

oh, yeah. for passwords i know you're supposed to hash (with a slow hashing function to slow down brute force attacks) and compare the hashes

i think you're supposed to also encrypt too, but using specific kinds of encryption, the details of which are beyond me

[nova cove]

mhmm

[tight isle]

Fernet (cryptography.Fernet) is what I use for encrypting files, r is a variable - response from a POST request (requests lib)

[gloomy rivet]

anyone knows how to add trigger so when i upsert it updates when insert is done created_on and last_updated but when update is done it updates last_updated for sqlite3

[meager kernel]

@client.event
async def on_message_delete(message):
    logChanID = db(f"logChan-{message.guild.id}")
        
    if logChanID == "None":
      return

    logChan = client.get_channel(logChanID)
        
    msgCHAN = message.channel.mention
    embed = discord.Embed(title=f"Deleted Message",description=f"{message.content}", color=0xFF0000)
    embed.add_field(name=f"Channel", value=f"{msgCHAN}")
    embed.set_author(name=message.author)
    embed.set_footer(text="View audit log for more information.")
    await logChan.send(embed=embed)

that is my code

Ignoring exception in on_message_delete
Traceback (most recent call last):
  File "/home/runner/Database-testing/venv/lib/python3.8/site-packages/discord/client.py", line 351, in _run_event
    await coro(*args, **kwargs)
  File "main.py", line 57, in on_message_delete
    logChanID = db(f"logChan-{message.guild.id}")
TypeError: 'Database' object is not callable

thats my error message
im using replit db

[fluid lava]

It would need to be persistent in an RDS and updated as the other tables are updated. The source tables tables are not set up to be joined by fk/pk. Maybe there's a trade-off I'm not considering...what are your thoughts?

[fluid lava]

Thanks for thorough reply! It would need to stay in the db as the source tables(rds). Pandas actually has a very efficient way of interfacing the database including a layer over sqlalchemy.

[harsh pulsar]

i know that pandas can query tables with sqlalchemy, but my understanding was just that it would pull the data right into a dataframe

no "lazy" computation for example

unless there's a feature for it that i'm not aware of?

if you use pandas.read_sql then it pulls the data entirely into memory, at least that's how it worked whenever i've used it

[paper flower]

Use update statement:

update users set username = 'Doctor' where id = 42;

[golden vector]

I'm new to postgres and I'm seeing the standard 'postgres' databse, but I can't drop it.
Is it neccessary for anything?; are there any (dis)advantages on using it?; is it not droppable so the server doesn't crash, if there aren't any other dbs?

[grim vault]

No need for triggers. just define a default value of CURRENT_TIMESTAMP for the columns in the create table and omit them in the insert. In the upsert command just add last_updated=CURRENT_TIMESTAMP to the update set.

[paper flower]

You can, but you're probably connected to it 😉

So it can't be dropped

[golden vector]

Nope, I can't even disconnect

[golden vector]

Also I can drop dbs while connected to them

[late tinsel]

What library is recomended to build microsoft sql server queries. I need to be able to dynamically build the query where clause. So something where I can generate something like this dynamically:
where <ColumnName1> like "<Value1>" and <ColumnName2> in (<Value2-1>, <Value2-2>)
There could be any number of columns being added.

[jaunty linden]

i have 13 million records.. queries take a long time to return .. even with indexing... anyone ever heard of index of an index? does anyone make index of an index for faster queries?

[grim vault]

How does the table looks like? Which index are defined for the table? What is the select? Did you look at the explain plan of the query?

[silk elk]

how to connect sqlite browser to python

i need to make the database store the channelid's

@client.slash_command(description="Set up a mod log channel.")
async def SetModLog(ctx, channelid, member: disnake.Member):

the channelid will be set to the database

[willow rain]

db = sqlite3.connect('mainbank.db')
cursor = db.cursor()

cursor.execute("INSERT OR IGNORE INTO table_name VALUES ( ? )", (channelid))
db.commit()

[grim vault]

(channelid,) the second parameter must be an iterable like tuple or list.

[naive sandal]

And for a discord bot you should use an async wrapper like aiosqlite

Or prisma

[hallow holly]

Hello python peeps

Can y'all suggest some data structures book to start with

I wanna master data structures and algorithms

[sterile pelican]

How do I get rid of this? The library is literally asynchronous, I don't get why it can't complete multiple operations at time

asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress```

Or is it just a warning

If so, how to suppress it

[naive sandal]

are you using a cursor?

[sterile pelican]

Is there even such a thing in asyncpg

[naive sandal]

If so, asyncpg requires you to use it in a transaction

[sterile pelican]

I couldn't find it

@naive sandal found something in docs, is this the right way?

async with connection.transaction():
    await connection.execute('SELECT smth FROM some_table')
    res = await connection.fetchall()
return res```

Nvm it is still giving me that thing

I made this function but I don't know how to make it so it doesn't raise that error

async def execute(self, *args, fetch_mode: FetchMode = FetchMode.NONE, **kwargs):
        async with self._con.transaction():
            if fetch_mode == FetchMode.NONE:
                await self._con.execute(*args, **kwargs)

            elif fetch_mode == FetchMode.ALL:
                return await self._con.fetch(*args, **kwargs)

            elif fetch_mode == FetchMode.VAL:
                return await self._con.fetchval(*args, **kwargs)

            elif fetch_mode == FetchMode.ROW:
                return await self._con.fetchrow(*args, **kwargs)```

[naive sandal]

connections have state so you probably shouldn't be doing this

a connection pool would probably fix it

[sterile pelican]

So uh how do pools work

[naive sandal]

https://magicstack.github.io/asyncpg/current/usage.html#connection-pools

[sterile pelican]

I create connections in pools?

And then use the connection?

[naive sandal]

Create a pool, then acquire a connection when you want to query the db

[sterile pelican]

Okay thanks

This won't slow it down right?

[naive sandal]

don't think so

[coarse patrol]

I have a MSSQL database with a table that contains ~500,000 records. One of the columns contains delivery_state, and I need to query the database to select the rows that contain a certain value, run a Python based API query to check for a change, and then update each specific row with the updated state.

Is there a logic way to use two SQL queries to select the specific rows with a given state, and then update them in the database? I'm not sure how the index is tracked with sub samples of the database.

[cerulean ledge]

"SELECT count(*) FROM suggestions WHERE suggestion_id = {SuggestionID}", why does this just return "There is not a column such as xy"?

in this case "xy" is SuggestionID, altough, the column which xy is searched for is suggestion_id, isn't it?

[torn sphinx]

Can someone help me, I'm triyng to insert a new usbr with their password into my database but it is saying that my database is locked

Code:

def AddUser():
    user_addUsername = input("Enter the user's username: ")
    user_addPassword = input("Enter the user's password: ")
    statement = f"""INSERT INTO users
                (username, password, id)
                VALUES
                ('1','1','1')"""
    con = sql.connect("users.db", timeout=10)
    cur = con.cursor()
    cur.execute(statement)
    con.close()
    cur.close()```

Error Code

  File "C:\Users\calcu\OneDrive\Desktop\Spammy\MainV2.py", line 53, in AddUser
    cur.execute(statement)
  File "C:\Users\calcu\OneDrive\Desktop\Spammy\MainV2.py", line 39, in MainPage
    AddUser()
  File "C:\Users\calcu\OneDrive\Desktop\Spammy\MainV2.py", line 25, in Login
    MainPage()
  File "C:\Users\calcu\OneDrive\Desktop\Spammy\MainV2.py", line 59, in <module>
    Login()```

[nova cove]

what db are you using? sqlite, postgres, mysql?

[torn sphinx]

sqlite3

[nova cove]

ok so

you might have two cursors writing at the same time somewhere

or

you don't commit when you make changes to the database

[torn sphinx]

how do i commit to the database?

[nova cove]

and first off, put the statement in the execute

cur.execute("INSERT INTO table_name (..., ..., ...) VALUES (?, ?, ?)", ())

and your code doesn't follow pep8

[torn sphinx]

what's pep8

[nova cove]

!pep8

[delicate field]

PEP 8 is the official style guide for Python. It includes comprehensive guidelines for code formatting, variable naming, and making your code easy to read. Professional Python developers are usually required to follow the guidelines, and will often use code-linters like flake8 to verify that the code they're writing complies with the style guide.

More information:
• PEP 8 document
• Our PEP 8 song! :notes:

[torn sphinx]

what am i doing wrong?

[nova cove]

1st: don't use f strings for the executes
2. use placeholders in the VALUES () like: VALUES (?, ?)
3. , (placeholder1, placeholder2, ...)

[torn sphinx]

how would I be able to take an input from the user and insert it into the query then>

[nova cove]

and also

[torn sphinx]

?*

[nova cove]

can you shjow me the rest of the code, specifically where you import sqlite3

as per the docs

[torn sphinx]

import smtplib as smtp, ssl, json, sqlite3 as sql


username = input("Enter your username: ")
password = input("Enter your password: ")

def Login():
    loggedIn = False
    while loggedIn == False:
        con = sql.connect("users.db", timeout=10)
        cur = con.cursor()
        statement = f"SELECT username from users WHERE username = '{username}' AND Password = '{password}';"
        cur.execute(statement)
        if not cur.fetchone():
            print("Error username not found in DB.")
            return
        else:
           print(f"Sucessfully logged in! Welcome {username}!")
           loggedIn = True
           MainPage()
           con.close()
           cur.close()
           

def MainPage():
    id = f"SELECT id from users"
    con = sql.connect("users.db", timeout=10)
    cur = con.cursor()
    cur.execute(id)
    print(cur.fetchone)
    print(f"Please select an option! {username}\n1) Email Bomber\n2) Admin Panel")
    option = int(input("Select Option: "))
    if option == 2:
        AddUser()
        con.close()
        cur.close()


def AddUser():
    user_addUsername = input("Enter the user's username: ")
    user_addPassword = input("Enter the user's password: ")
    con = sql.connect("users.db", timeout=10)
    cur = con.cursor()
    cur.execute(f"INSERT INTO users (username, password) VALUES ('{user_addUsername}', '{user_addPassword}')", ())
    con.close()
    cur.close()



Login()
MainPage()
AddUser()

[nova cove]

don't even got sqlite3 imported

[torn sphinx]

i do

[nova cove]

oh you are doing it all in one line

uhh i wouldn't do that unless they are related

as per the docs,

import sqlite3
con = sqlite3.connect('...')
cur = con.cursor()
cur.execute('''...''')
con.commit()
con.close()

you don't need to close the cursor

i would refactor code and make sure to follow pep8

[torn sphinx]

you know how you said not to use f strings, how else would I be able to implement the users' input into the query

[nova cove]

after the sql statement

thats why i put , ()

inside those () is where you put what the placeholders will be

ex:

db.execute("INSERT INTO table_name WHERE id = ?", (user.id))

the user.id corresponds to the first placeholder id = ?

[torn sphinx]

like this then?

[nova cove]

cur.execute('''INSERT INTO users (username, password) VALUES (?, ?)''', (..., ...))

i would also suggest you learn SQL before doing database related things

[torn sphinx]

like that?

[clever steppe]

How is this way of changing/replacing values in a string called? There's f-strings and format(), does that method has a name?

[nova cove]

this is just for SQL queries with python, not python in general

[clever steppe]

Ahhh, got it, I'll port it to make it cleaner, I was using format(), https://github.com/santigo-zero/Zettelpy/blob/4220d07778fbac50d0052a6a479c49c70b167bdf/zettelpy/slip_box.py#L43

[nova cove]

mhmm all good

VALUES ('{}', ...) would be VALUES(?, ?, ?)", (..., ..., ...)) @clever steppe

[torn sphinx]

my database is still aparently locekd

[nova cove]

mhmm prolly because multiple connections might be open at once

[torn sphinx]

I've closed all connections before hand

[clever steppe]

Here you are using sql.connect when the correct syntax is sqlite3.connect

[nova cove]

he did sqlite3 as sql

in the import

[torn sphinx]

I have imported sqlite3 as sql

[nova cove]

maybe try only running one function?

[clever steppe]

Ahhhh, got it, can you SELECT from the DB at least?

[torn sphinx]

I can in my previous querys

Its just not letting me write

import smtplib as smtp
import ssl
import json
import sqlite3 as sql

username = input("Enter your username: ")
password = input("Enter your password: ")

def Login():
    loggedIn = False
    while loggedIn == False:
        con = sql.connect("users.db", timeout=10)
        cur = con.cursor()
        statement = f"SELECT username from users WHERE username = '{username}' AND Password = '{password}';"
        cur.execute(statement)
        if not cur.fetchone():
            print("Error username not found in DB.")
            return
        else:
           print(f"Sucessfully logged in! Welcome {username}!")
           loggedIn = True
           MainPage()
           con.close()
           

def MainPage():
    id = f"SELECT id from users"
    con = sql.connect("users.db", timeout=10)
    cur = con.cursor()
    cur.execute(id)
    print(cur.fetchone)
    print(f"Please select an option! {username}\n1) Email Bomber\n2) Admin Panel")
    con.close()
    option = int(input("Select Option: "))
    if option == 2:
        AddUser()


def AddUser():
    user_addUsername = input("Enter the user's username: ")
    user_addPassword = input("Enter the user's password: ")
    con = sql.connect("users.db", timeout=10)
    cur = con.cursor()
    cur.execute(f'''INSERT INTO users (username, password) VALUES ('{user_addUsername}', '{user_addPassword}');''')
    con.commit()
    con.close()



Login()
MainPage()
AddUser()

[nova cove]

maybe connect to the database once so there is one connection

procedurally outside all the functions

[clever steppe]

Also he's using a while, instead of an if

[nova cove]

idk if that matters

[nova cove]

instead of closing at the end of a query, do commit

[torn sphinx]

still saying its locked

[nova cove]

well as i said, there might be another process writing to the database

since the regular sqlite3 isn't asyncio, processes can't wait on other processes before they finish'

[torn sphinx]

FGS I FEEL STUPID NOW HAHA it was open on my pc

sorry guys

thanks for the help

[nova cove]

np

[torn sphinx]

yo how do i connect pymongo with mongodb atlas

ive been trying out different stuff for literally hours now

i want to connect it with a db on mongodb atlas, not one stored on my computer using localhost

[soft gorge]

If I'm using ON CONFLICT ABORT with sqlite. Is there a way for me to return False if the Conflict occurred and return True if the conflict didn't occur?

[silk elk]

hey

@client.slash_command(description="Set up a mod log channel.")
async def SetModLog(ctx, channelid):
  db = sqlite3.connect('channels.db')
  cursor = db.cursor()

  cursor.execute("INSERT OR IGNORE INTO channels VALUES (?, ?)", (channelid, ctx.guild.id))
  db.commit()

why is this not working

[soft gorge]

Can someone show me an example of ON CONFLICT ABORT? here is my code and it's saying error near ABORT

     c.execute("INSERT INTO General (ID, Value) VALUES (:a, :b) ON CONFLICT(ID) ABORT", {"a": mID, "b": None})

[wild pelican]

What's the best way to open a SQLite database at the start of everything and close the database once the script is stopping?

[sinful rivet]

ok, so how to select spesific row in database

[haughty crag]

I have a datagrip/database question. I believe I'm connected to a database hosted on Amazon. How do I know my database is up-to-date? Whenever I do a query, am I querying an up-to-date database in the cloud? Do I need to refresh?

For example, when I use Prisma Studio, and my schema.prisma file is out of date, I can't read any data in tables. So I have to update my schema.prisma file.
How do I do the same thing for Datagrip?

[torn sphinx]

"SELECT column FROM table;"

?

[haughty crag]

Thank you!

[torn sphinx]

Can't seem to find a error in the syntax?

how to see logs in heroku cli

[grim vault]

Is there a comma , before the closing ) at the last line (primary key) of that create statement?

[torn sphinx]

yes

[grim vault]

Well, remove it.

[crimson schooner]

may I ask about sql

[torn sphinx]

sure

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @crimson schooner until <t:1644082149:f> (9 minutes and 59 seconds) (reason: attachments rule: sent 9 attachments in 10s).

[paper bluff]

!unmute 872842852412039229

[delicate field]

:x: There's no active mute infraction for user @crimson schooner.

[clever steppe]

Use a context manager: with

[wild pelican]

Can I get an example/documentation on that, please?

[sacred patio]

of what

[wild pelican]

The context manager “with”. I’ve never used it before 😅

[nova cove]

with open(…)

!pypi aiosqlite

[delicate field]

aiosqlite v0.17.0

asyncio bridge to the standard sqlite3 module

[nova cove]

if you use aiosqlite at the start of the script you can do:
async with aiosqlite.connect(‘…’) as db: …

[sly pivot]

sql and python
i am trying to remove certain row where specific data exist that data is user input
sqlliet3

    email=str(input("Enter your email assosiated with with email subusbsription:"))
    conect=sqlite3.connect("data.db")
    c=conect.cursor()
    c.execute("DELETE FROM customer WHERE emails=email VALUES emails=(")
    conect.commit()
    conect.close()```

[fading patrol]

That looks fine except for c.execute("DELETE FROM customer WHERE emails=email VALUES emails=(") ... I'm not sure but maybe what you want is c.execute("DELETE FROM customer WHERE emails=?, (email))

[sly pivot]

@fading patrol I got that part

I had another question

How would u search email and print out true extra

[fading patrol]

Not sure what you're asking, especially about "print out true extra" but SELECT email FROM customer would return your whole email column

[blissful basalt]

new to sql, im watching a tutorial on youtube but when he writes "name" it doesnt get highlighted like mine. What does this mean and does it matter?

[nova cove]

it shouldn’t matter

[blissful basalt]

but it seems to be a keyword, doesn't that mess things up?

[nova cove]

no it should be fine

its not a keyword from my knowledge

its happened to me before

and it didn't break the program

[blissful basalt]

ah ok, how do i delete a row?

[nova cove]

DELETE FROM table WHERE ...

[blissful basalt]

aha ty

[strange fox]

still in case use [name]

[late tinsel]

If I am using pypika to build a query, how can I validate strings for sql injection. My code dynamically builds the query where clause in the sense that I have a json column and will be using inputs provided in a request json body to add where clause filters for that column. For example I want to get data in the json column where category = "headwear" and cost > 3. The json passes the column name and the value to filter with, however using parameterization you can't set the column name, only the value.

[torn sphinx]

i get this error sqlite3.OperationalError: database is locked

[nova cove]

are there multiple connections open or multiple operations occurring on the database at the same time (shouldn't be an issue if you use the async bridge)

[blissful basalt]

Whats the difference between a primary key and using UNIQUE? To me it seems like neither can have 2 values that are the same

[storm mauve]

depends on which database you are using

most of the time primary key is essentially a shortcut for unique + some other attributes

[nova cove]

and there can only be one primary key, unlike there can be multiple unique keys in a table

and unique keys can be null

[torn sphinx]

there are no errors but simply it doesn't work

[slender atlas]

Remove the .id in the fourth line

This should have been asked in #discord-bots by the way

[torn sphinx]

okey

[robust current]

it's showing error that 'int' object has no attribute 'cursor' .. what am i doing wrong?

[slender atlas]

How are you calling the function?

The first argument you are giving it is supposed to be a database connection object, but it seems you are passing an int object instead

[stable drift]

hey
im working with python and mysql
i need to use a certain column value from my parent table in sql and select corresponding record from the table having the foreign key
what is the syntax for that?
need to do it in python btw

[trim lintel]

This is called a Join.

Join tables together

[ivory moat]

im making a menu driven program for shop management using python-mysql i have created a function for new account creation but i want the program to show data of existing user like login thing or something how to make that ?

[dry crag]

Guys, what's the best practise in writing SQL queries - using " marks to delimit identifiers while always keeping them lowercase, or keeping them lowercase without using " marks? " marks should make the code slightly more compatible in case someone else uses Uppercase letters in identifier.

so, e.g.

SELECT "id" FROM "poop"

or

SELECT id FROM poop

instead of

SELECT "iD" FROM "pOOp"

[robust current]

Mhm thank u so much

[kindred spear]

It depends on the structure of your database tables. For instance, if your column names contain spaces you'd need to use quotation marks. If you have the choice, I'd go for the second example (without double quote marks), as personally I think it is more readable...

[prime kelp]

how tf are you supposed to pass list-like objects into sqlite fields

[nova cove]

I Do the second

[grim vault]

You must be careful because not all databases have the same case rules. So it could be that id != "id" (SQL standard is: id == "ID", iD == Id, "iD" != "Id", so only unquoted identifiers are case-insensitive).

And as always different databases different rules.

[toxic berry]

I am looking for best Java database book

[frozen coyote]

https://www.oreilly.com/library/view/java-database-best/0596005229/

[dry crag]

All right, thanks people. One more question - I'm employing psycopg2 to query the DB. I encountered a strange behaviour and I wonder if anyone can explain it to me. When I use sql.Identifier wrapper on my manual column selection in the query, my quote marks in VALUES are being escaped by backslashes, but when i use sql.SQL wrapper, they are ok. Can anyone explain this behavior?

E.g.

table_name = sql.SQL('testo')
listOfColumns = tuple(Zdzisiek.mappedDict.values())
mapped1 = sql.SQL(', ').join(map(sql.Identifier, listOfColumns))

query = cur.mogrify(
    sql.SQL("INSERT INTO {}({}) VALUES (%s, %s, %s, %s);").format(table_name, mapped1), (Zdzisiek.name,             Zdzisiek.surname, Zdzisiek.email, Zdzisiek.gender))
print(query) # b'INSERT INTO testo("first_name", "last_name", "email", "gender") VALUES (\'Zdzislaw\', \'a\', \'[e-mail]\', \'Male\');'

mapped2 = sql.SQL(', ').join(map(sql.SQL, listOfColumns))

query = cur.mogrify(
    sql.SQL("INSERT INTO {}({}) VALUES (%s, %s, %s, %s);").format(table_name, mapped2), (Zdzisiek.name,             Zdzisiek.surname, Zdzisiek.email, Zdzisiek.gender))
print(query) # b"INSERT INTO testo(first_name, last_name, email, gender) VALUES ('Zdzislaw', 'a', '[e-mail]', 'Male');"

I don't understand how my VALUES are affected by wrapper concerning testo(first_name, last_name, email, gender) in the first place.

[toxic berry]

Thanks

[grim vault]

It seems like sql.SQL() is returning a byte string, so the print is using b'...' or b"...". If there is a ' inside the string it used b" ... 'name' ..." and vice versa. If there is a ' and a " inside the string it just prints one as escaped, because it's part of the string.

!e

quote_single = b"--''--"
quote_double = b'--"--'
quote_both = quote_single + b" and " + quote_double

print(quote_single)
print(quote_double)
print(quote_both)

[delicate field]

@grim vault :white_check_mark: Your eval job has completed with return code 0.

001 | b"--''--"
002 | b'--"--'
003 | b'--\'\'-- and --"--'

[grim vault]

and this table_name = sql.SQL('testo') should be table_name = sql.Identifier('testo')

You can try: print(query.as_string(cur))

[dry crag]

I'm trying to wrap my head around what youve written to me. I understand the reason why one type of quote marks is backslashed now, but.

[dry crag]

It seems like sql.SQL() is (...) <- don't you mean cur.mogrify()?

I dont get how I should approach building a query now, if it's only a problem with mogrify() method or it won't work for execute as well

[grim vault]

Ah, yes, didn't see that. Never heard of mogrify, I concentrated on the strings.

Just use sql.Identifier() for table and column names and you should be fine?

[dry crag]

I can do so, but second part of query -> '

(...) VALUES (%s, %s, %s, %s);").format(table_name, mapped1), (Zdzisiek.name, Zdzisiek.surname, Zdzisiek.email, Zdzisiek.gender))

is using ' quote mark only

And sql.Identifier uses " quote marks

e.g.

[dry crag]

        table_name = sql.Identifier('testo')
        listOfColumns = tuple(Zdzisiek.mappedDict.values())
        mapped1 = sql.SQL(', ').join(map(sql.Identifier, listOfColumns))

        query = cur.mogrify(
            sql.SQL("INSERT INTO {}({}) VALUES (%s, %s, %s, %s);").format(table_name, mapped1),                       (Zdzisiek.name, Zdzisiek.surname, Zdzisiek.email, Zdzisiek.gender))
        print(query) # b'INSERT INTO "testo"("first_name", "last_name", "email", "gender") VALUES (\'Zdzislaw\', \'a\', \'[e-mail]\', \'Male\');

[grim vault]

Yes, that's ok. " (double quotes) are for sql identifiers and ' (single quotes) are for string literals. That's SQL standard.

[dry crag]

Ok, I think i got it. It gets executed properly, but messes up mogrify due to the behaviour of byte code

Thanks a lot for help!

[grim vault]

Have you tried: print(query.as_string(cur))?

[torn sphinx]

does anyone know how to sort and find the top 10 mongodb objects by a field?

I am trying to sort this by trophies

[dry crag]

Yes, but only for mapped1, I'm not able to use it on bytestring from mogrify()

print(mapped1.as_string(cur)) # "first_name", "last_name", "email", "gender"

[grim vault]

Maybe print(query.decode()) could work.

[dry crag]

That did work! INSERT INTO "testo"("first_name", "last_name", "email", "gender") VALUES ('ddsas', 'a', '[e-mail]', 'Male');I

[torn sphinx]

    rankings = mayy_bot_users.find().sort('trophies',-1).limit(5) 

    index = 0
  
    for f in rankings:

it says motor object isn't iterable for

for f in rankings:

[grim vault]

Don't you need a .to_list()?

    cursor = mayy_bot_users.find().sort('trophies',-1).limit(5) 
    rankings = await cursor.to_list(None)

    index = 0
  
    for f in rankings:

[torn sphinx]

I fixed it, added async to the for loop

thx thi

[cobalt rapids]

Hi, i have a problem filling my PostgreSQL database.
General information for my problem. PG admin is running in a Docker Container, every time i try to upload a file it says not allowed and it also says /home/pgadmin/ does not exist. I would be very thankful if somebody knows how to resolve that issue.

[swift crater]

Hello, I have a Python program with a Sqlite database that I'd like to be able to do the following with:

(1) create sequential integer numbering sequence based off ascending order of an existing column for all items in a table column

(2) subtract all numbers above ascending order numbering for all items in a table integer column

(3) check sequential sequence of ascended ordered list (1, 2, 3, 4...) for an integer column in a table

Thanks

essentially would like to be able to delete an item from a table and have the sequence number to shift down from the deleted item. Additionally, I'd like to check to see if the sequence number is off/incorrect and if so, re-create the sequence based as closely to the existing incorrect but still more correct than just re-numbered sequence

[ancient pier]

hey i want help with openc v and media pipe stuff

[silent violet]

How many PRIMARY KEY constraint columns can be present in a single table?

I was reading through microsoft sql docs and found this line A table can contain only one primary key constraint.

But just above it , it had If a primary key constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the primary key constraint definition must be unique.

https://docs.microsoft.com/en-us/sql/relational-databases/tables/primary-and-foreign-key-constraints?view=sql-server-ver15#PKeys

[keen minnow]

the primary key is the thing that uniquely identify a row.
So the DB only needs one definition.

If you want to introduce some uniqueness constraint, then there is the constraint UNIQUE

[storm mauve]

You can define a "composite" primary key, but that is different from defining primary key twice

this covers it fairly well: https://stackoverflow.com/a/1110364

[silent violet]

oh

Thanks @keen minnow @storm mauve

[sinful rivet]

[16:07:30] Could not commit table structure. Error message: NOT NULL constraint failed: item_list.SEED

PRAGMA foreign_keys = 0;

CREATE TABLE sqlitestudio_temp_table AS SELECT *
                                          FROM item_list;

DROP TABLE item_list;

CREATE TABLE item_list (
    NAME   TEXT NOT NULL,
    CARROT INT  NOT NULL,
    RICE   INT  NOT NULL,
    FISH   INT  NOT NULL,
    MEAL   INT  NOT NULL,
    LOG    INT  NOT NULL,
    SEED   INT  NOT NULL
);

INSERT INTO item_list (
                          NAME,
                          CARROT,
                          RICE,
                          FISH,
                          MEAL,
                          LOG
                      )
                      SELECT NAME,
                             CARROT,
                             RICE,
                             FISH,
                             MEAL,
                             LOG
                        FROM sqlitestudio_temp_table;

DROP TABLE sqlitestudio_temp_table;

PRAGMA foreign_keys = 1;

and i use SQLITE studio

[grim vault]

... SEED INT NOT NULL ... but you don't include the SEED inside the insert which then will default to NULL which is not allowed.

[sinful rivet]

ah i seeeeee

ok, ty (how to edit code SQlite studio-)

[nova cove]

one

[torn sphinx]

for same id, I have to update name wherever there is null
can someone help to write query to get O/P?

[nova cove]

UPDATE table_name SET name = ? WHERE id = 2 AND name = NULL ?

[torn sphinx]

this is just an small example, my need is to update name where it null with the value present in same id

for each unique id there will be only 1 row having not null name and other have null name

[grim vault]

UPDATE test
   SET name = (SELECT DISTINCT B.name
                 FROM test AS B
                WHERE B.id = test.id
                  AND B.name IS NOT NULL)
 WHERE name IS NULL;

[torn sphinx]

thank you so much, this works for me

[untold thorn]

Hi,
is there any tool that will translate the syntax from teradata to hive?

What does this part mean

``` - teradata syntax
Is there any replacment for hive query?

[fallen vault]

Would it be okay to use information within the database as a salt. Like if I hash first name, password, last name.

[robust current]

ummm what does it mean?

[barren minnow]

is there much of a difference in syntax between MySQL and other implementations?

If anyone knows 🙂

[fallen vault]

Not from what I’ve seen. At least with the basic stuff. Getting into schema and pragma. Or anything about SET, Select, update etc. join and higher things I’m not sure about.

[barren minnow]

cool, thanks 🙂

[brave bridge]

Can you show your code?

[robust current]

thanks you so much but figured it out .. it was referring to closed connection so yea..thanks

[gleaming jacinth]

how do I querry all the drums

mongo db drums are an optional field

[clever steppe]

Is there a way of resetting the ID of all my rows? The problem is that the id is auto-incremental and my primary key, so if I delete a row and keep inserting new rows into my table the id keeps incrementing, so is there a way of telling SQLite to tidy up the ID column? Instead of having: 1, 2, 5, 6, 9 I'll have 1, 2, 3, 4, 5

I think I could instead query the database to check the id of the last row and increment it with python and instead of using INTEGER PRIMARY KEY NOT NULL I would use INTEGER PRIMARY KEY NULL, but that might not be the cleanest way.

[fading patrol]

Is there a reason you want to do this? Having a discontinuous index like that shouldn't be a problem and fiddling with it probably isn't a good idea

[clever steppe]

It's just bothering me to have jumps in numbers in that column and that they are not in perfect order, that is the only reason.

[fading patrol]

Better to just get used it ;) Here's a little more detail: https://stackoverflow.com/questions/13146441/how-to-restore-a-continuous-sequence-of-ids-as-primary-keys-in-a-sql-database

[clever steppe]

Okay, it makes sense and I was already doing something like this with two columns, thanks for the link!

[unreal radish]

Any recommendations to practice SQL for interviews at FAANG? Specifically tricky questions

[harsh pulsar]

i think hackerrank has sql questions

i did one for an interview once

https://www.hackerrank.com/domains/sql?filters[skills][]=SQL (Advanced) hmm only 4 of them in the "advanced" category

[earnest cypress]

I am creating some tool for transcript and I am using SQLite, but I am transcribing Shadow of War audio files and there is a lot of these (900 usable files, but second archive has over 20k over these)

Save for optimising queries by designing API to handle limits, would switching from SQLite to MySQL give some performance increase?

Also I thought about MongoDB

Basically, I am going to store info about:
name of file,
category (character),
audio data like length, channels, frequency
transcript,
check for further audio edit

[nova cove]

SQLite is faster at first glance but it doesn’t have concurrency. As the db grows and as there are more simultaneous queries, there will be performance loss with SQLite.

But they are kind of hard to compare as SQLite is running on your machine while MySQL is running on a server. If you don’t have tons of data SQLite should work but use MySQL if you have large tables and tons of data

It really depends on the use case when you decide whether to use MySQL or SQLite

[earnest cypress]

On local machine they are running

[nova cove]

?

[earnest cypress]

I mean, that I run them on my machine, not cloud hosting if that's what you meant

[nova cove]

Ye ik I was talking about the machine

but MySQL is running on a server not ok your local machine

[earnest cypress]

Aah, right 😄

Hmm... Given that, for larger datasets like from Shadow of War it's better to use MySQL, but for smaller - SQLite

[nova cove]

Ye pretty much

[earnest cypress]

Good thing I use SQLAlchemy 😄

It makes stuff easier for migrating, right?

[nova cove]

It’s just an ORM

It just maps classes to the db

[earnest cypress]

Still trying to get hang with it 😄

As I've recently started using it

[nova cove]

all good

[earnest cypress]

Thanks for letting me know @nova cove ^^

[nova cove]

np

[swift crater]

Anyone able to help me out with the following in SQlite?

(1) Create a sequential integer numbering sequence based off ascending order of an existing column for all items in a table column (basically assigning each row with a 1, 2, 3, 4...)

(2) Subtract all numbers above ascending order numbering for all items in a table integer column (so I can first delete an entry, and then move the position int value for every row after down one)

(3) Check sequential sequence of ascended ordered list (1, 2, 3, 4...) for an integer column in a table (to see if there's any duplicate or out of order numbers)

[harsh pulsar]

this looks like a homework assignment, quiz, or interview take-home task

[swift crater]

it is not

making a soundboard

with many attributes

I did the third one with a for loop, but would be nice to do it in SQL if it's possible via the entry methods

I could do it all with for loops but, if there's a better way via SQL, that's the preferred method

[harsh pulsar]

fair enough

sqlite has auto-incrementing integers, if that helps with (1)

[swift crater]

that is only for new entries though, I think?

[harsh pulsar]

ah, yes

[swift crater]

was looking for a way to map a sequence to an existing column

[harsh pulsar]

generally sql doesn't have good support for operations between rows

[swift crater]

if for loops is the way, then alright

[harsh pulsar]

let me do some searching, but yeah you might just need a loop

[swift crater]

this is how I'm currently checking the order:

positionOrder = True
count = 1
for item in position:
    if item != count:
        positionOrder = False
        break
    count += 1
        
if positionOrder == False:
    print("Error in position order")

but that isn't so bad since I've already pulled the list out with a single SQL statement

rather than running several SQL commands

[soft edge]

hey, I want to convert couple of df as df in a dict with new keys. i have been trying for a long time now

ik to_dict can be used but i cant keep the df as df with this

[harsh pulsar]

you should maybe do ORDER BY in your sql to make (3) easier

then you just have to check sequential entries

[swift crater]

oh, that's what I did for the 3rd one

just was hoping to have a way to use a SQL statement to apply to multiple rows at the same time, at least for the first 2

if it's not possible, I can go the less elegant way lol

I mean, I'm sure there's a way to select rows above a certain number and do a -1

or I hope there is

ah, I guess I'll just do loops lol

[grim vault]

You can use a window function for the first, like:

select *, row_number() over (order by colname) from tabname;

see https://www.sqlite.org/windowfunctions.html

[torn sphinx]

can someone help me with my code?

import os

with open("prices.json") as f:
  data = json.load(f)

new_price = 20
prices = data["prices"]
prices["Apple"] = f"${prices['Apple'] - new_price}"

with open("prices.json", "w") as f:
  json.dump(data, f, indent=4)

  print(data)

i am trying to subtract the new price from the price of apples, making the price = to 20 since right now it is 40

prices["Apple"] = f"${prices['Apple'] - new_price}"
TypeError: unsupported operand type(s) for -: 'str' and 'int'

[grim vault]

Looks like prices["Apple"] is stored as a string. You'll need to convert it first to a number (integer or float) if you want to make arithmetic calculations with it.

And according to your code you are adding a $ sign in front of the value?

[torn sphinx]

int(prices["Apple"]) == f"${prices['Apple'] - new_price}" like this?

[grim vault]

No, the int() must be on the left side.

but if the entry is $40 it won't work,

[torn sphinx]

so what im trying to do isn't gonna work?

[grim vault]

sure there is:

prices["Apple"] = f"${int(prices['Apple'].lstrip('$')) - new_price}"

[torn sphinx]

lstrip

whats this?

[grim vault]

prices['Apple'].lstrip('$') -> remove a leading $
int(prices['Apple'].lstrip('$')) -> and convert it to int

[torn sphinx]

holy, it works. so l.strip removes whatever you put in it, then i convert it into a string

int**

[grim vault]

left-strip, leading is the key, there is also rstrip() for trailing (right) and strip alone for both sides.

[torn sphinx]

ahhh okay i understand

[grim vault]

No, the int() part converts the string to a number.

[torn sphinx]

okay so i convert the string into a number, and strip the '$"

[grim vault]

The other way around, first strip the $ and then convert it, because $40 can't be convertet.

[torn sphinx]

okay got it, thank you very much. One question though. So if we are stripping the '$' how is it still apart of the code?

ohh nvm i see

i have one more question if you don't mind

how would i check if the valure inside of the json file is = > < a certain value?

[grim vault]

You'll have to convert it first.

[torn sphinx]

okay so same code we used up there, but with an if statment. Then check if its > < = a value

[grim vault]

The better solution would be to store the number in the json and not a string.

[torn sphinx]

what do you mean?

remove the quotes from the values?

[grim vault]

To late now I guess, but instead of storing "$40" just store the number 40

[torn sphinx]

okay ill do that and edit the code

prices = data["prices"]
prices["Apple"] = f"${(prices['Apple']) - new_price}"

with open("prices.json", "w") as f:
  json.dump(data, f, indent=4)

  print(data)

works perfectlty

[grim vault]

Only the first time, now you are storing the string again!

[torn sphinx]

opps im an idiot

i see now

[grim vault]

If you are storing the number inside the json you only need to:

new_price = 20
prices = data["prices"]
prices["Apple"] = prices['Apple'] - new_price

and new_price is misleading because you substract that value, not set it to it.

So new_price actually reduces the current price.

[torn sphinx]

i know its a misleading variable name its just a place holder

yeah

import os

with open("prices.json") as f:
  data = json.load(f)

new_price = 20
prices = data["prices"]
prices["Apple"] = prices['Apple'] - new_price

if prices["Apple"] == 20:
  print(prices["Apple"])

with open("prices.json", "w") as f:
  json.dump(data, f, indent=4)

  print(data)

this works, the if statement

it prints the price of apples but if isn't 20 it doesnt print

[tropic fable]

=== Ask for SQLAlchemy ===
Does anyone know how to make this error happen?
TimeoutError: QueuePool limit of size 5 overflow 10 reached

[old socket]

def add_newstudent(a, b, c, d):



    
    sql = "INSERT INTO table_1 (student_age, student_name) VALUES (%d,%s)",format(a, b)
    sql1 = "INSERT INTO table_1 (unique_id,class) VALUES (%d,%d)", format(c, d)
    mycursor.execute(sql)
    mycursor.execute(sql)
    print("executed")

    mydatabase.commit()

a  = 12
b ="yes"
c = 11
d =11011
add_newstudent(a, b, c, d)
mycursor = mydatabase.cursor()
mycursor.execute("SELECT * FROM table_1")
table_1 = mycursor.fetchall()
for table_1 in table_1:
    print(table_1)```

my error:sql = "INSERT INTO table_1 (student_age, student_name) VALUES (%d,%s)",format(a, b)
ValueError: Invalid format specifier

[brave bridge]

@old socket what database are you using?

[old socket]

mysql

sorry didnt mention earlier

[brave bridge]

Do not use format or f-strings or %-formatting with queries. This will lead to security issues.
This is the right way to do it:

    sql = "INSERT INTO table_1 (student_age, student_name) VALUES (%d,%s)"
    sql1 = "INSERT INTO table_1 (unique_id,class) VALUES (%d,%d)"
    mycursor.execute(sql, (a, b))
    mycursor.execute(sql, (c, d))

[old socket]

ohhh

ty man

[brave bridge]

(probably, I don't know what your table structure is)

[brave bridge]

why are you doing two INSERTs?

[old socket]

it said only accepts only 2 parameters

so when i tried passing all in one statement it didnt work

ok mb

i used %s for everything

and it worked

[brave bridge]

?

show the error and the code

[old socket]

i tried this

[brave bridge]

You don't have a class column in you table. But you have student_class, maybe you meant that

[brave bridge]

.

[old socket]

yh i had an error earlier where it said .format accepts only 2 parameters or smtng so searched up in google and got a stack overflow answer where they used two inserts

after i did that i didnt get the parameter error

so tgtht tht was the right way to go abt it

[brave bridge]

A single INSERT statement inserts a single row into the database.

So you will have two rows, each with half the data.

What you probably need is ```py
sql = "INSERT INTO table_1 (unique_id, student_age, student_name, student_class) VALUES (%s, %s, %s, %s)"
params = (unique_id, age, name, class_)
mycursor.execute(sql, params)

[old socket]

👍

i didnt mention class name properly mb, thanks for being patient and responding calmly

much appreciated

[grim vault]

Just to clarify: the placeholder for mysql is always %s no matter the datatype.

[brave bridge]

oh I didn't realize that

thank you

[old socket]

is there any site that any of u could reccomend for basic syntax and queries

can someone help pls

[sterile phoenix]

Hi does anyone know how to make a registration and login form in tkinter with a csv database?

[harsh pulsar]

https://sqlbolt.com

[harsh pulsar]

a csv file is not really a good database, because you can't write rows anywhere except at the end, and you can't delete rows -- unless you rewrite the entire file for such a change

[sterile phoenix]

oo

[old socket]

ohh ty i did see it in pinned later

[grim vault]

Normaly in a documentation [] means something optional and doesn't include the brackets itself. so bla bla [something] bla [something else] just means that something is optional.

[sterile phoenix]

well the school requires us to read the data into a csv file or excel

[old socket]

bro i removed all []

and

caps for keywords

still it kept showing error

nvm that

def delete_student():
    student_name = input("enter student name to be modified")
    sql = "DELETE FROM table_1 WHERE student_name=%s;"
    mycursor.execute(sql,(student_name))
    mydatabase.commit()```

im trying to delete by name

but how do i delete with comparing to a string variable

[grim vault]

(student_name) is not a tuple, you need (student_name,)

[old socket]

is it possible

def delete_student():
student_name = input("enter student name to be modified")
s = tuple(student_name,)
sql = "DELETE FROM table_1 WHERE student_name=%s;"
mycursor.execute(sql,(s))
mydatabase.commit()

would this work?

[grim vault]

You don't need the s just add the comma in the call itself.

def delete_student():
    student_name = input("enter student name to be modified")
    sql = "DELETE FROM table_1 WHERE student_name=%s;"
    mycursor.execute(sql,(student_name,))
    mydatabase.commit()```

[old socket]

okk

[grim vault]

And it should read to be deleted not modified

[old socket]

so if i have more than one parameter to my table how do i delete all of them like i have 4 columns how do i remove all other columns for this name too

[old socket]

yhh

[grim vault]

A DELETE always deletes the whole row, no matter how many columns there are.

[old socket]

it shows me this error

[old socket]

for this code

would like the entire code?

[grim vault]

yes

[delicate field]

Hey @old socket!

You either uploaded a .txt file or entered a message that was too long. Please use our paste bin instead.

[old socket]

okk

whatever

grt

[grim vault]

The whole traceback should do.

[old socket]

HOW TO DO I SEND THEIR PSTE BIN SAVED THINGY NOW

sry caps

accidental

[grim vault]

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

[old socket]

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

a lot of it is incomplete

[grim vault]

You didn't input a name I guess.

[old socket]

wat

[grim vault]

!e

student_name = ""
print(tuple(student_name,))
print((student_name,))

[delicate field]

@grim vault :white_check_mark: Your eval job has completed with return code 0.

001 | ()
002 | ('',)

[grim vault]

your version doesn't work without an input.

[old socket]

yh see ss i did give an input

[grim vault]

use <#databases message>

[old socket]

BRO U ARE A REAL SAVIOUR TYSM

[harsh pulsar]

in ms sql server [] is used for quoting non-syntactic names, like "" in ansi sql

[torn sphinx]

is there a way to open .db file in phpmyadmin?

[ruby flint]

Hey guys, what's a good approach to making a copy of a MYSQL database to store as backup incase my database get's erased due to some hacker? I'm currently using sql alchemy and Flask. Specifically, I want to be able to incrementally store a new copy of the database each day and remove the old copy

I hope that makes sense