databases

[proven arrow]

Python None type should do it

[modest pulsar]

no, i already tried

i didn't know i could equal it to null

thx

[runic saffron]

Getting a weird error psycopg2.ProgrammingError: no results to fetch when i try and fetch data from my heroku/postgres database if you could help that would be great!
question: https://stackoverflow.com/questions/67812675/heroku-postgres-psycopg2-programmingerror-no-results-to-fetch

[mystic bluff]

any guy that know a way to scrap on pastebin without the scrap api

or just sell me a pro account

[proven arrow]

You didn’t execute any query to select, so the error is correct. There is no data to fetch, because you never asked for any from the database.

[runic saffron]

I am really new so sorry for the dumb question but what should i actually do?

[proven arrow]

See this, https://www.psycopg.org/docs/usage.html
You can see how they execute a select statement before fetching

[runic saffron]

I am still confused as to what it should look like, is there anything I should take out of my code or just stuff to add?

[proven arrow]

Do you understand what fetch_x() does?

[runic saffron]

only vaguely

[proven arrow]

Not sure what you are trying to do because your print statement says connection established and then your trying to print some data

[proven arrow]

When you query the database for some data, using a select statement it returns some data back as rows. Fetch is for getting that data.

[runic saffron]

that is just a test statement

[runic saffron]

so i need to fetch from a specific table? can i just pick a random row from a random table?

to test that it works?

[proven arrow]

You can add cursor.execute(“SELECT 1;”)

[runic saffron]

and that would just find something?

[proven arrow]

It’ll give back value 1

[runic saffron]

how does that help me? I am trying to fetch some actual data?

[proven arrow]

Then do it, if you know what you want why are you even asking

[mystic bluff]

.

[runic saffron]

because I don't know how to do it?

[proven arrow]

Don’t know how to do what? Select from a table?

[ember yew]

Hey guys. This must sound very stupid but I am new in Python and I want to know hoy to put a list inside a dictionary

[proven arrow]

They have an api for that, no?

[mystic bluff]

without the api

or just sell me a pro account

u need it to use the scrap api

[proven arrow]

my_dict = {‘a_list’ : [1,2,3]}

[mystic bluff]

ik that i can automate google chrome to search keywords but its a very messy

[proven arrow]

Yeah I don’t think anyone would sell it here.

[ember yew]

thx!!!

[simple geyser]

Could anyone check #help-ramen for my question? It's appreciable

[exotic sand]

When using SQL++, if you have a statement

FROM table1 AS T1, table2 AS T2

Is there an implicit join happening like there would be in SQL?

[harsh pulsar]

@exotic sand it appears that way https://ci.apache.org/projects/asterixdb/sqlpp/manual.html

[exotic sand]

Thanks @harsh pulsar

[hidden glade]

could someone here help me with sqlite on python?
I am making a password keeper and you'd have to login or register
I have made a login and registration system with a database and also when user logs in
you can put in the info you want to save like account usernames and password
what i need help with is I want to create a database for each user who signs/registers in
so everyone has different data instead of all users having same data saved LOL

[torn sphinx]

create an ACCOUNTS table

give it username, password, etc as columns

[jaunty galleon]

Isn't it delete_one ?

[delicate field]

Hey @flat fox!

It looks like you tried to attach a Python file - please use a code-pasting service such as https://paste.pythondiscord.com

[torn sphinx]

yo

[cyan yacht]

I'm using aiomysql
Is it possible to subclass aiomysql.connection.Connection and connect to that?

[torn sphinx]

@bot.group(name="inv", invoke_without_command=True)
async def inv(ctx, user: discord.Member = None):
    if user == None:
        user = ctx.author

    cursor.execute("select durapot, healpot, vigopot, quagun, enebazo, molecule, orb, box from users where client_id = %s", [user.id])
    result = cursor.fetchall()
    dpi = result[0][0]
    hpi = result[0][1]
    vpi = result[0][2]
    qgi = result[0][3]
    ebi = result[0][4]
    moi = result[0][5]
    ori = result[0][6]
    boi = result[0][7]

    if len(result) == 0:
        await ctx.send("Just Started? run ``.rpg info`` to learn more about the game 👌️")
    else:
        pf = discord.Embed(title="", description="", color=random.choice(colors))
        pf.set_author(name=f"{user.name}'s inventory", icon_url=user.avatar_url)
        pf.add_field(name="Items:", value=f"{dpe} Durability Potions ─ {dpi}\nID → ``dp``\n_ _", inline=True)
        pf.add_field(name="    _ _ ", value=f"    _ _", inline=True)
        pf.add_field(name="Collectibles", value=f"{ame} Molecule ─ {moi}\nID → ``molecule``", inline=True)
        pf.add_field(name="_ _", value=f"{hpe} Health Potions ─ {hpi}\nID → ``hp``\n_ _", inline = True)
        pf.add_field(name="    _ _ ", value=f"    _ _", inline=True)
        pf.add_field(name="Market Items", value=f"UNDER DEV", inline=True)
        pf.add_field(name="_ _", value=f"{vpe} Vigour Potions ─ {vpi}\nID → ``vp``\n_ _", inline = False)
        pf.add_field(name="_ _", value=f"{woe} Witch's Orbs ─ {ori}\nID → ``orb``\n_ _", inline=False)
        pf.add_field(name="_ _", value=f"{qge} Quantum Guns ─ {qgi}\nID → ``qg``\n_ _", inline = False)
        pf.add_field(name="_ _", value=f"{ebe} Energy Bazookas ─ {ebi}\nID → ``eb``\n_ _", inline = False)
        pf.add_field(name="_ _", value=f"{mbe} Mystery Boxes ─ {boi}\nID → ``box``\n_ _", inline = False)

my inventory command, i just can't figure out how to only display the items which are owned

[upper basin]

is there an sqlite version of NoSQL/mongodb, where i dont have to connect to a server and it can just be stored locally?

[proven arrow]

SQLite and mongodb are two completely opposite things.

[upper basin]

....yes i know

im asking if there's something like sqlite for Nosql

where its stored locally

[proven arrow]

Tinydb I think

[upper basin]

ahh, thank you!

[proven arrow]

Never used it so you’ll have to double check but I’m sure it does what you ask for

[upper basin]

yupp, just reading through the docs, this is perfect

thank you!

[topaz wharf]

How do I avoid/remove duplicates from mongodb?

[dreamy cedar]

Hii i have question regarding postgres

Is it allowed to use one DML statements inside another DML statement as a subqurey ?

Like;

INSERT INTO utility(used_quantity) VALUES (10) WHERE utility_ID = 1

So, what I intend to do is that * VALUE (10) or whatever value which is utilized should be debited from another table called items.

[strong relic]

if you intend to copy data from one table to another, you can have a select statement inside the insert statement, so yes you can have a subquery

[hidden glade]

how can i print data based on which user is logged on with python & mysql

i already have the data stored, but every user has the same data if you get me\

[jovial orchid]

I'm using boto3 to insert rows into glue db with INSERT INTO statement but when i retrieve the table, it did not update. Anyone have experience with this?

[proven arrow]

How can every user have the same data? Surely something is wrong with your design. It’s like giving everyone the same social security number.

You need a way to uniquely identify each user.

[hidden glade]

No I mean there is one table and every user can see it or they all have the same one

I want to make an individual one for each user

[proven arrow]

Users can only see what you show them.

Not sure how they can all have the same data too.

And you don’t make a new table for each user. It’s just not worth it.

[hidden glade]

Ah yes so I would just link each input to a user ID

[proven arrow]

You can use an auto incrementing primary key as a way to uniquely identify each user. This can server as your user_id for your app.

[smoky tartan]

is there a way to use df.replace to replace more than one thing at a time? for example I want two strings to be lets say 1 then two other strings to be 2 in one line(all from the same column)

[strong relic]

df['column'].replace(x, 1).replace(y, 2)

[harsh pulsar]

(assuming this is pandas) it'd be nice if .map had a "passthrough" option

replacements = {'x': '1', 'y': '2'}
df['column'].map(lambda val: replacements.get(val, val)}

this could work too

[strong relic]

this is also a good solution, but it has more overhead and would be more difficult to understand as a one-liner

[harsh pulsar]

you can reduce the variable lookup overhead with

df['column'].map(lambda val: {'x': '1', 'y': '2'}.get(val, val)}

and i would argue that this could be more efficient on a huge dataset where you don't want to make 2 passes over the data (would be interesting to benchmark). harder to read and understand, i agree with

again, another reason why .map lacking a passthrough option is annoying

using a defaultdict or other class with a __missing__ method allows for a default value, but not a passthrough value

[strong relic]

I do agree with your points, on absolutely massive datasets I would avoid using replace due to the double pass issue, I would probably declare a separate dictionary (or defaultdict as you suggested), use map, and encapsulate all this logic into a function in the case of incoming batch data or something that has to be dealt with periodically

[harsh pulsar]

sadly it seems like the pandas devs are more or less afraid to touch .map and .apply

there's been an feature request for years to port the na_action= kwarg from .map to .apply

and the answer is basically "no, apply is really complicated already and we don't want to make it more complicated"

i've looked at the pandas source code, it's way more complicated than i expected. pretty unfortunate

[strong relic]

Definitely, I'm sure there's background reasons though, it could be that the data pipelines code of a lot of companies revolve around using .map and .apply to their limits, so making changes could require tons of refactoring on the programmer end. But it's not like the developers/maintainers do anything else anyways

[harsh pulsar]

i've looked at the pandas internals

it's alarmingly dense and complicated

so i don't blame them for not wanting to make it more complicated. frankly i don't understand why it has to be so complicated, but i assume it was done that way for a good reason

as usual, open source project is grossly underfunded relative to how many billions of dollars in value are generated on its back every year

[strong relic]

Exactly, it's no surprise since it's an incredibly popular python library. Tons of users also means tons of contributors and features, to keep it cutting edge, for various applications, so I'm not surprised that its as dense as it is

[little quiver]

im making a messaging app and im using postgresql. What would be the best way to store conversations? they could with one or more people

.@ me if you respond plz

[harsh pulsar]

@little quiver message_id | conversation_id | sender_id | message maybe?

[proven arrow]

You may also want to add a threads/channel table, with a foreign key for it in the messages table. And also a participants table which are users that can be part of a channel.

Ah I see salt already added the conversation_id in it. So yeah same thing.

[waxen lagoon]

Hello,

Trying to learn MongoDB and really confused on how to add an additional field to an existing object in a document. Here is my loop:

        df['in_communication'] = \
            [{time: {'time': time, 'in': time_pass(timedelta, interval), 'timestamp_obj': time_obj}}
             for timedelta, interval, time_obj
             in zip(df['dt_delta_min'], df['interval'], df['dt_server'])]

        for station, new_entry in zip(station_list, df['in_communication']):
            db['communication_data'].update_one(
                {'_id': station, f'in_communication.{day}': day},
                {'$set': {f'in_communication.{day}': new_entry}},
                True)

so i use the list comprehension to create the object I want to insert and have the query set to update on a specific _id, but I get an error saying there is a duplicate key... there isn't another one in the collection. I'm not trying to create a new document so I'm using update and all the examples I've looked at seem like this should work.

here is the error:

pymongo.errors.DuplicateKeyError: E11000 duplicate key error collection: 609b4c5d671c397c1af13398_stations.communication_data index: _id_ dup key: { _id: "SCE-2017" }, full error: {'index': 0, 'code': 11000, 'keyPattern': {'_id': 1}, 'keyValue': {'_id': 'SCE-2017'}, 'errmsg': 'E11000 duplicate key error collection: 609b4c5d671c397c1af13398_stations.communication_data index: _id_ dup key: { _id: "SCE-2017" }'}

[torn sphinx]

Ahh Im struggling, how to "define" the standart???

Ik its germam but uh

There are only 4 options

None, this, NULL and CURRENT_TIMESTAMP

It should be for the email input ?-?

[cold basin]

How I resolve this while installing oracle database 18c on windows 10

[tranquil totem]

does anyone know what 0: object is?

i want to rename it to something better if possible

if its a unique id how do i set it up while uploading an item to the db?

[burnt turret]

it's saying that the item at index 0 is an Object

[tranquil totem]

ohh

okay

[tranquil totem]

    @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"]

            if message.author.id in [x["id"] for x in find_user]:
                # find_user.remove(i)
                # await message.channel.send(f"Welcome back, {message.author.mention}! I have removed your AFK")
                await collection.update_one(
                    {"_id": message.guild.id},
                    { "$pull": { "id": message.author.id } }
                )
                await message.channel.send(f"Welcome back, {message.author.mention}! I have removed your AFK.")
