#databases | Python | Page 152

elder elk May 27, 2021, 8:32 PM

#

insertquery5 = '''UPDATE soilhealth.fertcom SET 
                    furt_comb_two_name = %s,
                    furt_comb_two_quantity = %s 
                    WHERE shc_no = %s and sr_no= %s  and furt_comb_one_name= %s;'''


                    record_to_insert = [Furt_Comb_Name_Two,
                                        Furt_Comb_Quantity_Two,
                                        SHC_No,SrNo,Furt_Comb_Name]

                    #cursor.execute(insertquery5, record_to_insert)
                    execute_batch(cursor,insertquery5, record_to_insert)

                    connection.commit()

#

throws error like this

#

Failed to insert record into FertRecommandation table One 'int' object does not support indexing
Failed to insert record into FertRecommandation table Two string index out of range
Failed to insert record into FertRecommandation table One 'int' object does not support indexing
Failed to insert record into FertRecommandation table Two not all arguments converted during string formatting

#

where I'm making mistake, please help

#

**where I'm making mistake, please help**

proven arrow May 27, 2021, 9:00 PM

#

elder elk ```**where I'm making mistake, please help**```

For execute_batch the values need to be a list iterables.

#

From the error its seems you are only supplying a single list.

elder elk May 27, 2021, 9:01 PM

#

please explain in details I'm new to this

#

@proven arrow

torn sphinx May 27, 2021, 9:02 PM

#

Hello, does anyone know why i get a interactionerror here?

    @cog_ext.cog_slash(name="test", guild_ids=slash_ids)
    @commands.is_owner()
    async def test(self, ctx):

        try:

            if ctx.author.id not in premiumusers.values():
                return await ctx.send(f"{ctx.author.mention} you are not stored into premium users so you cant use this command!")

            if ctx.author.id in premiumusers.values():
                return await ctx.send("You are a premium member!")

        except pymongo.errors.DuplicateKeyError:
            await ctx.send("Are your joking? This Member is alredy a Premium User!")

#

using MongoDB

proven arrow May 27, 2021, 9:05 PM

#

Execute batch execute the sql query several times, against all parameters set found in the args list. So if you want to insert two rows, args list needs to be a for example a list, with 2 sublists.

cursor = conn.cursor()

apple_fruit = [1, "Apple"]
orange_fruit = [2, "Orange"]

# list with sublists
argslist = [apple_fruit, orange_fruit] 

execute_batch(cursor, "INSERT INTO fruits (id, name) VALUES (%s, %s)", argslist)
conn.commit()

@elder elk

dusk junco May 27, 2021, 9:05 PM

#

Hello, I'm getting the following error ERROR: there is no unique constraint matching given keys for referenced table "pipeline" when trying to create these tables below. DO I need to include a primary key on PipelineID?


CREATE TABLE IF NOT EXISTS
GITGROUP
(
    ID int PRIMARY KEY NOT NULL,
    Web_url varchar(255) UNIQUE NOT NULL,
    GName varchar(50) DEFAULT NULL
);

--#################################################################

CREATE TABLE IF NOT EXISTS
GITPROJECT
(
    ID int PRIMARY KEY NOT NULL,
    PName varchar(140) NOT NULL,
    GroupID int,
    PDescription varchar(255) DEFAULT NULL,
    Web_url varchar(255) NOT NULL UNIQUE,
    Pstatus varchar(50) DEFAULT NULL,
    FOREIGN KEY (GroupID) REFERENCES GITGROUP (ID)
);


--#################################################################


CREATE TABLE IF NOT EXISTS
PIPELINE
(
    ID int NOT NULL,
    PStatus varchar(50) DEFAULT NULL,
    Web_url varchar(255),
    ProjectID int NOT NULL,
    Start_time date,
    End_time date,
    FOREIGN KEY (ProjectID) REFERENCES GITPROJECT (ID),
    UNIQUE (ID, ProjectID)
);

CREATE TABLE IF NOT EXISTS
JOBS
(
    JobID int NOT NULL,
    PipelineID int NOT NULL,
    Web_url varchar(255),
    job_status varchar(50) DEFAULT NULL,
    FOREIGN KEY (PipelineID) REFERENCES PIPELINE (ID),
    UNIQUE (JobID, PipelineID)
);

torn sphinx May 27, 2021, 9:06 PM

#

this error is not related to the slashcommands or discord.py its something with MongoDb and i know that i do anything wrong here not a issure with MongoDB itself

elder elk May 27, 2021, 9:06 PM

#

please correct it code if possible @proven arrow

#

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

proven arrow May 27, 2021, 9:07 PM

#

dusk junco Hello, I'm getting the following error `ERROR: there is no unique constraint ma...

Your pipeline table id doesnt have a unique constraint.

#

You can set it as primary key

elder elk May 27, 2021, 9:11 PM

#

I'm little bit confuse,can you please edit one line for me as reference

#

@proven arrow

elder elk May 27, 2021, 9:13 PM

#

elder elk please correct it code if possible <@!613362435860070414>

@proven arrow

proven arrow May 27, 2021, 9:17 PM

#

elder elk I'm little bit confuse,can you please edit one line for me as reference

execute_batch should be outside the for loop.
Inside the for loop you just need to append to your args list.

argslist = []

for dirpath,dirname,filenames in os.walk(filePath):
  for file in filenames:
    ....
    for eachCard in contents:
        ....
        record_to_insert = [Test_Parameter_Id,Test_Parameter_Name,Test_Value,Unit,
                              Rating,SHC_No,Soil_Sample_No,District_Name]
        argslist.append(record_to_insert)

# outside loop
cursor = conn.cursor()

execute_batch(cursor, "INSERT INTO soilhealth.soiltestresult(testparamid,testparamname,testvalue,unit,rating,shc_no,soil_sample_no,district) VALUES (%s,%s,%s,%s, %s,%s,%s,%s)", argslist)

conn.commit()

#

You should be able to figure the rest out now

elder elk May 27, 2021, 9:18 PM

#

thank you so much @proven arrow , I will try it bro

tight smelt May 27, 2021, 9:36 PM

#

its a currency system and its a public bot

digital bronze May 27, 2021, 9:37 PM

#

Would it be better to create additional identical tables for guild data (table name the guild ID) or just have massive tables with a guild ID section to parse through? I think it'd be better with the tables way since then to select user info you just go to SELECT * FROM 1234-user_info; or something and you'll have guild 1234's info instead of doing SELECT * FROM user_info WHERE guild_id = 1234.

harsh pulsar May 27, 2021, 9:45 PM

#

the latter means that you don't have to modify the database schema when you add or remove a guild

#

that's a big advantage imo

#

you can create an index on the guild_id field if queries get slow

torn sphinx May 27, 2021, 9:47 PM

#

anyone knows a solution there:

An exception has occurred while executing command `test`:
Traceback (most recent call last):
  File "D:\Downloads\Python\python 3.8.7\lib\site-packages\discord_slash\client.py", line 744, in invoke_command
    await coro
  File "D:\Downloads\Python\python 3.8.7\lib\site-packages\discord_slash\model.py", line 227, in invoke
    return await self.func(self.cog, *args, **kwargs)
  File "D:\my_python_projects\MongoDB Test\Cogs\music.py", line 472, in test
    if ctx.author.id not in premiumusers:
  File "D:\Downloads\Python\python 3.8.7\lib\site-packages\pymongo\collection.py", line 3432, in __next__
    raise TypeError("'Collection' object is not iterable")
TypeError: 'Collection' object is not iterable

    @cog_ext.cog_slash(name="test", guild_ids=slash_ids)
    async def test(self, ctx):
        await ctx.defer()

        try:

            if ctx.author.id not in premiumusers:
                return await ctx.send(f"{ctx.author.mention} you are not stored into premium users so you cant use this command!")

            if ctx.author.id in premiumusers:
                return await ctx.send("You are a premium member!")

        except pymongo.errors.DuplicateKeyError:
            await ctx.send("Are your joking? This Member is alredy a Premium User!")

elder elk May 27, 2021, 10:17 PM

#

proven arrow execute_batch should be outside the for loop. Inside the for loop you just need ...

Thank you so much bro for saving me, it worked 😋

compact solar May 27, 2021, 11:48 PM

#

someone knows if it's possible to make flask-migrate detect secondary tables declared with "db.Table" ?

south marlin May 28, 2021, 12:07 AM

#

Can someone point me in the right direction when it comes to proper project organizing for SQLAlchemy?

digital bronze May 28, 2021, 1:04 AM

#

harsh pulsar you can create an index on the `guild_id` field if queries get slow

How do I do that!?! I barely know any SQL so 🙂

serene pivot May 28, 2021, 1:49 AM

#

digital bronze How do I do that!?! I barely know any SQL so 🙂

CREATE INDEX some_name ON some_table(columns...)

digital bronze May 28, 2021, 1:50 AM

#

How do I use it though? No idea what indexes are.

#

(Just need a basic thing, I can figure out the rest myself)

serene pivot May 28, 2021, 1:52 AM

#

indexes are for decreasing query times.

#

they can dramatically increase performance by avoiding sequential scans on the tables.

digital bronze May 28, 2021, 1:53 AM

#

So if I did

CREATE INDEX 1234-user_info ON user_info(guild_id);

Then it'd be faster to get something like:

SELECT * FROM user_info WHERE guild_id = 1234;

??

hexed estuary May 28, 2021, 1:55 AM

#

If a column isn't an index one, the only way to run a SELECT by that column is by scanning every single record and checking if that column's value is the one you need

compact solar May 28, 2021, 1:55 AM

#

great, i just accidentally dropped all tables on a database at work.. and don't have permission to restore the backup.

serene pivot May 28, 2021, 1:56 AM

#

Here's my issue, my table schema is as follows

+----+--------------------+--------------------+---------+----------------------------+
| id |     server_id      |     channel_id     | command |         insertion          |
+----+--------------------+--------------------+---------+----------------------------+
| 1  |       BIGINT       |       BIGINT       |  TEXT   |         TIMESTAMP          |
+----+--------------------+--------------------+---------+----------------------------+

I'm trying to get this result from selecting the entire table

+--------------------+--------------------------------------------------------------------
|     server_id      |                           result                                  |
+--------------------+--------------------------------------------------------------------
|      BIGINT        | {channel_id: ['string','string'], channel_id_2, ['more_strings']} |
+--------------------+--------------------------------------------------------------------

#

so far, I've only been able to get this as a result

#

+--------------------+---------------------------------------------------------------------------------------------------------------+
|     server_id      |                                                   array_agg                                                   |
+--------------------+---------------------------------------------------------------------------------------------------------------+
| 805638877762420786 | [['805638877762420789', 'avgping'], ['805638877762420789', 'replytime'], ['808425536519012402', 'replytime']] |
+--------------------+---------------------------------------------------------------------------------------------------------------+

#

using this query ```sql
SELECT server_id, array_agg(array[channel_id::TEXT, command]) FROM command_config GROUP BY server_id;

#

granted I could use some simple python after selecting to put it into the format I need, but is there a better way to do this inside of postgres?

harsh pulsar May 28, 2021, 2:00 AM

#

compact solar great, i just accidentally dropped all tables on a database at work.. and don't...

that's... awful. why do you have the ability to drop tables?

serene pivot May 28, 2021, 2:00 AM

#

compact solar great, i just accidentally dropped all tables on a database at work.. and don't...

So sorry to hear that. Hope everything can be rectified.

compact solar May 28, 2021, 2:03 AM

#

there was a backup taken just 2 hours before so should be ok. not sure why i have that ability.. that app using the db is not developed by me.. i'm just getting data from it as a secondary binding in my Flask app and had no idea flask would drop both my primary and secondary binding

harsh pulsar May 28, 2021, 2:04 AM

#

yeah, seems like a good time to double check your permissions

#

flask shouldn't have any database access at all.. sqlalchemy?

compact solar May 28, 2021, 2:04 AM

#

yes

#

just a stupid mistake, but it could cost a few hours of work depending on how fast it's restored...

harsh pulsar May 28, 2021, 2:07 AM

#

@serene pivot ```sql
SELECT
server_id,
json_build_object((array_agg(channel_id))[1]::TEXT, array_agg(command))
FROM
command_config
GROUP BY server_id;

#

https://www.db-fiddle.com/f/4ExKZaR2vcJ2Q2xUyuZpJ2/1

DB Fiddle - SQL Database Playground

An online SQL database playground for testing, debugging and sharing SQL snippets.

#

there might be a better way to get the distinct channel id here

serene pivot May 28, 2021, 2:11 AM

#

oo yes that's perfect. I got this as the result.

+--------------------+--------------------------------------------------------------+
|     server_id      |                      json_build_object                       |
+--------------------+--------------------------------------------------------------+
| 805638877762420786 | {"805638877762420789" : ["avgping","replytime","replytime"]} |
+--------------------+--------------------------------------------------------------+ 
``` I just need to figure out how to select for all channel ids.

harsh pulsar May 28, 2021, 2:11 AM

#

what do you mean by this?

select for all channel ids.

serene pivot May 28, 2021, 2:14 AM

#

I should clarify. I'm trying to load the entire table into a defaultdict(dict)
the structure would be

# Server ID
805638877762420786: {
  # Channel IDs.       # List of commands
  805638877762420789: ['ping']
  808425536519012402: ['replytime', 'avgping']
}

#

so each channel would point to a list of commands. And I would get that list from a query like this.

SELECT channel_id, ARRAY_AGG(command)
FROM command_config
GROUP BY channel_id;

#

@harsh pulsar
So that query ^ would return something like this

+--------------------+--------------------------+
|     channel_id     |        array_agg         |
+--------------------+--------------------------+
| 808425536519012402 |      ['replytime']       |
| 805638877762420789 | ['avgping', 'replytime'] |
+--------------------+--------------------------+
``` and my goal would be to have each server ID point to those records for the respective channels

harsh pulsar May 28, 2021, 2:22 AM

#

so you want to do the entire thing in postgres and not in python?

serene pivot May 28, 2021, 2:25 AM

#

harsh pulsar so you want to do the entire thing in postgres and not in python?

Ideally. If the query would get ugly then I could use python

harsh pulsar May 28, 2021, 2:30 AM

#

yeah i think it would get ugly

#

if postgres had a first() aggregation function it'd be easier

#

i don't really understand why this is so hard across all databases

#

there must be some implementation reason why

#

oh this isnt bad

#

well its not great but its not bad

#

