databases

[jaunty galleon]

#discord-bots

[torn sphinx]

I thought it was more of a database problem

[proven arrow]

CREATE TABLE users (
    user_id INTEGER NOT NULL AUTO_INCREMENT,
    email varchar(255) NOT NULL,
    PRIMARY KEY (user_id)
);

CREATE TABLE items(
    item_id INTEGER NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    PRIMARY KEY (item_id)
);

CREATE TABLE user_inventory (
  user_id INTEGER NOT NULL,
  item_id INTEGER NOT NULL
  PRIMARY KEY (user_id, item_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES items(item_id) ON DELETE CASCADE
)

@upper basin This is how the tables would look.

To get count then you can do SELECT COUNT(*) FROM user_inventory WHERE user_id = ?

[upper basin]

🧐 quick question about this, it would have a different row for each object?

[proven arrow]

Yes

[upper basin]

i see, i think that solves my problem, thanks for your help!

[cedar pagoda]

How do I Insert, with python, a tuple into a sql database?

[median swift]

rows = await self.client.conn.fetch("SELECT * FROM moderation WHERE type <> $1 AND userid = $2 AND serverid = $3", "Note", str(user.id), str(ctx.guild.id))

This is asyncpg, how do I order this based on numerical order of the id column?

[proven arrow]

Use order by https://www.w3schools.com/sql/sql_orderby.asp

[proven arrow]

Thats very vague question. Can you be more specific with which database/library

[median swift]

I understand this, but ORDER BY id does not order it 😦

[cedar pagoda]

['Helle wqda', 'awdawd a dawda', 'awdadw awdawd wadawd awdawd']

I want to add in python this tuple into a SQLite Database

[proven arrow]

Have you made the connection to the db yet?

[cedar pagoda]

yes

[proven arrow]

Works for everyone else. Run the query in a terminal and show output

[median swift]

it is a BIGSERIAL column, does that affect things?

I've solved it

It was because there were empty stuff in my database which must of affected things

thanks for your help 🙂

[proven arrow]

data = ['Helle wqda', 'awdawd a dawda', 'awdadw awdawd wadawd awdawd']
connection = db.connect(...) # whatever your connection is
connection.execute('INSERT INTO table_name (column_1, column_2, column_3) VALUES (?, ?, ?)', data)

each ? in the query corresponds to a value from the list

[torn sphinx]

How an earth do I format this data from MongoDB, I have been trying for days

        for result in results:
            id = str("_id")
            username = str("username")
            reason = str("reason")
            warner = str("warner")
            embed.add_field(name ="e", value = "Id:"+reason"Username:"+username"Reason:"+reason"Warner"+warner, inline=False)

[proven arrow]

What data?

[cedar pagoda]

db.execute("INSERT INTO applychoices VALUES (?, ?)", emoji, q_list)

I have it like that but I get this Error:

_ClientEventTask exception was never retrieved
future: <ClientEventTask state=finished event=on_command_error coro=<bound method Bot.on_command_error of <lib.bot.Bot object at 0x000001F36065F1C0>> exception=InterfaceError('Error binding parameter 0 - probably unsupported type.')>
Traceback (most recent call last):
  File "C:\Users\Lasse\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\client.py", line 348, in _run_event
    await self.on_error(event_name, *args, **kwargs)
  File "C:\Users\Lasse\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\Lasse\Documents\Discord Bots\None\lib\bot\__init__.py", line 137, in on_command_error
    raise exc.original
  File "C:\Users\Lasse\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\Lasse\Documents\Discord Bots\None\lib\cogs\modmail.py", line 191, in addapply_command
    db.execute("INSERT INTO applychoices VALUES (?, ?)", emoji, q_list)
  File "C:\Users\Lasse\Documents\Discord Bots\None\lib\db\db.py", line 66, in execute
    cur.execute(command, tuple(values))
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

Line 66 in "C:\Users\Lasse\Documents\Discord Bots\None\lib\db\db.py"

def execute(command, *values):
    cur.execute(command, tuple(values))

[proven arrow]

The second parameter is supposed to be an iterable. See how i did it

https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.execute

[pliant tiger]

I'm using this as an example, but when I retrieve a list I store inside of an SQLite database, I want to parse this as a real list, not as this weird string thing. Is this possible?

[proven arrow]

json.loads(mylist)

[pliant tiger]

that didn't seem to work

[cedar pagoda]

What is the second parameter?

[proven arrow]

Right because that string you showed would give an error as its not valid json.

This would work,

my_list = '["this is", "a test"]'
print(json.loads(my_list))

[proven arrow]

the list

[pliant tiger]

ah

[cedar pagoda]

the tuple (q_list) is changing from e.g:

['Helle wqda', 'awdawd a dawda', 'awdadw awdawd wadawd awdawd']

to

['Helle wqda', 'awdawd a dawda']

So its changing his lenght "randomly", how do I make it when the lenght of the tuple is not given?

[pliant tiger]

tysm, i didn't even notice that

[proven arrow]

Doesn't make sense. Show all the relevant code for that part

[cedar pagoda]

        q_list = []


        embed = Embed(title=f"Question Setup for {emoji}",
                      description=f"Starting Question query...",
                      colour=EColour,
                      timestamp=datetime.utcnow())

        QEmbed = await ctx.send(embed=embed)

        while question_wait:

            counter = counter + 1

            embed = Embed(title=f"Question Setup for {emoji}",
                          description=f"Send your {counter} Question which should be asked to applicant or type `stop` to stop adding questions.",
                           colour=EColour,
                           timestamp=datetime.utcnow())

            await QEmbed.edit(embed=embed)


            def checkM(m):
                return m.author.bot is False and m.channel.id == ctx.channel.id

            question = await self.bot.wait_for("message", check=checkM, timeout=1200)
            await question.delete(delay=2)
            if question.content == "stop":
                question_wait = False

            else:
                q_list.append(question.content)


        embed = Embed(title=f"Question Setup for {emoji}",
                      description=f"Finished the Question query.\nDo you want to confirm these Questions?",
                      colour=EColour,
                      timestamp=datetime.utcnow())

        await QEmbed.edit(embed=embed)


        await QEmbed.add_reaction("\U00002705")
        await QEmbed.add_reaction("\U0000274c")

        def checkYN(reaction, user):
            return user.bot is False and reaction.message.id == QEmbed.id and str(reaction.emoji) in ["\U00002705", "\U0000274c"]

        reaction, user = await self.bot.wait_for("reaction_add", check=checkYN, timeout=120)


        if str(reaction) == "\U00002705":

            print(q_list)

            db.execute("INSERT INTO applychoices VALUES (?, ?)", emoji, q_list)

def execute(command, *values):
    cur.execute(command, tuple(values))

[proven arrow]

What object is db? and what is emoji?

[delicate field]

Hey @cedar pagoda!

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

[cedar pagoda]

https://paste.pythondiscord.com/pimasubawe.py
Thats the full code of the discord.py command

[cedar pagoda]

def execute(command, *values):
    cur.execute(command, tuple(values))

db is following to this

[proven arrow]

db is an sqlite connection object or a custom class?

And emoji you are passing is a discord.Emoji class. Its not a type supported by the database.

[cedar pagoda]

custom class

[cedar pagoda]

k

[torn sphinx]

Can somebody help me format this MongoDB data so it looks cleaner? I tried this but it didn't really work.
https://paste.pythondiscord.com/ojolefutah.py

[whole mica]

mongodb question
how can i delete all data from a collection except the one with '_id': 0

[burnt turret]

I think a query like collection.delete_many({'_id': {'$ne': 0}}) would work

[whole mica]

it worked thanks!

[upper basin]

is it possible to select the sum of three columns and order it by the sum?

'select SUM(threestars*fourstars*fivestars) from users order by SUM(threestars*fourstars*fivestars) limit 10' doesn't seem to work

[burnt turret]

By sum of three columns, do you mean the sum of all values of those columns? Or just the sum per each row

[upper basin]

per row

[burnt turret]

Actually, I'm guessing you mean the latter because the first option wouldn't have multiple rows to order by

Yeah so you aren't supposed to use the SUM function there

SUM finds the sum of values over multiple rows, not columns

The query would just be something like

SELECT threestars + fourstars + fivestars AS 'total' FROM users ORDER BY total LIMIT 10
``` I think

[upper basin]

o.O i see, didnt know sum was over rows only

thank you

[inner sentinel]

Is there a specific way one is supposed to use those IDs?

[high geyser]

https://stackoverflow.com/questions/67657100/redis-hash-get-key-from-value

[spark geyser]

Hello

[torn sphinx]

Can someone help me structure my MongoDB data for my Discord bot

This is what I did but it doesn't work :\

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

[eager igloo]

def server_prefix(bot, message):
    stats = prefix_table.find_one({"guild_id" : message.guild.id})
    if stats is None:
        return "p?"
    else:
        prefix = stats.get("prefix")
        for i in stats:
            return i

how can I get it to return just the data? Currently it returns <function server_prefix at 0x000001B0E9CC8F70>

[eager igloo]

What you mean?

Ik

It should be stats["prefix"]

I think

didn't work

😦

[remote plinth]

def server_prefix(bot, message):
    stats = prefix_table.find_one({"guild_id" : message.guild.id})
    if stats is None:
        return "p?"
    else:
        prefix = stats.get("prefix")
        return prefix
```when u call it dont forget ()

[eager igloo]

I don't need () it returns data without

I just need to know why it is returning the wrong thing

[remote plinth]

u need

!e

def test(x):
   return x += "!"
print(test)

whoops

[eager igloo]

what u mean

what is !e for?

[remote plinth]

!e

def test(x):
  y = x += "!"
  return y
print(test)

[eager igloo]

I don't understand

Let me explain

[remote plinth]

this

[eager igloo]

I have a MongoDB database

and I have prefixes

I need this command to go into the database and pull the server's prefix

and return the prefix

[eager igloo]

I don't know how to apply this to mine

[remote plinth]

im just showing u the different without () lmao

[eager igloo]

how can I put into my code

I am so confused

[remote plinth]

can i see full code

• when u call the function

[eager igloo]

prefix_table = database["Prefixes"]
mycol = database["levelling"] 

prefix = 'p?'




def get_prefix(bot, message):
    stats = prefix_table.find_one({"guild_id" : message.guild.id})
    if stats is None:
        prefix = 'p?'
    else:
        prefix = stats.get("prefix")
    return commands.when_mentioned_or(*prefix)(bot, message)

bot = commands.Bot(command_prefix=get_prefix,
case_insensitive = True
)
  
def server_prefix(bot, message):
    stats = prefix_table.find_one({"guild_id" : message.guild.id})
    if stats is None:
        prefix = 'p?'
    else:
        prefix = stats.get("prefix")
    return commands.before_invoke(*prefix)(bot, message)

here is main.py stuff

the server_prefix is what I am working on

the difference is that get_prefix is to set the prefix

and server_prefix is just to make a variable holding the prefix

    def __init__(self, bot):
        self.bot = bot
        print("Prefix Module Loaded")

    @commands.command(aliases=['prefix'])
    async def change_prefix(self, ctx, prefix):
        stats = prefix_table.find_one({"guild_id" : ctx.guild.id})
        if stats == None:
            prefix_table.insert_one({"guild_id" : ctx.guild.id, "prefix" : prefix})
        else:
            prefix_table.update({"guild_id": ctx.guild.id}, {"$set":{"prefix":prefix}})

here is my cog where I upload the prefixes it works

@remote plinth

[remote plinth]

.

[eager igloo]

?

[remote plinth]

im reading

get_prefix working right?

[eager igloo]

yep

it allows to use the prefix and to mention the bot

The idea is to use the server_prefix in my help command

so that it uses the server's prefix in it

I can't use the ctx.prefix because it will use the @bot

[remote plinth]

basically, the prefix that the user entered in message u can get it by ctx.prefix

for example //help
ctx.prefix: //

[eager igloo]

yes

but here is the problem

if they do

@bot help config

it would say

the command is

@bot enable

I want it to say the server's prefix enable

that is why I need this function

@remote plinth

[remote plinth]

try not using the function and put this in ur help cmd use prefix variable for it

    stats = prefix_table.find_one({"guild_id" : message.guild.id})
    if stats is None:
        prefix = 'p?'
    else:
        prefix = stats.get("prefix")

[eager igloo]

ok

gimme 1 sec

@remote plinth now neither of my prefixes work

idky

[remote plinth]

huuuh

[pallid shard]

timestamp=datetime.datetime.now()``` How to change timezone here

GMT+2

me timezone

[jaunty galleon]

Isn't related to databases

[jaunty galleon]

I'll help you if you'd go to any help channel wich is open, or general

[pallid shard]

ok

[worldly panther]

Does INSERT IGNORE or INSERT OR IGNORE inserts data only if it doesn't exist? I've read that's its functionality but every time I run my application after closing it it inserts the data again. I'm using SQL

[brazen charm]

generally yes

[proven arrow]

It inserts, but during the insert if any error occurs it will ignore the error.
If your getting duplicate data then the reason is no error is being generated. And this might be because you don’t have the proper constraints on the table.

[past current]

Hi everyone, when creating a social media application, should I create a profiles table or just put all user data in the users table?

[proven arrow]

Depends on your application requirements and size

With 2 tables you can store identity data (email, password) in users, and the profile data in profiles.

[past current]

@proven arrow I see, yeah with two tables I belief It would be better.

[proven arrow]

Yeah that would be my choice also. Profile data can get big depending on what you store. Best to keep it as a different set.

[past current]

@proven arrow thank you very much again for you help. ☺️

[worldly panther]

I see... I'm using PyQt5's QSql module, but I'm executing every query with QSqlQuery, using SQL. I thought it would work as the commands are just SQL but every time the function is run by the program it inserts the data again (and I only want it to insert the data one time, when the user installs the app the first time). Any clue why is this happening? I can send the code, if necessary

[proven arrow]

Well like i said its because you wont have any constraints on the table that are causing conflicts. And what database are you using?

[worldly panther]

I understand. I'm using SQLite 3

[proven arrow]

Im not familiar with pyqt5, but if you have access to the sqlite database from a db browser/cli then can you show the output of: SELECT sql FROM sqlite_master WHERE name = 'your_table_name'

[worldly panther]

I have a SQL IDE extension that opens the database in table-format. However I can try sending that command

[smoky radish]

i always recommend you use a proper database tool, my favourite is DBeaver tho that is not a lightweight tool

[harsh pulsar]

Squirrel sql isnt bad

For sqlite3 you can use db browser

[worldly panther]

I'm going to check those out

Thanks

[high comet]

a postgres
user table
contains everything about a particular user
its contains 3 values/variables
1 ) name
2) bal
3) assests

assest would contain 0 no of values . but with time no of values would increase . There is limit to values.
The values that are in asset contain their own set of charactericts .
How this can be done

[robust fjord]

What's Row Level Security in PostgreSQL?

I've looked at the docs, but I didn't really understand it.

[rain plank]

What's the difference between serial and int generated by default as identity in postgesql?

[proven arrow]

It’s the same. Just that generated as identity is the newer way of doing it, and it's SQL compliant.

[proven arrow]

It’s for access control. Allows you to have stricter permissions

[remote plinth]

    @commands.Cog.listener()
    async def on_guild_join(self, guild):
        sql = f"INSERT INTO welcome (id) VALUES ({guild.id})"
        self.db.execute(sql)
        self.db.commit()

    @commands.Cog.listener()
    async def on_guild_remove(self, guild):
        sql = f"DELETE * FROM welcome WHERE id = {guild.id}"
        self.db.execute(sql)
        self.db.commit()
```will this work? new to sqlite

[proven arrow]

Yeah but for delete you can remove *

Delete removes the entire row, so no need for that.

[remote plinth]

DELETE FROM?

[grim vault]

Please use bindings, not f-strings

        sql = "INSERT INTO welcome (id) VALUES (?)"
        self.db.execute(sql, [guild.id])```

[proven arrow]

Yeah, and you should avoid using f strings to make queries with parameters. See why, #databases message

[grim vault]

Yes

        sql = "DELETE FROM welcome WHERE id = ?"
        self.db.execute(sql, [guild.id])```

[remote plinth]

edited

    @commands.Cog.listener()
    async def on_guild_join(self, guild):
        sql = "INSERT INTO servers (id) VALUES (?)"
        self.cur.execute(sql, [guild.id])
        self.db.commit()

    @commands.Cog.listener()
    async def on_guild_remove(self, guild):
        sql = "DELETE FROM servers WHERE id = ?"
        self.cur.execute(sql, [guild.id])
        self.db.commit()```

    @commands.command()
    @commands.guild_only()
    async def prefix(self, ctx, prefix):
        sql = "UPDATE servers SET prefix=? WHERE id=?"
        self.cur.execute(sql, [prefix, ctx.guild.id])
        await ctx.send(f"prefix is set to {prefix}")
```and this?

(thanks!)

[grim vault]

Looks good on the SQL side (because I have no clue about the bot thing).

[brave bridge]

I need to store two kinds of data:

• one is very rarely written to and is being read often
• the other one is a sort of an 'event log', to which certain events are written fairly often; I also need to run folds on these events (perhaps with some kind of cache)
  What would be a good way to store this data?
  I think I can get away with something simple like SQLite for the first one, but for the second one I assume I need something which supports concurrent writes.

[proven arrow]

Not sure what is your concern? Picking the database or somethign else?

[brave bridge]

Yes, I wanted to know what's the "standard" storage for this event-like data.

Or maybe I could just use Postgres for everything

[proven arrow]

There is no standard. Sqlite can work as well, just a matter of how you use it.

[brave bridge]

Perhaps instead of inserting the often-written data, I could pool the data and write it in batches. Would that make sense?

[cunning jolt]

for logs you might wanna look into loki or similar dbs

[proven arrow]

Sure it does make sense, if you start face issues with sqlite for that. Not sure how much or often you will be inserting.

[brave bridge]

that's not really logs

[cunning jolt]

a time series database would also be an option

[cunning jolt]

then what is it?

[proven arrow]

I have event logging setup for when i develop locally. Inserts around 30 per request excluding whatever else is going on in the app. Never had an issue.

[brave bridge]

I want to support about 50-100 insertions per second. I guess it will be fine

[cunning jolt]

i mean i use loki with a scylla backend, which handles log like data damn well

[proven arrow]

Yeah should be good. Your best option is to benchmark it.

[brave bridge]

as always

[brave bridge]

Users of the service will submit content that will be rated ("judged") according to a series of tests. I want to store those judgements so that it's possible to collect statistics and inspect how individual submissions succeeded/failed.

[cunning jolt]

i mean sql should work

[grim vault]

TBL: ...\Message.csv, 134469512 in 1376.99s ~ 97655/s```

[brave bridge]

yeah, I was just concerned with SQLite not supporting writing + (doing something else at the same time) (if I understand it correctly)

[grim vault]

SQLite on an SSD.

[brave bridge]

huh, that's impressive

Can you share the benchmark?

[grim vault]

That's my own importer from an csv backup into a new database.

[proven arrow]

Yeah but in a newer version they released something that can do this. Let me find the link

https://www.sqlite.org/wal.html

And here are some things you can go through which might help you, https://sqlite.org/whentouse.html

[brave bridge]

ooh

thanks

I think I'm underestimating sqlite

[brave bridge]

tested on my computer, when pooling rows in chunks of 1000, I get 100-200k writes per second

that's alien technology

[mystic vale]

MB someones can help me. How I can remote connect to Mysql DB using python?

[wooden stirrup]

With mysql.connector

[mystic vale]

Probably some guides

Or other stuff.

[wooden stirrup]

And yes you can find a good documentation for this module

[mystic vale]

I know. But I tryied and received some errors.

[wooden stirrup]

Show me the code

But probably not here

(And don't forget not to show the password of the db)

[mystic vale]

Ok)

[wooden stirrup]

Or there will be surprises in it x)

[main pelican]

Error:

raise ServerSelectionTimeoutError(
pymongo.errors.ServerSelectionTimeoutError: <pvt>.mongodb.net:27017: [Errno 11004] getaddrinfo failed, Timeout: 30s, Topology Description: <TopologyDescription id: <pvt>, topology_type: Single, servers: [<ServerDescription ('<pvt>.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('<pvt>.mongodb.net:27017: [Errno 11004] getaddrinfo failed')>]>

[sullen mauve]

GRANT ALL PRIVILEGES ON 'typing_gamedb'.* TO 'typing_game' @'%';

It says im doing something wrong? anything obvious?

[torn sphinx]

hello im kinda starting on python if sombady can pls help me with a script :
i need a py script that can create a txt file on the directory i want pls help and ty

[inner sentinel]

Asyncpg or aiopg?

[burnt turret]

asyncpg is what I hear most people to prefer

[inner sentinel]

ye

and sqlalchemy

[torn sphinx]

Idk rly i just start 🥶

@inner sentinel so do u know i look on many website but found nothing

[jaunty galleon]

@burnt turret You said ping you? I have a table called ranks_system, with mebmer_id, guild_id and xp (All are bigint, not pkey or something). How will I make the levels work? A giant dict with xp as key and level as value?

[burnt turret]

why do you need a dict?

the level will always be related to the XP right?

[jaunty galleon]

Yeah

[burnt turret]

so you can just have that calculation run when you retrieve the data

wdym by dict of xp to level though

[torn sphinx]

@burnt turret can u help me too pls?

Once u finish

[burnt turret]

uh what's your question

[torn sphinx]

This

[jaunty galleon]

Yeah I just figured it won't work

[burnt turret]

you should try asking in a help channel #❓｜how-to-get-help , this isn't related to databases

[torn sphinx]

Fuck sry

Imma look

Ty

[jaunty galleon]

But I want to do it with levels, how will I detect if the user progressed a level? Should I get his xp from before and than after and do the calculations ?

[burnt turret]

wdym by before and after?

shouldn't the database be the only source of truth

[jaunty galleon]

Should I also store the level than?

[burnt turret]

how are you planning on calculating the level?

[jaunty galleon]

First, on_message giving the member 10 more xp. Before giving the xp ,I get his data, and than giving him the xp. Than I get the xp after giving him, And doing this calculation:
current_level = round(new_xp ** 0.30)
old_level = round(old_xp ** 0.30)
if current_level > old_level:
new level
else:
not a new level

[burnt turret]

🤔

anyways i'd keep it this way itself? i.e calculating the level in python code itself

if you're fancy and want the db to do that work you could define a function for it

https://www.postgresql.org/docs/9.1/sql-createfunction.html and then use that while querying

[jaunty galleon]

Wdym let the python do it?

[burnt turret]

don't store level info

[jaunty galleon]

Yeah I won't

[burnt turret]

whenever you need the level, calculate it with python code

[jaunty galleon]

This is what I sent above

Let me do a small code to present what I did

@commands.Cog.listener()
async def on_message(self, message):
  old_xp = await a_func_i_made_to_get_the_xp(message.guild.id, message.author.id)
  await update_xp(message.guild.id, message.author.id, 10)
  new_xp = await a_func_i_made_to_get_the_xp(message.guild.id, message.author.id)
  if round(new_xp ** 0.30) > round(old_xp ** 0.30):
    #new level
  else:
    #not a new level```

[burnt turret]

i don't know how to feel about querying the database 3 times for every message 🤔

i guess databases are built for that sort of load

[jaunty galleon]

Any better idea maybe?

[burnt turret]

i'd still try reducing the number of queries though

[jaunty galleon]

@commands.Cog.listener()
async def on_message(self, message):
  old_xp = await a_func_i_made_to_get_the_xp(message.guild.id, message.author.id)
  new_xp_test = old_xp + 10
  if round(new_xp_test ** 0.30) > round(old_xp ** 0.30):
    #new_level
  else:
    #same_level
  await update_xp(message.guild.id, message.author.id, 10)

Is that better?

[burnt turret]

i guess so lol

[jaunty galleon]

Is there better idea?

[burnt turret]

how is that level system working though

[jaunty galleon]

Wdym?

[burnt turret]

shouldn't the new_xp ** 0.3 always be bigger?

[jaunty galleon]

It's round

Oh I get it

I am stupid

Damn it's harder than I thought

[burnt turret]

i'm too tired to think this through right now 😅

i haven't implemented it so this is a rough idea

but i'd have some sort of exponential function as you have for determining the amount of xp needed to get to next level

then i'd do a single update query, with a RETURNING clause that will return the new xp

then i'll check if the new xp meets the conditions for new level

[jaunty galleon]

Yeah but where do I store the levels

[burnt turret]

you don't need to

[jaunty galleon]

How much will I give a user every message?

[burnt turret]

🤔 a fixed amount, you can decide that

[jaunty galleon]

Yeah but if I gave him more than 0.5 it'll be wrong

[burnt turret]

wrong how?

[jaunty galleon]

It'll always go to the next level

[burnt turret]

on what intervals should the levels increase

it'd be simple if you had fixed intervals

not so simple if you want each interval to be bigger than the last

but that's just math

[jaunty galleon]

Have you looked at my nickname ?

[burnt turret]

an exponential function will rise way too fast https://why.life-is-pa.in/WO0X1G

ah lol

maybe a geometric progression could work here 🤔

but that math stuff isn't on topic for this channel 🙁

[jaunty galleon]

Yeah

I'll get to it later tho

Thanks

[burnt turret]

[alpine moon]

How can I insert something if ID doesn’t exist but Update if ID exists?

[proven arrow]

You can look for “upsert” in whatever database you use. That will do what you want.

[alpine moon]

Got it thanks

[chilly nimbus]

I need a database for my discord bot
I know basic sql does that mean i know postgres, also is psycopg2 easy to use if you know sql basic

[proven arrow]

No it does not mean you will know Postgres just like that, but sure you will be able to use it and pick it up quickly.

[chilly nimbus]

wdym pick it up quickly

they arent the same?

arent the queries that ill need to send the same

[proven arrow]

Yeah mostly the same, some different syntax between different database systems.

[chilly nimbus]

different wha

i spent an hour llearning sql

i need to spend another for postgres goddamn

i just need to do basic stuff, setting up the table, having unique ids for contents, updating tthe databasee etc

[proven arrow]

Yeah don’t worry it’s good. You don’t need to spend any more. If you have a question where something you learnt doesn’t work you can look it up for Postgres.

[chilly nimbus]

you mean most of the basic stuff like insert update where etc will work as well in postgres?

[proven arrow]

Yes

[chilly nimbus]

alr

one more thing

could yo please suggest me a postgres video

[proven arrow]

Sql is a language that has a standard. Database vendors can choose to follow it or follow it with some variations in certain places. For example add their own features etc.

[chilly nimbus]

or a tutorial

where postgres is used in python, psycopg2 i mean

because i am really new in sql

[proven arrow]

Not sure if there are any good videos on something that specific.

Also you should use asyncpg for a discord bot

https://magicstack.github.io/asyncpg/current/index.html#

[chilly nimbus]

oh ty for telling otherwise idhave spent another hour on this without knowing it wont work

[proven arrow]

It works but psycopg it’s not async module.

[chilly nimbus]

i see

ty

[proven arrow]

And here’s a link with info to use with discord #databases message

[wet stump]

Hello help me

[frigid glen]

With?

[torn sphinx]

i need help

help me to fix this eiro

hello

[fierce sand]

hello,sadly i cant help

[wet stump]

Write a query that swaps places for nearby students – 1 to 2, 3 to 4, and so on, if the number of students is odd, you do not need to change its numbering.

[chilly nimbus]

how do i establish a connection to postgres with asyncpg, i have the database in my pc

while installing postgresql, after the installation finished, it said failed to load sql models, will i face any issues if i dont resolve this issue, also, whats the problem

[chilly nimbus]

how do i fix this error
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

[spice whale]

hello I have created an collection with mongoDB and in this is a array of User IDs. Now I want something like that:

if ctx.author.id in db[ids]:

But with the db[ids] its not working.

The Code:

cluster = MongoClient("STRING")
db = cluster["id"]
collection = db["Premium"]

[proven arrow]

Make sure the database server is running on your machine

[chilly nimbus]

How o i do that

[soft escarp]

my mongo db stuck here for while

.from mongoDb compass*

[dim sluice]

Damn

Bro

[soft escarp]

yes?

[chilly nimbus]

please help

[torn sphinx]

am so good at databases

feel free to ask me

[chilly nimbus]

@torn sphinx i literally referenced my question, if you wanna answer then answer already

[soft escarp]

/

still not working i even created test user for that cluster! but still it's stuck in connecting thing!

[torn sphinx]

need help?

[minor ruin]

            "name"    TEXT,
            "user_id"    TEXT,
            PRIMARY KEY("user_id")
            );'''```

Yes

That’s sql code

[kind void]

anyone here using tortoise orm have a better example of using signals? their docs don't really have much information.

[short flicker]

Anyone familiar with SQLAlchemy and doing/maintaining a Type 2 table?
Ideally having SQLAlchemy manage the eff_date, exp_date, and current_ind meta data flags

good articles/videos are more than welcome
thank you for anyone able to contribute

[raw blade]

hello! i'm trying to save a nested dictionary using mongoengine, in doing so i need to create a subclass of mongoengine.Document but my dictionary has integer keys along the way--how do people typically deal with this?

[harsh pulsar]

sometimes there's no option but to recursively walk through a dict and replace values

but idk what mongoengine can support - maybe it does the number-to-string conversion for you?

[white vortex]

Hello I'm trying to make a password manager exactly like what kalle hallden made but the change is that the app encrypt the password and put it into the database and decrypt it when I want to show the passwords

my program is something like this
: list
DATA IS HERE
wait a sec
: list

ROWID = 1
Service name = facebook
Email = [e-mail]
Password = secretpasswordlol

ROWID = 2
Service name = youtube
Email = [e-mail]
Password = PASSWORD

:
looks like this
but in linux if i typed strings database.db it will show the data and the tables
looks like this
SQLite format 3
tableaccountsaccounts
CREATE TABLE accounts (
service DATATYPE text,
email DATATYPE text,
password DATATYPE text)
[e-mail] PASSWORD
[e-mail]
can I encrypt the password??

[harsh pulsar]

@white vortex encrypt the password in your python application, save the encrypted password to the db

Or maybe use sqlite and encrypt/decrypt the entire db at once

[cyan yacht]

Is it possible to subclass aiomysql.connection.Connection and connect to that?

[grim vault]

(user) is not iterable use (user,) or [user]

[jaunty galleon]

tf you changed your nick to @velvet ridge

Math is not easy

[grim vault]

datas is either None if nothing is found or a tuple not a 2D array.

result = await datas.fetchall() and after that use result.

or append .fetchall() to the execute line.

Yeah, I think so. I don't use the async version.

also data != datas you are using both names

maybe you can just:

async for row in db.execute("SELE..."):
  # row is now your selected columns

But as I said, I have no clue about the async stuff.

[dusty peak]

Write the SQL sentence that provides an 8% increase in the salaries of all employees working in the 'Internet Banking' project. help me

[dusty peak]

UPDATE EMPLOYEES SET maas= CASE proje_adi WHEN 'Internet Banking' THEN maas+(maas*.08) ELSE maas END WHERE proje_adi IN ('Internet Banking');

is it ture?

[brazen charm]

Thats a very complicated query for what it's doing

Although this looks awfully alot like a homework / school work question 👀

[dusty peak]

excersice

help

[brazen charm]

well

firstly

[dusty peak]

We don't see sql in school yet

[brazen charm]

CASE proje_adi WHEN 'Internet Banking' THEN maas+(maas*.08) ELSE maas END you dont need any of this

[dusty peak]

hmm

[brazen charm]

there isnt any point doing that when you have that WHERE proje_adi IN ('Internet Banking'); which is filtering out the stuff you want in the first place

going onto WHERE proje_adi IN ('Internet Banking');, you shouldn't use the IN keyword like that

it's only a single item so you're better off doing WHERE proje_adi = 'Internet Banking';

[subtle drift]

morning from Greece

[jaunty galleon]

How to handle db that doesn't work? After once or twice after I open the db(PostgreSQL(asyncpg(elephantsql))) but it says:
Too many connections for role "usernameindb"

[grim vault]

Close the connection after you used it? Or keep a global connection to use all the time?

and: maas+(maas*.08) = maas * (1 + 0.08) = maas * 1.08

[dusty peak]

keep maybe

close

[remote plinth]

pls help

i can't access to my server host's terminal so how can i set mongodb path using python

if there is a way

i can't mongod --dbpath ....

[harsh pulsar]

You usually shouldn't be starting mongod from inside python @remote plinth

You don't need to know the db path to connect a client

[remote plinth]

even if its local?

i mean

i didnt do mongod --dbpath "path/..."

i cant

[torn sphinx]

Does anyone know whats wrong? I use SQLite3

[harsh pulsar]

You need the hostname, port, username, password

If its on your physical machine then the hostname is localhost

[burnt turret]

the port should be 27017 by default as well

[remote plinth]

what i need:
i want to set my db path like using mongod --dbpath "the path" but i don't have access so some stuff to do this in my host so im asking if there is a way to set the path using python

[harsh pulsar]

@remote plinth are you trying to start the mongo server?

Or connect to an existing server?

[remote plinth]

start

if i cant set the path using python, i will switch db i have some sql knowledge ._.

[white vortex]

but i can't decrypt it

password manager

[harsh pulsar]

i see. so you have access to run python programs, but not to a regular shell?

trying to run a mongo server by running a python program might be considered an attempt to circumvent restrictions. are you trying to do this on repl.it or something?

[white vortex]

wut no

[harsh pulsar]

why can't you decrypt it?

[white vortex]

when i type list

it just return null

[remote plinth]

there is a startup command which runs the main file

i can change main file's name..

[harsh pulsar]

can you provide more context and detail? you're forcing people to guess about your situation, which makes it hard to help.

"I am using X platform which imposes A B and C requirements."

[remote plinth]

i'll screenshot

[white vortex]

me or @remote plinth ??

[remote plinth]

me

[white vortex]

i'm getting confused

[harsh pulsar]

@white vortex in the discord interface, it should show which post i am replying to

[remote plinth]

here is the startup command, if i start the server it will run the main file

[harsh pulsar]

see the small line above my message? @white vortex that is the message i am replying to

[white vortex]

i'm blind

xD

[harsh pulsar]

so you have been given a container and you are only able to run a python script in it?

who is providing this to you?

[remote plinth]

not only py

[remote plinth]

well its free so...

[white vortex]

i'm using another file called hasher when the password-manager.py send an info to the database-manager.py the password will go to hasher.py

[remote plinth]

the admins can change startup command to me when i claim ticket in their discord server

[burnt turret]

perhaps it would be better to use mongodb atlas in this case then?

[naive sandal]

@dense lily I would ask your question here

[remote plinth]

meh 512mb ..

[harsh pulsar]

hashing and encryption are different things. if you're creating a password database, you should be encrypting and not hashing.

[burnt turret]

what sort of data are you storing? @remote plinth

[white vortex]

how can i encrypt the database??

[remote plinth]

512 is enough but would be better if i had in local host

ima try sqlite

ik both so np

[white vortex]

i didn't use mongodb before so it will be hard to recreate the project

[dense lily]

Great

[white vortex]

@harsh pulsar ?

[burnt turret]

can you give some more context? i don't see a no ratelimits in your code but it is there in your output?

can you explain what you want to be doing + what is happening that is not expected

[grim vault]

Have you tried:

async for row in db.execute("SELECT guild_id, user_id, content FROM log WHERE user_id = ?", (user_id,)):
    # row is a tuple(guild_id, user_id, content)
    print(row)

[white vortex]

how can i encrypt a database?????

[grim vault]

add a LIMIT 4 to the end of the statement of the SELECT

Do you have a column you can order by?

sql_stmt = (
  "SELECT guild_id, user_id, content"
   " FROM log"
  " WHERE user_id = ?"
  " ORDER BY current_time DESC LIMIT 4"
)
async for row in db.execute(sql_stmt, (user_id,)):
    # row is a tuple(guild_id, user_id, content)
    print(row)```

descending order to get the newest first

The column current_time is used, whatever it is in there.

for that user, yes.

[white vortex]

how can i encrypt a database???

[grim vault]

It's highly database dependent.

[chilly nimbus]

"UPDATE channelinfo SET status = $1 WHERE channel_id = $2", status, channel_id

when i run this query, its creating a new row instead of updating

[dull vessel]

what problem?

[obsidian carbon]

Anyone familiar with Dictalchemy?

[remote plinth]

database disk image is malformed sqlite error

[grim vault]

Your value will be a tuple of the columns of the paroli table, so -> (1, "123") or maybe even a list of one tuple because you used .fetchall() -> [(1, "123")].
Just print out the value and take a look.

And I'm pretty sure if value != text: is not what you want.

[dull vessel]

yeah thanks

[chilly nimbus]

@grim vault

[grim vault]

An UPDATE should not make a new row.

[chilly nimbus]

help bro

[grim vault]

How do you know it's the UPDATE which is generating the new row?

[chilly nimbus]

this is what i am doing
in my update
i have the first column as normal and second column has primary key

will that make an error

[chilly nimbus]

because i am calling this function and getting

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: UniqueViolationError: duplicate key value violates unique constraint "channelinfo_pkey"
DETAIL: Key (channel_id)=(836218450560417796) already exists.
i am also getting this error when trying to update

@grim vault

[grim vault]

also?

Whats the table look like?

Do you have some sort of SQL-shell to access the database to try out commands?

UniqueViolationError looks like an INSERT failure.

[chilly nimbus]

No, i am using asyncpg in postgres

[chilly nimbus]

Yes, its trying to insert a value wtf even internet has no solutions

[grim vault]

The screenshot looks like another way to access the database.

[chilly nimbus]

When it comes to skills I get only common skills everyone has and when it comes to errors, I get the most rare errors,

[chilly nimbus]

Its pgadmin, i was about to say, yes i can access the db through my pgadmin

And i can use it as shell

Let me try

[grim vault]

Lets try it then:

UPDATE channelinfo SET status = 'err' WHERE channel_id = 50560417796;```

[chilly nimbus]

I tried doing that in shell

It updated the values, and when nothing was present, it didnt show any errors but didnt inster anything either

But it just doesnt seem to work in python

Wtf

[dull vessel]

how to use last value?

self.rows[?][1]

[grim vault]

-1 is the index of the last entry in python.

[chilly nimbus]

What do i do @grim vault

[grim vault]

More debugging?

[chilly nimbus]

bro

[grim vault]

I do it like:

stmt = "UPDATE table SET volumn = value WHERE column = ? AND column = ?"
bind = (value1, value2, value3)
print("SQL:", stmt, bind)
res = curs.execute(stmt, bind)

[chilly nimbus]

see this

async def upchnl(self, ctx, status : str):
        await self.bot.mydb.channel_test(ctx.channel.id, status)
        await ctx.send("Done")

async def channel_test(self, channel_id, status):
        await self.db.execute("INSERT INTO channelinfo (channel_id, status) VALUES ($1, $2)", channel_id, status)
    
    async def up_chnl(self, id, value=15):
        await self.db.execute(f"UPDATE channelinfo SET status = {value} WHERE channel_id = {id}")

🙂

[grim vault]

argl, f-strings in SQL.

[chilly nimbus]

i just thought i was calling up_chnl but i was calling upchnl

[chilly nimbus]

which calls channel_test

:>>>>>>

https://tenor.com/view/konosuba-kazuma-nervous-smile-huh-gif-16687883

alr now i can start working on my database

[grim vault]

Yes, and please, no f-strings!

Even if you're sure whats in the variable.

[chilly nimbus]

why*

[chilly nimbus]

which formatting to use then

[grim vault]

Use the $n notation with the binding vars to avoid SQL-injection.

[chilly nimbus]

i see ok

but what is sql injection

[grim vault]

Basicly someone could use a SQL-command as username which can be executed on your database.

f"UPDATE users SET name = '{user_name}' WHERE user_id = {user_id}"
-> I enter as username '; DROP TABLE users; --
-> UPDATE users SET name = ''; DROP TABLE users; --' WHERE user_id = 1
-- is the line-comment in SQL like # in python-code

[chilly nimbus]

i see

[dull vessel]

why dont working?

[grim vault]

What do you expect? You select the entry where pass = 123 and that's what you get.

[dull vessel]

How can I make the column get out completely?

[grim vault]

I really don't know what you want. DELETE to remove entries in the table, UPDATE to change, INSERT to make new one and SELECT to retrieve them. I suggest looking for an SQL-Guide/Tutorial online.

[dull vessel]

i need write 123 = ok
write 333 = ok

[grim vault]

I hate screenshots. But here we go:

pass_input = int(self.lineEdit.text())
check_pass = cur.execute("SELECT COUNT(*) FROM paroli WHERE pass = ?", (pass_input,)).fetchone()[0]
if check_pass > 0:
    # passed the test```

[dull vessel]

go

[grim vault]

SELECT COUNT(*) will count the rows where the given condition is true. Only .fetchone() is needed because COUNT(*) will only produce one line and the [0] is because the result is a tuple and we only need the first entry of it.

[remote plinth]

is 512mb (mongodb) enough? im storing data like prefixes, welcome channels, etc...

[brazen charm]

Err probably

[remote plinth]

k..

[brazen charm]

Like unless you're storing a bunchhhhhh of data I doubt you'll get over 512MB or even close

[remote plinth]

well if its not enough i'll make another acc

for another collection hehe

[brazen charm]

I mean that is against ToS and they have things to prevent that but sure

I mean you gotta have a pretty big bot and store alot of stuff to get to 512MB really

unless you're storing a bunch of images in said DB

[remote plinth]

kk no problem tho

[dapper halo]

Hey guys, please I need help coming up with an SQL code.

I have a table with datetime field (2020-05-26 00:00:00:00),

I want to write a query that groups all similar month and year together and count them.

i.e

Date | Count
2020-05 | 4
2020-01 | 6
2019-04 | 2
2018 - 11 | 8

[dapper halo]

SELECT COUNT(date) , date
FROM public."<Table name>"
GROUP BY date

I have this, but this includes the DD of YYYY-MM-DD

🆘

[grim vault]

What is the database system and what is the column type?

[torn sphinx]

Ive changed to portgresql and i didn't understand whatt i need to change in my code

[jaunty galleon]

Why won't you send the code and we would see what you got?

[torn sphinx]

Ok wait

https://mystb.in/MidNodeFails.python

[jaunty galleon]

Are you sure this is PostgreSQL

[torn sphinx]

No i said i want to change to PostgreSQL and i did not understand what i need to change in my code

[jaunty galleon]

Ah ok

!d asyncpg I would recommend using asyncpg(the only one I know, many people use it)

[delicate field]

asyncpg

[torn sphinx]

i did pip install asyncpg

but i did not understand what i need to change in my code

[jaunty galleon]

Well, it's a little more complicated

[torn sphinx]

docs maybe?

[jaunty galleon]

If you want to host it locally, you should download PostgreSQL via https://www.postgresql.org/ , else, see https://www.elephantsql.com/ to host it as a service

[torn sphinx]

I donwloaded it

downloaded*

[jaunty galleon]

There is a free version, yes

[jaunty galleon]

Oh ok, now you'd need to create a user from some window of PostgreSQL, something very weird

Let me google it one sec

[torn sphinx]

pgAdmin4?

[jaunty galleon]

No, that is to view the data

[torn sphinx]

Oh

so how the app called

?

[jaunty galleon]

https://www.guru99.com/postgresql-create-database.html

[torn sphinx]

WT*

[jaunty galleon]

When it asks for password, input(I don't think it'll show the password you are writing) to the prompt, and than continue

You have a default user named postgres

[torn sphinx]

Oh thats why i didnt see the password

[jaunty galleon]

When you downloaded PostgreSQL, is sould have told you to make a password, you do remember it right?

[torn sphinx]

OFC

Done it

Why it doesnt pint nnothig

print

@jaunty galleon

[jaunty galleon]

It doesn't print CREATE DATABASE ?

[torn sphinx]

NNOPE

[jaunty galleon]

And just so you know, this is a database, not a table. A database is constructed out of tables

[torn sphinx]

Ummmmmmm so how do i make tables or i cant

[jaunty galleon]

You make tables in the python script

[torn sphinx]

Oh ok

[torn sphinx]

.

[jaunty galleon]

Yeah that's weird

[torn sphinx]

I can create with pgadmin too

[jaunty galleon]

Oh try it than

Make sure you know the username and password and local host

[torn sphinx]

Ik

I create and it worka

What now?

[jaunty galleon]

!d asyncpg.pool.Pool

[delicate field]

asyncpg.pool.Pool

class Pool```
A connection pool.

Connection pool can be used to manage a set of connections to the database. Connections are first acquired from the pool, then used, and then released back to the pool. Once a connection is released, it’s reset to close all open cursors and other resources *except* prepared statements.

Pools are created by calling [`create_pool()`](https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.pool.create_pool "asyncpg.pool.create_pool").

[grim vault]

The question is why you want to change from sqlite to postgresql? Do you have performance trouble? Do you have size trouble?

[torn sphinx]

I use heroku for host and everytime i deploy something the .db files are reset

[grim vault]

Ok, that sounds inconvenient.

[torn sphinx]

Doesnt help

[jade swan]

I have this command

    @commands.command(aliases=['acc', 'prof', 'profile'])
    async def account(self, ctx, member: discord.User = None):
        if member == None:
            member = ctx.author

        data = getUserById(member.id)
        print(data)
        return

(it's way longer than just that) I'm trying to print the data that returns because it passes a check that it shouldn't pass since the data should be not found.

This is the getUserById function :

def getUserById(user_id: int):
    results = accs.find({"_id": user_id})
    return results

This is what I get :

<pymongo.cursor.Cursor object at 0x0000026B1D182EE0>

Can someone please tell me what is that?

[harsh pulsar]

@jade swan https://pymongo.readthedocs.io/en/3.11.0/api/pymongo/collection.html#pymongo.collection.Collection.find find returns a Cursor object

[jade swan]

sorry edited last time

because when i do a for loop it says local variable 'result' references before assignment

[harsh pulsar]

you have to iterate over the Cursor object to get your data

in this case you should just use find_one instead of find since you are expecting at most one document

def getUserById(user_id: int):
    return accs.find_one({"_id": user_id})

[jade swan]

alright let me do it

[harsh pulsar]

if you did need to use find because you were expecting multiple documents, you could use list() to consume the entire cursor and collect the results in a list

[jade swan]

works! thank you!

nah im looking for a single document

[harsh pulsar]

cursor = accs.find({"color": "red"})
results = list(cursor)

more generally,

cursor = accs.find({"color": "red"})
for doc in cursor:
    # do something with each doc

[quaint sky]

Hello I am new to mssql and originally Ive been using mysql. For mssql unlike mysql, when I try pandas dataframe.to_sql it throws me operational error saying integer cant be with text type. My data contains mixture of numeric values and text values in a column.

My column names are inserted properly and from mssql it seems as it has automatically set the column type as text type. From stack overflow I realized text type does not allow integer, and I should use varchar type.

Is there a way where I can tell mssql that my columns in pandas dataframe will be varchar type from Python?

[harsh pulsar]

@quaint sky you might need to convert the integer values to strings in python before saving

[quaint sky]

I tried df[columnName].apply(str) but I still got tthe same error

[harsh pulsar]

.apply doesn't replace the column

what's the name of the column? i can show you an example

if the column is called y, you can use df.assign(y=df['y'].apply(str)).to_sql(...)

[dull vessel]

Who can help me pls??

[quaint sky]

Sorry for the late reply! Im currently on my way to work.. Ill try it once I arrive.

[harsh holly]

When implementing data in google colab does anyone know why i get this error? ```AttributeError Traceback (most recent call last)
<ipython-input-10-22a39e0f2182> in <module>()
1 import io
----> 2 dataset = pd.read_csv(io.BytesIO(uploaded['companiesForML'.csv]))
3 dataset.head(10)

AttributeError: 'str' object has no attribute 'csv```

[trim marten]

Because io.BytesIO() doesn't return a CSV

[torn elm]

the specific error looks to be caused by your single quote not including the .csv file extension. It thinks you are trying to call a method named csv on your 'companiesForML' string.

[harsh holly]

yea i got that fixed im not sure how i missed that

[trim marten]

Ugh, oh yeah me too 🙃

[harsh holly]

lol

thanks tho

[trim marten]

🤣 Am just going to go back to the work I was avoiding

[harsh pulsar]

In a MongoDB aggregation, is it possible to combine these 2 $addFields stages into a single one?

[
  {
    "$addFields": {
      "fooCollection": { "$first": "$fooCollections" }
    }
  },
  {
    "$addFields": {
      "fooItems": "$fooCollection.items"
    }
  }
]

(edited for readability; i don't care about explicitly creating the fooCollection field if there's a way to do it in one step)

[woeful garnet]

hi

can i ask for a help?

[torn sphinx]

ad = {'Fatih':'Terim','Ali':'Rıza'}
def kayit(gelen_ad,gelen_soyad):
ad[gelen_ad] = gelen_soyad
print((""""Adınız {} Soyadınız {} olarak kayıt oldunuz.

    """).format(gelen_ad,gelen_soyad))

sor = input("Giriş yapmak istermisiniz? y/n >>>")
if sor=='y':
    giris_kontrol(gelen_ad,gelen_soyad)
else:
    exit()

def giris_kontrol(gelenad,gelensoyad):
s_d = False
for i in ad:
ad_g = i
soyad_g = ad[i]
if gelensoyad==soyad_g and gelenad==ad_g:
print(("""
Giriş başarılı oturum açıldı
Hoşgeldiniz {} {} Bey
""").format(ad_g,soyad_g))
s_d= True
if s_d==False:
print("""Hesap bulunamadı,Kayıt olmak istermisiniz?
y/n
""")
sor = input(">>>")
if sor == 'y':
ad_Sor = input("""
İsim:
""")
soyad_Sor = input("""
Soyad:
""")
kayit(ad_Sor,soyad_Sor)
else:
print("Çıkılıyor")
exit()

def kullanici():
ad = input('Adınız >>>')
soyad = input('Soyadınız >>>')
giris_kontrol(ad,soyad)

kullanici()

kebap + I run

[little flicker]

I'm not sure how that would ever work, since rows[0][3] is not 4 in the example you gave

Also, the whole bit there is weird. Why iterate through them but only look at the first row?

[torn sphinx]

@jaunty galleon

[torn sphinx]

I need HELP...I want a tree text file...I have some mp3's and folders in my e drive...each folder has mp3's and subfolders with more mp3's...there r other pdf and other format files as well...I want to create a text file which contains all the mp3's names in lists under album names with album numbers all sorted by hierarchy and order....for example...album1 would contain the names of all the mp3's in the home page of e drive...album2: names of all the mp3's in the 1st folder of e drive...album3 would be names of all mp3's in 1st subfolder of 1st folder...if 1st subfolder has no more sub folders, album 4 would be the names of all mp3 files in 2nd subfolder of 1st folder...if 1st folder has just 2 subfolders then move on to the next folder in e drive and so on...All other files lyk pdf,etc r to be ommited

[pseudo lake]

async def get_last_row_id_in_db(self, table_name):
        last_row_id = (await self.db.query('''SELECT MAX(ID)  FROM ?''', (table_name, )))[0][0]
        if last_row_id == None:
            last_row_id = 1
        else:
            last_row_id += 1
        return last_row_id

gives:

Traceback (most recent call last):
  File "C:\Users\feroz\AppData\Roaming\Python\Python38\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "d:\Feroz Programs\FerozCollaborationProjects\ClassBot\cogs\economy.py", line 43, in create_account
    last_economy_row = await self.get_last_row_id_in_db("Economy")
  File "d:\Feroz Programs\FerozCollaborationProjects\ClassBot\cogs\economy.py", line 163, in get_last_row_id_in_db
    last_row_id = (await self.db.query('''SELECT MAX(ID)  FROM ?''', (table_name, )))[0][0]
  File "d:\Feroz Programs\FerozCollaborationProjects\ClassBot\utils\utils.py", line 11, in query
    await cursor.execute(sql,vars)
  File "d:\Feroz Programs\FerozCollaborationProjects\ClassBot\asqlite.py", line 144, in execute
    return await self._post(self._cursor.execute, sql, parameters)
  File "d:\Feroz Programs\FerozCollaborationProjects\ClassBot\asqlite.py", line 53, in _call_entry
    result = entry.func(*entry.args, **entry.kwargs)
sqlite3.OperationalError: near "?": syntax error

cant "?" be used like that?

and if it cant is there anyway i can dynamically provide the table name?

[tribal cargo]

Hej hej, I try to use csv Data to create data in my database. I'm using Flask and SQL alchemy. I tried this code:

def add_romms():
    tempsession = Session()
    with open('rooms.csv', 'r') as csv_file:
        csv_reader = csv.reader(csv_file)
        next(csv_file)
        for row in csv_reader:
            new_room = Room(roomnumber=row)
            tempsession.add(new_room)
        tempsession.commit

[pseudo lake]

try tempsession.commit()

?

[tribal cargo]

and I created the following Error:

[long peak]

how would you go about designing the tables where a reservation can only have 1 coupon

the top? or the bottom but with a unique constraint on the reservation_id?

[tribal cargo]

I thought I could asked about python here

It seems my table is view only.

[proven arrow]

Can multiple reservations have the same coupon?

[long peak]

yes

there's a coupon table for that, thus the coupon_id FK

[proven arrow]

Then the top

Have the foreign key coupon_id inside the reservations table.

[long peak]

ignore the FK on the redeeemed_date, my mistake XD

[long peak]

I'd lose the redeemed_date metadata if I do that and the CTO anticipates more fields there in the future so I thought it needs to have its own table so yea

[proven arrow]

Redeemed is at the time of booking no?

Seems redundant to me

[long peak]

you're right though doing that would prevent us from adding more metadata on the reservation coupon but I guess I'll just go and ask upfront the CTO what are these fields he's anticipating 🤔 but if we assume, the fields would grow, do you think the design looks good? @proven arrow

[proven arrow]

Depends what kind of metadata. Personally I don’t see what extra data comes from coupons, other than the ones you can already derive from data you already have. However, if you really insist and need to store extra data for each coupon and can’t store it an already existing table then you can create an extra table, to store that data.

The extra table would have the booking_id, coupon_id, and your other columns you want to store data about.

[long peak]

which is the bottom one in the screenshot, right?

[proven arrow]

Yeah looks like that. But not sure why you have a foreign key on the so called metadata

[long peak]

the reedeemed_date having a FK there is a mistake haha, kindly ignore that xD

[proven arrow]

Oh yeah makes sense then

Well you have your options. Choose one that works for you. Generally there is no right/wrong apart from the basic rules, and there can be multiple way of doing the same thing. It’s a matter of picking the one that suits your business requirements best.

[long peak]

either really works thus the confusion haha, but one thing for sure that works for now best is the top one so I think I'll go for that and keep things simple, I'm having a hard time imagining what other metadata could be added there as well tbh

aight, thanks for the input @proven arrow and have a good day ahead!

[elder elk]

I'm inserting data into DB using json files
with os.walk() but after some times speed decreased
any solution for this

[delicate field]

Hey @elder elk!

Uh-oh! It looks like your message got zapped by our spam filter. We currently don't allow .txt attachments, so here are some tips to help you travel safely:

• If you attempted to send a message longer than 2000 characters, try shortening your message to fit within the character limit or use a pasting service (see below)

• If you tried to show someone your code, you can use codeblocks
(run !code-blocks in #bot-commands for more information) or use a pasting service like:

https://paste.pythondiscord.com

[elder elk]

please find link for code that I'm using

https://paste.pythondiscord.com/iwopaqenec.swift

[proven arrow]

Inserting how many items?

[elder elk]

millions

at starting it shows good speed

but as time passes its get slower

[torn sphinx]

Any help?

[proven arrow]

Well your making a call to the db for each insert. And since each insert comes with overhead, and so for millions of items that will obviously add up. Generally the best is to use executemany() which inserts many rows at a time, in a single db call.

[elder elk]

can please suggest correction in code, use above link @proven arrow

[proven arrow]

Not sure what psycopg2 has for the executemany function but you can look it up in their documentation. It should be called something like that.

[proven arrow]

Help with what?

[torn sphinx]

Wait a sec pls

[torn sphinx]

i want to change to PostgreSQL and i did not understand what i need to change in my code

[proven arrow]

Change from what?

[torn sphinx]

sqlite

[proven arrow]

Well what library do you intend to use for this, and what changes have you made already to your new database?

[torn sphinx]

I didnt change anythhing

[brave bridge]

How can I use an SQLite version different to what Python was built with? I have built an sqlite3 binary myself.

[proven arrow]

Which OS?

[brave bridge]

Ubuntu 18.04

[proven arrow]

Oh, not sure about linux systems, but for windows you just replace the DLL file in the python installation folder. For linux it is probably similar but just different file type.

But you can download the one you need for your os from here, https://www.sqlite.org/download.html

[brave bridge]

the website provides a 32-bit binary, which doesn't work on my VPS

so I built it from source

oh, I figured it out

there's a pysqlite3 pypi package which uses the system binary, apparently?..

or something like that

it has the right version

Huh, for some reason pysqlite3 doesn't want to install on my VPS.

# python -m pip install pysqlite3
python -m pip install pysqlite3
Collecting pysqlite3
  Using cached pysqlite3-0.4.6.tar.gz (40 kB)
Using legacy 'setup.py install' for pysqlite3, since package 'wheel' is not installed.
Installing collected packages: pysqlite3
    Running setup.py install for pysqlite3 ... done
Successfully installed pysqlite3

arrgh, built from source per https://charlesleifer.com/blog/compiling-sqlite-for-use-with-python-applications/, now it says

ModuleNotFoundError: No module named 'pysqlite3._sqlite3'

[brazen charm]

Using the binary package

A binary package (wheel) is available for linux with a completely self-contained pysqlite3, statically-linked against the most recent release of SQLite.

$ pip install pysqlite3-binary

why this over the inbuilt setup though?

[brave bridge]

It's using the system sqlite version, which is 3.22

and I need 3.35

I think I'm figuring it out

okay, I give up. I'll use lastrowid instead of RETURNING

[jaunty galleon]

Can anyone help me with a PostgreSQL elephantsql problem? Not really python-related

It's an asyncpg error

[wet stump]

Hello help me

with postgresql

pls

[civic shale]

Hi

[wet stump]

My query gives me bad answer

[civic shale]

Let's see it.

[wet stump]

How many clients were activated and deactivated during June of 2013. Please provide both numbers as a result of one query.

select count (case when status = 'ACTIVATED' then 1 end) as activated
,     count (case when status = 'DEACTIVATED' then 1 end) as deactivated
from      exaster
where      status in ('ACTIVE', 'DEACTIVATED')
and     ( to_char (deletion_date, 'YYYY-MM') = '2013-06' and to_char (activation_date, 'YYYY-MM') = '2013-06') 
;```

[civic shale]

And it returns what?

[wet stump]

Data sample

[civic shale]

And what are you trying to get it to return?

[wet stump]

Count of activated and deactivated persons during 2013-06 period

[civic shale]

Hmm

[wet stump]

Something is wrong with my query

[civic shale]

Yes.

I can't see where.

Sorry.

The only thing I can think of is the deletion and activation dates

Are they made to be the same?

[wet stump]

maybe

select count(*) as activated, count(*) as deactivated from exaster where status='ACTIVE' OR status = 'DEACTIVATED' and SUBSTRING(TO_CHAR(deletion_date, 'YYYY-MM-DD'), 1 , 7)='2013-06' and SUBSTRING(TO_CHAR(activation_date, 'YYYY-MM-DD'), 1 , 7)='2013-06'```

I tried this also

Result

[grim vault]

How about sum() instead of count and add a else 0 in the case.

[wet stump]

Seems not helped

[grim vault]

Some databases have a filter option for aggregate function.

[wet stump]

which one?

I am on postgresql

[grim vault]

Yes it does have them, at least v13 docu says so.

aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

[wet stump]

never used this

[grim vault]

count (*) filter (where status = 'ACTIVATED') as activated ? I never used it myself.

[lusty inlet]

I suggest taking this a step at a time -- e.g. first take a look without aggregating, so you can be sure that the where is doing what you want

[wet stump]

 select count (*) filter (where status = 'ACTIVATE') as activated
,     count (*) filter (where status = 'DEACTIVATED') as activated
where ( to_char (deletion_date, 'YYYY-MM') = '2013-06' and to_char (activation_date, 'YYYY-MM') = '2013-06') 
;

[grim vault]

no from?

and add deto the second activated :)

[grim vault]

ok, make a select * from ... as Matt suggested.

[lusty inlet]

missed your * between select and from

I guess postgres doesn't mind just quietly selecting nothing at all

[grim vault]

But we now know that the 0, 1 result were correct.

[lusty inlet]

you're only catching rows with activation AND deletion that month -- is that intended?

[wet stump]

How many clients were activated and deactivated during June of 2013. Please provide both numbers as a result of one query.

[lusty inlet]

Hmm, ambiguous wording in the first sentence, but.... if you're going to have two different numbers I have to assume that these are separate groups. (1) how many clients were activated? (2) how many clients were deactivated?

[wet stump]

In one query

[lusty inlet]

... so you want the date filter to pick up OR

[wet stump]

how

[lusty inlet]

That way you're grabbing both (1) and (2)

[wet stump]

how

[lusty inlet]

right now you have where (... and ...) --- change to where (... or ...)

[grim vault]

Now add the count(*) filter .. again

[lusty inlet]

thinking about it having a look over the data, it probably makes sense to just move the where conditionals up into sum()s instead.
select sum(case when [activation_date in june 2013] then 1 else 0 end) as activated, etc.

current(?) status seems unrelated to the question

[grim vault]

Looking at <#databases message> the data is not very clear. Is the deletion_date set also on deactivation? It doesn't seems to get set for suspended.

[untold quartz]

How do I SELECT from a table where (the "last name" and "first name" columns concatenated together with a space in between) contains a certain substring?

in Microsoft SQL Server

[waxen temple]

you can use concat and check against it, just replace = :your_string with sub string matching

SELECT 
  * 
    
FROM 
  person p
   
WHERE
  CONCAT(p.first_name, ' ', p.last_name) = :your_string;

[cold ocean]

this is my messages table. is there a way to make it so when doing "SELECT * FROM messages WHERE sender = ?" it will return the number of row it is opposed to the id colomn?

like for row 4 that has an id of 36 it will return 4 instead of 36

[proven arrow]

You can use row_number() window function

[fleet mountain]

can someone help me setup pymongo

[quaint sky]

Hello, I have a new question. In MySQL we can change the innodb pool size so that it doesnt cause memory error but I cant find how to change the setting in MSSQL. I searched on google and people recommended splitting the dataframe and send it to sql. However, I want to find a way to increase the pool size for mssql so that it doesnt cause memory error when I use pandas df.to_sql.

[lusty inlet]

Have you looked through https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15 ?

[quaint sky]

Thank you!!! This was what I was looking for!!!!!!!!

[nimble wyvern]

So i tired to add new field life in to collection (used mongodb)

collection.aggregate([
    { "$addFields" : { "life" : 1}}
])

It will run but no changes seen

[nimble wyvern]

    {"life": {"$exists": False}},
    {"$set": {"life": 1}}
)```
This did the job lol

[eager cloak]

is there any way to write into existing excel file and keep the original formatting intact?
openpyxl and xlsxwriter engines in pandas are removing that existing formating

[cyan yacht]

I'm using aiomysql
Is it possible to subclass aiomysql.connection.Connection and connect to that?
So that I can have different functions as methods for the subclass

[tight smelt]

I need a free database which is simple to use for my discord bot. Its going to be a small currency system

recommendations please

[cold ocean]

https://www.sqlitetutorial.net/sqlite-python/creating-database/
i found this site to be great for learning the basics

[wet stump]

Need count how many are active, deactivated members through 2013-06 period in one row

[grim vault]

Haven't we solved this yesterday? <#databases message>
Your where clause was wrong the count filter should work now.

[midnight birch]

how can i store bytes using sqlalchemy

[wet stump]

count(*) filter?

[grim vault]

Yes, this one:

select
  count(*) filter (where status = 'ACTIVATED') as activated,
  count(*) filter (where status = 'DEACTIVATED') as deactivated
from exaster
where (
  to_char(deletion_date, 'YYYY-MM') = '2013-06' or
  to_char(activation_date, 'YYYY-MM') = '2013-06') 
;```

[grim vault]

I'm not sure using the status column is the correct way, but the questions and dataset is a little vague.

And what should I do with the screenshot? It tells me nothing.

[wet stump]

Ok so what could be correct answer?

[grim vault]

Do you have a deactivation_date column?

[wet stump]

yes

[grim vault]

What about:

select
  sum(case when to_char(activation_date, 'YYYY-MM') = '2013-06' then 1 else 0) as activated,
  sum(case when to_char(deactivation_date , 'YYYY-MM') = '2013-06' then 1 else 0) as deactivated
from exaster;```

[wet stump]

Answers are different

[grim vault]

Of course. status can now be ACTIVE for rows where deletion_date is 2013-06. The difference is expected. I don't know what the correct answer is.

And deletion != deactivation

[wet stump]

So with sum is better?

[grim vault]

From my perspective sum() is the better choice, yes.

[south marlin]

Would here be the best place to ask on how to structure my project with Flask and SQLAlchemy? Specifically on abstracting the router, db models, and other aspects of the ORM.

[wet stump]

Query by which you could extract active clients at the 2013-06-19.

select * from exaster where status='ACTIVE' and activation_date='2013-06-19';```

I used this query

How many active clients had more than one SIM card on 2013-06-19. Unique client could be identified by using unique device information (prepaid customers are usually not identified in the systems).

why null?

[proven arrow]

This is not a code writing service. You’ve been told before as well. Just dumping questions here is not how you do things. Every single one of your questions in this channel is like that.

[wet stump]

I am having issue

I am getting null

It is

hmm weird

[grim vault]

You should start with an SQL tutorial. In this case you are missing the from so the database has no clue which table you are referencing.

[wet stump]

Oh sorry I miss it.

With from also same situation

[grim vault]

Look closely at the HINT line.

[wet stump]

Yes I looked

I tried this also

[lusty inlet]

there's a space in the column name 🤦‍♂️

[lusty inlet]

there's something worse than a space in the column name 🤦

[wet stump]

I have no idea what can cause it?

[grim vault]

loose the exaster. or quote the column and column name separately.

[lusty inlet]

ah of course it thought you were trying to specify the column exaster.imei_a of the table exaster when you put the whole thing in quotes

[wet stump]

So I need to separate?

[oak carbon]

https://cdn.discordapp.com/attachments/829950386139430942/847499143253196840/unknown.png

default.ist_time(), default.ist_time() + timedelta(days=30)```

can anyone help me that why is that tz info is coming ?

[wet stump]

Seems working but very strange

[brazen charm]

Not really strange

Table got created with a space in it

so it expects that space

[grim vault]

s/table/column

[oak carbon]

can anyone also help me

because of that tzinfo i can not compare time

[grim vault]

One is a naive datetime and the other is a timezone aware one. How do they get assigned?

[oak carbon]

def ist_time():
    time_now = datetime.utcnow()
    ist_time = time_now + timedelta(hours=5, minutes=30)
    return ist_time

premium_time=datetime.datetime(2021, 5, 27, 20, 20, 26, 564740), premium_expire=datetime.datetime(2021, 6, 26, 14, 50, 26, 564740, tzinfo=datetime.timezone.utc)

they get saved like this ^

[grim vault]

Shouldn't be. There must be something else.

>>> from datetime import datetime, timedelta
>>>
>>> def ist_time():
...     time_now = datetime.utcnow()
...     ist_time = time_now + timedelta(hours=5, minutes=30)
...     return ist_time
...
>>> a = ist_time()
>>> b = ist_time() + timedelta(days=30)
>>> a
datetime.datetime(2021, 5, 27, 21, 36, 2, 775298)
>>> b
datetime.datetime(2021, 6, 26, 21, 36, 2, 776296)
>>> ```

Use datetime.now(timezone.utc) it's better to have an aware datetime.

[oak carbon]

yh i even tried printing it before inserting in db it its printing in good format

but when i save the tz thing come

[grim vault]

Maybe your database driver has some datetime adapter.

[wet stump]

Hello

Why I am getting null?

SELECT 
 account_id ,
 COUNT(*) count
FROM 
 exaster
GROUP BY
 account_id,
 "exaster"."imei_a "
HAVING
COUNT(*) > 1;```

How many active clients had more than one SIM card on 2013-06-19. Unique client could be identified by using unique device information (prepaid customers are usually not identified in the systems).

Oh sorry I forget

SELECT 
 account_id ,
 COUNT(*) count
FROM 
 exaster
 where status='ACTIVE' and activation_date='2013-06-19'
GROUP BY
 account_id,
 "exaster"."imei_a "
HAVING
COUNT(*) > 1;```

[harsh pulsar]

what's the point of that HAVING?

[wet stump]

had more than one SIM

[harsh pulsar]

oh, it's > not >=

[grim vault]

@oak carbon Try:

def ist_time():
    time_now = datetime.now(timezone.utc)
    ist_time = time_now + timedelta(hours=5, minutes=30)
    return ist_time

[harsh pulsar]

you have a space in your imei_a column

did you really mean "imei_a ", or "imei_a"?

[oak carbon]

what will it do?

[grim vault]

scroll back and you'll know :)

[wet stump]

"ime_a "

[grim vault]

It will always return a timezone aware datetime.

[wet stump]

That column works with queotes

[harsh pulsar]

there's a literal in the column name?

[wet stump]

Main info about the columns of a prepaid customer base:
-- account_id - unique SIM / Client ID
-- msisdn - phone number of the client
-- activation_date - service activation date
-- deletion_date - service deactivation date
-- status - the newest/current status of the service/client
-- imei_a - unique phone/device ID (IMEI).
-- device_brand - Phone brand
-- device_model - Phone model
-- device_type - Phone type

[wet stump]

yes

[harsh pulsar]

ouch

[wet stump]

Column is find

Now problem with null data

Output is null

[grim vault]

It's and artefact from some csv conversion, would be my guess.

[harsh pulsar]

are you getting 0 rows? or data containing a null unexpectedly?

[wet stump]

I am getting null rows of query

SELECT 
 account_id ,
 COUNT(*) count
FROM 
 exaster
 where status='ACTIVE' and activation_date='2013-06-19'
GROUP BY
 account_id,
 "exaster"."imei_a "
HAVING
COUNT(*) > 1;```

[oak carbon]

asyncpg.exceptions.DataError: invalid input for query argument $6: datetime.datetime(2021, 5, 27, 21, 46, 1... (can't subtract offset-naive and offset-aware datetimes)

[harsh pulsar]

@wet stump https://www.db-fiddle.com/f/28Z8TZgTfo6b3gfzNUTFQx/1

this works just fine

[oak carbon]

@grim vault i tried checking the db query, this was the error

[grim vault]

Ups.

I would suggest using timezone-aware datetime (if possible).

[lusty inlet]

again recommend that you first form your un-aggregated query, be sure you're capturing the cases you expect with where -- then once you're sure that it working, aggregate as you want it

[ebon skiff]

Traceback (most recent call last):
  File "C:\Program Files\PostgreSQL\13\pgAdmin 4\web\pgAdmin4.py", line 98, in <module>
    app = create_app()
  File "C:\Program Files\PostgreSQL\13\pgAdmin 4\web\pgadmin\__init__.py", line 347, in create_app
    if not os.path.exists(SQLITE_PATH) or get_version() == -1:
  File "C:\Program Files\PostgreSQL\13\pgAdmin 4\web\pgadmin\setup\db_version.py", line 19, in get_version
    return version.value
AttributeError: 'NoneType' object has no attribute 'value'
``` got this error in pgadmin, solved when removed, folder 'pgadmin' inside appdata->raoming.
I would like to know why it solved it.

[wet stump]

As I understand account_id can have more than one imei_a

[lusty inlet]

I like helping out with the occasional query or weird spot, but to be honest @wet stump you just have a lot to learn and this probably isn't the best venue for you to work through learning SQL basics

[wet stump]

I know basics, I am using sql not first year, previously worked as pl/sql developer

I used also advanced queries, I also was in reverse engineering, data modeling, database programming

[wet stump]

stop insulting me

[lusty inlet]

I did not intend to insult you.

And I'm not the boss of this channel, just voicing my personal view as I've been one of the people helping to solve your questions

[wet stump]

Ok thanks for helping

I still don't get where is a problem

[torn sphinx]

That's not an insult dude he's recomending you

[harsh pulsar]

well i already showed you that i cannot reproduce the problem on sample data, and you also haven't explained what exactly you mean by "null"

so it's hard to help beyond this point, without more information

[wet stump]

Null data, no records. I thought to get number of how many accounts have more than one sim

imei_a is device id

[proven arrow]

Can you provide a minimal reproducible example?

[wet stump]

So each account_id can have several imei_a id

which corresponds to sim

[proven arrow]

Not screenshots, but actual code/fiddle someone can use

[wet stump]

SELECT 
account_id, "exaster"."imei_a " ,
 COUNT(*) count
FROM 
 exaster
 where status='ACTIVE' and activation_date='2013-06-19'
GROUP BY
 account_id,
 "exaster"."imei_a " 
HAVING
COUNT(*) > 1```

I wrote query which generate null zero records

Active members with active date

[proven arrow]

Thats not reproducible, doesnt tell us anything of how the table looks or what data it contains

[proven arrow]

https://stackoverflow.com/help/minimal-reproducible-example

[wet stump]

Here is data sample

account_id - unique SIM / Client ID
-- msisdn - phone number of the client
-- activation_date - service activation date
-- deletion_date - service deactivation date
-- status - the newest/current status of the service/client
-- imei_a - unique phone/device ID (IMEI).
-- device_brand - Phone brand
-- device_model - Phone model
-- device_type - Phone type

[proven arrow]

Use something like https://dbfiddle.uk/?rdbms=postgres_13
to create your tables and populate it with some data.

[wet stump]

Can you access postgresql?

http://127.0.0.1:52062/browser/

[proven arrow]

No i dont use postgres or have it installed

[wet stump]

I mean I running in server

postgresql admin

How many active clients had more than one SIM card on 2013-06-19. Unique client could be identified by using unique device information (prepaid customers are usually not identified in the systems).

each account having more than one sim card (device id)

[patent cove]

how do i get it so it doesnt replace privious data

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

[lusty inlet]

right now you replace the existing file on the last line with json.dump(data, json_file)

and if you change the open line to
with open("journalEntrys.json", "r") as json_file: you'll just open the file to read, not write

[torn sphinx]

Can I use .sql files to run SQL queries with PostgreSQL and SQLite3?

But

With placeholders.

I have a class that is an abstraction of SQLite3 async bridge library methods and I'm wondering if something like this seemed valid if my .sql file looked like the one at the second code block:

async with Database() as db:
  sql_payload = ('here\'s', 'some', 'data')
  await db.run_script('sqlscripts/my-script.sql', payload=sql_payload, commit=True)

INSERT INTO 'someTableName' (
  'huh',
  'hm',
  'oh'
) VALUES(?, ?, ?);```

If I wanted to insert here's into someTableName>huh and all the way through all values into their respective columns.

[wet stump]

Hello

Help me pls

I am getting blannk output

[torn sphinx]

Hi Youall, Do you have any advice for data engineer to pick up python quickly , any courses you recommend

[prisma girder]

Generally yes but it will fail when you will have database-specific features in your queries

[prisma girder]

Have you seen pinned messages?

[torn sphinx]

What do you mean by database-specific features exactly?

Like executing psql queries?

Which are only for PostgreSQL.

[prisma girder]

Like using ifs for example
https://www.postgresql.org/docs/9.5/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS
https://stackoverflow.com/questions/14580294/sqlite-syntax-for-if-else-condition/14581068

[torn sphinx]

Oh that is out of scope of my knowledge of SQL but thank you.

Time to port everything into .sql files cause I hate integrating SQL with Python.

[prisma girder]

I am using ORM instead of writing SQL queries

[torn sphinx]

ORM?

[prisma girder]

Object Relational Mapping

Like SQLAlchemy

[torn sphinx]

OH

That thing, well idk how good it is so ykyk.

I'll check it out tho I know that Flask had that, maybe it'll fit me better.

[prisma girder]

You can define model

class Address:
  country: str
  city: str
  street: str

class Person:
  name: str
  address: Address

Then you can create Python objects without writing SQL queries

session = ...
address = Address(...)
person = Person("John", address)
session.add(person)
session.commit()

[torn sphinx]

Yeah

[prisma girder]

However you should start with raw SQL queries to understand what is going on and how to work with relational databases

[torn sphinx]

I agree even with the ORM model stuff I'll still need to know how to create databases and stuff.

[prisma girder]

This is also created automatically

[torn sphinx]

I barely know how to create a database and let a particular user manage it.

[prisma girder]

I mean tables

Not database at all

[torn sphinx]

Oh

I have a few database table creation scripts.

[prisma girder]

It's good point to start

[torn sphinx]

I suppose, thank you.

[prisma girder]

You're welcome!

[torn sphinx]

Just to clarify

InsertBoosters.sql > sql INSERT INTO "boosters" (serverId, userId, personalRoleId, teamRoleId) VALUES(?, ?, ?, ?);

Will work with the paired example of passing in the payload right?

Yea

[prisma girder]

It should work in both databases

[lapis snow]

Can someone teach me how to make a database?

[torn sphinx]

Oh okie!

Looks good

Although I should rename each file to an operation so it ends up looking like Select.sql and not SelectBoosters.sql cause that's unnecessarily long ;-;

[prisma girder]

Yeah, if you have queries in directed directories there is no need to suffix like Boosters because it's already in Boosters/ directory 🙂

[torn sphinx]

Yuh

[prisma girder]

Have you seen pinned messages?

[torn sphinx]

Didn't think that I'd create separate dirs for the scripts at first lol.