```Why doesnt the `$pull` part work?

[velvet fable]

So im trying to use PostgreSQL to store an image/gif, I already have the image data, but how would I go about storing that into my database? I tried just putting the type as bit and got this error, https://mystb.in/LogosMissionSort.sql.

[torn sphinx]

so if wanted to store a dictionary and still be able to access its values how could i do that

[proven arrow]

It’s because you used the wrong type. Have a read of this, the link in message gives different options and types you can use to store it #databases message

[tranquil totem]

why doesnt this remove the item from the list:```py
await collection.update_one(
{"_id": message.guild.id},
{ "$pull": { "id": message.author.id } }
)

[proven arrow]

Make sure it’s running or the value actually exists

[tranquil totem]

yeah all of them exist

wouldnt it give an error if they didnt exist

someone help

[novel leaf]

Anyone knows :
How to Upgrade my PostgreSQL ?
But most importantly, How to Backup my Postgres Database Data and retrieve that data afterwards ?
Ping me if anyone replies

[hexed zephyr]

how to get the sku value ?

[rustic geyser]

ai_chat_servers = {}

@client.command()
async def configure(ctx, channel: discord.TextChannel):
    server_id = ctx.guild.id
    ai_chat_servers[server_id] = channel.id
    await ctx.send(f'AI Chat is now set to {channel}')``` The value of ai_chat_servers get reset whenever i reboot the bot, how do i make it that it never gets reset

how do i make a database ;-;

[tranquil totem]

make a database

first, choose a database

[torn sphinx]

hello devs how do i a=make a data base and then store a dictionary?
also how do i get infomation from that dictionary

[tranquil totem]

someone help me pls

[cold basin]

@velvet ridge
I did two times but same

[cold basin]

What should I do?

[pseudo flame]

are you using id as your primary key or _id

[tranquil totem]

its a field that has a list of dicts with id

[pseudo flame]

ok...

you have to tell mongo that it's nested

i think

I still don't understand how your fields are structured but if you're trying to access a nested object then you need to refer to that in your query

[pseudo flame]

you need to do ```
{
$pull: {list_field: thing_to_remove}
}

[cold basin]

@cold basin
Help

[pseudo flame]

just checked mongo docs

you need to specify the field with the list

[pseudo flame]

restart your machine and try again?

[cold basin]

@pseudo flame
I deleted the files and did restart , again I reinstall the oracle same error

[pseudo flame]

idk, try contacting oracle support or something as this seems trivial

[cold basin]

Ok

[tranquil totem]

My data is like this:py "_id": guild.id "afk_members":[ { "id": author.id, "status": status, "date": datetime.datetime.utcnow() }] So a list of dicts:```py
"_id": "284628572697298"
"afk_members":[
{
"id": "3456787654323458"
"status": "Sleeping"
"date": datetime.datetime.utcnow()
},
{
"id": "987654345678241",
"status": "Eating",
"date": datetime.datetime.utcnow()
}]

[pseudo flame]

and you're trying to delete an object from the list?

what you want to do is:

{
  "$pull": {"afk_members": {"id": message.author.id}} 
}

@tranquil totem

i believe

[tranquil totem]

yeah thanks, i did this

@pseudo flame could you tell me why this is happening:```py
status = [x["status"] for x in find_user if x["id"] == member.id]
await message.channel.send(f"{member.name} is AFK: {status}")

those extra brackets and single quotes

[modest pulsar]

how can I define my db and its cursor at the beginning of my class, to not having to define it in each function ?

[past stag]

try status[0]

[tranquil totem]

yeah it worked

i finally made the command

[hexed zephyr]

am i doing this correctly?

[burnt turret]

close, $set has to be as a string

"$set"

and that first argument to update_one has to be a dict as well

[hexed zephyr]

        mylist = collection.find(mysku)
        for x in mylist:
            myid = x['_id']
            newprice = ({$set: {"buy": buy, "sell": sell}})
            collection.update_one(myid, newprice)
        else:
            collection.insert_one(sku_listing)```

[burnt turret]

instead of just myid you'd do {"_id": myid}

[burnt turret]

you didn't change anything here?

[hexed zephyr]

nope not yet

        mylist = collection.find(mysku)
        for x in mylist:
            myid = x['_id']
            newprice = {"$set": {"buy": buy, "sell": sell}}
            collection.update_one({"_id": myid}, newprice)
        else:
            collection.insert_one(sku_listing)```

@burnt turret like this?

[burnt turret]

try it and see

[hexed zephyr]

it helps me to create second one

how to make it to update the info if sku is found in my list

and if it isnt create a new one

@burnt turret

[pseudo flame]

status is a list

[rose sphinx]

Hey Guys, I want to delete duplicates from columns in excel. Can somebody help me please. I have posted in the Help-Corn channel.

[hexed zephyr]

i need help over here guys

[burnt turret]

https://sqlbolt.com

😄

[south ruin]

Hey guys, I've got a question about MongoDB
I have 2 running apps:

• Inserts data into a couple of collections, two of those contain tens of millions of recipe_id s
• Fetches all recipe_id s, removing duplications and processes them.

Since both apps running simultaneously and the first inserts documents that the other later fetches, is there away to only fetch documents which were inserted after a certain ID? (The DB does not contain date of insertion)

[burnt turret]

how are these IDs generated? if they're just incrementing you could do collection.find({ _id: { $gt: <specific id> }})

[harsh pulsar]

the easy solution might be to add insertion timestamp to the db 🙂

[worthy shell]

hi, im trying to store data for a bot. is csv or pandas better? i won't have data more than 100 rows. i do wish to change values

[south ruin]

Can't, the first app has been running for a couple of days now, can't stop it

[south ruin]

Interesting, gonna check that, thanks! 😁 😁

[harsh pulsar]

pandas is for data in python, csv is for data on disk. use neither, use sqlite instead.

[strong robin]

i use sql

🙂

[grim vault]

if it's only one object you might just use json.dump() and json.load() if your datatypes are json compliant.

[south ruin]

I let MongoDB insert them by itself, they look like the following: ObjectId("60b40c867b6a804d33bc396c"), ObjectId("60b40c867b6a804d33bc396d")

[elder socket]

        strs = 'select MONTHNAME(b.row_date) as \'Month\', WEEK(b.row_date) as \'Week\', b.om_name as \'OM Name\', a.row_date as \'Date\','\
        'b.stage as \'Stage\', b.site as \'Site\', b.lob as \'Lob\', b.logid as \'Login ID\', b.agent_name as \'Agent Name\', b.skill as \'Skill\','\
        'a.acdcalls as \'ACD Calls\', a.acdtime as \'Acd Time\', a.holdtime as \'Holdtime\', a.i_acdothertime as \'I_acdothertime\', a.acwtime as \'ACW TIME\','\
        'a.acdauxoutcalls as \'AUX Outtime With ACD calls held\', a.i_auxouttime as \'I_AUXOUTTIME\', a.i_acwouttime as \'I_ACWOUTTIME\','\
        '(a.i_auxouttime - a.i_acdaux_outtime + a.i_acwouttime) as \'Outbound\', a.ti_stafftime as \'Staffed Time\', a.ti_auxtime0 as \'Aux 0\','\
        'a.ti_auxtime1 as \'Aux 1\', a.ti_auxtime2 as \'Aux 2\', a.ti_auxtime3 as \'Aux 3\', a.ti_auxtime4 as \'Aux 4\', a.ti_auxtime5 as \'Aux 5\','\
        'a.ti_auxtime6 as \'Aux 6\', a.ti_auxtime7 as \'Aux 7\', a.ti_auxtime8 as \'Aux 8\', a.ti_auxtime9 as \'Aux 9\', a.ti_availtime as \'Avail\','\
        'a.i_acwtime as \'ACW TIME_SS\', a.conf as \'Conference\''\
        'from Reports.dbo.t_AA_Consolidated b  join BI.dbo.DAgent a on a.logid = b.logid'\
        'where date(b.row_date) =\'' + self.sd + '\''
        engine = create_engine(r'dialect+driver://username:password@host:port/')
        self.df=pd.read_sql(strs,engine)```
Hi,

is this bad practice in writing sql query using python?

[brave bridge]

1. instead of doing \', just use double-quoted strings.
2. you can use multiline strings:

"""
You can use "double" and 'single'
quotes here, and you can write multiline
strings.
"""

3. Never use string concatenation when building queries. This is prone to SQL injection. Instead, use the tools your adapter library provides to substitute parameters

[grim vault]

2a. use double quotes " for identifiers
2b. use single quotes ' for string literals

  SELECT "column" AS "name" FROM "table" WHERE "anothercolname" = 'Berndulas'```

[hexed zephyr]

            "success": True,
            "sku": sku,
            "currency": None,
            "name": name,
            "source": "bptf",
            "time": int(time.time()),
            "buy": buy,
            "sell": sell
        }
        print(sku_listing)
        mysku = {"sku": sku}
        mylist = collection.find(mysku)
        for mysku in mylist:
            collection.update_one({"_id": mysku['_id']}, {"$set": {"time": int(time.time()), "buy": buy, "sell": sell}})
        else:
            collection.insert_one(sku_listing)```
what is wrong with my code?

it updated the new value for buy and sell and created the second one

why?

[timber nymph]

free code?

lmao

[brave bridge]

oh, I meant using " for the Python string

so that you don't have to escape the 's

[sullen wolf]

Can anyone help me with DB that can be used with discord.py?

[novel oak]

any one, I mean the type of DB isn't normally directly related to the program you are making, you can use anyone, for discord.py you can use SQLite or MySQL(or MariaDB wich is the same)

[sullen wolf]

Okay.

[crystal rose]

but if you're gonna use a SQL DB please be aware of SQL injection

especially if you parse user input

[sullen wolf]

What is that simply?

[crystal rose]

Very simply put it's when someone views data they shouldn't

Exploiting something in your code

[grim vault]

Yeah, I know. I should have mentioned that what I wrote is the defined SQL-standard for quotes.

[hexed zephyr]

what's wrong with this code?

[novel oak]

Any good tutorial or docs to learn MySQL for python? (how to connect insert and get data?)

[solemn cove]

Depends on what you use to connect and make queries. You can use simple MySQL connector for Python but have to make raw SQL queries, like here: https://github.com/mysql/mysql-connector-python

[solemn cove]

If you search for tutorials for that library you'll get a lot. There is an examples folder in there too.

[lethal spindle]

so im using asqlite to store user data. and ive never used a db before so idk if im doing it right. is this all i need (this is example code from the github repo) or do i need something else ?

import​ ​asyncio​
​import​ ​asqlite​

​async​ ​def​ ​main​():
    ​async​ ​with​ ​asqlite​.​connect​(​'example.db'​) ​as​ ​conn​:
        ​async​ ​with​ ​conn​.​cursor​() ​as​ ​cursor​:
            ​# Create table​
            ​await​ ​cursor​.​execute​(​'''CREATE TABLE stocks​
​                                    (date text, trans text, symbol text, qty real, price real)'''​)

            ​# Insert a row of data​
            ​await​ ​cursor​.​execute​(​"INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)"​)

            ​# Save (commit) the changes​
            ​await​ ​conn​.​commit​()

​asyncio​.​run​(​main​())```

heres the repo: https://github.com/Rapptz/asqlite

[twilit oyster]

How can I use $addToSet in mongodb on a object instead of a array

actually nvm

[zealous rampart]

hewo?

need som lil help wit mongodb

moji-cluster0.pogzg.mongodb.net

pymongo.errors.ConfigurationError: The DNS response does not contain an answer to the question: _mongodb._tcp.moji-cluster0.pogzg.mongodb.net. IN SRV

and wen i use mongodb://

it connects ig

but

[twilit oyster]

How would I go through a array directly to the values inside a object in mongodb?

[zealous rampart]

dis gets tripped [Errno -5] No address associated with hostname')>]>

[twilit oyster]

https://stackoverflow.com/questions/67852186/go-through-a-array-and-search-inside-objects

[burnt turret]

https://docs.mongodb.com/manual/tutorial/query-array-of-documents/#specify-a-query-condition-on-a-field-embedded-in-an-array-of-documents

[tacit fjord]

if my bot keeps getting a missing permissions error will it force stop it self

because my bot went down again, i look in the console and its just thousands of missing permissions error