@serene pivot ```sql
SELECT
json_build_object(
t1.server_id,
array_agg(t1.channel_obj)
) server_obj
FROM
(
SELECT
cc.server_id,
json_build_object(
cc.channel_id,
array_agg(cc.command)
) channel_obj
FROM command_config cc
GROUP BY server_id, channel_id
) t1
GROUP BY t1.server_id

#

https://www.db-fiddle.com/f/4ExKZaR2vcJ2Q2xUyuZpJ2/2

DB Fiddle - SQL Database Playground

An online SQL database playground for testing, debugging and sharing SQL snippets.

#

ah not quite

#

oh boy https://www.db-fiddle.com/f/4ExKZaR2vcJ2Q2xUyuZpJ2/4

DB Fiddle - SQL Database Playground

An online SQL database playground for testing, debugging and sharing SQL snippets.

#

-- https://dba.stackexchange.com/a/269321/105807
CREATE AGGREGATE jsonb_object_concat_agg(jsonb) (
  SFUNC = 'jsonb_concat',
  STYPE = jsonb,
  INITCOND = '{}'
);

SELECT
  jsonb_build_object(
    t1.server_id,
    jsonb_object_concat_agg(t1.channel_obj)
  ) server_obj
FROM
(
  SELECT
    cc.server_id,
    jsonb_build_object(
      cc.channel_id,
      jsonb_agg(cc.command)
    ) channel_obj
  FROM command_config cc
  GROUP BY server_id, channel_id
) t1
GROUP BY t1.server_id;

it's definitely approaching "ugly" now

#

probably still more efficient to compute than doing it in python, although you still have to actually get the data back to python over the network

#

probably not that much more efficient than just select server_id, channel_id, command and looping

crisp flint May 28, 2021, 2:48 AM

#

import.sys from file

#

d

digital bronze May 28, 2021, 2:56 AM

#

hexed estuary If a column isn't an index one, the only way to run a SELECT by that column is b...

Oh awesome! Thanks!

crisp flint May 28, 2021, 3:31 AM

#

me too

#

thx

#

wow column

serene pivot May 28, 2021, 4:01 AM

#

wow thank you so much salt that works like magic. Really appreciate your time.

south marlin May 28, 2021, 4:36 AM

#

Do we really need to use psycopg2 with SQLAlchemy?

#

It looks like I was able to do CRUD without using psycopg2

fierce sand May 28, 2021, 5:05 AM

#

:-) im going to make simple cloud

#

And make some sweet sweet e-cash out of it

upper basin May 28, 2021, 6:27 AM

#

so ive got 2 queries,

select rank,threestars+fourstars+fivestars from(select users.*, rank() over (order by threestars+fourstars+fivestars desc) as rank from users) users where userid = {user.id}

and

select userid,threestars+fourstars+fivestars as wishcount from users where wishcount <= ? order by wishcount desc LIMIT ?

is there any way to merge these 2 queries into 1?

#

for more context,

create table if not exists users(
  userid int,
  threestars int,
  fourstars int,
  fivestars int)

tranquil totem May 28, 2021, 6:47 AM

#

How do you see if a collection exists in database in pymongo?

#

@quick bloom could you help me with the database part pls?

burnt turret May 28, 2021, 6:51 AM

#

use db.list_collection_names with a filter that will match for the specified collection name

#

https://pymongo.readthedocs.io/en/stable/api/pymongo/database.html?highlight=collection_names#pymongo.database.Database.list_collection_names

tranquil totem May 28, 2021, 6:51 AM

#

oh

#

okay

fierce sand May 28, 2021, 6:53 AM

#

How do i edit/write a text to .txt file but using string

#

fh = write()
^ doesn't work with string so i cant save string

tranquil totem May 28, 2021, 7:09 AM

#

I get this error :/```py
Ignoring exception in command afk:
Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "/home/runner/CaliberBot/cogs/afk.py", line 24, in afk
await server_collection.insert_one(post)
AttributeError: '_asyncio.Future' object has no attribute 'insert_one'

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

Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 902, in invoke
await ctx.command.invoke(ctx)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 864, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 94, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: '_asyncio.Future' object has no attribute 'insert_one'
Future exception was never retrieved
future: <Future finished exception=TypeError('filter must be an instance of dict, bson.son.SON, or any other type that inherits from collections.Mapping')>
Traceback (most recent call last):
File "/usr/lib/python3.8/concurrent/futures/thread.py", line 57, in run
result = self.fn(*self.args, **self.kwargs)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/database.py", line 857, in list_collection_names
common.validate_is_mapping("filter", filter)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/common.py", line 494, in validate_is_mapping
raise TypeError("%s must be an instance of dict, bson.son.SON, or "
TypeError: filter must be an instance of dict, bson.son.SON, or any other type that inherits from collections.Mapping

fierce sand May 28, 2021, 7:11 AM

#

Lot of error lemon_eyes

vale pebble May 28, 2021, 7:15 AM

#

@fierce sand
with {'nameoffile.txt'} .open(mode='w') as file:
file.write('String to write' )

#

above is used if it is a Path object.
if not, use open(nameoffile.txt,mode='w')

fierce sand May 28, 2021, 7:26 AM

#

Ok

lapis snow May 28, 2021, 7:36 AM

#

No

grim vault May 28, 2021, 8:07 AM

#

I would make three tables. One for users, one for guilds and one to join them. You can have multiple guilds and a user can be joined with many guilds. It's called a n:m relation between user and guild.

CREATE TABLE User
(
  user_id INTEGER PRIMARY KEY,
  -- additional columns for general user info
);

CREATE TABLE Guild
(
  guild_id INTEGER PRIMARY KEY,
  -- additional columns for general guild info
);

CREATE TABLE UserGuild
(
  user_id INTEGER NOT NULL,
  guild_id INTEGER NOT NULL,
  -- additional columns for user info per guild

  PRIMARY KEY (user_id, guild_id),

  FOREIGN KEY (user_id) REFERENCES User(user_id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (guild_id) REFERENCES Guild(guild_id)
    ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE INDEX fk_guild_id ON UserGuild(guild_id);

-- select all user of a guild with all info
SELECT *
  FROM User
  JOIN UserGuild ON UserGuild.user_id = User.user_id
 WHERE UserGuild.guild_id = 1234
;

torn sphinx May 28, 2021, 8:25 AM

#

Hello, does anyone know how i can loop if a value is in premiumusers and if it is then it run a code if not it run another code? i got following code so far but i dont know what this will return like True or False or something else. if someone knows what it will return then i easy can do it by my own.
Here is the code where i dont know what it will return:

premiumusers.find_one(member.id)

tight smelt May 28, 2021, 8:31 AM

#

{
"perma_blacklisted" = []

"week_blacklist" = []
}

#

is that how you use json

umbral jungle May 28, 2021, 8:37 AM

#

just make perm_blacklist.json and when you load the cog

tight smelt May 28, 2021, 8:37 AM

#

yea im not gonna make a new cog

#

but let me read

umbral jungle May 28, 2021, 8:38 AM

#

do it in main file no issue

#

just remove self

tight smelt May 28, 2021, 8:38 AM

#

mk

#

btw

#

im appending

#

to a list

#

so i dont think i will dump

umbral jungle May 28, 2021, 8:38 AM

#

make 2 jsons

tight smelt May 28, 2021, 8:38 AM

#

why?

#

eh whatever

wet stump May 28, 2021, 8:42 AM

#

Hello help me pls

#

select account_id, top 5 device_brand, count(device_brand)
from exaster
group by device_type

#

Not works well for condition: Select currently active clients and pick up TOP5 device brands by each phone type. Please provide the result in one single query.

tight smelt May 28, 2021, 8:44 AM

#

@umbral jungle

#

perm_blacklist[str(ctx.guild.id)] = user

#

what does that mean?

umbral jungle May 28, 2021, 8:45 AM

#

tight smelt perm_blacklist[str(ctx.guild.id)] = user

use ctx.user.id

wet stump May 28, 2021, 8:45 AM

#

Hello

tight smelt May 28, 2021, 8:45 AM

#

yea

#

i thought

#

lol

#

 if duration == 'perma':
            with open(r"json/perm_blacklist.json",'r') as f:
                perm_blacklist = json.load(f) 
                perm_blacklist[str(user.id)] = user
                with open("json/prefixes.json","w") as f:
                    json.dump(perm_blacklist,f,indent=4)
                await ctx.send(f"`{user}` has been permanently blacklisted.")   ```

#

the file:

{
    "perma_blacklisted": []

}```

#

not workign

#

no error

#

not sending a message

grim vault May 28, 2021, 9:03 AM

#

~~"perma_blacklisted": [] means it's a list, but perm_blacklist[str(user.id)] = user is for dictionaries. This should fail with an error.~~ edit: I just saw that perm_blacklist is a dictionary, sorry about that.
You use different filenames for reading and writing and you write inside the read (the with statements should have the same indent).

rustic osprey May 28, 2021, 9:09 AM

#

Hey, I am trying to save user ids to a database. im saving them using this code and it works. the id's are correctly commited.

def subscribe(userid):
    importusers()
    if str(userid) not in allusers:
        allusers.append(userid)
        sql = "INSERT INTO registered VALUES (%s, %s)"
        val = userid, True
        try:
            mycursor.execute(sql, val)
            mydb.commit()

They come back like this: 123456

But when i read them with this code:

def importusers():
    mycursor.execute('SELECT userid FROM registered')
    sqlqry = mycursor.fetchall()
    for row in sqlqry:
        print(row)
    return sqlqry

They come back as
(123456,)
(123456,)

#

any idea why?

quick bloom May 28, 2021, 9:18 AM

#

tranquil totem I get this error :/```py Ignoring exception in command afk: Traceback (most rece...

You need to use a MongoClient object

proven arrow May 28, 2021, 9:29 AM

#

rustic osprey any idea why?

Because each row is represented as a tuple.

#

And so you getting a tuple back

torn sphinx May 28, 2021, 9:50 AM

#

SELECT productID
FROM Products
WHERE Products like 'chee[s,z]e';```

#

is this correct?

#

if i want to find cheese/ cheeze

potent spoke May 28, 2021, 9:53 AM

#

i am getting pymongo.errors.ServerSelectionTimeoutError while trying to connect mongoDb to my poython code. can anyone please help?

torn sphinx May 28, 2021, 9:58 AM

#

anyone know why the bot is still allowing users playnow command after i revoked the premiumstatus from them (delete from Database)?

Link: https://pastebin.com/MugCmUFZ
Password: Bv1PxgRdBn

Pastebin

Pastebin.com - Locked Paste

Pastebin.com is the number one paste tool since 2002. Pastebin is a website where you can store text online for a set period of time.

proven arrow May 28, 2021, 10:07 AM

#

torn sphinx is this correct?

Which database?

torn sphinx May 28, 2021, 10:08 AM

#

SQL query to retrieve the productID of products which have a productName that contains the word cheese (or cheeze).

#

using regex

proven arrow May 28, 2021, 10:09 AM

#

That doesn’t say which database

#

The [] won’t work in all databases for the LIKE pattern matching. I know Sql server supports it but not sure other vendors. In which case you can use the underscore _ although that would allow any character in it’s place meaning ‘Cheele’ would work also. If you want more specific then use regex

nimble wyvern May 28, 2021, 11:32 AM

#

how to check if a field exists in a document in mongodb?

burnt turret May 28, 2021, 11:33 AM

#

you'd use the $exists operator

#

https://docs.mongodb.com/manual/reference/operator/query/exists/

nimble wyvern May 28, 2021, 11:34 AM

#

burnt turret https://docs.mongodb.com/manual/reference/operator/query/exists/

how to be specific to a single document not whole collection while using it?

burnt turret May 28, 2021, 11:34 AM

#

🤔

#

you could add some more info to the query that would be specific to that document?

#

collection.findOne({_id: <doc id>, field: {$exists: 1}}

nimble wyvern May 28, 2021, 11:35 AM

#

collection.find({"_id": 0,'date':{"$exists": False}})

burnt turret May 28, 2021, 11:35 AM

#

yeah

nimble wyvern May 28, 2021, 11:35 AM

#

I'm using this it's not giving me anything

burnt turret May 28, 2021, 11:36 AM

#

wdym?

nimble wyvern May 28, 2021, 11:36 AM

#

even if a date exists

burnt turret May 28, 2021, 11:36 AM

#

what does it return?

nimble wyvern May 28, 2021, 11:37 AM

#

burnt turret May 28, 2021, 11:37 AM

#

yes find returns a Cursor

#

you're supposed to iterate over it to see the documents it returned

nimble wyvern May 28, 2021, 11:40 AM

#

l = [ i for i in collection.find({"_id": 0,'date':{"$exists": False}})] something like this would be good to be used on if statement
I want to check if date exists or not

if doesn't exist create a new one

else check the date with some value and makes changes

#

@burnt turret

#

srry for pinging lol

burnt turret May 28, 2021, 11:41 AM

#

why are you using find here instead of find_one?

nimble wyvern May 28, 2021, 11:41 AM

#

burnt turret why are you using `find` here instead of find_one?

oh that can be fixed I was trying everything and send the one with find lol

burnt turret May 28, 2021, 11:42 AM

#

right

#

what do you get with the current code?

#

is the list empty or something?

nimble wyvern May 28, 2021, 11:42 AM

#

yes the list empty so i can use it as a falsy value

burnt turret May 28, 2021, 11:42 AM

#

right

#

wait so whats the issue lol

nimble wyvern May 28, 2021, 11:43 AM

#

burnt turret wait so whats the issue lol

I just found the solution as i was chatting with u lol

#

I wanted to know if there's a better way to do this like from the query itself

#

condition within the query like case 1 do this and case 2 do that

burnt turret May 28, 2021, 11:43 AM

#

ah

#

if you only had to do option 1 in that, you could've done an upsert but i see that's not an option

#

i don't know of a way you could do that in the query itself

#

actually brb

nimble wyvern May 28, 2021, 11:44 AM

#

burnt turret if you only had to do option 1 in that, you could've done an `upsert` but i see ...

yes upsert wont do here

burnt turret May 28, 2021, 11:46 AM

#

burnt turret actually brb

nvm i don't know of a way to do that in a single query, sorry lemon_sweat

nimble wyvern May 28, 2021, 11:48 AM

#

burnt turret nvm i don't know of a way to do that in a single query, sorry <:lemon_sweat:7544...

so gotta do with the traditional python then

burnt turret May 28, 2021, 11:48 AM

#

there might be some way that i don't know of, but the easiest would be just using python here i guess

nimble wyvern May 28, 2021, 11:48 AM

#

oh ok thanks dude

fierce sand May 28, 2021, 12:51 PM

#

i made a file that write the database program again (rewrite) incase someone somewhy edit the file

wooden basin May 28, 2021, 1:05 PM

#

hello, i have this problem and i dont know how to solve it: psycopg2.errors.OutOfMemory: out of memory DETAIL: Failed on request of size 448.

rustic osprey May 28, 2021, 1:48 PM

#

proven arrow And so you getting a tuple back

thanks. that was a push in the right direction. i do it like this now:

def importusers():
    mycursor.execute('SELECT userid FROM registered')
    sqlqry = mycursor.fetchall()
    allusers = [row[0] for row in sqlqry]
    return allusers

ruby magnet May 28, 2021, 2:05 PM

#

i'm making a sqlite3 controler using python and i'm making the delete data from a table feature, but it says it can't find column, code:

#

def delete_data(table, first_data, first_data_in_row):
    cur = connection.cursor()
    cur.execute('DELETE FROM {} WHERE {}=?;'.format(table, first_data), (first_data_in_row,))

table = input("input table:")
first_data_in_table = input("input first data in the table:")
first_data_in_row = input("input the first data of a row u want to delete")
delete_data(table, first_data_in_table, first_data_in_row)

#

it's not the best interface but it's the only think i can think off

#

when i put in the data it says it didn't find the column and it deleted the whole data in the table

lusty inlet May 28, 2021, 2:23 PM

#

ok so (1) learn about creating safe queries https://realpython.com/prevent-python-sql-injection/ -- even when you're just working on your own thing, it's good practices to not drop arbitrary strings into queries

#

(2) as you're working on this, try printing/logging/viewing the queries you've built before execution. in this case it sounds like first_data didn't properly capture the column you wanted

ruby magnet May 28, 2021, 2:27 PM

#

alr, thx man

torn sphinx May 28, 2021, 2:39 PM

#

Guys I have this huge file with 30k lines and every few hundred lines there is a list that begins with Position and has 100 entries or so

#

then inbetween there is other unrelated info

#

How can I find the lines where the title of the table is

#

I used this but didnt work:

#

file = open(path_to_file, "r")
n = 1
line = []
for line in file.readlines():
    if line == "POSITION                                       TOTAL-FORCE (eV/Angst)":
      print("Line found at ", n)
      line.append(n)
    n += 1
print(len(line))

raven trail May 28, 2021, 2:46 PM

#

What is the best way to check if a table exists using Python/SQL query?

#

using PostgreSQL

harsh pulsar May 28, 2021, 3:09 PM

#

torn sphinx Guys I have this huge file with 30k lines and every few hundred lines there is a...

!with

delicate fieldBOT May 28, 2021, 3:09 PM

#

The with keyword triggers a context manager. Context managers automatically set up and take down data connections, or any other kind of object that implements the magic methods __enter__ and __exit__.

with open("test.txt", "r") as file:
    do_things(file)

The above code automatically closes file when the with block exits, so you never have to manually do a file.close(). Most connection types, including file readers and database connections, support this.

For more information, read the official docs, watch Corey Schafer's context manager video, or see PEP 343.

harsh pulsar May 28, 2021, 3:10 PM

#

also you don't need .readlines to iterate over a file - in fact you probably shouldn't do this

torn sphinx May 28, 2021, 3:10 PM

#

i figured it out

#

harsh pulsar May 28, 2021, 3:10 PM

#

.readlines reads the entire file into a list; you can just iterate over the file object directly

#

please read the blurb i posted about with open

torn sphinx May 28, 2021, 3:10 PM

#

oh

harsh pulsar May 28, 2021, 3:11 PM

#

lines = []
n = 0
with open(path_to_file, "r") as fp:
    for line in fp:
        if line == "POSITION                                       TOTAL-FORCE (eV/Angst)":
            print("Line found at ", n)
            lines.append(n)
            n += 1
print(len(lines))

torn sphinx May 28, 2021, 3:11 PM

#

so its faster that way?

harsh pulsar May 28, 2021, 3:11 PM

#

this would be a more idiomatic way to write the code

#

its safer (no chance of leaking resources) and more memory efficient

#

good habits are important

torn sphinx May 28, 2021, 3:12 PM

#

yes definitely

#

thank very much

harsh pulsar May 28, 2021, 3:12 PM

#

👍

#

do you want to extract the 100 lines afterwards?

#

you can do it in a single pass over the file

torn sphinx May 28, 2021, 3:13 PM

#

yeah thats exactly what I need to do afterwards

harsh pulsar May 28, 2021, 3:13 PM

#

e.g. if you have a file like this

as;dlf
zxcv

dfvas
START
1
2
3
4
5
END
a;oij
aoi

awino
START
8
9
10
END
ijo
iourtoij

#

you can get all the data between START and END

patent cove May 28, 2021, 3:13 PM

#

so im not good with json, and im trying to get all of the data to append to entrys in my json file.

{
    "entrys": [
           
    ]
}

and rn im completly lost

def save():
    with open("journalEntrys.json",) as json_file:
        maintext = maintextbox.get(1.0, "end-1c")
        nametext = namebox.get(1.0, "end-1c")
        date = datetime.datetime.utcnow()
        date = date.strftime("%m/%d/%Y, %H:%M:%S")
        data = {
            "name": nametext,
            "text": maintext,
            "utc-time": date
        }
        loaded = json.load(json_file)
        for i in loaded["entrys"]:
            print(i)

harsh pulsar May 28, 2021, 3:14 PM

#

btw neither of these are "database" questions

patent cove May 28, 2021, 3:14 PM

#

im getting for i in loaded["entrys"]: KeyError: 'entrys'

harsh pulsar May 28, 2021, 3:14 PM

#

these really both should be in help channels (see #❓｜how-to-get-help )

torn sphinx May 28, 2021, 3:14 PM

#

I was unsure, I did solve part of my problem in one of the help channels

#

but im interested in getting the data between start and end as well

harsh pulsar May 28, 2021, 3:15 PM

#

yup continued in #help-bread

#

@patent cove ask this in a help channel too

proven arrow May 28, 2021, 3:22 PM

#

raven trail What is the best way to check if a table exists using Python/SQL query?

From the information schema

#

It should have a table called tables.

#

https://www.postgresql.org/docs/13/infoschema-tables.html

PostgreSQL Documentation

36.50. tables

36.50. tables The view tables contains all tables and views defined in the current database. Only those tables and views are …

rustic osprey May 28, 2021, 3:49 PM

#

    if str(userid) in str(allusers):      
        try:
            mycursor.execute("DELETE FROM registered WHERE userid = ", str(userid))

i'm trying to use this to delete a single row where the userid matches. the query works on the mysql console from my python code it seems to skip it. why is this wrong?

harsh pulsar May 28, 2021, 3:52 PM

#

@rustic osprey i assume you have except: after that try?

#

if so, don't do that

#

also what is allusers? a list?

#

str() on a list makes a "pretty" version of the list as a single string

#

it does not turn each element of the list into a string

rustic osprey May 28, 2021, 3:53 PM

#

yeah its a list. i have a debug output before and after that sql query.

#

the one before gets printed, the one after it doesnt

harsh pulsar May 28, 2021, 3:53 PM

#

ok, do you understand the problem with str(allusers)?

rustic osprey May 28, 2021, 3:54 PM

#

kind of.

harsh pulsar May 28, 2021, 3:54 PM

#

!e ```python
allusers = [1234, 5678, 9876]
print(repr(str(allusers)))

delicate fieldBOT May 28, 2021, 3:54 PM

#

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

'[1234, 5678, 9876]'

harsh pulsar May 28, 2021, 3:54 PM

#

str(allusers) returns a single string '[1234, 5678, 9876]'

#

this is almost certainly not what you want

rustic osprey May 28, 2021, 3:54 PM

#

the list doesnt have much to do with the sql query. im just syncing the database into that list to quickly check if the id is in there somewhere

harsh pulsar May 28, 2021, 3:55 PM

#

well that could be why it's "python seems to skip it"

#

because your if doesn't work

#

or if it does work sometimes, it's pure luck

rustic osprey May 28, 2021, 3:55 PM

#

but why does it show the print thats in that if statement?

harsh pulsar May 28, 2021, 3:56 PM

#

because technically the string '1234' is part of the string '[1234, 5678, 9876]' so it kind of works sometimes

#

!e print('1234' in '[1234, 5678, 9876]')

delicate fieldBOT May 28, 2021, 3:56 PM

#

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

True

harsh pulsar May 28, 2021, 3:56 PM

#

but fair enough, that isn't this particular problem

#

more likely is that you are abusing except which is causing python to swallow and ignore an error

#

show more of your code if you can

rustic osprey May 28, 2021, 3:57 PM

#

    if str(userid) in str(allchats):      
        try:
            print("this works")
            mycursor.execute("DELETE FROM registered WHERE userid = ", str(userid))
            print('this works too')
            importusers()
            return "ok."
        except
            return 'Cannot delete userid from database.'
    else:
        return 'not registered in database'

#

if i remove the prints it goes directly to except

#

i removed try / except. -> ValueError: Could not process parameters

#

i guess that means the variable is in wrong format?

proven arrow May 28, 2021, 4:29 PM

#

rustic osprey i guess that means the variable is in wrong format?

Parameters should be provided as an iterable like a list, tuple, dict

grim vault May 28, 2021, 4:33 PM

#

harsh pulsar ```python lines = [] n = 0 with open(path_to_file, "r") as fp: for line in f...

You might want to line.strip() because this method will include the line ending (or add a \n to the comparison which I would not recommend). Maybe even line.startswith("POSITION ") would be enough.

harsh pulsar May 28, 2021, 6:39 PM

#

yep, that's important

#

@balmy aspen i got it working with regex, not pretty but effective https://regex101.com/r/QPAgsH/2/

barren zinc May 28, 2021, 7:01 PM

#

I would like to download a file and then store it in my own database and then upload it. So i want to get a link so people can download it from my database. Is it possible? I would like to use https://gofile.io/

Gofile - File sharing platform, anonymous and free

Gofile is a free file sharing and storage platform. You can store and share data of all types. There is no limit and everything is free.

balmy aspen May 28, 2021, 7:02 PM

#

harsh pulsar <@!534837275234795530> i got it working with regex, not pretty but effective <ht...

U pinged the wrong person i think

harsh pulsar May 28, 2021, 7:03 PM

#

nope, i pinged you. you had suggested using regex for a problem that "Supreme Patriarch" was working on

balmy aspen May 28, 2021, 7:03 PM

#

Aaaaah

#

Yeah i mean, when i think about matching some pattern i go to regex. He needed that so thats why i suggested him to use regex

harsh pulsar May 28, 2021, 7:25 PM

#

i wonder if this particular regex would be faster than looping linewise in python

#

doesn't seem like there would be much backtracking as long as the data meets the assumptions

torn sphinx May 28, 2021, 7:55 PM

#

how does relational DB sharding (MySQL, postgres) compare to noSQL db sharding (like mongoDB)?

#

apparently both support sharding but surely there is a big difference between the two?

copper dragon May 28, 2021, 7:58 PM

#

SQL DBs have structured data while NoSQL are unstructured
SQL have more relationship options(onetoone, manytomany, onetomany, foreignkeys etc) hence better for something like a social media while MongoDB have JSON formatted easy to use and work with data

harsh pulsar May 28, 2021, 8:17 PM

#

torn sphinx apparently both support sharding but surely there is a big difference between th...

im not sure there needs to be a big difference in terms of the implications for the user

inner veldt May 28, 2021, 8:24 PM

#

Hey guys, does anyone know about any python's packages for rain data and forecasting?

pure sleet May 28, 2021, 8:40 PM

#

inner veldt Hey guys, does anyone know about any python's packages for rain data and forecas...

https://openweathermap.org/api use this api alongside the request module

Weather API

Simple and fast and free weather API from OpenWeatherMap you have access to current weather data, hourly, 5- and 16-day forecasts. Historical weather data for 40 years back for any coordinate. Weather maps, UV Index, air pollution
and historical data

inner veldt May 28, 2021, 8:45 PM

#

thanks!

nimble wyvern May 29, 2021, 7:16 AM

#

how to whitelist my heroku application ip in mongodb atlas?

torn sphinx May 29, 2021, 8:11 AM

#

how do i edit a database using python

#

sql databse

#

How to create a database and learn these codes like SELECT, CREATE, etc?

#

xD

wary nebula May 29, 2021, 11:17 AM

#

https://thepygrammer.blogspot.com/2020/11/login-system-1-database-creation.html
A login system program using MySQL connection with Python.

LOGIN SYSTEM #1: Database Creation

In a world where security is first, a simple login system can do us a world of good in many places. A login system in Python is simple yet effective.

burnt turret May 29, 2021, 12:54 PM

#

nimble wyvern how to whitelist my heroku application ip in mongodb atlas?

does heroku give you static external IPs? i don't believe so

nimble wyvern May 29, 2021, 12:55 PM

#

burnt turret does heroku give you static external IPs? i don't believe so

Yah it doesn't

#

Gotta add some extra adons to make it satic or something

#

Whitelisted every IP for now 😥

nimble wyvern May 29, 2021, 12:57 PM

#

nimble wyvern Gotta add some extra adons to make it satic or something

This the only way right?

burnt turret May 29, 2021, 12:59 PM

#

dunno, haven't tried it

torn sphinx May 29, 2021, 1:03 PM

#

hey guys,
how do you suggest I run my django database migrations when im deploying to google cloud run?
should i add it to the CMD in the Dockerfile?
im using google cloud sql with postgres 13 btw

rancid cloud May 29, 2021, 1:04 PM

#

hey everyone what's the difference between postgresql and mysql in which case is one useful than the other and
which DBMS is used by snapshat

brazen charm May 29, 2021, 1:07 PM

#

which DBMS is used by snapshat

Probably several e.g. Redis / Keydb, Cassandra / Scylla, with some extra relational database aswell

#

as for

between postgresql and mysql
They're both relational SQL databases, however postgres is generally the preferred database in the modern world because it just outperforms MySQL in most cases, has much better SQL standard support and much more utility (MySQL also has several issues like UTF-8 Encoding but thats fixed with something like MariaDB)

rancid cloud May 29, 2021, 1:16 PM

#

brazen charm > which DBMS is used by snapshat Probably several e.g. Redis / Keydb, Cassandra...

im just wondering on how that thing where you send a snap and it stay still in your phone until you're connected and so much more stuff i like when it comes to how snapchat deals with data

torn sphinx May 29, 2021, 1:17 PM

#

brazen charm as for > between postgresql and mysql They're both relational SQL databases, ho...

What’s wrong with MySQL encoding?

torn sphinx May 29, 2021, 1:18 PM

#

rancid cloud im just wondering on how that thing where you send a snap and it stay still in y...

There is not way of knowing for sure what they do it like without seeing their code or architecture

#

Some ways they can do this is by using local storage or local device db

sullen wolf May 29, 2021, 1:50 PM

#

How do I make a list in a SQlite DB?

proven arrow May 29, 2021, 1:50 PM

#

torn sphinx What’s wrong with MySQL encoding?

Nothing is wrong if you use the correct encoding type.

brazen charm May 29, 2021, 1:56 PM

#

the issue is that its not UTF-8 by default 😔

fallow coral May 29, 2021, 2:46 PM

#

absolute noob to databases but im using sqlite3 for a discord.py bot.
is it a bad idea to write a wrapper like this? it seems safer to use a wrapper than to rely on me remembering to open and close the connection every time i write a new function

def conclose(func):
    def wrapper(*args):
        conn = sqlite3.connect(
            str(Path(__file__).resolve().parent)+"/namehere.db")
        c = conn.cursor()
        func(c, *args)
        conn.commit()
        conn.close()
    return wrapper

burnt turret May 29, 2021, 3:04 PM

#

🤔 feels like something that would be neatly done with a context manager

#

i'd thought earlier that using the connection object itself as a context manager would have it close the connection, but that doesn't look like it is the case; looks like it autocommits

#

might be a nice place to use contextlib.closing lemon_thinking

#

!d contextlib.closing

delicate fieldBOT May 29, 2021, 3:05 PM

#

contextlib.closing


contextlib.closing(thing)```
Return a context manager that closes *thing* upon completion of the block. This is basically equivalent to:

```py
from contextlib import contextmanager

@contextmanager
def closing(thing):
    try:
        yield thing
    finally:
        thing.close()
```...

fallow coral May 29, 2021, 3:10 PM

#

wow i dont understand that lmao

#

i'll have to look into it. Thanks

proven arrow May 29, 2021, 3:11 PM

#

Doesn't sqlite alread support context manager, why make your own?

burnt turret May 29, 2021, 3:11 PM

#

the docs seem to say it won't close the connection

#

it just commits

#

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

grim vault May 29, 2021, 3:27 PM

#

The question is if you need to open/close the connection all the time.

torn sphinx May 29, 2021, 3:28 PM

#

proven arrow Nothing is wrong if you use the correct encoding type.

oh ok

#

Does anyone have any idea how to make editable pages like CMS. What would design look like or how to set this up.

#

Simple cms functionality to use with some pages on a site.

fallow coral May 29, 2021, 3:40 PM

#

grim vault The question is if you need to open/close the connection all the time.

theres gonna be long periods (possibly days) where no commands will be sent, id rather shut the connection off if its idle for so long. im not really expecting more than ten commands a day either

grim vault May 29, 2021, 3:44 PM

#

Okay, with that low traffic it won't hurt the performance much if you open and close it for every sql command. It's just sqlite has some things to do if it opens the database.

proven arrow May 29, 2021, 3:52 PM

#

torn sphinx Simple cms functionality to use with some pages on a site.

Define how simple? How do you intend to use it or write your code for it. CMS can be quite complex, not only the database but also how you write the server side code.

torn sphinx May 29, 2021, 4:00 PM

#

Currently we have a homepage and offers page. Content on it is currently coded from the server. Changing this content means i must have to update the code each time, even if its just like text wording change. Client currently has to pay for this, so they want something long term where they can update themselve.

Here is the request they sent me:
An admin user should be able to change content of the home/offers page directly from the Admin portal.
This is for our phase 2 development.

#

For code i will just code it so that for homepage it will know to look for x data, and for offers it will know to look for y data.
Current content is like images for slider, header text etc.

proven arrow May 29, 2021, 4:18 PM

#

So if i understand correctly you just want them to edit the content you have, but not the ability to style/choose its location on page etc?

#

@torn sphinx

torn sphinx May 29, 2021, 4:18 PM

#

Yes

proven arrow May 29, 2021, 4:21 PM

#

That could be achieved with something like this

remote plinth May 29, 2021, 4:22 PM

#

quick question: can we use += in sqlite?

#

like UPDATE .... SET x += 1

grim vault May 29, 2021, 4:23 PM

#

remote plinth quick question: can we use += in sqlite?

No

remote plinth May 29, 2021, 4:23 PM

#

SET x = x + 1?

#

if no, how can i increment

grim vault May 29, 2021, 4:23 PM

#

remote plinth SET x = x + 1?

👍 _ _

remote plinth May 29, 2021, 4:24 PM

#

grim vault 👍 _ _

u mean ye?

grim vault May 29, 2021, 4:24 PM

#

remote plinth u mean ye?

Yes you can set x = x+1

remote plinth May 29, 2021, 4:24 PM

#

thanks.

proven arrow May 29, 2021, 4:24 PM

#

@torn sphinx Its what i have in one project. Diagram is self explanatory but pages just stores details of the page (template refers to a file within the code that has code which tells it how to be setup.)
page_contents stores a key value of the content you want for that page.

remote plinth May 29, 2021, 4:25 PM

#

"UPDATE tags SET users = users + 1"

#

would that increment users?

grim vault May 29, 2021, 4:26 PM

#

if it's not null and a number, yes.

#

for ALL entries in tags.

fallow coral May 29, 2021, 4:28 PM

#

is there a wildcard for WHERE? i know its kinda redundant but i have a use for it

harsh pulsar May 29, 2021, 4:29 PM

#

@fallow coral what do you have in mind? hypothetically even

fallow coral May 29, 2021, 4:32 PM

#

i have an existing function which takes in one argument and finds all entries where a given column has that value. It does some other stuff too.

i just realised that the bug staring me in the face was that i used it when i had intended to select from two columns.

if i could have def myfunc(existingArg, newArg="wildcard") then by default i can use the function as it is now, or by passing something to newArg i can specify from a second column too

#

instead of having two nearly identical functions i figure it makes more sense to just use one with an optional arg

harsh pulsar May 29, 2021, 4:34 PM

#

im not entirely sure what you mean by "wildcard" - is this for the LIKE operator?

#

or do you need to construct a sql query for a dynamic set of columns?

#

you might have to just use string interpolation/concatenation for that, unfortunately

#

you can only parameterize "data", not column names or expressions

fallow coral May 29, 2021, 4:37 PM

#

basically, in a function myfunc(existingArg, newArg="wildcard_here") I want to be able to accept any value of name unless i specify a value for newArg;
cursor.execute(f"SELECT * FROM tasks WHERE space="{existingArg}" AND name="{newArg}")

#

it currently works when i dont have newArg=... or AND name=... in there, im not sure what to put as a default for newArg to make it accept any value for name

grim vault May 29, 2021, 4:39 PM

#

I'm doing it like this:

def build_update_stmt(tbl_name, cols, *cols_id):
    return (
        f"UPDATE {tbl_name} SET {'=?,'.join(cols)}=?"
        f" WHERE {'=? AND '.join(cols_id)}=?"
    )

def update_from_dict(tbl_name, upd_colums, **id_colums):
    cols, bind = zip(*[col for col in upd_colums.items()])
    cols_id, bind_id = zip(*[col for col in id_colums.items()])
    bind += bind_id
    stmt = build_update_stmt(tbl_name, cols, *cols_id)
    return stmt, bind

tbl_name = "user"
upd_colums = {"name": "Berndulas", "age": 50}

stmt, bind = update_from_dict(tbl_name, upd_colums, user_id=1, aktive="yes")
print(stmt, bind)

output:

UPDATE user SET name=?,age=? WHERE user_id=? AND aktive=? ('Berndulas', 50, 1, 'yes')```

harsh pulsar May 29, 2021, 4:41 PM

#

fallow coral basically, in a function `myfunc(existingArg, newArg="wildcard_here")` I want to...

oh, don't use f-strings for this. use parameters

grim vault May 29, 2021, 4:41 PM

#

I can't?

harsh pulsar May 29, 2021, 4:41 PM

#

@grim vault i was replying to the other user

#

you're doing it correctly

#

wcj is not

fallow coral May 29, 2021, 4:42 PM

#

do f-strings misbehave? or is it a best practice thing

harsh pulsar May 29, 2021, 4:42 PM

#

both

#

it's best practice for a reason

#

even if security is not relevant for your use case, escaping is a nightmare and generally not something you want to do manually

#

e.g. for sqlite3

cursor.execute("SELECT * FROM tasks WHERE space=? AND name=?", (existingArg, newArg))

#

consult the docs for your particular sql library for the placeholder style

#

it can be ?, $1, %s, or i think one other option

#

!e ```python
import sqlite3
print(sqlite3.paramstyle)

delicate fieldBOT May 29, 2021, 4:43 PM

#

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

qmark

harsh pulsar May 29, 2021, 4:43 PM

#

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

Python.org

PEP 249 -- Python Database API Specification v2.0

The official home of the Python Programming Language

fallow coral May 29, 2021, 4:43 PM

#

okay, ill use params if fstrings are an issue

harsh pulsar May 29, 2021, 4:44 PM

#

not just f-strings - any and all string concatenation, interpolation, etc

#

you see how bernudas constructed the sql code, then left ? placeholders for the data?

#

that's the right way to do it

fallow coral May 29, 2021, 4:44 PM

#

okay, cool

fallow coral May 29, 2021, 4:45 PM

#

harsh pulsar e.g. for sqlite3 ```python cursor.execute("SELECT * FROM tasks WHERE space=? AND...

is there some value i can give to newArg that means "literally any value is accepted for this column"?

harsh pulsar May 29, 2021, 4:45 PM

#

ah, i see

#

no, you'd have to have an if in your python code and 2 different queries, or construct a query like bernudas is doing

#

def myfunc(cursor, arg1, arg2=None):
    query = "SELECT * FROM tasks WHERE space=?"
    params = [arg1]
    if arg2 is not None:
        query += " AND name=?"
        params.append(arg2)
    cursor.execute(query, params)

#

or

def myfunc(cursor, arg1, arg2=None):
    if arg2 is None:
        cursor.execute("SELECT * FROM tasks WHERE space=?", (arg1,))
    else:
        cursor.execute("SELECT * FROM tasks WHERE space=? AND name=?", (arg1, arg2))

fallow coral May 29, 2021, 4:48 PM

#

okay, i'll do it like that if theres no other way

grim vault May 29, 2021, 4:48 PM

#

Or use keyword argument packing.

fallow coral May 29, 2021, 4:49 PM

#

the ? thing works in INSERT INTO table VALUES (?, ? ,?), right?

harsh pulsar May 29, 2021, 4:49 PM

#

yes

fallow coral May 29, 2021, 4:49 PM

#

okay, thanks for your help guys!

nova dawn May 29, 2021, 5:17 PM

#

!e

#

!e

import glob
for file in glob.glob("*"):
  print(file)

#

!e

import glob
for file in glob.glob("/config"):
  print(file)

delicate fieldBOT May 29, 2021, 5:18 PM

#

@nova dawn :warning: Your eval job has completed with return code 0.

[No output]

nova dawn May 29, 2021, 5:19 PM

#

!e

import glob
for file in glob.glob("*"):
  print(file)

delicate fieldBOT May 29, 2021, 5:19 PM

#

@nova dawn :white_check_mark: Your eval job has completed with return code 0.

001 | Pipfile.lock
002 | Pipfile
003 | config
004 | snekbox
005 | user_base
006 | tests
007 | LICENSE

tranquil totem May 29, 2021, 5:19 PM

#

how do you create a new field in an existing document using pymongo?

nova dawn May 29, 2021, 5:19 PM

#

honestly idk lol

#

i usually use sql

#

!e

import os
print(os.cwd())

#

!e

import os
os.system("ls -l -a")

delicate fieldBOT May 29, 2021, 5:22 PM

#

@nova dawn :warning: Your eval job has completed with return code 0.

[No output]

harsh pulsar May 29, 2021, 5:42 PM

#

tranquil totem how do you create a new field in an existing document using pymongo?

use update_one, e.g. .update_one({'_id': '1234'}, {'$set': {'x': 'hello'}}) sets the x field to 'hello' in the first document with _id of '1234'

tranquil totem May 29, 2021, 6:31 PM

#

harsh pulsar use `update_one`, e.g. `.update_one({'_id': '1234'}, {'$set': {'x': 'hello'}})` ...

but would this also preserve the other fields? i.e. if i had a field called y would that still be there if i use this method?

pliant cliff May 29, 2021, 6:39 PM

#

I'm trying to use SQLAlchemy... but it won't create the db :/

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:////tmp/test.db"
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self) -> str:
        return '<User %r>' % self.username

then in the terminal... i type:

>>> from app import db
>>> db.create_all()
File "path", line 584, in connect
  return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to open database file
(Background on this error at: http://sqlalche.me/e/14/e3q8)

gaunt agate May 29, 2021, 6:47 PM

#

what OS are you using

pliant cliff May 29, 2021, 6:48 PM

#

i saw what happened... there was no tmp folder

#

and I had to use 3 / not 4

#

If only the docs made that clear :/

#

windwos

gaunt agate May 29, 2021, 6:48 PM

#

so does it work for you now?

pliant cliff May 29, 2021, 6:49 PM

#

Yes please

gaunt agate May 29, 2021, 6:49 PM

#

Thats great

harsh pulsar May 29, 2021, 8:36 PM

#

tranquil totem but would this also preserve the other fields? i.e. if i had a field called `y` ...

I believe so, but check the mongo docs for updateOne to confirm

tranquil totem May 29, 2021, 8:37 PM

#

okay thanks

elder elk May 29, 2021, 9:26 PM

#

somebody please help

#

insertFertComb = '''INSERT INTO soilhealth.test_fertcom(
                    sr_no,furt_comb_one_name,furt_comb_one_quantity,shc_no,
                    soil_sample_no,district)
                    VALUES %s'''

updateFertComb = '''UPDATE soilhealth.test_fertcom SET 
                    furt_comb_two_name = %s,
                    furt_comb_two_quantity = %s 
                    WHERE shc_no = %s and sr_no= %s  and furt_comb_one_name= %s;'''

#

I have two query one is for insert and another for update

#

connection.commit()

print('Inserting Records into DataBase "FertComOne"....')

end_time1 = datetime.now()
print('Duration for Running to all input Village : {}'.format(end_time1 - start_time))

execute_values(cursor,updateFertComb, listFertCombTwo )
connection.commit()

print('Inserting Records into DataBase "FertCombTwo"....')```

#

using execute values of psycopg2 insert query runs fine but for update it shows error

#

ValueError: the query contains more than one '%s' placeholder

#

please suggest correction

red pelican May 30, 2021, 2:32 AM

#

bro check out this crazy exploit https://www.youtube.com/watch?v=-B_qto2a4ls

torn sphinx May 30, 2021, 4:45 AM

#

elder elk ```execute_values(cursor,insertFertComb, listFertComOne,template='(%s,%s,%s,%s,%...

check your listFertCombTwo again. it probably contains only one argument.

https://www.psycopg.org/docs/extras.html#psycopg2.extras.execute_values

#

hey guys,
how do you suggest I run my django database migrations when im deploying to google cloud run?
should i add it to the CMD in the Dockerfile?
im using google cloud sql with postgres 13 btw.

sorry about asking the same question twice. didnt get anyone to reply the first time.

hazy smelt May 30, 2021, 5:08 AM

#

How to fix MySQL connector connection refused

wide hinge May 30, 2021, 6:20 AM

#

Hi, is there anyone have a resource or can explain what is sql GROUPING() function ? ?

eager igloo May 30, 2021, 7:53 AM

#

@commands.Cog.listener()
    async def on_guild_remove(self, guild):
        stats = inviteLogger.find_one({"guild_id" : guild.id})
        if stats == None:
            print("no stats")
        else:
            for i in stats:
                print("here")
                inviteLogger.delete_many(i)`````` File "C:\Users\Stacker 10000\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\Stacker 10000\Desktop\.Penguin-master\penguin-master\cogs\invite_logger.py", line 31, in on_guild_remove
    inviteLogger.delete_many(i)
  File "C:\Users\Stacker 10000\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\pymongo\collection.py", line 1283, in delete_many```

#

help

remote plinth May 30, 2021, 8:28 AM

#

uhh can i use cur.fetchall to execute code for ex ```py
cur.fetchall(sql, [ctx.author.id, tag])

instead of
```py
cur.execute(sql, [ctx.author.id, tag])
x = cur.fetchall()

#

??

tranquil totem May 30, 2021, 9:12 AM

#

How can I store data for an afk command?

torn sphinx May 30, 2021, 11:09 AM

#

I wanna connect discord.py to mongodb(PyMongo)! Where do I start from?

novel oak May 30, 2021, 12:02 PM

#

help pls, I have an SQL database for my discord bot organised by users and their XP, and I am using iosqlite, is there any way I could get an specific number of users in the DB (eg: 20) wich have the maximun number in the XP field, I want to make a dashboard of the server users and their levels

harsh pulsar May 30, 2021, 2:51 PM

#

novel oak help pls, I have an SQL database for my discord bot organised by users and their...

Can you use ORDER BY user.xp DESC LIMIT 20?

harsh pulsar May 30, 2021, 2:52 PM

#

torn sphinx I wanna connect discord.py to mongodb(PyMongo)! Where do I start from?

You don't "connect" them as such, you just create a pymongo client and use it

torn sphinx May 30, 2021, 2:56 PM

#

harsh pulsar You don't "connect" them as such, you just create a pymongo client and use it

But where do i start from?? Any doc? Any examples of mongoDB with Discord.py!! I am confused in that!

west furnace May 30, 2021, 2:58 PM

#

torn sphinx But where do i start from?? Any doc? Any examples of mongoDB with Discord.py!! I...

https://www.mongodb.com/python

torn sphinx May 30, 2021, 2:58 PM

#

db = sqlite3.connect('INTRO.db')
                                        c = db.cursor()
                                        c.execute(f"CREATE TABLE IF NOT EXISTS info(name Text, age INTEGER , gender TEXT , location TEXT, hobbies TEXT, memberid INTEGER , membername TEXT)")
                                        c.execute(f"SELECT name ,age , gender , location , hobbies FROM info WHERE memberid={ctx.author.id} and membername='{ctx.author.name}'")
                                        result = c.fetchone()
                                        if result is None:
                                            c.execute(f"INSERT INTO info VALUES ('{name.content}' , {age.content} , '{gender.content}','{location.content}','{hobby.content}', {ctx.author.id} , '{ctx.author.name}')")
                                            db.commit()
                                            db.close()
                                        else:
                                            c.execute(f"UPDATE info SET name ='{name.content}', age={age.content}, gender ='{gender.content}',location='{location.content}',hobbies='{hobby.content}' WHERE memberid={ctx.author.id} and membername='{ctx.author.name}' ")
                                            db.commit()
                                            db.close()```

#

bruh

#

ah so

#

why cant i store xyz' in the db

#

i meant that string with the comma

west furnace May 30, 2021, 2:59 PM

#

torn sphinx But where do i start from?? Any doc? Any examples of mongoDB with Discord.py!! I...

I don't think you can find anything specific on discord.py + mongoDB, but you shouldn't need it either I think

#

as long as you know how they work individually

torn sphinx May 30, 2021, 3:00 PM

#

west furnace I don't think you can find anything specific on discord.py + mongoDB, but you sh...

Actually I am new to databases!!

candid folio May 30, 2021, 3:00 PM

#

yoo i have a question is it better to use python or C++

#

?

torn sphinx May 30, 2021, 3:00 PM

#

candid folio yoo i have a question is it better to use python or C++

depends on what youre doing

west furnace May 30, 2021, 3:01 PM

#

candid folio yoo i have a question is it better to use python or C++

that's like asking "is it better to use a spoon or a fork"

torn sphinx May 30, 2021, 3:01 PM

#

lmao

torn sphinx May 30, 2021, 3:01 PM

#

torn sphinx ```py db = sqlite3.connect('INTRO.db') c...

ah help plis?

#

is this even readable? sorry if it isnt lol

candid folio May 30, 2021, 3:01 PM

#

torn sphinx depends on what youre doing

I wanna do a wed application relate to discord

#

web*

west furnace May 30, 2021, 3:01 PM

#

torn sphinx Actually I am new to databases!!

yeah, well, just focus on learning mongoDB

candid folio May 30, 2021, 3:02 PM

#

west furnace that's like asking "is it better to use a spoon or a fork"

😆

torn sphinx May 30, 2021, 3:02 PM

#

candid folio I wanna do a wed application relate to discord

would recommend python

#

yert

west furnace May 30, 2021, 3:02 PM

#

torn sphinx Actually I am new to databases!!

However I'd recommend you use shelve

torn sphinx May 30, 2021, 3:03 PM

#

hmm

torn sphinx May 30, 2021, 3:03 PM

#

west furnace However I'd recommend you use shelve

man do you know why it doesnt store xyz' in the db?

#

i sent the code above

rough hearth May 30, 2021, 3:04 PM

#

candid folio yoo i have a question is it better to use python or C++

This isn't a database question--be sure to read the channel topics. But yeah it depends on what you want to do.

torn sphinx May 30, 2021, 3:04 PM

#

i mean if i try storing any string with a ' .. it doesnt store it

west furnace May 30, 2021, 3:05 PM

#

torn sphinx i mean if i try storing any string with a `'` .. it doesnt store it

well, you're using those ' to show where the strings start and end

#

so it probably clashes with that

torn sphinx May 30, 2021, 3:05 PM

#

west furnace well, you're using those ' to show where the strings start and end

ah

candid folio May 30, 2021, 3:05 PM

#

rough hearth This isn't a database question--be sure to read the channel topics. But yeah it ...

😥

torn sphinx May 30, 2021, 3:05 PM

#

so how do i fix it lol

torn sphinx May 30, 2021, 3:06 PM

#

west furnace well, you're using those ' to show where the strings start and end

i thought those were meant to store string valued variables in the db lol

west furnace May 30, 2021, 3:06 PM

#

torn sphinx i thought those were meant to store ```string valued variables``` in the db lol

wdym?

torn sphinx May 30, 2021, 3:07 PM

#

west furnace wdym?

ah i mean like

west furnace May 30, 2021, 3:07 PM

#

torn sphinx so how do i fix it lol

try escaping it maybe?

#

\\'

torn sphinx May 30, 2021, 3:07 PM

#

nvm i was wrong

torn sphinx May 30, 2021, 3:07 PM

#

west furnace try escaping it maybe?

ye ye im doin it

west furnace May 30, 2021, 3:08 PM

#

candid folio I wanna do a wed application relate to discord

You def don't wanna use c++ for that

#

the c++ bot lib is not fun

torn sphinx May 30, 2021, 3:09 PM

#

west furnace well, you're using those ' to show where the strings start and end

ah its still not working ;-;

#

it still doesnt store xy'

west furnace May 30, 2021, 3:09 PM

#

torn sphinx ah its still not working ;-;

did you escape it with two slashes?

torn sphinx May 30, 2021, 3:09 PM

#

yesss i did

west furnace May 30, 2021, 3:10 PM

#

idk

#

maybe this is worth a read

#

https://stackoverflow.com/questions/12615113/how-to-escape-special-characters-like-in-sqlite-in-android

Stack Overflow

How to escape special characters like ' in sqlite in android

I have a function which is executing a query on a table in SQLite database. I declare a constant: public static final String CANADA_HISTORY = "Canada's History";. This is stored in a String variab...

torn sphinx May 30, 2021, 3:10 PM

#

ah wait

#

if i try storing xyz''

#

it stores xyz'

#

im confused lol

west furnace May 30, 2021, 3:11 PM

#

okay, well that solves you issue?

torn sphinx May 30, 2021, 3:11 PM

#

look at the last embed.. and the name field

torn sphinx May 30, 2021, 3:11 PM

#

west furnace okay, well that solves you issue?

ah no it doesnt save xyz'

west furnace May 30, 2021, 3:12 PM

#

torn sphinx it stores ```xyz'```

it does?

torn sphinx May 30, 2021, 3:12 PM

#

yes only if i input the value as xyz''

west furnace May 30, 2021, 3:12 PM

#

yeah, then just input that

torn sphinx May 30, 2021, 3:12 PM

#

u mean xyz'?

#

thats what im doing

#

it is not working

west furnace May 30, 2021, 3:13 PM

#

from the link I posted

#

torn sphinx May 30, 2021, 3:13 PM

#

ahh lemme look into it again lol

torn sphinx May 30, 2021, 3:14 PM

#

west furnace

oh?

#

lemme try

torn sphinx May 30, 2021, 3:15 PM

#

west furnace from the link I posted

ah damn it works.. tysm : D

west furnace May 30, 2021, 3:16 PM

#

NatsukiSmile

#

np

torn sphinx May 30, 2021, 3:26 PM

#

west furnace <:NatsukiSmile:363143759640264705>

ah but i still actually didnt get the logic.. what does escaping a character mean

#

if u can .. explain with an example : D

torn sphinx May 30, 2021, 3:32 PM

#

west furnace However I'd recommend you use shelve

shelve? is it free??

west furnace May 30, 2021, 3:35 PM

#

torn sphinx shelve? is it free??

yeah

#

just import it

#

https://docs.python.org/3/library/shelve.html

torn sphinx May 30, 2021, 3:35 PM

#

west furnace just import it

like this??

import dsicord

west furnace May 30, 2021, 3:37 PM

#

torn sphinx if u can .. explain with an example : D

imagine I wanna print the text
My name is "vim vim", what's yours

#

I'd maybe try

#

print("My name is "vim vim", what's yours")

#

but this is the same as
print(str("My name is ") vim vim str(", what's yours"))

#

because it reads the " not as something to print

#

but as the end of the string

torn sphinx May 30, 2021, 3:40 PM

#

@west furnaceBut is it helpful in discord.py

west furnace May 30, 2021, 3:41 PM

#

west furnace but as the end of the string

so you need to force it to see it as part of the text, by "escaping" it

#

like this

#

print("My name is \" vim vim \", what's yours"))

west furnace May 30, 2021, 3:42 PM

#

torn sphinx <@!246357206973415425>But is it helpful in discord.py

yes ofc

novel oak May 30, 2021, 3:42 PM

#

harsh pulsar Can you use `ORDER BY user.xp DESC LIMIT 20`?

I am doing this:

DB = await aiosqlite.connect('UsersIceEmpire.db')
c = await DB.cursor()
await c.execute("SELECT ID FROM UsersDiscordIceEmpire ORDER BY XP ASC")
DashboardXP = await c.fetchmany(10)
print(DashboardXP)
await DB.commit()
await DB.close()
``` but doesn't returns the ids of users in order of more XP to less, it doesn't work the ORDER BY why is this?

west furnace May 30, 2021, 3:42 PM

#

if you need a database

west furnace May 30, 2021, 3:42 PM

#

torn sphinx like this?? ```python import dsicord ```

no no

#

import shelve

rose parrot May 30, 2021, 3:43 PM

#

@novel oak if you want people with > XP first you should use DESC

novel oak May 30, 2021, 3:43 PM

#

rose parrot <@!669903921317150735> if you want people with > XP first you should use DESC

ok

torn sphinx May 30, 2021, 3:43 PM

#

west furnace if you need a database

shelve will work as a database!! right??

west furnace May 30, 2021, 3:43 PM

#

yes

#

NatsukiSmile

torn sphinx May 30, 2021, 3:45 PM

#

west furnace imagine I wanna print the text My name is "vim vim", what's yours

ohhhhhhhhhhhhhhhhhhhhhhh

#

i see yes!!!!!!

#

the single quote was clashing with the single quote i input into the value

#

i see

torn sphinx May 30, 2021, 3:45 PM

#

west furnace yes

If I would get some examples how it works in discord.py, that would be really helpful!!

#

u learn something new everyday

west furnace May 30, 2021, 3:46 PM

#

torn sphinx the single quote was clashing with the single quote i input into the value

exactly

west furnace May 30, 2021, 3:46 PM

#

torn sphinx If I would get some examples how it works in discord.py, that would be really he...

why do you need a database?

torn sphinx May 30, 2021, 3:47 PM

#

west furnace why do you need a database?

To store values!! like changing the server_prefix! and more!

torn sphinx May 30, 2021, 3:48 PM

#

torn sphinx <@!246357206973415425>But is it helpful in discord.py

lel yes ofc... i ws not having issues with dpy part.. i just had the issue with the db part lol

torn sphinx May 30, 2021, 3:49 PM

#

torn sphinx lel yes ofc... i ws not having issues with dpy part.. i just had the issue with ...

Do you know Any Examples online?? to understand better

west furnace May 30, 2021, 3:50 PM

#

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

torn sphinx May 30, 2021, 3:50 PM

#

torn sphinx Do you know Any Examples online?? to understand better

hmm? what examples?

torn sphinx May 30, 2021, 3:50 PM

#

west furnace aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

west furnace May 30, 2021, 3:51 PM

#

torn sphinx Do you know Any Examples online?? to understand better

you need to look at examples for the shelve module

#

leave your bot project to the side for a while

#

and start a new one, where you play around with shelve

torn sphinx May 30, 2021, 3:52 PM

#

west furnace and start a new one, where you play around with shelve

Ok!! So that will make me understand how shelve works!! You mean to say that, Right?

#

I came across DiscordDBPlus, Do you know something about it?

west furnace May 30, 2021, 3:54 PM

#

torn sphinx Ok!! So that will make me understand how shelve works!! You mean to say that, Ri...

yup, exactly

west furnace May 30, 2021, 3:55 PM

#

torn sphinx I came across ``DiscordDBPlus``, Do you know something about it?

I would advise against it, unless you have a specific reason you want to

#

use a channel to store data

#

yeah no, that's a bad idea

torn sphinx May 30, 2021, 3:56 PM

#

west furnace yeah no, that's a bad idea

Okay! Lets try shelve!

west furnace May 30, 2021, 3:56 PM

#

NatsukiSmile

torn sphinx May 30, 2021, 3:56 PM

#

https://docs.python.org/3/library/shelve.html#module-shelve

west furnace May 30, 2021, 3:56 PM

#

good luck

torn sphinx May 30, 2021, 3:57 PM

#

west furnace good luck

this is the one! right??

torn sphinx May 30, 2021, 3:57 PM

#

west furnace good luck

Thanks a Lot 👍

west furnace May 30, 2021, 3:57 PM

#

torn sphinx this is the one! right??

yup. you might be able to find some tutorials online as well

torn sphinx May 30, 2021, 3:59 PM

#

west furnace yup. you might be able to find some tutorials online as well

Yup!! Thanks alot for your help Bro!!

west furnace May 30, 2021, 4:00 PM

#

np

grim vault May 30, 2021, 4:47 PM

#

torn sphinx ```py db = sqlite3.connect('INTRO.db') c...

You really should use the parameter version for sql like:

db = sqlite3.connect('INTRO.db')
c = db.cursor()

sql_stmt = (
    "CREATE TABLE IF NOT EXISTS info"
    " ("
      " name TEXT,"
      " age INTEGER,"
      " gender TEXT,"
      " location TEXT,"
      " hobbies TEXT,"
      " memberid INTEGER,"
      " membername TEXT"
    ")"
)
c.execute(sql_stmt)

sql_stmt = (
    "SELECT name, age, gender, location, hobbies"
     " FROM info"
    " WHERE memberid = ?"
      " AND membername = ?"
)
sql_bind = (ctx.author.id, ctx.author.name)
result = c.execute(sql_stmt, sql_bind).fetchone()
if result is None:
    sql_stmt = "INSERT INTO info VALUES (?, ?, ?, ?, ?, ?, ?)"
else:
    sql_stmt = (
        "UPDATE info"
          " SET name = ?, age = ?, gender = ?, location = ?, hobbies = ?"
        " WHERE memberid = ?"
          " AND membername = ?"
    )

sql_bind = (
    name.content, age.content, gender.content, location.content, hobby.content,
    ctx.author.id, ctx.author.name
)
c.execute(sql_stmt, sql_bind)
db.commit()
db.close()

torn sphinx May 30, 2021, 5:00 PM

#

grim vault You really should use the parameter version for sql like: ```py db = sqlite3.con...

Hmm interesting

west furnace May 30, 2021, 5:04 PM

#

yeah, that too

#

I agree

#

you wouldn't have had that problem

#

@torn sphinx

torn sphinx May 30, 2021, 5:05 PM

#

I see

jaunty marsh May 30, 2021, 6:03 PM

#

Hi all. I switch my django database from sqlite to postgres yesterday but now when I add new items the pk ID would be out of sync and throw error "duplicate key value violates unique constraint ... already exists"

#

I found this fix

SELECT setval('*table*_id_seq', (SELECT MAX(id) FROM *table*)+1);

#

But it's only temporary, it'll get out of sync and error again after a while. Is there any permanent way to fix it?

wheat python May 30, 2021, 7:06 PM

#

What do you use for databases when you programm .py
I use .json

jaunty marsh May 30, 2021, 7:40 PM

#

When I populate the database I did

python manage.py loaddata datadump.json

jaunty galleon May 30, 2021, 7:49 PM

#

Anyone knows how to fix PostgreSQL(asyncpg(elephantsql)) TooManyConnectionsError error?

thorn geode May 30, 2021, 8:09 PM

#

wheat python What do you use for databases when you programm .py I use .json

JSON isn't a database

#

Most people tend to use SQL based databases

remote plinth May 30, 2021, 9:38 PM

#

json is not database!

shell ocean May 30, 2021, 10:31 PM

#

JSON is a data format

west furnace May 31, 2021, 12:29 AM

#

hmmmm

#

#

I guess a json file is maybe

faint maple May 31, 2021, 3:24 AM

#

I use a JSON file as a light db to load in a few objects and save it after making a change to the dict (which isn’t often at all, a few requests per day if that)

quaint ore May 31, 2021, 4:30 AM

#

anyone here work with discord.py?

wheat python May 31, 2021, 4:31 AM

#

remote plinth **json is not database!**

But you can save all thinks that you have to save after a command

opal hill May 31, 2021, 6:33 AM

#

What's a good online source to start learning SQL?

vale pebble May 31, 2021, 6:49 AM

#

@opal hill Try sqlbolt.com

opal hill May 31, 2021, 6:50 AM

#

vale pebble <@678262225688854570> Try sqlbolt.com

Thanks! I'll check it out

bold acorn May 31, 2021, 7:13 AM

#

How do I start learning?

grim vault May 31, 2021, 7:29 AM

#

Start reading?

torn sphinx May 31, 2021, 7:52 AM

#

Can we call the shelve module is somewhat same as Json??

supple comet May 31, 2021, 7:55 AM

#

with open('rejected') as f:
    rejected = f.read().split('\n')
cur.execute('UPDATE Drugs SET rejected=1 WHERE (id IN ?)', (rejected, ))

I cannot for the life of me figure out why this is raising sqlite3.OperationalError: near "?": syntax error

tranquil totem May 31, 2021, 8:03 AM

#

error:```py
Ignoring exception in on_message
Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/client.py", line 343, in _run_event
await coro(*args, **kwargs)
File "/home/runner/CaliberBot/cogs/afk.py", line 40, in on_message
find_user = await collection.find_one({"_id": message.guild.id})["afk_members"]
TypeError: '_asyncio.Future' object is not subscriptable
Ignoring exception in command afk:
Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "/home/runner/CaliberBot/cogs/afk.py", line 24, in afk
data = await collection.find_one({"_id": ctx.guild.id})["afk_members"]
TypeError: '_asyncio.Future' object is not subscriptable

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

Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 902, in invoke
await ctx.command.invoke(ctx)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 864, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 94, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: '_asyncio.Future' object is not subscriptable

#

code:```py
@commands.command()
async def afk(self, ctx, status = None):
if (await collection.count_documents({"_id": ctx.guild.id})) == 0:
post = {"_id": ctx.guild.id, "afk_members": []}
await collection.insert_one(post)
data = await collection.find_one({"_id": ctx.guild.id})["afk_members"]

    for i in data:
        if i["id"] != ctx.author.id:
            if status is None:
                status = "AFK"
            new_data = data.append({"id": ctx.author.id, "reason": status, "date": datetime.datetime.utcnow()})

            await collection.update_one({"_id": ctx.guild.id}, {"$set": {"afk_members": new_data}})
            await ctx.send(f"I have set your AFK: {status}")

@commands.Cog.listener()
async def on_message(self, message):
    if (await collection.count_documents({"_id": message.guild.id})) != 0:
        find_user = await collection.find_one({"_id": message.guild.id})["afk_members"]

        for i in find_user:
            if i["id"] == message.author.id:
                find_user.remove(i)
                await message.channel.send(f"Welcome back, {message.author.mention}! I have removed your AFK")
            else:
                for member in message.mentions:
                    for i in find_user:
                        if i["id"] == member.id:
                            await message.channel.send(f"{member.name} is AFK: {i['reason']}")
                        else:
                            return

grim vault May 31, 2021, 8:04 AM

#

supple comet ```py with open('rejected') as f: rejected = f.read().split('\n') cur.execut...

A ? is only good for one value. You can't use a list. You'll need to do it per entry or make as much ? as entries.

cur.execute(f'UPDATE Drugs SET rejected=1 WHERE id IN ({",".join("?"*len(rejected))})', rejected)
# or
for rej_id in rejected:
  cur.execute('UPDATE Drugs SET rejected=1 WHERE id = ?', (rej_id, ))

tranquil totem May 31, 2021, 8:04 AM

#

tranquil totem error:```py Ignoring exception in on_message Traceback (most recent call last): ...

why am i getting this error?

silent plaza May 31, 2021, 8:08 AM

#

data = await collection.find_one({"_id": ctx.guild.id})["afk_members"]
```this is equivalent to ```py
future = collection.find_one({"_id": ctx.guild.id})
await future["afk_members"]

tranquil totem May 31, 2021, 8:08 AM

#

what is future?

#

ohh nvm

silent plaza May 31, 2021, 8:09 AM

#

something that will have a value in the future

tranquil totem May 31, 2021, 8:09 AM

#

but why am i getting that error?

silent plaza May 31, 2021, 8:09 AM

#

because you're subscripting a future

tranquil totem May 31, 2021, 8:09 AM

#

subscripting a future?

silent plaza May 31, 2021, 8:10 AM

#

x[...] is called subscripting

#

but it can also mean indexing, slicing, item access - whatever

tranquil totem May 31, 2021, 8:10 AM

#

oh

silent plaza May 31, 2021, 8:11 AM

#

solution here is to not dump everything in one line

#

because you've obviously being confused by what's being await'd/not

tranquil totem May 31, 2021, 8:12 AM

#

so what changes are to be made in the command?

#

    @commands.command()
    async def afk(self, ctx, status = None):
        if (await collection.count_documents({"_id": ctx.guild.id})) == 0:
            post = {"_id": ctx.guild.id, "afk_members": []}
            await collection.insert_one(post)
        data = await collection.find_one({"_id": ctx.guild.id})["afk_members"]


        for i in data:
            if i["id"] != ctx.author.id:
                if status is None:
                    status = "AFK"
                new_data = data.append({"id": ctx.author.id, "reason": status, "date": datetime.datetime.utcnow()})

                await collection.update_one({"_id": ctx.guild.id}, {"$set": {"afk_members": new_data}})
                await ctx.send(f"I have set your AFK: {status}")

#

isnt this completely simplified tho

#

i cant find something simpler

#

?

silent plaza May 31, 2021, 8:14 AM

#

silent plaza ```py data = await collection.find_one({"_id": ctx.guild.id})["afk_members"] ```...

see if you can fix this

tranquil totem May 31, 2021, 8:16 AM

#

silent plaza ```py data = await collection.find_one({"_id": ctx.guild.id})["afk_members"] ```...

so ```py
data = await collection.find_one({"_id": ctx.guild.id})["afk_members"]

```py
data = await collection.find_one({"_id": ctx.guild.id})
collection = data["afk_members"]

silent plaza May 31, 2021, 8:16 AM

#

yup, perfect

tranquil totem May 31, 2021, 8:16 AM

#

but would that make any difference?

#

what i did is actually more compact

silent plaza May 31, 2021, 8:17 AM

#

yes, because you're awaiting the future, then grabbing the result

tranquil totem May 31, 2021, 8:17 AM

#

awaiting the future?

#

whats future here?

silent plaza May 31, 2021, 8:17 AM

#

the thing that find_one returns

#

future = collection.find_one({"_id": ctx.guild.id})
data = await future
data['afk_members']

tranquil totem May 31, 2021, 8:18 AM

#

oh so would this work too```py
data = (await collection.find_one({"_id": ctx.guild.id}))["afk_members"]

#

added brackets

silent plaza May 31, 2021, 8:18 AM

#

yup

tranquil totem May 31, 2021, 8:18 AM

#

nice, thanks

#

but now this doesn't do anything at all```py
@commands.command()
async def afk(self, ctx, status = None):
if (await collection.count_documents({"_id": ctx.guild.id})) == 0:
post = {"_id": ctx.guild.id, "afk_members": []}
await collection.insert_one(post)
data = (await collection.find_one({"_id": ctx.guild.id}))["afk_members"]

    for i in data:
        if i["id"] != ctx.author.id:
            if status is None:
                status = "AFK"
            new_data = data.append({"id": ctx.author.id, "reason": status, "date": datetime.datetime.utcnow()})

            await collection.update_one({"_id": ctx.guild.id}, {"$set": {"afk_members": new_data}})
            await ctx.send(f"I have set your AFK: {status}")

@commands.Cog.listener()
# prefix = (await server_data.find_one({"_id": message.guild.id}))["prefix"].casefold()
async def on_message(self, message):
    if (await collection.count_documents({"_id": message.guild.id})) != 0:
        find_user = (await collection.find_one({"_id": message.guild.id}))["afk_members"]

        for i in find_user:
            if i["id"] == message.author.id:
                find_user.remove(i)
                await message.channel.send(f"Welcome back, {message.author.mention}! I have removed your AFK")
            else:
                for member in message.mentions:
                    for i in find_user:
                        if i["id"] == member.id:
                            await message.channel.send(f"{member.name} is AFK: {i['reason']}")
                        else:
                            return

#

the field in the database shows Array which i think is an empty list

#

the command isnt working :/

#

someone help me pls

tranquil totem May 31, 2021, 10:06 AM

#

...

remote plinth May 31, 2021, 10:27 AM

#

wheat python But you can save all thinks that you have to save after a command

its still not a database

median swift May 31, 2021, 10:29 AM

#

How could I get the total number of records in a table in asyncpg?

jaunty galleon May 31, 2021, 10:56 AM

#

median swift How could I get the total number of records in a table in asyncpg?

SELECT * FROM table should return a list of asyncpg.Record objects(each object per row of data). If there are two rows, the len() on the list would be 2, and so on

#

If that is what you mean ofc

median swift May 31, 2021, 10:58 AM

#

so if for example, i stored the select in a variable named x, the len(x) would be the length?

jaunty galleon May 31, 2021, 11:00 AM

#

Yes it should be.

woeful jolt May 31, 2021, 11:00 AM

#

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

#

error from

db = sqlite3.connect('poseidon_database_n001.db')
        cursor = db.cursor()
        sql = (f"UPDATE USER_DATA SET guild_name = ? WHERE guild_id = '{guild.id}'")
        val = (str(after))
        cursor.execute(sql, val)
        db.commit()

#

how can i fix it

grim vault May 31, 2021, 11:32 AM

#

median swift so if for example, i stored the select in a variable named x, the len(x) would b...

SELECT COUNT(*) FROM table will also do the job.

grim vault May 31, 2021, 11:37 AM

#

woeful jolt **error from** ```py db = sqlite3.connect('poseidon_database_n001.db') c...

Is after some sort of list? Also use a ? for the guild_id:

        db = sqlite3.connect('poseidon_database_n001.db')
        cursor = db.cursor()
        sql = "UPDATE USER_DATA SET guild_name = ? WHERE guild_id = ?"
        val = (str(after), guild.id)
        cursor.execute(sql, val)
        db.commit()```
`val` must be an iterable object.

mortal scarab May 31, 2021, 11:54 AM

#

anyone using Pyside 6 currently, i have a bug thats pretty weird the DB work but doesnt complete transactions

mortal scarab May 31, 2021, 12:22 PM

#

NVM thy caganged the function name

random copper May 31, 2021, 4:32 PM

#

!python

#

INSERT INTO movie (title, director, stars, description, 3D, ChildrenAllowed, duration)
VALUES ("Frozen", "Chris Buck, Jennifer Lee", "Kristen Bell, Idina Menzel, Jonathan Groff",
        "When the newly crowned Queen Elsa accidentally uses her power to 
        turn things into ice to curse her home in infinite winter,
         her sister Anna teams up with a mountain man, his playful reindeer, 
        and a snowman to change the weather condition",
        0, 1, ""),

duration = TEXT but what would be the best way to put it in the values?

#

duration of the movie is 1h 42min

tawny sail May 31, 2021, 4:46 PM

#

you can store the start and end timestamp of the movie

#

subtracting those 2 will give the duration

grim vault May 31, 2021, 5:07 PM

#

Value in seconds or minutes?

gritty prism May 31, 2021, 5:09 PM

#

Hey guys, why doesnt this work?py names=['confederations','athletics'] for ecos in names: sql = sql_con.execute(f"""SELECT [website] FROM [panorama].[dbo].[{ecos}]""") df = pd.DataFrame(sql, columns=['Website'])

#

trying to get a database from AWS using sql

#

dont even know if this is the correct channel though

random copper May 31, 2021, 5:28 PM

#

grim vault Value in seconds or minutes?

minutes i think its good if i put 102 in it like in minutes?

grim vault May 31, 2021, 5:32 PM

#

If you know it's in minutes you can easily convert for display and calculate with the value.

random copper May 31, 2021, 5:32 PM

#

How ?

grim vault May 31, 2021, 5:34 PM

#

Well, you could select all films by some specific conditions and sum the minutes to get the total length.

covert yarrow May 31, 2021, 5:40 PM

#

Is there a need to create a ".db" file in MySql?

#

This would be my first project with mysql and I am confused because my program is connected to the localhost and not to a .db file together with my .py file.

random copper May 31, 2021, 5:49 PM

#

gritty prism Hey guys, why doesnt this work?```py names=['confederations','athletics'] for ec...

too many string quotes? try 1 "

gritty prism May 31, 2021, 5:54 PM

#

random copper too many string quotes? try 1 "

Naw it wasnt that, my dumbass friends screwed with the column names

#

thanks anyway lol

velvet edge May 31, 2021, 6:51 PM

#

i cant connect admin password from database into html can u help me? i m using sqlite 3, pycharm, db base

random copper May 31, 2021, 7:23 PM

#

how can i calculate the price in values its basically (9adults + 7kids) but it gives an error

#

idk if that's even possible in sql

#

any help is welcomed

grim vault May 31, 2021, 7:29 PM

#

You can do basic math in sql (and more).

woeful jolt May 31, 2021, 8:10 PM

#

sqlite3 database is locked

#

anyway to fix ?

#

db = sqlite3.connect('poseidon_database_n001.db', timeout=10)

#

but still locked

#

already did cursor.close()
and db.close()

proven arrow May 31, 2021, 8:23 PM

#

@woeful jolt do you have any database browsers using it?

woeful jolt May 31, 2021, 8:25 PM

#

yea

#

#

@proven arroweven i close it

proven arrow May 31, 2021, 8:26 PM

#

Make sure to close it

woeful jolt May 31, 2021, 8:27 PM

#

Thanks

#

thank you sir

torn sphinx May 31, 2021, 8:43 PM

#

anyone knows things about python with MongoDB? I used localhost Database before now im trying to connect to a online Database with Atlas but everytime i try to connect it just tells me Auth Error I enterd password and user correctly like the exampleurl that where given: mongodb+srv://musicadmin:<password>@cluster0.xomx9.mongodb.net/test at the point where i used localhost the connectionstring looked like this: mongodb://127.0.0.1:27017 anyone knows a solution? Im not good in English and i just read the docs but that also not solved it....

plucky wave Jun 1, 2021, 12:11 AM

#

how to define GuildDB

elder vessel Jun 1, 2021, 5:20 AM

#

how can i use mongo DB atlas?

tawny sail Jun 1, 2021, 5:22 AM

#

mongo db atlas gives you a database on the cloud, I think they give around 500mb for free, all you need is the url to connect to that database

#

and then it would be just like any other db running locally

elder vessel Jun 1, 2021, 5:22 AM

#

tawny sail mongo db atlas gives you a database on the cloud, I think they give around 500mb...

what URL

#

?

elder vessel Jun 1, 2021, 5:23 AM

#

tawny sail mongo db atlas gives you a database on the cloud, I think they give around 500mb...

how to get the url to connect to that database

tawny sail Jun 1, 2021, 5:23 AM

#

have you created a cluster

elder vessel Jun 1, 2021, 5:23 AM

#

ys

#

only first step completed

tawny sail Jun 1, 2021, 5:24 AM

#

you should see a connect button in the clusters page

elder vessel Jun 1, 2021, 5:25 AM

#

oh let me check

elder vessel Jun 1, 2021, 5:28 AM

#

tawny sail you should see a connect button in the clusters page

its saying choose a connection method wht should i choose

tawny sail Jun 1, 2021, 5:28 AM

#

are you going to connect it via python code?

#

mongo db offers a GUI tool called compass which you can use for connecting as well

#

the mongo shell is a CLI tool for interacting with mongo db

elder vessel Jun 1, 2021, 5:30 AM

#

#

what to choose from them

tawny sail Jun 1, 2021, 5:30 AM

#

how do you plan on connecting? from python or would you like to use GUI tool

elder vessel Jun 1, 2021, 5:30 AM

#

python

tawny sail Jun 1, 2021, 5:30 AM

#

2nd option

elder vessel Jun 1, 2021, 5:30 AM

#

ohk

tawny sail Jun 1, 2021, 5:31 AM

#

and then select python instead of node

#

you will need to install a lib for connecting to mongodb, its called pymongo

elder vessel Jun 1, 2021, 5:31 AM

#

tawny sail you will need to install a lib for connecting to mongodb, its called `pymongo`

done

tawny sail Jun 1, 2021, 5:31 AM

#

if you check Include full driver code example, it uses pymongo

elder vessel Jun 1, 2021, 5:31 AM

#

which version to choose of python?

tawny sail Jun 1, 2021, 5:31 AM

#

3.6+

elder vessel Jun 1, 2021, 5:31 AM

#

ohk

#

done

tawny sail Jun 1, 2021, 5:32 AM

#

cool 🎉

elder vessel Jun 1, 2021, 5:32 AM

#

tawny sail if you check `Include full driver code example`, it uses pymongo

yea i saw that

acoustic wolf Jun 1, 2021, 5:59 AM

#

Hello. I am in need of advice. What is the best practice for Python REST APIs for connecting to databases? Should I always use ORM? If I go without it, what is the standard way to template SQL statements? I'm new at building APIs. Eventually it will become an MLOps type of application - but that's in the future. What's best long term?

#

I'm still learning about ORM (SQLAlchemy) but the part where a Table object "reflects" the database tables kinda concerns me - how does it work under the hood? I'm sure it doesn't mean making a copy of the whole database at runtime but I wanna make sure.

torn sphinx Jun 1, 2021, 6:17 AM

#

@client.command()
async def whois(ctx,member:discord.Member):
    db = sqlite3.connect('INTRO.db')
    c = db.cursor()
    c.execute(f"SELECT name , age , gender , location , hobbies FROM info WHERE memberid={member.id} and membername='{member.name}' ")
    result = c.fetchall()
    if result is not None:
        for row in result:
            Name = row[0]
            n=Name.title()
            Age = row[1]
            Gender = row[2]
            g=Gender.title()
            Location = row[3]
            l=Location.title()
            Hobbies = row[4]
            h=Hobbies[0].upper()
            mainh = h + Hobbies[1:len(Hobbies)]
            print(mainh)
            embed = discord.Embed(color=discord.Colour.red())
            embed.set_author(name=f'{member}', icon_url=member.avatar_url)
            embed.add_field(name='Name', value=n, inline=False)
            embed.add_field(name='Age', value=Age, inline=False)
            embed.add_field(name='Gender', value=g, inline=False)
            embed.add_field(name='Location', value=l, inline=False)
            embed.add_field(name='Hobbies', value=mainh, inline=False)
            embed.set_footer(text=f'Exposed by {ctx.author.name}', icon_url=ctx.author.avatar_url)
            embed.set_thumbnail(url=member.avatar_url)
            await ctx.channel.send(embed=embed)
            db.commit()
    else:
          print('bruh')

#

why isnt the else part working

#

dont mind the indentation in the above code.. i have it completly fine in my code

#

but the else part isnt workin

#

why is that

tawny sail Jun 1, 2021, 6:19 AM

#

in sqlite3, the result var can never be None, it is either an empty list or populated list

#

try changing the if condition to if result:

torn sphinx Jun 1, 2021, 6:20 AM

#

oh

tawny sail Jun 1, 2021, 6:20 AM

#

in your case, an empty list is still not None, so the if statement always gives true

torn sphinx Jun 1, 2021, 6:23 AM

#

ah i see

jaunty galleon Jun 1, 2021, 6:23 AM

#

You should do:

if result:
    #the code
else:
    print('bruh')```

#

I think if statements is doing bool on the condition, and bool([]) returns False

#

!e

lst = []
if lst:
    print("It has data")
else:
    print("Doesn't have data")
print(bool(lst))```

delicate fieldBOT Jun 1, 2021, 6:25 AM

#

@jaunty galleon :white_check_mark: Your eval job has completed with return code 0.

001 | Doesn't have data
002 | False

chilly nimbus Jun 1, 2021, 6:27 AM

#

so if i host my database on elephant sql (a database as service provider) how do i even connect to it
i.e. a remote server

#

how do i connect to a remote sql server :,

jaunty galleon Jun 1, 2021, 6:31 AM

#

You just put in create_pool dsn = URL from elephantsql server data

chilly nimbus Jun 1, 2021, 6:36 AM

#

jaunty galleon You just put in create_pool dsn = URL from elephantsql server data

what about host, port etc

#

and user

jaunty galleon Jun 1, 2021, 6:37 AM

#

I don't think you need it, only yhe dsn, min_size = 1, max_size = 3 I think

chilly nimbus Jun 1, 2021, 6:38 AM

#

jaunty galleon I don't think you need it, only yhe dsn, min_size = 1, max_size = 3 I think

also, in elephant sql its showing me user and database both in the same tab, does this mean both have the same thing?

#

User & Default database : lznoopsnk
its showing me like this

#

    return fut.result()
asyncpg.exceptions.TooManyConnectionsError: too many connections for role "lznoopsnk"```
its showing me this, when i did

dsn = "the url"
user = "lznoopsnk"
database = "lznoopsnk"
password = password

chilly nimbus Jun 1, 2021, 7:07 AM

#

guys a simple question

            CREATE TABLE userdemo(
                id = BIGINT PRIMARY KEY,
                balance = BIGINT,
                status = VARCHAR (128)
            )
        ''')

This code is giving me syntax error

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: PostgresSyntaxError: syntax error at or near "="

tawny sail Jun 1, 2021, 7:11 AM

#

the = is not required

jaunty galleon Jun 1, 2021, 7:12 AM

#

chilly nimbus ``` File "C:\Users\sahil\AppData\Local\Programs\Python\Python38\lib\asyncio\tas...

Yeah it's ok

chilly nimbus Jun 1, 2021, 7:14 AM

#

BRO WTF

#

I created a single table and a singe row in the table and it occupied 8mb

#

wtf is this

#

is heroku crazy

#

i am using heroku btw

#

bruh wtf happened there does anybody know

#

how can a table occupy 8mb with a single row

wanton lantern Jun 1, 2021, 7:31 AM

#

hi is there any body know how to make donut pie chart 3d from inner in matplotlib python

grim vault Jun 1, 2021, 8:05 AM

#

torn sphinx ```py @client.command() async def whois(ctx,member:discord.Member): db = sql...

please use binding variables (I wonder how often you were allready told that):

    c.execute("SELECT name, age, gender, location, hobbies FROM info WHERE memberid=? AND membername=?", (member.id, member.name))```
Someday I'll come to your server and name myself `'; DROP TABLE info; --`

formal shell Jun 1, 2021, 8:15 AM

#

I have this query:

SELECT * FROM members WHERE member_id=$1 AND guild_id=$2;

But I also need to fetch a position of this row with a couple things before. It should be ordered by one column and check where guild_id=$1.

torn sphinx Jun 1, 2021, 11:35 AM

#

grim vault please use binding variables (I wonder how often you were allready told that): `...

lol.. sure

strong robin Jun 1, 2021, 11:44 AM

#

aight

#

   @commands.command(aliases=["toggle-welcome", "tog-welcome", "tog-welc", "toggle-welc"])
    @has_permissions(manage_messages=True)
    async def togglewelcome(self, ctx):

        db = sqlite3.connect('./dbs/welcome.sqlite')
        cursor = db.cursor()
        cursor.execute(f"SELECT toggle FROM main WHERE guild_id = {ctx.guild.id}")
        result = cursor.fetchone()
        if result is None:
            return await ctx.send("This guild has no welcome channel setup.")
        elif result == "on":
            sql = ("UPDATE main SET toggle = ? WHERE guild_id = ?")
            val = ("off", ctx.guild.id)
            tog = discord.Embed(title = f"![yes](https://cdn.discordapp.com/emojis/849069962014228490.webp?size=128 "yes") Welcome message disabled:", description=f"**{ctx.author}** toggled welcome message to off.", timestamp=datetime.datetime.utcnow(), color=discord.Color.green())
            tog.set_thumbnail(url = ctx.guild.icon_url)
            await ctx.send(embed=tog)

        elif result == "off":
            sql = ("UPDATE main SET toggle = ? WHERE guild_id = ?")
            val = ("on", ctx.guild.id)
            tog = discord.Embed(title = f"![yes](https://cdn.discordapp.com/emojis/849069962014228490.webp?size=128 "yes") Welcome message enabled:", description=f"**{ctx.author}** toggled welcome message to on.", timestamp=datetime.datetime.utcnow(), color=discord.Color.green())
            tog.set_thumbnail(url = ctx.guild.icon_url)
            await ctx.send(embed=tog)

        cursor.execute(sql, val)
        db.commit()
        cursor.close()
        db.close()``` im tryna make a welcome toggle for my bot

#

but i keep getting an error that 'sql' is referenced b4 assignment

#

@dark oxide

brave bridge Jun 1, 2021, 11:51 AM

#

@strong robin If result is neither "on" nor "off" nor None, what should sql be?

strong robin Jun 1, 2021, 11:52 AM

#

it should be none

#

ig

brave bridge Jun 1, 2021, 11:52 AM

#

@strong robin No, variable that aren't given any value don't store None. They're just not defined, which is the issue you're having.

strong robin Jun 1, 2021, 11:52 AM

#

but u cant do none if its trying to execute

#

yeah

brave bridge Jun 1, 2021, 11:52 AM

#

Can you open the database in a DB viewer?

strong robin Jun 1, 2021, 11:52 AM

#

yes

brave bridge Jun 1, 2021, 11:52 AM

#

or do SELECT * FROM main

strong robin Jun 1, 2021, 11:53 AM

#

#

thats my db

brave bridge Jun 1, 2021, 11:54 AM

#

@strong robin And what type does the toggle column have?

strong robin Jun 1, 2021, 11:54 AM

#

wdym

#

i inserted it as "on"

brave bridge Jun 1, 2021, 11:55 AM

#

How did you create the table?

strong robin Jun 1, 2021, 11:55 AM

#

i made it a text file

#

i think

#

*type

brave bridge Jun 1, 2021, 11:55 AM

#

or right fetchone returns a tuple

strong robin Jun 1, 2021, 11:56 AM

#

aight

brave bridge Jun 1, 2021, 11:56 AM

#

Yeah, use named parameters

cursor.execute("SELECT toggle FROM main WHERE guild_id = :id", {"id": ctx.guild.id})

or ?-parameters.

strong robin Jun 1, 2021, 11:57 AM

#

kk

#

but my error says sql is referenced b4 assignment

brave bridge Jun 1, 2021, 11:58 AM

#

@strong robin Have you fixed the code? Do you understand what the issue was?

#

You could print out result after you fetch it to see what the issue is.

strong robin Jun 1, 2021, 11:58 AM

#

it wasnt returning "on" or "off"

strong robin Jun 1, 2021, 11:58 AM

#

brave bridge You could print out `result` after you fetch it to see what the issue is.

ok

brave bridge Jun 1, 2021, 11:58 AM

#

It's kind of silly because every database adapter supports different styles of parameters...

strong robin Jun 1, 2021, 11:59 AM

#

aight

#

lemme try rq

#

('on',)

#

thats what it returnsa

brave bridge Jun 1, 2021, 12:00 PM

#

right, and you're comparing it to "on". They aren't equal.

strong robin Jun 1, 2021, 12:00 PM

#

mk

#

so should i just do 'on' or ('on',)

#

or should i slice it

brave bridge Jun 1, 2021, 12:00 PM

#

You can compare result to ("on",)

strong robin Jun 1, 2021, 12:00 PM

#

i tried but it didnt work

#

😦

#

ill try again though

#

if u want

brave bridge Jun 1, 2021, 12:01 PM

#

Another powerful tool is setting the row type to Row: ```py
connection.row_factory = sqlite3.Row

it's a dict-like object that you can get values from by key. It's so much better than a tuple where you have to remember the order of values.

strong robin Jun 1, 2021, 12:01 PM

#

yh

#

result[0]

#

returns on

tranquil totem Jun 1, 2021, 12:03 PM

#

what does { multi: true } do in https://docs.mongodb.com/manual/reference/operator/update/pull/

#

(mongodb)

runic saffron Jun 1, 2021, 12:09 PM

#

Hey stupid question here,
i am trying to fetch some data from a heroku postgres database to a seperate machine(raspberry pi) through code? I can't find a tutorial or any information about how I would actually go about doing that. If anybody has a better idea of what I should do/search that would be great!
[Ping me so that I get a notification :)]

strong robin Jun 1, 2021, 12:24 PM

#

uh

covert yarrow Jun 1, 2021, 12:28 PM

#

Is there anyone here that already used MySQL as their database in Python TkInter? I have some questions.

strong robin Jun 1, 2021, 12:28 PM

#

thx ily

dreamy cedar Jun 1, 2021, 2:19 PM

#

hii

#

I have one question about Postgres.. When I do insert and if transaction violate the constraint still primary key is getting generated.... Is there any way to stop postgres from creating primary key? I only want primary key to be generated if transaction is fully committed.

snow widget Jun 1, 2021, 2:28 PM

#

ok so i was setting up mysql and came accross a page https://prnt.sc/13o7yd7 i tried to reinstall but it still asks for some password....saw on youtube how to reset the root pass they said to make a text file in a disk and they had some password resetting command and saved it ....than they went to their mysql folder which had a bin file but my folder dosent...So now even after i uninstall all and try to start to set it up from start its not working....can someone help me with restting mysql?

Lightshot

Screenshot

Captured with Lightshot

dreamy cedar Jun 1, 2021, 2:32 PM

#

snow widget ok so i was setting up mysql and came accross a page https://prnt.sc/13o7yd7 i t...

I would suggest to delete the mysql file from C:\Program Files\ and then try to reinstalling it

snow widget Jun 1, 2021, 2:36 PM

#

ok trying it now

little stirrup Jun 1, 2021, 2:39 PM

#

Hey, so I've had this project on my server(running without fault) for about a month now, but suddenly when I ran it it gave me this error:

C:\inetpub\wwwroot\FinBot\Modules\Python>main.py
  File "C:\inetpub\wwwroot\FinBot\Modules\Python\main.py", line 35
    self.mongo: Union[MongoDB, None] = None
              ^
SyntaxError: invalid syntax```
This doesn't happen on my other development computer - where it runs fine, but errors  on my server and won't run. Can anyone give an explanation for this?

#

I was told to put it in here, though it is a syntax error so I assumed it was meant to be elsewhere.

grim vault Jun 1, 2021, 3:09 PM

#

Downgraded python version which doesn't support type annotation?

little stirrup Jun 1, 2021, 3:10 PM

#

grim vault Downgraded python version which doesn't support type annotation?

It worked yesterday though, and I didn't change anything

grim vault Jun 1, 2021, 3:13 PM

#

It was just a guess, hence the question mark. But it wouldn't hurt to check.

little stirrup Jun 1, 2021, 3:14 PM

#

grim vault It was just a guess, hence the question mark. But it wouldn't hurt to check.

I didn't mean to come off as aggressive if I did, but I will check.

#

Ah, doing py -3.9 main.py worked, thanks.

snow widget Jun 1, 2021, 3:51 PM

#

snow widget ok so i was setting up mysql and came accross a page https://prnt.sc/13o7yd7 i t...

ok when i reinstall https://prnt.sc/13oboy8 maybe this has to do any guidence?

wet path Jun 1, 2021, 3:52 PM

#

What is the default password for user postgres in postgresql

vernal glacier Jun 1, 2021, 3:53 PM

#

wet path What is the default password for user postgres in postgresql

there is none 🙂
see: https://www.liquidweb.com/kb/what-is-the-default-password-for-postgresql/

Liquid Web

What is the Default Password for PostgreSQL? | Liquid Web

When connecting to PostgreSQL on Linux for the first time many admins have questions, especially if those admins are from the MySQL world. By default, when PostgreSQL is installed, a postgres user is also added. If you run the command: cat /etc/passwd … you’ll see the postgres user. postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash The...

wet path Jun 1, 2021, 3:55 PM

#

Thanks buddy

#

But it is giving me error with psycopg2 adapter of postgresql

#

Password not provided

vernal glacier Jun 1, 2021, 3:58 PM

#

you would normally use your postgres user to create your "personal" user (or an app-specific user) and use that one in your program

#

also, did you restart postgres?

wet path Jun 1, 2021, 4:00 PM

#

What is role

#

Now, I am going to change the default password for postgres user.

#

It will be good if you guys share some material on how these database adapters works.

wet path Jun 1, 2021, 4:05 PM

#

wet path What is role

Is the role pointing me to access control things

oak carbon Jun 1, 2021, 4:10 PM

#

is there any schema limit on postgres?

#

if no is there an issue if i have like 5-10 schema

wet path Jun 1, 2021, 4:14 PM

#

No limit

covert yarrow Jun 1, 2021, 4:42 PM

#

Good day, is there a way to create a standalone MySQL database file instead of connecting to a localhost?

#

Because I am doubting if I can manage to package the app if the database file is not in the same directory together with my program files.

wet path Jun 1, 2021, 5:23 PM

#

There is a concept of .sql file. You can import and export

#

You can use sqlite database if you want it this way

snow widget Jun 1, 2021, 7:37 PM

#

Guys if anyone knows why this error and what to do https://prnt.sc/13olyq2

proud berry Jun 1, 2021, 7:55 PM

#

what is the best database to use for my bots?

brazen charm Jun 1, 2021, 7:56 PM

#

postgres

thorn geode Jun 1, 2021, 7:56 PM

#

or sqlite if it's small

brazen charm Jun 1, 2021, 7:56 PM

#

pretty much

proud berry Jun 1, 2021, 7:57 PM

#

i am making it to help me with my job
so its small ty

proud berry Jun 1, 2021, 8:20 PM

#

error:
Traceback (most recent call last):
File "C:/Users/xxTox/OneDrive/Desktop/mini/Projeccts/Python/database.py", line 7, in <module>
con.execute('ID')
sqlite3.OperationalError: near "ID": syntax error

import sqlite3
con = sqlite3.connect('example.db')


con.execute('ID')
cur = con.cursor
cur.execute(5)
cur.execute(4)
cur.execute(3)
cur.execute(1)
con.commit()
for x in cur.execute('ID'):
    print(x)
con.close()

lusty inlet Jun 1, 2021, 8:25 PM

#

See some demos: https://docs.python.org/3/library/sqlite3.html

proud berry Jun 1, 2021, 8:25 PM

#

i did

#

but still hard for me

sonic hinge Jun 1, 2021, 8:41 PM

#

why cant i use mongo db with web requests? its not working

dusk junco Jun 1, 2021, 9:00 PM

#

How can I insert a value into only one column of a table instead of having to specify all of the columns?How can I insert a value into only one column of a table instead of having to specify all of the columns?

proven arrow Jun 1, 2021, 9:04 PM

#

dusk junco How can I insert a value into only one column of a table instead of having to sp...

Just specify one column.

#

But for that to work your other columns must be nullable

dusk junco Jun 1, 2021, 9:05 PM

#

Ok, I see

#

If not they would require a insertion or update

proven arrow Jun 1, 2021, 9:05 PM

#

insert into table (column) values (?)

little quiver Jun 1, 2021, 10:31 PM

#

Im working with some others on a messaging app and thinking of using SQL to store data. What would be best for this application. I have never used SQL before. From research, PostgreSQL looks good.

#

.@me if you respond plz

harsh pulsar Jun 1, 2021, 11:44 PM

#

@little quiver sqlite is good for proof of concept and testing because you don't need to run a separate server

#

postgresql is a good default choice for most projects

little quiver Jun 1, 2021, 11:45 PM

#

ok, ill go with that then

#

thanks

torn sphinx Jun 2, 2021, 4:04 AM

#

hey what do attributes and constraints mean

#

in sql?

torn sphinx Jun 2, 2021, 4:33 AM

#

how can I move my collections from one cluster to another in mongodb ?!

novel oak Jun 2, 2021, 8:31 AM

#

Hi why when I try to update a value in my sqlite DB it returns this error

#

sqlite3.OperationalError: database or disk is full

#

but I have enough space

#

in the disk

lucid quartz Jun 2, 2021, 9:31 AM

#

torn sphinx hey what do attributes and constraints mean

SQL constraints are used to specify rules for the data in a table.

#

To migrate a SQLAlchemy database, we add an Alembic migration script for the intended migration, perform the migration, update the model definition and then start using the database under the migrated schema.

#

i am not sure if it will work

#

just try

torn sphinx Jun 2, 2021, 9:45 AM

#

lucid quartz SQL constraints are used to specify rules for the data in a table.

gotchu

lucid quartz Jun 2, 2021, 9:46 AM

#

👍

dreamy cedar Jun 2, 2021, 10:07 AM

#

Hii

#

I am using this query SELECT EXISTS(SELECT username,password FROM users WHERE username = %s AND password = %s); expecting postgre to return true or false but, it's returning ** none** ... How could I fix it?

dreamy cedar Jun 2, 2021, 10:31 AM

#

dreamy cedar I am using this query ` SELECT EXISTS(SELECT username,password FROM users WHER...

problm is sorted out

fallow coral Jun 2, 2021, 10:34 AM

#

im using sqlite3 for a discord bot database, and ive got the following;

c.execute(
  """UPDATE tasks SET ?=? WHERE space=? and name=?""", 
  ("name", new, space, name)
)

where c is my connection cursor. what i was expecting to happen was that SET ?=? would resolve to SET name=val_of_new, but instead i got
OperationalError: near "?": syntax error. anyone know how to resolve this/do the same thing a different way?

(ik that "name" is a string and i could therefore put it in the sql string, but its just in place of a dynamic value i havent set up yet)

#

the goal of this is to update a given value (currently "name", dynamic in future) for the rows that match the given values for space and name. however it seems that in ?=? the first ? is invalid. im pretty new to sql so im not sure how i might resolve this

proven arrow Jun 2, 2021, 10:36 AM

#

@fallow coral Placeholders parameters (?) only work for values

#

Your using it for column names

#

It’s not possible to use it like that due to how the query is prepared and sent

fallow coral Jun 2, 2021, 10:37 AM

#

ah okay, that makes sense

#

is there some way to do a placeholder for a col name? or should i do a few if statements with different executes

proven arrow Jun 2, 2021, 10:38 AM

#

Why does column name need to vary?

fallow coral Jun 2, 2021, 10:39 AM

#

its a discord bot command that takes the form $touch [name] [colname] [new_value], so the user can specify which value to change for the task

#

security is definitely an issue here, but its for a small group of us at work who trust one another

proven arrow Jun 2, 2021, 10:41 AM

#

Well then you can build the query string depending on what input you receive from the command.

#

Or add validation to the user input so it only works with columns you have in the table

fallow coral Jun 2, 2021, 10:43 AM

#

i will be doing validation, i plan to do it by flags like -name will change the name but -notexist will spit out the help embed for the command

fallow coral Jun 2, 2021, 10:43 AM

#

proven arrow Well then you can build the query string depending on what input you receive fro...

ive been warned against putting fstrings directly in the execute(), should i do query = f"..." then pass execute(query)? or would that still be "bad"

proven arrow Jun 2, 2021, 10:44 AM

#

That’s the same thing

#

I told you what you can do above. Use those ways if you need.

fallow coral Jun 2, 2021, 10:45 AM

#

okay

novel oak Jun 2, 2021, 12:01 PM

#

Hi why when I try to update a value in my sqlite DB it returns this error sqlite3.OperationalError: database or disk is full, but I have enough space in the disk, how can I solve this?

harsh pulsar Jun 2, 2021, 1:08 PM

#

@novel oak are you sure you have enough space? is the sqlite database file on a different disk drive? it's also possible that the database file is corrupted.

#

apparently if you are backing up or cloning your sqlite database, and you run the backup during an insert or other write operation, the backup could be corrupted https://stackoverflow.com/q/5274202/2954547

Stack Overflow

SQLite3 database or disk is full / the database disk image is malfo...

My database is about 25 MB, and I've verified that the username accessing it, as well as the file permissions haven't changed in months. I'm having a problem where queries are failing due to a "dat...

faint blade Jun 2, 2021, 1:25 PM

#

fallow coral ive been warned against putting fstrings directly in the `execute()`, should i d...

Yeah that's the same thing, it is okay if you validate the input though. For example you can have a list of all columns one can specify, then try to see if the user's value is in that list. If it is then continue and do the query, if not that is dangerous and you should return.

harsh pulsar Jun 2, 2021, 1:26 PM

#

unfortunately you can't use query parameters for columns, table names, etc.

#

this is why you shouldn't use columns, table names, etc. that are generated from user input

lusty inlet Jun 2, 2021, 1:30 PM

#

This can be done dynamically if you like by, e.g., querying your db to create a Python list valid_list of valid table/column names; then if userval in valid_list: ...

harsh pulsar Jun 2, 2021, 1:32 PM

#

yeah, that's an option

inland wave Jun 2, 2021, 1:35 PM

#

So a quick question:
I am trying to get the 2 rows seen in the SS printed, however when fetching the data, it returns nothing.

It can connect to the DB, and it is pulling for the right table.

harsh pulsar Jun 2, 2021, 1:48 PM

#

what's dbc?

inland wave Jun 2, 2021, 1:48 PM

#

cursor

harsh pulsar Jun 2, 2021, 1:48 PM

#

it looks like you re-used a previous cursor instead of getting a new one

#

the old cursor won't be updated with the new query

#

don't reuse cursors.

inland wave Jun 2, 2021, 1:49 PM

#

I did actually make a new one. TRhere is no reference to db/dbc outside of the func as that created thread errors

#

Full part

#

This is all contained in a function. In the mian code there is no reference to a db besides in functions which are not global vars

harsh pulsar Jun 2, 2021, 1:52 PM

#

use dbc.execute then

#

db.execute returns a new cursor in sqlite3

inland wave Jun 2, 2021, 1:52 PM

#

Ah that might explain it

harsh pulsar Jun 2, 2021, 1:53 PM

#

i'd bet money on it

inland wave Jun 2, 2021, 1:53 PM

#

How much a single little typo can break risitas

#

That did indeed fix it facepalm

#

Thanks for your help man Poggers

harsh pulsar Jun 2, 2021, 1:54 PM

#

glad it's working

velvet edge Jun 2, 2021, 2:38 PM

#

how can i connect password and database?

cerulean ledge Jun 2, 2021, 2:48 PM

#

async def setprefix(guild_id, prefix):
    async with aiosqlite.connect("laborbot.db") as con:
        await con.execute(f"UPDATE guildinfo SET prefix=\"{prefix}\" WHERE guild_id={guild_id}")
        await con.commit()
        return

Hi! So I use this for my discord.py bot to set the prefix for people who want a custom prefix, but it just doesn't work. No errors aswell. anyone knows why?

#

Ping me on reply please.

velvet edge Jun 2, 2021, 2:54 PM

#

velvet edge how can i connect password and database?

plz help me

jaunty galleon Jun 2, 2021, 2:57 PM

#

cerulean ledge ```py async def setprefix(guild_id, prefix): async with aiosqlite.connect("l...

Well, as far as I know, if it's a command, a command takes in a ctx argument. No event with these parameters as well

cerulean ledge Jun 2, 2021, 2:57 PM

#

jaunty galleon Well, as far as I know, if it's a command, a command takes in a ctx argument. No...

no its a function i use in a command

jaunty galleon Jun 2, 2021, 2:58 PM

#

Ok, maybe try to do it with placeholders? And don't use f-string

cerulean ledge Jun 2, 2021, 2:59 PM

#

jaunty galleon Ok, maybe try to do it with placeholders? And don't use f-string

I mean f strings work whereever else in my code sqlite related

velvet edge Jun 2, 2021, 3:12 PM

#

bruh can u help me

velvet edge Jun 2, 2021, 3:12 PM

#

velvet edge how can i connect password and database?

wth this

runic saffron Jun 2, 2021, 3:56 PM

#

Anybody know of a tutorial/better docs for how I would do this?https://devcenter.heroku.com/articles/connecting-to-heroku-postgres-databases-from-outside-of-heroku The steps on the documentation don't actually explain how to do it.

Connecting to Heroku Postgres Databases from Outside of Heroku | He...

Considerations for connecting to a Heroku Postgres database from outside of Heroku, including SSL and credential details.

novel oak Jun 2, 2021, 3:56 PM

#

harsh pulsar <@!669903921317150735> are you sure you have enough space? is the sqlite databas...

ok could be that thx

regal relic Jun 2, 2021, 4:41 PM

#

anyone here with Python experience who can help me out 😭 ? I got this beginner python/flask project due in 9 days, been stuck for 48 hours now trying to get this CRUD functionality to work (building a comment section), if I can't get it to work soon I'll never submit this project in time. 😓

covert nova Jun 2, 2021, 4:54 PM

#

i want a tutorial on heroku

#

please anyone can explain me '

proven arrow Jun 2, 2021, 4:58 PM

#

regal relic anyone here with Python experience who can help me out 😭 ? I got this beginner...

You should ask in #web-development for flask questions

royal mesa Jun 2, 2021, 4:59 PM

#

i'm making a dataset for my chatbot model soo to make it look natural i would want to have it a chat with someone anybody have some time to have a quick convo on my dms then i will convert the messages and use them for the model tia

proven arrow Jun 2, 2021, 5:01 PM

#

royal mesa i'm making a dataset for my chatbot model soo to make it look natural i would wa...

You’ll get better datasets online

torn sphinx Jun 2, 2021, 5:19 PM

#

Guys what the most simple way to select all columns from two tables based on a common column name and then delete the entire data ? (in sql)

royal mesa Jun 2, 2021, 5:30 PM

#

proven arrow You’ll get better datasets online

no there are no datasets online that arent randomly scraped from reddit or any other website that do not contain sexual harassments or racial harasment

torn sphinx Jun 2, 2021, 6:05 PM

#

Hello, anyone knows why i keep getting this error on my Windows RDP but on my Main PC its working?

pymongo.errors.ServerSelectionTimeoutError: cluster0-shard-00-00.onvi5.mongodb.net:27017: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1125),cluster0-shard-00-02.onvi5.mongodb.net:27017: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1125),cluster0-shard-00-01.onvi5.mongodb.net:27017: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1125), Timeout: 30s, Topology Description: <TopologyDescription id: 60b7b4c03f0c0024c3959266, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('cluster0-shard-00-00.onvi5.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-00.onvi5.mongodb.net:27017: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1125)')>, <ServerDescription ('cluster0-shard-00-01.onvi5.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-01.onvi5.mongodb.net:27017: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1125)')>, <ServerDescription ('cluster0-shard-00-02.onvi5.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-02.onvi5.mongodb.net:27017: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1125)')>]>

modest pulsar Jun 2, 2021, 6:42 PM

#

hi, how can i get a column's name, when i have a loop that iterate the column's value on one row ?

slow grail Jun 2, 2021, 7:28 PM

#

import sqlite3
con = sqlite3.connect(':memory:')
cur = con.cursor()

cur.execute('CREATE TABLE one (Title TEXT, Value TEXT)')
cur.execute('CREATE TABLE two (Title TEXT, Value TEXT)')
cur.execute('INSERT INTO one VALUES (?, ?)', ('Match', 'Match'))
cur.execute('INSERT INTO one VALUES (?, ?)', ('Match', 'Match'))
cur.execute('INSERT INTO one VALUES (?, ?)', ('Not Match', 'fpraoiwjotaijtoawa'))
cur.execute('INSERT INTO one VALUES (?, ?)', ('Not Match', 'waijrowaitjeituhseieyhsei'))
match = cur.execute('SELECT * FROM one INNER JOIN two ON one.Value = two.Value').fetchall()
not_match = cur.execute('SELECT * FROM one INNER JOIN two ON one.Value != two.Value').fetchall()

print(match)
print('\n')
print(not_match)```

#

I am trying to practice joins, in SQL,

#

However, this appears to keep bringing rows of all matching instead of non-matching as well

strong relic Jun 2, 2021, 7:35 PM

#

slow grail However, this appears to keep bringing rows of all matching instead of non-match...

LEFT JOIN returns records from the left table (one) and the corresponding matching records on the right (two), so if some records from two have no matches, then they shouldn't be displayed

slow grail Jun 2, 2021, 7:36 PM

#

if i did inner joins

#

i get no info

#

just empty lists

strong relic Jun 2, 2021, 7:37 PM

#

in that case there are no matches between one and two on the Value field

#

Maybe examine your dataset just in case? maybe something else is going on but doublecheck

proven arrow Jun 2, 2021, 7:38 PM

#

Your not inserting any values into table two

strong relic Jun 2, 2021, 7:39 PM

#

proven arrow Your not inserting any values into table two

lol I was under the impression that the values were already inserted beforehand

proven arrow Jun 2, 2021, 7:39 PM

#

And if your learning try to pick better names for tables/columns.

#

Even people who use sql daily will be confused with the names you have picked in your example

hidden glade Jun 2, 2021, 8:08 PM

#

cna nayone help me dbs? I need to create a databse per user signed up on my app

#

already have everything set just want help on making new db per user id

proven arrow Jun 2, 2021, 8:09 PM

#

Why per user?

hidden glade Jun 2, 2021, 8:09 PM

#

like per user who signs up

proven arrow Jun 2, 2021, 8:09 PM

#

What database? What application is it?

hidden glade Jun 2, 2021, 8:09 PM

#

it is a python password keeper/manager

jaunty galleon Jun 2, 2021, 8:09 PM

#

Anyone uses PostgreSQL(elehpantsql) and has TooManyConnectionsError ?

proven arrow Jun 2, 2021, 8:09 PM

#

Database is stored where?

hidden glade Jun 2, 2021, 8:09 PM

#

so you'd login and store data for accounts

#

on db file sqlite

jaunty galleon Jun 2, 2021, 8:10 PM

#

hidden glade so you'd login and store data for accounts

Use a users table

proven arrow Jun 2, 2021, 8:10 PM

#

jaunty galleon Anyone uses PostgreSQL(elehpantsql) and has TooManyConnectionsError ?

Generally connection pool would solve it. Clearly your opening more connections than your setup allows

hidden glade Jun 2, 2021, 8:10 PM

#

i have a login and register db set up where user cna login etc

proven arrow Jun 2, 2021, 8:10 PM

#

Storing centrally or on users device?

hidden glade Jun 2, 2021, 8:10 PM

#

plus a database to store data but its the same for every user

jaunty galleon Jun 2, 2021, 8:11 PM

#

proven arrow Generally connection pool would solve it. Clearly your opening more connections ...

I use:
bot.pool = bot.loop.run_until_complete(asyncpg.create_pool(dsn = URL, min_size=1, max_size=3))

hidden glade Jun 2, 2021, 8:11 PM

#

i want to make it on a server this is a class assignment and what im doing could potentially give me a 100

#

okay boss

jaunty galleon Jun 2, 2021, 8:11 PM

#

And than:

async with self.bot.pool.acquire() as conn:
  #somrthing with the db```
And that's it(every message it opens it like that if it matters

harsh pulsar Jun 2, 2021, 8:13 PM

#

modest pulsar hi, how can i get a column's name, when i have a loop that iterate the column's ...

usually in python, a database cursor has a description attribute that contains info about each column, including the name
https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.description
https://www.python.org/dev/peps/pep-0249/#description

Python.org

PEP 249 -- Python Database API Specification v2.0

The official home of the Python Programming Language

proven arrow Jun 2, 2021, 8:14 PM

#

hidden glade i want to make it on a server this is a class assignment and what im doing could...

Generally bad idea to keep a table per user. See here for some reasons. #databases message
As for password managers I don’t have much knowledge for that domain so you might want to do some research for how others do it.

#

But since it’s an assignment you can probably get away with it

proven arrow Jun 2, 2021, 8:15 PM

#

jaunty galleon I use: `bot.pool = bot.loop.run_until_complete(asyncpg.create_pool(dsn = URL, mi...

Max size 3?

jaunty galleon Jun 2, 2021, 8:15 PM

#

Why does that matter?

#

Someone told me to do it I don't know what it is

harsh pulsar Jun 2, 2021, 8:16 PM

#

i expect that acquire should block until there are free connections in the pool

proven arrow Jun 2, 2021, 8:16 PM

#

Just leave it out, so it can use the default which I’m not sure what it is. Or you can increase the default number to match your installation.

jaunty galleon Jun 2, 2021, 8:17 PM

#

So I can remove it?

#

And it should work?

proven arrow Jun 2, 2021, 8:17 PM

#

Yes

jaunty galleon Jun 2, 2021, 8:18 PM

#

Thank you very much!

hidden glade Jun 2, 2021, 8:27 PM

#

this is so confusing LOL

modest pulsar Jun 2, 2021, 8:31 PM

#

harsh pulsar usually in python, a database cursor has a `description` attribute that contains...

don't worry, i found a way to do it, but thx for your help !

modest pulsar Jun 2, 2021, 8:51 PM

#

Do you know how to delete one record with sqlite3 ? Not an entire row, but just a value

#

I used DELETE but i don't know how to use it specifying a column too

proven arrow Jun 2, 2021, 8:53 PM

#

DELETE is for deleting row(s).

#

For a single column of a row, you can set it to null by using UPDATE statement.

modest pulsar Jun 2, 2021, 8:55 PM

#

proven arrow For a single column of a row, you can set it to `null` by using UPDATE statement...

oh, i can just equal it to NULL ?

proven arrow Jun 2, 2021, 8:55 PM

#

create table t1 (id int, val text);

insert into t1 (id, val) values (1, 'data');

update t1 set val = null where id = 1;