[jaunty galleon]

#discord-bots

[tacit fjord]

oh i thought i cliced discord.py

thats my bad sorry guys

[torn sphinx]

comparing cassandra and mongoDB, can anyone provide me with any evidence on which one is more performant?

[torn sphinx]

Why code not executing?

[jade leaf]

Anyone know of any good tools for database design?

Like some kind of visual mockup tool that I can then use to build the real database off of

I could just do it with pen and paper, but if a tool like that exists it would be very useful

[torn sphinx]

@jade leaf https://app.diagrams.net/

for ER diagram, you can also use MySQL workbench

[jade leaf]

I actually just tried out https://dbdiagram.io/d, seems to work kinda well

i dont like that you cant edit the things in the visual tables though

[torn sphinx]

whatever works for you ^^

[jade leaf]

does your tool have the ability to translate itself into sql?

[torn sphinx]

the link no, the MySQL workbench i believe it does

[jade leaf]

interesting, i might try that out then

thanks!

i feel like the ideal tool would have a visual design tool that can go straight to sql seamlessly

[torn sphinx]

anyone can help me?

https://pypi.org/project/PyMySQL/#documentation

¯_(ツ)_/¯

[jade leaf]

kinda hard to help if there's no output

if you were running into an error that would make it easier to debug, if there's no error then something's set up incorrectly or you're not doing what you think you're doing

[torn sphinx]

but in phpmyadmin sql executor this code worked normally but in code this code not executing

[jade leaf]

is the db on localhost? or is it hosted on a remote server?

[torn sphinx]

localhost

dud, you still haven't told us WHAT error you got

-_-

[torn sphinx]

without error

perfect

then you have no problem

[jade leaf]

could be a permission problem, or the database isn't even running. could be any number of problems lol

[torn sphinx]

problem solved

[jade leaf]

you need to verify that the database is running, and that you have access to it from wherever you're executing the code from

[torn sphinx]

other codes working perfectly but this fragment of code not work

[jade leaf]

if it works in one place but not in another, that's what i'd look at first

someone else might have more insight, i only use sqlalchemy and i have work to do so i dont have the time to learn pymysql lol

[torn sphinx]

@torn sphinx i noticed you provided no DB password? is that intentional

[torn sphinx]

i didnt set password

intentional = yes

[jade leaf]

not really good practice to have no password to access your database, even if it's at the prototype stage

[torn sphinx]

How to fix it?

[brave bridge]

1. Don't use bare except. Either use except Exception (so that you don't catch cancellations and KeyboardInterrupts), or just provide e
2. If you want to know what the error is, why are you hiding it? If you want to just print it, use traceback.print_exc https://docs.python.org/3/library/traceback.html#traceback.print_exc

So do that, and tell use what the error is.

what is exit? where is it defined?

[torn sphinx]

exit returning to this:

to this

[brave bridge]

Can you just remove the try/except?

[torn sphinx]

yes, wait a sec

without error

[brave bridge]

ah, I see

[torn sphinx]

i executed this mysql code in php my admin mysql executor and all work

[brave bridge]

When you run exit, you raise a SystemExit exception.

[torn sphinx]

yes

[brave bridge]

That's why people say that you should never do a bare except:.

You're catching the exception that exit raises.

[torn sphinx]

hm

but row in database didnt change

@brave bridge

uh sorry for ping

[brave bridge]

perhaps you need to run commit on the connection or the cursor

I'm not familiar with the library you're using, you should check out its documentation to see how to work with cursors and connections

Also, why are you using pyarmor?

[torn sphinx]

i want to hide my code

to encrypt

[brave bridge]

Are you making a client-side application that talks to a remote database that you host?

[torn sphinx]

That should work?

[brave bridge]

I don't know. You can run it and see.

[torn sphinx]

i will buy hosting to host mysql database

and i must encrypt all data

[brave bridge]

If you distribute a program that has access to a remote database, the person you're distributing the program to will be able to do anything with that database.

It's a program that's running on their machine. They can inspect the memory of any process and get the credentials to access the database.

[torn sphinx]

hmm

[brave bridge]

Also, can you tell what will happen when the user inputs '; DROP TABLE users -- as their password?

[torn sphinx]

HMM

bigbrain 😉

I DID IT

can i do anty dbg in python file?

[brave bridge]

?

[brave bridge]

@torn sphinx That's why you should never use f-strings to format queries. You should use the tools provided by the library to safely substitute values into the query.

Another important thing is that you should be hashing passwords. You can see this article https://security.blogoverflow.com/2011/11/why-passwords-should-be-hashed/ or this video https://www.youtube.com/watch?v=8ZtInClXe1Q if you want to know why.

[torn sphinx]

anty debug encrypts connections with python file to servers

[brave bridge]

Whatever you do with your program, it's always possible to just dump all the memory that belongs to a process. There's no tool that will prevent that.

So:

1. If a client program talks to your remote database directly, assume that its user has full access to it.
2. Don't use string formatting to build SQL queries. It's prone to SQL injection.
3. You need to hash passwords.

[torn sphinx]

in 2.
how to dont using string formatting to build sql queries

[brave bridge]

What library are you using?

[brave bridge]

sql = "UPDATE users SET password=%(password)s WHERE username=%(username)"
cur.execute(sql, {"password": password, "username": username})

The PyPI page has an example: https://pypi.org/project/PyMySQL/
The documentation further explains that you can use either a tuple or a dict for substitution: https://pymysql.readthedocs.io/en/latest/modules/cursors.html#pymysql.cursors.Cursor.execute

[torn sphinx]

okay

[brave bridge]

If you do this, the library will escape all the special characters so that the query is formatted correctly.

[little quiver]

I need some help with postgresql triggers. I have a users table and a friends table. When a new user is added, i need to add an empty array in the friends column in the friends table. Heres the code
`CREATE FUNCTION create_friends_list()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
INSERT INTO friends (user_id, friends) values(NEW.user_id, '{}');
RETURN NEW;
END;
$$;

CREATE TRIGGER create_friends_row
AFTER INSERT ON users EXECUTE PROCEDURE create_friends_list()`

[torn sphinx]

how can i encrypt code with connections to database?

[brave bridge]

What do you mean?

Do you want to prevent users of your program who have it installed from getting the credentials to the database?

[torn sphinx]

no

[brave bridge]

You're making a program that will be literally distributed to your users as a client, right?

[torn sphinx]

yes

[brave bridge]

Then, as I already said, if the client talks to a remote database that you own, your users will be able to get the credentials for that database.

[torn sphinx]

how can i protect credentials for database

[brave bridge]

You'll have to hide that database behind an API that you provide.

Just like every web application out there.

[torn sphinx]

where i can find tutorial with api which connect with database

[brave bridge]

If you want a tutorial on how to make a web API, you can check out this tutorial: https://programminghistorian.org/en/lessons/creating-apis-with-python-and-flask or this tutorial for the FastAPI library: https://fastapi.tiangolo.com/tutorial/first-steps/.

[torn sphinx]

okay, thank you for help

but my code will still open-source to peoples?

[brave bridge]

PyArmor will probably obfuscate/encrypt your code pretty well, so it's going to be hard to reverse-engineer. But the data (for example, a password to your database) is not hidden in any way. It's going to be stored in plain-text somewhere in RAM.

[torn sphinx]

okay, thank you very much
i will try to do restapi

[jade leaf]

@torn sphinx another option is to host it as a web application, then you don't have to worry about that stuff

but yeah, there should be no scenario in which you need to write out raw SQL if you're using an SQL ORM library in python, which you should probably use

[brave bridge]

well, they're not using an ORM 🙂

that could be yet another layer of complexity

[twilit oyster]

Will look into it

[torn sphinx]

So I have a command which grabs data from my mongoDB database. One problem is that whenever I update the data in the database and I use another command to grab the data that command grabs the previous entry but doesn't grab the updated one. Is there any way i can fix this probem? (The code is running the whole time)

Sorry for my terrible explanation lmao

[raven coral]

Hlo, Anyone using Mongo Db here?

[raven coral]

Anyone here?

[burnt turret]

just ask your question, someone who can will answer

[jade salmon]

@fiery rose Create 3 tables, one for users, one for groups, and one for user groups.

I don't know if it's optimal, that's how I learned relational databases though.

your user table would have a primary key id, your groups would have a primary key id, and your user groups table would use foreign keys, pointing to the primary keys from the first two tables.

@fiery rose so, something like that.

[dreamy cedar]

Is there any issue with this query ?

I guess, I have followed all the procedures.. Can someone please help

[grim vault]

Like this: <#databases message>

[jade salmon]

It looks like you might have your quantity column set to numeric(10,10), which means 10 digits, 10 of which are behind the decimal point. So the value has to be between 0 and 1. Try adjusting this to like (10,2)

[dreamy cedar]

thanks so much, I just noticed that I was handling input conversion in wrong way so, that why was occurring 😅

One more question does float() data in python similar to NUMERIC(2,2) in postgreSQL.. Does both of them are diffrent?

[hexed estuary]

numeric is a fixed-point format. It is exact (it works like how you expect numbers to do, and doesn't lose precision with operations). Python floats are normal floating-point numbers, which have behaviours that one might not expect:

!float

[delicate field]

Floating Point Arithmetic
You may have noticed that when doing arithmetic with floats in Python you sometimes get strange results, like this:

>>> 0.1 + 0.2
0.30000000000000004

Why this happens
Internally your computer stores floats as binary fractions. Many decimal values cannot be stored as exact binary fractions, which means an approximation has to be used.

How you can avoid this
You can use math.isclose to check if two floats are close, or to get an exact decimal representation, you can use the decimal or fractions module. Here are some examples:

>>> math.isclose(0.1 + 0.2, 0.3)
True
>>> decimal.Decimal('0.1') + decimal.Decimal('0.2')
Decimal('0.3')

Note that with decimal.Decimal we enter the number we want as a string so we don't pass on the imprecision from the float.

For more details on why this happens check out this page in the python docs or this Computerphile video.

[hexed estuary]

(the disadvantages of fixed-point types are, say, that they can only represent numbers of certain size, and also that they are far slower to do operations on. Neither of these are particularly important for storage, so fixed-point numbers are usually a good idea)

[dreamy cedar]

hmm, thanks 😇

[dreamy cedar]

def update_raw_material_utility(connection, utilized_quantity, remaining_quantity, raw_material_utility_id, production_id):
    with connection:
        with connection.cursor() as cursor:
            cursor.execute(UPDATE_RAW_MATERIAL_UTILITY, (utilized_quantity, remaining_quantity, raw_material_utility_id, production_id))
            utility_id = cursor.fetchone()
            print(f'UPDATE_RAW_MATERIAL UTILITY : {utility_id}')
            print(utilized_quantity, remaining_quantity, type(raw_material_utility_id), production_id)
            return cursor.fetchone(), utility_id```

**Terminal : **UPDATE_RAW_MATERIAL UTILITY : ('561e4188-ac9f-4e33-b3bd-7619e9ee2788',) 0.1 0.2 <class 'str'> 1 (None, ('561e4188-ac9f-4e33-b3bd-7619e9ee2788',))

[dreamy cedar]

I am running this code , but when I am doing py return cursor.fetch.one() it's not working but when I am assigning the RETURNING value to any variable it's working perfectly...

IS there any issue? Pls can someone help.. I am stuck on it since few hours

[hexed estuary]

fetch.one?

[dreamy cedar]

fetchone() andfetchall()both working

[hexed estuary]

I mean isn't it fetchone() and not fetch.one()?

and what do you mean by not working?

[dreamy cedar]

yaa it's fetchone()

[dreamy cedar]

in terminal output is NONE also in side by side I printed variable which I assigned the returning from the query and for variable it's working value but

[dreamy cedar]

...

print(database.update_raw_material_utility(connection=connection, utilized_quantity=0.1, remaining_quantity=0.2,
                                     raw_material_utility_id='561e4188-ac9f-4e33-b3bd-7619e9ee2788', production_id=1))```

[hexed estuary]

utility_id = cursor.fetchone()
...
return cursor.fetchone()

you seem to be fetchoneing twice

is that right?

[dreamy cedar]

yaa it's allowed in postgres

[hexed estuary]

well, the second one seems to get you a None

[dreamy cedar]

yaa

when I removed the variable it's also returning none

ohh noo noo Wait it's working

[dreamy cedar]

seems this is the issue

I am using context manager so as soon as execute and call fetch once it seems to close the connection

Thanks

[grim vault]

Doesn't an UPDATE statement always return None? It's not an SELECT.

[austere portal]

How do I do connection pooling in psycopg2?
The docs don't give much info about it https://www.psycopg.org/docs/pool.html#psycopg2.pool.SimpleConnectionPool

[smoky solar]

Can I use mysqlconnector instead of sqlalchemy with Flask / FastAPI?

[brazen charm]

you can yes, although Sqlachemy does support mysql

[little quiver]

I need some help with postgresql triggers. I have a users table and a friends table. When a new user is added, i need to add an empty array in the friends column in the friends table. Heres the code
`CREATE FUNCTION create_friends_list()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
INSERT INTO friends (user_id, friends) values(NEW.user_id, '{}');
RETURN NEW;
END;
$$;

CREATE TRIGGER create_friends_row
AFTER INSERT ON users EXECUTE PROCEDURE create_friends_list()`

[tranquil totem]

how do i change the contents in a list? for example, here is my data:```py
_id: server_id
xp: [{
"id": message.author.id,
"xp": xp,
"xp_given_at": datetime.datetime.utcnow()
}]

how do i update `xp` and `xp_given_at` over here?

using mongodb

?

[little quiver]

please

[ionic pecan]

hello, what's the problem that you're having with the trigger?

[little quiver]

in the friends table, the user_id is null

[ionic pecan]

see the docs on the AbstractConnectionPool, use getconn to obtain a connection and putconn to put it back in there

[ionic pecan]

can you share your schema via our paste service or is it something private?

[little quiver]

sorry i started sql like 3 days ago im not sure what that is haha

no its not private

[torn sphinx]

When I did .exe file from .py mysql connections from .py file will be encypted?

[ionic pecan]

that's fine! your schema is the way you defined your database tables (such as CREATE TABLE users ..., i'll try to set it up locally

[torn sphinx]

this will be enrypted?

when file is .exe

[little quiver]

`create table users(
user_id bigserial not null PRIMARY KEY UNIQUE,
friend_code uuid not null UNIQUE,
public_key bit(3600) not null UNIQUE
);

create table conversations(
conversation_id bigserial not null PRIMARY KEY UNIQUE,
participants bigint[] not null /* list of user id's*/
);

create table messages(
message_id bigserial not null PRIMARY KEY UNIQUE,
conversation_id bigint REFERENCES conversations(conversation_id),
sender_id bigint REFERENCES users(user_id),
msg text,
is_read boolean not null default false
);`

[ionic pecan]

what about friends?

[little quiver]

create table friends( user_id bigint REFERENCES users(user_id), friends bigint[] /* list of user id's*/ );

thanks for the help

[ionic pecan]

i've got the schema set up locally, i am as confused as you are what's going on here

postgresql docs mention that this should be just fine

[little quiver]

yeah thats why im confused

[ionic pecan]

which postgresql version is this?

SELECT version();

[little quiver]

13.3

[ionic pecan]

i'll set up an older version and check there, since i've only found one other post out there with someone having a similar problem and that's also very recent

highly doubt this is a bug in postgresql, but maybe something changed over the versions

[little quiver]

maybe

[ionic pecan]

well, this also happens on postgresql 11, so that's not the answer

[little quiver]

oh ok

[brazen charm]

whats up

[brazen charm]

I dont see the point in this over using a traditional relational setup

you're better off having a table that has a set of IDs and you JOIN off that rather than creating a trigger function like that

[ionic pecan]

the entireNEW object is NULL

[brazen charm]

that way you also have the constants ensuring that the id exists and will be removed should the user be removed

rather than with an array that can lead to invalid data

[ionic pecan]

okay I found it

https://www.postgresql.org/docs/13/plpgsql-trigger.html

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. **This variable is null in statement-level triggers and for DELETE operations.**

the trigger definition was missing something, namely, FOR EACH ROW, so currently if you were to insert multiple entries at once, it would only be called once. but you want it to be called once for every input row

therefore, you need to update your trigger definition to

CREATE TRIGGER create_friends_row
AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE create_friends_list();

[little quiver]

ah i see

[ionic pecan]

and then you'll have the value as expected:

postgres=# INSERT INTO users VALUES (1, 'abc');
NOTICE:  NEW ROW (1,abc)
INSERT 0 1
postgres=# TABLE friends;
 user_id | friends 
---------+---------
       1 | {}
(1 row)

[little quiver]

yeah

thank you

[ionic pecan]

this is also a good idea, by the way

[little quiver]

yeah, so how would I do that

[ionic pecan]

CREATE TABLE friends(
    source BIGINT REFERENCES users(user_id),
    target BIGINT REFERENCES users(user_id),
    CHECK (source != target)
);

if you still want your friends as an array, that's straightforwarwd too.. one moment

test=# INSERT INTO users (user_id, name) VALUES (2, 'volcyy'), (3, 'martin'), (4, 'mark');
INSERT 0 3
test=# INSERT INTO friends (source, target) VALUES (2, 3), (2, 4);
INSERT 0 2
test=# -- get all friends for volcyy
test=# SELECT array_agg(target) FROM friends WHERE source = 2;
 array_agg 
-----------
 {3,4}
(1 row)

you don't need the array_agg, but then you don't need to rewrite your code to first gobbble that up into a single array, postgresql will already have it in one

[little quiver]

is this better than in a array

[lost mortar]

I'm having an issue with pyodbc and sql server

pyodbc.InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')

[frigid ruin]

so, this is my mongo document structure, how can I access any particular client with their clientEmail, in "mongoengine", and clients is an embedded document here

[lost mortar]

When I run driver_names = [x for x in pyodbc.drivers() if x.endswith(' for SQL Server')] in the script, I get back
['ODBC Driver 17 for SQL Server', 'ODBC Driver 13 for SQL Server']
So the drivers are clearly there

[lost mortar]

DRIVER = driver_names[0]
#  config['DB']['DRIVER']

sql_server_connection_string = f"mssql+pyodbc://{U}:{P}@{SERVER},{PORT}/{DATABASE}?driver={DRIVER}"```
Does absolutely nothing to fix this

[ionic pecan]

yes, because then if you wanted to e.g. find all friends of someone, e.g. for a profile site, you could use relational database constructs such as JOIN:

test=# SELECT name FROM users JOIN friends ON (friends.target = users.user_id) WHERE friends.source = 2;
  name  
--------
 martin
 mark
(2 rows)

[little quiver]

hm ok ill do that then

thank u so much for all your help

[ionic pecan]

no problem 👍

[novel oak]

Is there any asynchronous wraper for using mysql in python?

[solemn cove]

Do you mean an async connector for MySQL? https://github.com/aio-libs/aiomysql

[novel oak]

yes

[novel oak]

I've been reading the docs, but don't really getting how it works, any help?
I already have this but returns this error:

async with aiomysql.connect(host="", user="", password="", db="") as cnx:
  with await cnx.cursor() as c:
  sql = "SELECT XP FROM UsersDiscordIceEmpire WHERE ID = %s"
  val = (f"{member.id}", )
  await c.execute(sql, val)
  UserXP = await c.fetchone()

UserXp = math.trunc(int(UserXP[0]))

    with await cnx.cursor() as c:
AttributeError: __enter__

[versed flower]

I would like to insert the values ​​from the line edit to my sqlite3 table (I am using python pycharm) with pyqt5. When I execute there is an error so here is my code

[burnt turret]

Their docs haven't been updated in a while, hence the issue; in fact I have a PR open to their repo which fixes a lot of it but it looks like the repo is dead :/

For your thing, you should do async with instead of that with await

[novel oak]

ok thx

[torn sphinx]

Hey there just a quick question, I’d like to know once I select a row using Asyncpg with PostgreSQL how do I get the record number I’ll need it for numbering questions

[sonic idol]

Hi guys, is there any way (or even benefit) to parse out processes running from a WMIC prompt? like wmic process get description, processid

into a CSV I mean

[quick bloom]

You can't, not easily. An exe is written completely differently. You would have to decompile the exe and read the assembly code, and thats hoping the code is not obfuscated

It doesn't matter

An exe is not Python

[novel hollow]

Has anyone tried to download datasets from pinterest?

Some people have boards with very nice curated pictures, lol.

It would probably take some processing since all images are not of the same size and resolution

[inner sentinel]

er, actually. Some exes have the code pasted on the bottom. Pretty sure that's not how python does it, but a different language ik puts the interpeter above the code and then puts the code below it in ascii

[quick bloom]

Well I never knew this, thanks!

[inner sentinel]

again, idk how python does it

[solemn cove]

Were you able to figure this out? You can try the async with syntax as in the examples instead of with await. Also aiomysql is build quite similar to the PostgreSQL version so please check documentation here too: https://github.com/aio-libs/aiopg

[solemn cove]

What do you mean by record number? Were you able to solve this?

[torn sphinx]

I wasn’t able to solve this no, as in the number for each rows in order so for example

1. hi test
2. Hi. Test
3. Hi tetsys
   I am trying to get the numbers

I’ll have to show you later what I mean it’s currently 3AM

[solemn cove]

I guess these numbers are the Primary Key in the table, are they? Then you should try row.id as in example here: https://github.com/aio-libs/aiopg#example-of-sqlalchemy-optional-integration

[torn sphinx]

Yeah this is probably it thank you

Talking about the row ID

[twilit marlin]

Can someone suggest some article or video that will help me understand database relationships? Everytime I sit down to write models for a new project, I struggle to establish relations between different tables. What will be the relationship between a Whatsapp Group and a Message? One to Many?

[wise goblet]

https://sqlbolt.com/
perhaps to start from this. It is covering basics of SQL in a really fast way in interactive mode. I rushed in few hours through it at least. It should already get you some basic understanding on primary-foreign key level.

as for your request directly, I think you are basicallly asking to learn database modelling, this one book looks like all about it
https://www.oreilly.com/library/view/database-modeling-and/9780123820204/
Huh. I should probably read about it as well.

[wise goblet]

Whatsapp Group has many messages, right?
so every message will have foreign key attached to whatsapp group.
dunno how it named but the simpliest way would be

Group object
Primary key ID
Group Attributes

Message object
Primary key ID
Foreign key to Group primary key ID
Message attributes

one message can not be belonging to several groups? Or can be?

hmm what if it would be redirected? We can make a copy of an object though in this choice in order keep the model above

But if we want to not have copies then... this model can work...

Group object
Primary key ID
Group Attributes

Message object
Primary key ID
Message attributes

Group-message link object
Foreign key to Group primary key ID
Foreign key to Message primary key ID

could be of a more universal choice

I am not knowing a lot about database modeling though, so there can probably better ways to handle it

actually this book like better one
https://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0321884493/ref=pd_lpo_14_t_0/132-9812564-4626467?_encoding=UTF8&pd_rd_i=0321884493&pd_rd_r=0d0f4ad0-2289-425f-b0df-d37a7c7148c4&pd_rd_w=2EFtj&pd_rd_wg=J5DdO&pf_rd_p=a0d6e967-6561-454c-84f8-2ce2c92b79a6&pf_rd_r=5N5XD106N17DWJZ85K2C&psc=1&refRID=5N5XD106N17DWJZ85K2C

it has more reviews and higher score

and funny name

https://www.amazon.com/Database-Design-Mere-Mortals-Anniversary/dp/0136788041/ref=pd_sbs_1/132-9812564-4626467?pd_rd_w=jbqrF&pf_rd_p=43345e03-9e2a-47c0-9b70-a50aa5ecbd5c&pf_rd_r=JFPR4GGV9F7DG1GYDEMH&pd_rd_r=18fa517e-371e-48db-8a99-9ce29949a454&pd_rd_wg=qp8WJ&pd_rd_i=0136788041&psc=1
it was updated to 2020 year

[twilit marlin]

@wise goblet thank you for all these resources! They are really helpful and I'll get to using them right away.

[wise goblet]

u a welcome. helping you, I answered my own questions as well. Always wished to find some materials to get my knowledge deeper in this field. Hehe. I know which book I'll be reading next now.

[austere portal]

Thanks, I'll take a look at that

[pulsar kestrel]

Anyone know a good tutorial how to store datas from python to sqlite

[oblique latch]

Pretend it's json

[oak swan]

hi

Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to bigint.
can some one help regarding this error
when i run this query

exec sp_executesql N'SELECT * FROM C_ProjectISubtems_CivilWorks INNER JOIN C_SubItems ON C_SubItems.ID = C_ProjectISubtems_CivilWorks.C_SubItemID
WHERE C_ProjectISubtems_CivilWorks.C_SubItemID=@C_SubItemID
AND C_ProjectISubtems_CivilWorks.ID=@ID',N'@C_SubItemID nvarchar(21),@ID nvarchar(5)',@C_SubItemID=N'---Select Sub Item---',@ID=N'21712'```

[novel oak]

Yes yes I was finally able to figure it out how did it worked, thx again

[wise goblet]

https://sqlbolt.com/

better to use ORM though, but in my opinion you can't use ORM until you learned basics of SQL
and learning raw SQL you'll manage even without ORM

but better after that check how to use SQLAlchemy

it is the way

though, which framework are you using?

[solemn cove]

Totally second this ⬆️ @pulsar kestrel, if you go through some basic SQL, then using SQLite will be so much easier. Try it directly with a command for SQLite first, then with Python (use ORM if you like too).

[pulsar kestrel]

thanks

[torn sphinx]

Yeah anyone meh too

[calm prawn]

so when I try to update a filed in mongo db with a _id which is not present I do not get any error

How do I make sure that the update was successful

[harsh pulsar]

@calm prawn in pymongo and motor, the update_one method returns an object with an updated_id field showing the id of the updated document, if one exists

https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html
https://pymongo.readthedocs.io/en/stable/api/pymongo/results.html#pymongo.results.UpdateResult

Sorry, you would check modified_count

Ignore upserted_id if not using upsert

[calm prawn]

hmm thanks

[torn sphinx]

hi

[fringe swift]

What's the best beginner relational database for a discord bot?

[torn sphinx]

is it possible to store pdf files or other documents?

[fringe swift]

which could potentially have multiple simultaneous uses

[torn sphinx]

most use postgresql for discord bots.

[fringe swift]

thanks!

is that difficult to set up?

I've been using JSON so

you can tell I am the lowliest shit possible

[torn sphinx]

no, just install the package and follow the tutorial. in python you can install asyncpg

[fringe swift]

alright, ty

[modest ledge]

Whats the sql command to view what columns a tablas has?

Postgresql

[bleak crown]

Hi, i'm using aiosqlite. I'm working on a project and in that project i'm connecting to different databases and doing some entries etc. The problem is i dont want to commit the database, until i'm sure that every step is correct.

I want to do steps first, then commit those connections.

But i'm not sure what is the healthiest way to do it. The reason i want this is i want to be able revert the database and the best way seems like not committing until i'm sure that those datas are needed. (Because i can scrap the connection and all of those data entries will be gone when i feel the datas are wrong.)

I'm using async with to connect to database,

async with aiosqlite.connect("civtr_matches.db") as conn:
        cur = await conn.cursor()``` so it cuts out when the code block is going other lines, like i'm connecting to a different database. ```python
async with aiosqlite.connect("civtr_civs.db") as conn:
        cur = await conn.cursor()``` I thought holding the databases opened and then when the code is ready, doing commits to all of these databases but :(, i'm not sure.

For an example, i could use normal connection, not async with, name the connections differently, commit them when the all of the tasks are finished and commit + close all of the connections when it's finished.

But again, my project is async and i'm not sure will it block the database when it is working on it.

What is the cleverest way to do it?

[quiet quarry]

hello guys
I am facing problem with my sqlite3 database when i make changes it saves the changes for almost 13 hours and then retakes back all the changes that i made, i am using two threads in my code and i am not committing the data but i set the isolation mode to None,
i was using the same queries with mysql database with setting autocommit to on and it was working fine what could be the problem in your opinion?

[torn sphinx]

Is there a better thing for data storage that I can read/write, and load it into my python program as a dictionary?

[harsh pulsar]

better than what?

[torn sphinx]

ah, I meant to say JSON

[harsh pulsar]

how complicated is the data format?

you can store json in sqlite even, depending on the layout of the data

there is also https://pypi.org/project/tinydb/

[torn sphinx]

uh, it's currently just python dictionaries and lists

[harsh pulsar]

can you be more specific about it?

[torn sphinx]

This is one of the files (only a part of it)

{
  "drinkers": {
    "SkyCrafter0": 10,
    "BiFross_": 2,
    "Munchkinfoo": 1,
    "Sindenky": 4,
    "Moo": 1
  },
  "tabs": {
    "SkyCrafter0": 0,
    "BiFross_": 0,
    "Munchkinfoo": 288,
    "Sindenky": 848,
    "Moo": 315
  },
  "keywords": {
    "alcohols": [
      "weak beer",
      "beer",
      "strong beer",
      "poor wine",
      "wine",
      "fine wine",
      "mead",
      "sake",
      "gin",
      "brandy",
      "whiskey",
      "rum",
      "tequila",
      "vodka",
      "absinthe",
      "everclear"
    ],

[harsh pulsar]

if you have "per user" data it's often better to store it on a "user" object, rather than keep multiple collections

but if you don't want to change how your data is organized, tinydb seems like it could be a good option

[torn sphinx]

It's not really per user, it's just storing various statistics

Alright

[harsh pulsar]

sqlite could work too

you can store these stats in a few tables

but you'd have to convert them to a dict-like format

[torn sphinx]

They're already all stored in dictionaries

[torn sphinx]

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

hello, for my bot when i attempt to connect to the mongo db it throws this error

    raise ServerSelectionTimeoutError(```

also makes it slow

[harsh pulsar]

@torn sphinx the full error message suggests that there is an SSL problem w/ the mongo server

[torn sphinx]

Which I figured, so it’s just mongo acting up?

[harsh pulsar]

that i don't know, sorry

[torn sphinx]

hi all, i had a question, in pyspark I was having TRUNC function as 1) last_etl_dt >= TRUNC(DATE_SUB(CURRENT_TIMESTAMP(), 15),'DD')
2) last_etl_dt >= TRUNC(DATE_SUB(CURRENT_TIMESTAMP(), 30),'MM'). , I noticed that if i use (1) then data is missing for few days, if i use (2) then data is correct, but i was not able to understand why (1) would lead to that issue? Can someone help? Thanks !

[warped frigate]

that looks very similar to an error some people get with discord.py, are you on windows or MacOS?

[torn sphinx]

windows

@warped frigate

[warped frigate]

ok

lemme check the recommended solution

[torn sphinx]

kk thanks

[warped frigate]

this is the solution for discord.py, might need to be modified slightly for mongodb:
https://github.com/Rapptz/discord.py/issues/4159#issuecomment-640107584

wait that's expired certificate

there are some with fixes for verify failed for ubuntu, hmmm

i'd try downloading this (certificate for mongodb.net), and double clicking to install, then running again
https://crt.sh/?id=3952546796

[storm wind]

I'm trying to query records from a mongo db and find cursor iteration in pymongo to be very slow. I'm pretty convinced I must be doing something wrong.. but I'm not sure what. Here is the pertinent info:
sample document in my collection:

{
    "uid": "1000132741950",
    "player": "GoshDarnedHero",
    "timestamp": 1619236851,
    "eventType": "Session",
    "event": {
        "action": "leave",
        "sessionDuration": 11
    }
}

Total documents in collection 12,000
Query: {'eventType': 'Session', 'uid': "1000132741950"}
Total documents in result: 1,185

query takes 5 seconds

profile looks like this:

the next call takes 3ms, but is run 1185 times

I have an index set up for uid and eventType (compound / ascending)

any thoughts about how to speed that up?

python code looks like this:

        cursor = db_helper.event_collection.find(query_filter)
        cursor.batch_size(5000)
        game_list: list = list(cursor)

[harsh pulsar]

that does seem slow, is it a lot faster if you run it in the mongo shell?

(also +1 for profiling!!)

if it takes 5 seconds in mongo shell then it's just a slow query and maybe you need indexes on those fields

if it's significantly slower in python, then maybe you can try increasing the batch size even more

also this, maybe you're using the pure python backend https://stackoverflow.com/a/8976768/2954547

some good diagnostic suggestions in this answer (downvoted apparently because the original wording of the question was quite abrasive) https://stackoverflow.com/a/5480531/2954547

[quiet quarry]

Guys please could anyone give some advices to solve this problem

I noticed that when i save the data using the main thread almost everything is fine

[still whale]

@quiet quarry @bleak crown sqlite is not full featured. If you take the step to configure and use mysql, you can wrap SQL around a transaction with rollback

[slow pawn]

que? why do you tag me?

[still whale]

Whoops

[slow pawn]

edited-in tag won't tag the person 😉

I mean won't ping

[still whale]

@bleak crown

[quiet quarry]

I didn't understand do i have to add begin tran and commit to my queries to solve this problem?

[still whale]

This is a strong, SQL based approached to my understanding of your problem. may not work in sqlite

Yeah syntax is like START TRANSACTION, END, ROLLBACK

[quiet quarry]

Okay thanks i will try it 🌼

[torn sphinx]

hi

[earnest holly]

Hey, sorry for the dumb question & continuation of my question in #help-pancakes but I have this database & I'm looking to make a Discord command to print the Name's associated Region

async def region(ctx, name="Bob", Region=None):
    Database={
        "<insert users Discord ID>": {"Name": "Jim", "Region": "US"},
        "<insert users Discord ID>": {"Name": "Pam", "Region": "US"},
        "<insert users Discord ID>": {"Name": "Bob", "Region": "EU"},}

    for Discord_ID, Name_Value in Database.items():
        Name_Search = Name_Value['Name']
        if Name_Search == name:
            Name_Search = Name_Value['Region']
            break
        else:
            Name_Search = "Not there man"
    await ctx.send(Name_Search)```Outside of a function this loop works perfectly but when I put it into my Discord function, my `Discord_ID` variable becomes "not accessed".
How would I go about fixing this loop to find the associated region? ![peepoPeek](https://cdn.discordapp.com/emojis/614834652288385028.webp?size=128 "peepoPeek")

I should probably mention I have another if statement to check if the author's Discord ID is in the database & associate it with the respective Name-Region but it doesn't work if I check for someone else in the data base
So y'know ,region would find my ID & match it with my Name / Region but if I did ,region Bob then it's rip so yeah that's why I need to be able to find sub-values

Would it be better if I put this into a help channel?

[harsh pulsar]

@earnest holly what do you mean by "not accessed"

[earnest holly]

That's the thing, I'm not even sure entirely
Apologies for the late reply also

[harsh pulsar]

that's just pylance warning you that you never use the variable Discord_Id

also it's a good idea to use lower case for variable names in python, following standard code conventions makes your code easier to read

[earnest holly]

Alright, will do so in future
But how would I begin fixing my loop?

[harsh pulsar]

there's nothing wrong with your loop

def f():
    x = 1
    y = 2
    return y

if you copy and paste this, pylance will show the same warning for x

it just means you have a variable that you never use

[earnest holly]

alright interesting, thanks for verifying the loop is fine!

[velvet arrow]

losing my mind over installing sqlite3 on commandline

i had gotten it working a few days ago but for some reason once again getting the: 'sqlite3' is not recognized as an internal or external command,
operable program or batch file. error

i did the full environmental variable path add and followed a bunch of tutorials

anyone know what i might be missing?

[harsh pulsar]

what platform are you on?

[velvet arrow]

on windows

[harsh pulsar]

it sounds like you're trying to build it from source

[velvet arrow]

what i did was install the command line zip, extracted into C:/sqlite3

and added enviroment variable for sqlite3 in path

[harsh pulsar]

do you need the sqlite3 command line tool? i prefer https://sqlitebrowser.org/ on windows

you can also use C:\sqlite3.exe directly or whatever the path is

[velvet arrow]

yeah i do need for this specific case

i have to do this seed function that requires sqlite3

[harsh pulsar]

if it's C:\sqlite3\sqlite3.exe then PATH should contain C:\sqlite3

what seed function?

[velvet arrow]

its for this server code i am working on

[harsh pulsar]

i should have asked: what is a seed function?

it populates a database?

[velvet arrow]

oh yeah populating a database

tbh i can just use the full path in command prompt

but this is annoying i spent so long to try and fix lol

[brave bridge]

speaking of...

Has anyone tried using a version of SQLite different from the system one? I'd like to use the latest version (because of RETURNING and some other nice things), and I didn't have any luck even with https://charlesleifer.com/blog/compiling-sqlite-for-use-with-python-applications/

[lethal spindle]

has anyone used https://github.com/Rapptz/asqlite before ?

so i was basically wondering do i need everything in my code from the file setup.py ?

[brave bridge]

@lethal spindle No, setup.py is a special file that Python will run when you pip-install the library.

[lethal spindle]

ok ty
do i need the stuffs from __init__.py ?

[brave bridge]

If you want to install the library, you should be able to just do pip install git+https://github.com/Rapptz/asqlite.git

[lethal spindle]

i knows how to install it, but how do i makes table and stuffs ?

[brave bridge]

The README has an example

https://github.com/Rapptz/asqlite#asqlite

[lethal spindle]

ok ty

how do i have my bot add and edit and delete rows in the table though ?

[brave bridge]

The interface should be the same as in sqlite3, but with await/async with/async for where needed. If you've never used sqlite3, see https://stackabuse.com/a-sqlite-tutorial-with-python or the official docs: https://docs.python.org/3/library/sqlite3.html

[elder vessel]

how to connect to mongodb atlas using pymongo

leave it

[torn sphinx]

hello! I want to take the user's input and store it in database so that it can be accessible for late use! How do I do that in sqlite 3

[torn sphinx]

What input?

Like what data is this input collecting

A name and its value (strings)

So user details?

[quiet quarry]

hello guys
I am facing problem with my sqlite3 database when i make changes it saves the changes for almost 13 hours and then retakes back all the changes that i made, i am using two threads in my code and i am not committing the data but i set the isolation mode to None,
i was using the same queries with mysql database with setting autocommit to on and it was working fine what could be the problem in your opinion?
note : the script is for discord bot hosted on heroku

a friend gave me an advice to add begin tran and commit to the queries but it didn't work

https://replit.com/@AbdulrahmanShaw/YTUSU-Thread-MSSQL#main.py
this is my complete code

[torn sphinx]

Why are you not commuting then? If it worked in MySQL when you had commit on and now you have it off? @quiet quarry

[quiet quarry]

but i set the isolation mode to none

this should make autocommit mode on like mysql database

when i was working with mysql database i was depending on the autocommit mode

[torn sphinx]

Maybe try just using commit directly after executing your query

[quiet quarry]

could the problem be realated to the using of multiple threads?

[tranquil totem]

im making an unmute task loop
so the data is stored according to different server ids
each server id has a list of user ids that were muted and the time they should be unmuted at
now my task loop needs to check if utcnow is before or after the unmute time
if its after the unmute time then it unmutes the user
but im wondering how i can access the document first which has the list of muted members and their unmute times
because to access an item in that list, i would need to specify and search for the document with that list using the server id

[quiet quarry]

or related to the host

[torn sphinx]

Not sure tbh. Maybe something else is discarding the changes. Do you have separate connection for each thread?

[quiet quarry]

yes

[torn sphinx]

Then unsure. But try like I say, with commit directly. It should work like you said already with isolation mode none but idk 🤷‍♂️

[quiet quarry]

okay thanks for your effort dude🌼

[harsh pulsar]

I use mongodb+srv:// in the connection string for this

[storm wind]

The 'find' operation runs fast, but I don't know how to iterate a cursor in the mongo shell I'll have to look at that

[storm wind]

I checked python and pymongo and both are running with 'c'

[storm wind]

I ran from the cli and it's just as slow

[storm wind]

I have a compound index,

So, I tried from the mongo shell and iterated over the cursor with the default batch size. It took roughly the same time (5s)

I increased the batch size to 2000 (all 'found' records in one shot) and the initial find took ~ 5s .. subsequently itering the cursor was almost instantaneous

so it's not the cursor that's the issue

[storm wind]

I'm kind of convinced at this point that mongodb atlas might just be slow (M0 cluster). I'm leaning towards using Dreamhost / Dreamcompute

and installing my own

[harsh pulsar]

yeah, it looks like you've ruled out the query being slow since it's fast in the mongo shell with a larger batch size

throw money at the problem!

[plush herald]

Hey everyone ❤️ I made a chat bot for twitch, is it a good idea to save message log in an SQLite database? I will need to fetch entire chat history of a certain user in the future, Is there a better way to save the log? Thanks! Sorry for noob question ❤️ Please ping / reply so i see it haha

[harsh pulsar]

yes, sqlite is a great idea

[plush herald]

Okay thanks ❤️

[plush herald]

I just feel weird to use SQL to store this kind of data since it's not relational haha just 1 table

[vivid grail]

so im making a server listing discord bot, and im thinking about using either postgresql or sqlite . which one would be better for this ?

[torn sphinx]

help

[hasty juniper]

self.cursor.execute('SELECT user_cash, user_time, user_message FROM guildusers '
                            'WHERE guild = %s AND user_id = %s', self.guild_id, user_id)
        user_cash, user_time, user_message = self.cursor.fetchrow()
        return user_cash, round(user_time // 60), user_message
``````cmd
line 12, in user_stats
    self.cursor.execute('SELECT user_cash, user_time, user_message FROM guildusers
```its `psycopg2`

[proven arrow]

The way you are passing the parameters to the query is invalid. See how to pass parameters to the query here https://www.psycopg.org/docs/usage.html#query-parameters

[hasty juniper]

ye

Thx

[torn sphinx]

Solved

[placid vale]

Hi

[torn sphinx]

Hello?

[fringe mountain]

How do I bind a database to a server?

In postgres to a port number

[proven arrow]

@fringe mountain Generally you would set this up at the installation stage. But if you want to change the settings later you can do so by editing the config file called postgresql.conf

[still whale]

Postgresql server listens by default on 5432

[fringe mountain]

Yeah I want to change the server host later

[still whale]

I think you mean port... server could be confusing in this context

[snow crescent]

I'm trying to structure some data for a web app, and could use some advice on how to think about my data -- would appreciate any advice anyone has!

I have a backend application that's being fed data from a blockchain listener in the form of a list of dictionaries. Each dictionary contains key:value pairs about the transaction, which in this case, is a simple advertisement post - a message field, how much the user paid for the ad, an index, and a timestamp.

Users can also bump the posts, in which case the dict will also return how much was paid for the bump, along with the aforementioned fields.

I'm trying to figure out a way to structure my data such that we can sort in different ways--top 10 all-time, and top-10 daily in particular.

At first my plan was just to keep everything in one JSON file per sort category and add/sort/drop entries as they come in, but that seems inelegant and, frankly, pretty gnarly when it comes to summing "bump value" entries for the top-10-daily dict. Depending on how many people use the app I guess that could get super bogged down.

[still whale]

That's a cool problem. My question is if each dict can be unqiuely identified, or if each list in each dict can be uniquely identified

[torn sphinx]

hi

[still whale]

It also sounds like this is time-related data, so expect to have a pretty big table with potentially lots of data...

[snow crescent]

Exactly, and I can't just query the blockchain every time I want to look something up; blockchain i/o is pretty expensive.

So the plan is currently to uniquely identify each dict by writing a secondary JSON file after the sort, which will contain an ordering index, and to pass that off to the frontend guy.

[torn sphinx]

is there any way to fix this error in MySql? I understand that there is only one column.

pymysql.err.InternalError: (1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key')

My table:

await obj.execute(
            """CREATE TABLE IF NOT EXISTS bottemps(
            guildid BIGINT UNSIGNED AUTO_INCREMENT,
            userid BIGINT UNSIGNED AUTO_INCREMENT,
            tempwarns TINYINT(15) UNSIGNED,
            prioritarywarns TINYINT(5) UNSIGNED,
            PRIMARY KEY (guildid, userid)) ENGINE=MEMORY"""
        )

[snow crescent]

But I don't know the first thing about SQL/mongoDB (I've only really done data science stuff, so I'm coming from the world of Pandas tables), and I'm wondering if it'd be more efficient to do an add/sort/drop schema in one of those, or if those frameworks even support that sort of thing.

Either way if I add every "bump" from the blockchain without dropping old/irrelevant data somewhere, the data could get unmanageable really quickly.

[still whale]

Your definition includes two AUTO_INCREMENT columns. The error states you cannot have two of those.

[torn sphinx]

in other tables it allowed

[still whale]

If you don't know the first thing about SQL/mongodb, then you should start smaller. You can use the same data as input, but understanding how to format it perfectly for some data-analytics applications is non-trivial

So Mr.Idiot my recommendation for either SQL/noSQL is to have a simple schema and just get started ingesting and managing the data--you'll learn some stuff naturally this way too

[snow crescent]

Awesome! Thanks for the advice--I'll try to set up a simple schema and see how it turns out.

[torn sphinx]

@still whale then, which column of the primary key should have AUTO_INCREMENT, knowing that there would be 100 or more guilds and in each guild there would be 1000 or more users? (since it is compact)

[still whale]

I can't determine that right now. From the names of the columns, I'd expect each guildid and userid to each be foreign keys.

[torn sphinx]

when i try and call upon the db it says this, like if i try to blacklist someone or change a prefix
its trying to save it but throws that error

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

this error, i belive it has somthing to do with the cert being messed up, how would i fix it?

[still whale]

Depending on the library you are using, you should be able to make requests which do not verify the certificate (insecure requests)

You can also probably import the invalid (out of date or self signed) certificate into the operating system which is executing the code

[torn sphinx]

how?

@still whale

im using discod.py but this was a db error

python 9.5

pymongo

ect

[still whale]

Python 9.5 doesn't exist. python 3.5 has issues like this on macOS: https://github.com/Rapptz/discord.py/issues/423.

Please post the code failing to execute

[torn sphinx]

ok

        name='blacklist',
        description='Blacklist users from using the bot',
        usage='blacklist `[userid]`'
    )
    @commands.is_owner()
    async def blacklist(self, ctx, userid: int, reason=None):
        if blacklist.find_one({'user_id': userid}):
            await ctx.send(
                embed=create_embed(
                    'User ID already blacklisted.'
                )
            )
        else:
            if self.client.get_user(userid) != None:
                blacklist.insert_one({'user_id': userid})
                await ctx.send(
                    embed=create_embed(
                        f'User, <@{userid}> is now blacklisted.'
                    )
                )
                user = self.client.get_user(userid)
                await user.send(embed=create_embed(f'You have been blacklisted from Orby.\nHeat Level: `2`\nReason: `{reason}`'))
            else:
                await ctx.send(
                    embed=create_embed(
                        'Unknown User ID. Please make sure that user is in a server that I am in!'
                    ),
                    delete_after=30
                )```

    exec(open('helper.py').read())
except:
    print('DB Error')``` this part is in the main.py

[still whale]

Why is the code being executed in this way?

e.g. Why not import helper?

[torn sphinx]

i have impoerted it

at the top

it has everything efined and stuff wth the correct string

from helper import *

os.enviorment it all works but idk why its throwing this error

[still whale]

Hi @torn sphinx . It is unclear where that error is coming from. You should not execute code in helper.py via exec().

[torn sphinx]

i dont execut it in there

the blacklist command was in a dif file/cog

[still whale]

What's this then?

[torn sphinx]

and the that is in main.py

so it reads the enviorments aka the conection string ect

[still whale]

Did you review the github issue page?

Did you do some research about importing a certificate to your OS?

[torn sphinx]

ill go do that more in deph

thanks for the help 🙂

[torn sphinx]

grrrr i want to work on a project so bad

and i wanna use a database

[still whale]

Do it

[safe tusk]

Hi all, can someone help me out with dynamodb/python?
I'm trying to search based of the name but I'm running into issues.

it seems like there is 100 different ways to do it but I can't figure it out

[topaz wharf]

loop into db using for & in

[austere portal]

How can I execute dynamic queries using psycopg2?

[harsh pulsar]

what do you mean "dynamic queries"?

you can inject data into a query by using query parameters

[harsh pulsar]

don't use bare except:. it will hide errors from you.

maybe there is something else wrong in your python code... but you would never know, because you're just suppressing all errors.

[velvet edge]

🆘

i need help connecting password nad databse

[past current]

How would you share files from one device to another with a restful api?

[harsh pulsar]

@past current this isn't really a #databases question, nor is "a restful api" a good solution for sharing files between devices

what is your actual question?

[past current]

@harsh pulsar How to share files between devices?

[harsh pulsar]

ok, and you want to do this with python? because you can use something like https://file.pizza/ for this

[velvet edge]

?

[harsh pulsar]

if you want to ask about sharing files between machines using python, maybe a help channel is a good idea ( #❓｜how-to-get-help )

[harsh pulsar]

can you clarify what exactly you need help with?

[steel umbra]

whats the best way to go about lists in sqlalchemy? https://sqlalchemy-utils.readthedocs.io/en/latest/data_types.html#module-sqlalchemy_utils.types.scalar_list is proving to be a pain, and the only thing that works is https://stackoverflow.com/a/64356997/12692833, but I can't read it from the db since its a blob :\ any suggestions?

[harsh pulsar]

it should be text, not blob, according to the docs:

ScalarListType is always stored as text.

[steel umbra]

right, but I kinda gave up on ScalarListType because I couldn't figure out how to append data. I was trying to say the SO answer stored it as a blob, sorry for the miscommunication.

[harsh pulsar]

ah

it looks like you have to read the object from the db, modify its list attribute, then save it again

i can't imagine any other way to do it

[snow crescent]

Not sure if this is the right place for this question, since what I'm doing is basically pseudo-databasey, but I'm going to go for it anyway.

[steel umbra]

the only way it worked was session.discoveries = session.discoveries + [discovery], += didn't work, and .append didnt work, even though the type of session.discoveries was <class 'list'>. Any reason why?

[harsh pulsar]

+= has weird behavior with lists

what does "didn't work" mean?

[steel umbra]

it didn't update anything

[harsh pulsar]

specifically, lst += extra is lst.extend(extra), not lst = lst + extra

[steel umbra]

ah

session.discoveries.append(discovery) and session.discoveries.append([discovery]) made no change

[harsh pulsar]

well you'd have to save the object either way... not sure how to do that in sqlalchemy, haven't used it in a long time

[steel umbra]

just commiting it tho right?

im doing that :p

[snow crescent]

I have a list of dictionary items, such as

[
  {'name': 'carl', 'value': 20, 'timestamp': 1623257158},
  {'name': 'steve', 'value': 5, 'timestamp': 1623257914},
  {'name': 'steve', 'value': 1, 'timestamp': 1623259914},
  {'name': 'jane', 'value': 6, 'timestamp': 1623257158},
  {'name': 'jane', 'value': 3, 'timestamp': 1623257151}
]

Where I've sorted by name and then value(descending) fields, in that order. Is there a succinct way to drop all dicts for a given name value that are not the first occurrence in the list?

Target output would look something like:

[
  {'name': 'carl', 'value': 20, 'timestamp': 1623257158},
  {'name': 'steve', 'value': 5, 'timestamp': 1623257914},
  {'name': 'jane', 'value': 6, 'timestamp': 1623257158},
]```

The actual task is just to keep the highest value dict for a given name, which if there's a terse way to do that I would also be interested in, but I'm also interested in both problems for problems' sake

[placid vale]

This is a list of Dicts

What you can try to loop thour and reove value by key

Oh sorry i think i havent understand you question correct

[snow crescent]

I can try and reword it

What I'm trying to do is remove the whole dict from the list if it is not the first instance of {'name': 'steve'}

for example.

The problem I'm having is figuring out how to have a loop write the first time it sees {'name': 'steve'} to a new list, and ignore subsequent instances.

There are tons of examples for dropping duplicates in a list, but those are all for items that are wholly identical, not dictionaries that share a single key:value pair.

It's possible in Pandas with a .header() method, but I'm trying to keep external packages to a minimum

Otherwise I should probably just do this in MongoDB

Okay I think I found something that does work

nodup = []
indexlist = []
for item in mylist:
    if item['name'] not in nodup:
        nodup.append(item['name'])
        indexlist.append(mylist.index(item))
filterlist = [mylist[i] for i in indexlist]

I'm not entirely happy with it, as I think there's probably a better way to do it, but it works for now

[indigo condor]

Im having trouble trying to get Matplotlib and pyplot up and running

i downloaded matplotlib but pyplot isnt recognized by the interpreter

[torn sphinx]

Is aiosqlite same as sqlite3? If no wats the difference

Pls ping me if anyone replies

[brave bridge]

!blocking aiosqlite is for asynchronous code. Hopefully this explains what's the big deal.

[delicate field]

Why do we need asynchronous programming?

Imagine that you're coding a Discord bot and every time somebody uses a command, you need to get some information from a database. But there's a catch: the database servers are acting up today and take a whole 10 seconds to respond. If you do not use asynchronous methods, your whole bot will stop running until it gets a response from the database. How do you fix this? Asynchronous programming.

What is asynchronous programming?

An asynchronous program utilises the async and await keywords. An asynchronous program pauses what it's doing and does something else whilst it waits for some third-party service to complete whatever it's supposed to do. Any code within an async context manager or function marked with the await keyword indicates to Python, that whilst this operation is being completed, it can do something else. For example:

import discord

# Bunch of bot code

async def ping(ctx):
    await ctx.send("Pong!")

What does the term "blocking" mean?

A blocking operation is wherever you do something without awaiting it. This tells Python that this step must be completed before it can do anything else. Common examples of blocking operations, as simple as they may seem, include: outputting text, adding two numbers and appending an item onto a list. Most common Python libraries have an asynchronous version available to use in asynchronous contexts.

async libraries

The standard async library - asyncio
Asynchronous web requests - aiohttp
Talking to PostgreSQL asynchronously - asyncpg
MongoDB interactions asynchronously - motor
Check out this list for even more!

[stark turret]

For Django Rest API, is it better to access data available for the user via the User model with many foreign keys, or would be better to access data from the other Models and find data specific to the user?

[upper juniper]

So i have an app where users can create entries to log some items on, using SQLAlchemy
A user can have many entries, entries can have many items, an item can exist on multiple entries
Does this make sense?

association_table = Table(
    "entry_to_item",
    Base.metadata,
    Column("entry_id", Integer, ForeignKey("entries.id")),
    Column("item_id", Integer, ForeignKey("items.id")),
)

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    entries = relationship("Entry", backref="user")

class Entry(Base):
    __tablename__ = "entries"
    id = Column(Integer, primary_key=True, index=True)
    items = relationship("Item", secondary=association_table, backref="entry")

class Item(Base):
    __tablename__ = "items"
    id = Column(Integer, primary_key=True, index=True)

[eager igloo]

    print(mydoc["inviter"])
  File "C:\Users\Stacker 10000\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\pymongo\cursor.py", line 650, in __getitem__
    raise TypeError("index %r cannot be applied to Cursor "
TypeError: index 'inviter' cannot be applied to Cursor instances```

what does this mean

myquery = { "code": invite.code }
            mydoc = mycol.find(myquery)
            print(mydoc["inviter"])

[torn sphinx]

what mydoc is

Can I use aiosqlite in a cog file?

[eager igloo]

My doc is searching the collection(mycol) for the query

[thorn geode]

Yes you can.

[remote plinth]

can i make postgresql db without an app or something? (using python)

[harsh pulsar]

what do you mean by "an app or something"?

[covert harness]

are there any good resources for coding challenge/interview questions for MySQL/Python?

[proven arrow]

http://www.artfulsoftware.com/infotree/queries.php?w=378

It covers more than you’ll need for an interview but you can take your pick

[covert harness]

You're amazing! Will definitely be a good read up before going in. Thanks

[river ice]

the pick up of sql alchemy has been great . sql alchemy + postgresql in this week

I wouldn't go back to using mysql with mysql.connector . And i have just begun using postgres

sessions and declarative_base are so smooth , it's the first time i've used a python database module that wrote like any other python module .

I heard convincing reason to consider sql alchemy from this discord about a week ago

[elder elk]

Please help

I have raster data of satellite imagery and I want to load it into my postgres db

raster2pgsql -s 4326 -c -I -C -M -R -l 4 E:\S1_GRD_Parbhani\Processing\ToPsql\WaterBody20200604.tif -F -t 1000x1000 public.demelevation|| PGPASSWORD=PGPASSWORD psql -d raster_database -U postgres -h localhost -p 5432

currently I'm using above method, it runs without any errors

but after successful run it not showing into db

am I missing something, or need any corrections

if anybody have python script to load raster data that will be very helpful for me

I'm using it publish it on geoserver

[torn sphinx]

I want to update a value in sqlite3 by user giving input like this !change <oldName> <newName> the newName will then be replaced with oldname!! How do i Do this in data base?(sqlite3 python)

[jaunty galleon]

I don't use sqlite, but in normal SQL syntax it'll be something like:

'''UPDATE users SET name = ($1) WHERE name = ($2)''', newName, oldName```

In PostgreSQL:

await conn.execute('''UPDATE table_name SET name = ($1) WHERE name = ($2)''', newName, oldName)

[grim vault]

In SQLite:

conn.execute("UPDATE table_name SET name = ? WHERE name = ?", (newName, oldName))```

This will of course replace the oldName with the newName and not the other way as stated.

[remote plinth]

i always see pg admin in the tutorials that is it

[proven arrow]

PG admin is just a gui tool to manipulate the db. There are other similar tools as well. But If you mean can you do ddl statements from python then yes.

[remote plinth]

so, can i make the database using python only?

[proven arrow]

Yes

[valid vault]

I am getting the following issue:

0|app      |   File "/home/ollie/project/modules/ext/data.py", line 681, in update_xp
0|app      |     await conn.commit()
0|app      |   File "/home/ollie/.local/lib/python3.8/site-packages/aiomysql/connection.py", line 353, in commit
0|app      |     await self._read_ok_packet()
0|app      |   File "/home/ollie/.local/lib/python3.8/site-packages/aiomysql/connection.py", line 331, in _read_ok_packet
0|app      |     pkt = await self._read_packet()
0|app      |   File "/home/ollie/.local/lib/python3.8/site-packages/aiomysql/connection.py", line 574, in _read_packet
0|app      |     raise InternalError(
0|app      | pymysql.err.InternalError: Packet sequence number wrong - got 109 expected 1

Using aiomysql to handle async with a mysql database, and it is just not working

[proven arrow]

Please share code, also is your program multithreaded?

[valid vault]

Not multithreaded, just using async

let me just check a potential fix otherwise Ill share code one moment

thanks for response

async def update_xp(*data):

    async with pool.acquire() as conn:
        async with conn.cursor() as c:

            for id, level, xp in data:
                await c.execute("""UPDATE user SET xp=%s WHERE id=%s AND level=%s;""", (xp, id, level))
            
            await conn.commit()

This is the code erroring in the above example @proven arrow, but it is not restricted to this function, and it works most of the time but sometimes I just get this error

loop = asyncio.get_event_loop()
pool = loop.run_until_complete(aiomysql.create_pool(host=creds['host'], user=creds['user'], password=creds['password'], db=creds['database'], port=creds['port'], maxsize=250, loop=loop))

I've played around with the maxsize a bit, I'm aware 250 is big. Doesn't seem to make a difference to this issue

after a bit of research it seems the aiomysql package is just broken. I see a few people raised it online and the package is not active with updates anymore. very frustrating

just gonna have to switch to postgre ig

[proven arrow]

Yeah I was going to say. Code looks fine. Not sure how well maintained the package is, but if you can find similar issues on their repo then it might be the lib.

You may want to checkout the pymysql repo because that’s the underlying lib it uses.

[torn sphinx]

Hello

Have question

[valid vault]

Yeah honestly I've had a few issues with aiomysql that I've had no help debugging because the package is outdated. The docs are completely outdated (python2 syntax mostly), and it doesn't even support latest version of pymysql. I think I will just switch to asyncpg

[proven arrow]

Yeah up to you if your happy with the doing the migration.

[torn sphinx]

My question related with database

ETL processes

[remote plinth]

how cassandra db works? lol

[torn sphinx]

pls help me

[glad swan]

describe your question first, so anybody who can help will show up

[jade swan]

I get that error while using mongo db :

'Collection' object is not callable. If you meant 
to call the 'find_one' method on a 'Database' object it is failing because no such method exists.

That is my code :

main = MongoClient(
    "mongodb+srv://[e-mail]/Main?retryWrites=true&w=majority")

settings = MongoClient(
    "mongodb+srv://No [e-mail]/Settings?retryWrites=true&w=majority")

users = main["users"]

medicine = settings["medicine"]

def get_pharma(user_id: int):
    data = users.find_one({"_id": user_id})

    if data == None:
        return "not found"

    return data

[harsh pulsar]

@jade swan users is a database, not a collection. This is clearly stated in the error message.

[jade swan]

I realized that and fixed it, I forgot to determine which database I'm using and went ahead to doing the collection through the cluster itself

[jaunty galleon]

How can I handle TooManyConnectionError? any way to increase the connections amount allowed?

Should I do:

async with self.bot.pool.acquire() as conn:
  #some db stuff here
await self.bot.pool.release(conn)```

[torn sphinx]

c.execute("UPDATE tagtable SET userID = '%s' WHERE userID = '%s' AND tagName = '%s'" % (newUserID,userID,tagName))

Is this correct? (I dont have column name newUserId in my DB!)

[harsh pulsar]

if this is asyncpg, you shouldn't manually release the connection after, the async with does it for you

[harsh pulsar]

no, do not use % or any other string construction method to pass data into a sql query. your database query engine lets you use parameters instead, although the specific syntax varies for different database libraries. for example in sqlite, you would write

c.execute("UPDATE tagtable SET userID = ? WHERE userID = ? and tagName = ?", (newUserId, userId, tagName))

some libraries use $1, $2, ... as placeholders, others use %s. see https://www.python.org/dev/peps/pep-0249/#paramstyle and also consult the documentation for your specific database library

[austere portal]

I have a list containing the values I need to insert to the table, I tried using ", ".join(list) but if the list contains a string like "Test" "Test2" the query becomes INSERT INTO TABLE_NAME (col1, col2) VALUES (Test, Test2) which is invalid sql syntax, so the problem is how do I convert the items in the list to a valid sql data type like Test -> 'Test'

[harsh pulsar]

look in the post directly above yours, the one i just sent to the other user. use that same technique

it's called a "parameterized query"

[torn sphinx]

ok! Is it only for this case or for everytime not to use %

[harsh pulsar]

almost every time. for a beginner, assume it is every time.

that said, if you do need to interpolate data into a string, it might be tidier to use what are called "f-strings"

x = 123
print(f"The number is: {x}.")

instead of

x = 123
print("The number is: %d." % x)

[torn sphinx]

OK 👍! I'll tell u if it runs! thanks

[torn sphinx]

its giving me error --->

Extension 'data.tagCog' raised an error: TypeError: 'set' object is not callable

[austere portal]

Thanks I'll check that but how to I convert the values in the list to a valid SQL data type?

[harsh pulsar]

you don't, the database library does it for you

[harsh pulsar]

then you have other problems. show your code

[torn sphinx]

Main Code -->

@tagg.commands(name="transfer")
    async def tagtransfer(self, ctx, user: discord.Member.id, name):
        author = ctx.author.id
        DB.owner_change(user,author,name)
        await ctx.send("Transfer Successful")

db code ---->

def owner_change(newUserID,userID,tagName):
    c.execute("UPDATE tagtable SET userID = ? WHERE userID = ? AND tagName = ?",(newUserID,userID,tagName))
    db.commit()

[harsh pulsar]

@torn sphinx that error doesn't appear to be coming from this code. however, you should not reuse database cursors. create one cursor per query.

you might want to ask about this error in a help channel

[torn sphinx]

ok! first as u said I'll set the cursor per function!

[austere portal]

I am using the %s syntax but I still psycopg2.errors.SyntaxError

This is the query py "INSERT INTO %s (%s) VALUES(%s)" % (cls.table_name, ', '.join([str(i) for i in kwargs.keys()]), ', '.join([str(i) for i in kwargs.values()]))

[torn sphinx]

:0

i have a doubt

[harsh pulsar]

oh, you need to dynamically insert the table name? that's different. you need to insert the placeholders, not the data itself

[torn sphinx]

if you need to know SQL to use sqlite
why not just use SQL for your database

[harsh pulsar]

SQL is not a database, SQL is a language for interacting with a database

[torn sphinx]

I know?

[harsh pulsar]

so what is your question?

[torn sphinx]

i was asking why not just create a database with SQL if you know SQL anyways

instead of python

[harsh pulsar]

because you might want your python application to interact with the database

you don't need to create the tables using python specifically

[torn sphinx]

hmm

i see

that makes sense

[harsh pulsar]

@austere portal

table_name = cls.table_name
col_names = kwargs.keys()

col_string = ','.join(col_names)
param_string = ','.join('%s' for _ in range(len(col_names)))
query = f"INSERT INTO {table_name} ({col_string}) VALUES({param_string})"

db.execute(query, tuple(kwargs.values()))

you never pass in data using string formatting

you have to use it for column and table names because there is no other way

it's still somewhat dangerous

never accept column or table names from a user

if you are lucky, your code will break. if you are unlucky, someone can use a sql injection attack against you.

[austere portal]

Thanks

[austere portal]

But I wasn't executing user input

[torn sphinx]

Hello 👋
Wat does it mean by parameters are of unsupported type

[jaunty galleon]

I am not very great at sql queries, but you need AND

Or there is a better way

[harsh pulsar]

it means the parameters are of unsupported type 😉

"type" means data type - e.g. str is a data type. "unsupported" means the database library doesn't know what to do with it.

what do you mean by "last value"?

[torn sphinx]

With AND all the columns have to have values >0, OR doesn't work either.

[harsh pulsar]

you want the lowest ID number such that col_1 > 0, and the lowest ID number such that col_2 > 0, etc.?

[torn sphinx]

Now I just need the value from each column that is not zero

[torn sphinx]

yes

[toxic vector]

Has anyone here used TinyDB for something important before?
I have a small app that records data from some lab sensors. Right now I'm using the filesystem with big JSON documents to organize the data+metadata.
Moving to something like TinyDB for this would be pretty easy, but I'm wondering if using something like SQLite instead would be more future-proof/stable as the size of the data increases over time

[harsh pulsar]

you want these id's separately for each column, or you want the single lowest id number such that the entire row is nonzero?

@toxic vector sqlite is definitely going to be more stable and future-proof, if only because mongodb itself doesn't have a standard for its query language so tinydb will always be either playing catch-up or making up their own things

however tinydb does seem like a very nice library

@torn sphinx do you mind uploading some sample data to https://db-fiddle.net ?

[torn sphinx]

Thx 🙏;)

[harsh pulsar]

ah

@torn sphinx what database?

https://www.db-fiddle.com/f/pcQNpW24r88Y4gGbJUcXN1/1 like this?

[brave bridge]

Also @toxic vector, it seems that TinyDB is literally using a JSON file as its storage medium... that seems absolutely terrible.

If you have even thousands of items, it's not going to go well

[harsh pulsar]

wait, what

that's so cursed

[brave bridge]

it is 🙂

[harsh pulsar]

in that case definitely don't use it

sqlite json is objectively better lol

in fact, sqlite json is probably better than mongodb for a lot of basic uses that i've seen...

[toxic vector]

yeah when I first came across TinyDB a while back I was intrigued, but then I saw it was just storing JSON plain text.. on the filesystem.. which is what I was doing already, anyway. So using it seems like an extra middleman without much benefit.

Porting over all the existing JSON docs into SQLite seems not trivial though, so.. I shall see. Doing some reading up on it now

[harsh pulsar]

@toxic vector in python you can write json data into sqlite with json.dumps, sqlite internally will know how to handle the string-ified json

[brave bridge]

(well, you're probably better off not storing the JSON in SQLite, instead using it as a normal relational database)

[toxic vector]

i suspect this might be what I actually need to do

I've got three levels of nesting in each of my documents, so I'm trying to do some research first to figure out how to craft a sane table schema
this is the example I had so far when considering porting into TinyDB
I'd have many 'Project's, each with a variable number of dicts in the 'tests' array, each with a variable number of dicts in the 'readings' array

{
    "_default": {
        "1": {
            "uuid":"uuid",
            "name": "project",
            "customer": "customer",
            "productionCo": "productionCo",
            "submittedBy": "submittedBy",
            "field": "field",
            "sample": "sample",
            "sampleDate": "sampleDate",
            "recDate": "recDate",
            "compDate": "compDate",
            "analyst": "analyst",
            "numbers": "numbers",
            "notes": "notes",
            "tests": [
                {
                    "name": "testname",
                    "reportAs":"label",
                    "isBlank": true,
                    "chemical": "chemical",
                    "rate": 100,
                    "clarity": "clarity",
                    "toConsider": "toConsider",
                    "includeOnRep": "includeOnRep",
                    "obsBaseline": 75,
                    "notes": "notes",
                    "result": 100,
                    "readings":[
                        {
                            "average": 2,
                            "pump 1": 1,
                            "pump 2": 3,
                            "elapsedMin": "stamp"
                        }
                    ]
                }
            ]
        }
    }
}

[harsh pulsar]

yeah i'd just use json.dumps and worry about normalizing that data later

[torn sphinx]

I got the same syntax error. Do you know if we are able to apply the WHERE condition to each column individually? Because WHERE Col_1>1 works. So I am thinking maybe change in syntax? But which one, Col 1 >1, Col 2>1 doesn't work, maybe (Col 1, Col 2)>1 or something along those lines?

[harsh pulsar]

i'm not sure what you're asking for, there's no WHERE here

this query produces the non-zero minimum value in each column

[torn sphinx]

My thinking was maybe to go back and try a variation of what worked

[harsh pulsar]

what is the error?

it works in db-fiddle as you can see

!e ```python
import json
import sqlite3

sqlite3.register_converter('json1', json.loads)
sqlite3.register_adapter(dict, json.dumps)
sqlite3.register_adapter(list, json.dumps)

db = sqlite3.connect(
':memory:', detect_types=sqlite3.PARSE_DECLTYPES
)

with db:
db.execute(
'create table docs (i integer primary key, d json1)'
)

doc1 = {'a': 1, 'b': ['x', 'y', 'z'], 'c': None}
db.execute('insert into docs (d) values (?)', (doc1,))

doc2 = db.execute('select d from docs').fetchone()[0]
assert doc1 == doc2

[delicate field]

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

[No output]

[harsh pulsar]

@toxic vector ☝️

[toxic vector]

nice example, thanks

[torn sphinx]

ok sorry, like i was saying i am very new to this. I just realized how your code is working. One last question if you don't mind, where you have FROM mytable t. Guessing t is indexing each column correct?

[harsh pulsar]

@torn sphinx mytable t is shorthand for mytable AS t

[torn sphinx]

ahh ok, got it. Awesome, thanks. Saved me hours and days trying to figure this out. Thanks again!

[torn sphinx]

Pls help me in ETL processing

[civic swift]

Free suggested database?

[harsh pulsar]

sqlite

[civic swift]

I will check it out thanks

[river ice]

Just found that sql alchemy does not have an ‘’ignore duplicate’’ option while using ‘’insert’’ .. why is this not a thing ? I found a hacky way to do it for single-file-line inserts .. but nothing for bulk inserts . The solution for single ‘’insert’’ is using ‘’try:except:’’ . Bulk inserts stop at the first record that throws an exception. mysql has this option to insert only if it is not yet entered

The solution I am using is to first query the records

@grand current don’t do that here

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @brazen topaz until 2021-06-10 19:13 (9 minutes and 58 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

[river ice]

<@&831776746206265384>

[deft badge]

lol

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @grand current until 2021-06-10 19:14 (9 minutes and 58 seconds) (reason: chars rule: sent 3241 characters in 5s).

:incoming_envelope: :ok_hand: applied mute to @hearty sandal until 2021-06-10 19:14 (9 minutes and 58 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

[deft badge]

!pban 785192831614058537 ping spam

[delicate field]

:incoming_envelope: :ok_hand: applied purge ban to @hearty sandal permanently.

[deft badge]

!pban 397358728321564674 ping spam

[delicate field]

:ok_hand: applied purge ban to @grand current permanently.

[torn sphinx]

hey, i have a flask db and i need a db server for it, is there a lightweight db server yall would recommend

[river ice]

@torn sphinx lightweight i am not sure . i have heard good things of sqlite .. for database .

sql alchemy is a worthwhile library to use , that fits into python syntax . but i see learning how to use it , is learning how to use sql alchemy (a wrapper for communicating with databases)

flask has sql alchemy integrated into it , which i see offers some additional benefits when communicating with databases using sql alchemy . (i havent fully discovered them yet ) .. i see command line interfacing is packaged into flask while using sql alchemy

which i see makes it worthwhile

is your project on github by chance? @torn sphinx

i wonder where it is at

i see "salt rock lamp" has suggested sqlite to someone above .. and i've seen when he makes a suggestion it is having in mind what is easy to integrate with as a beginner

[torn sphinx]

Hi help me

[torn sphinx]

hey, thanks for your information! im using sqlite rn, but i need to use a db server instead now for docker. im usingsqlalchemy right now and its nice to use and it isnt on github its just a little project im making to have a better understanding about flask, docker, kubernetes and all of that

[harsh pulsar]

Do you need a separate server? If not, sqlite is good

Otherwise use postgres.

[torn sphinx]

i tried to do it with sqlite with docker and volumes, but couldnt get it to work so yeah i think ill need a seperate server