databases

[high geyser]

lots more

[pliant notch]

which module is best for making AI bot?

[rapid herald]

Hello! I switched to PostgreSQL since it can hold Columns of Arrays. At the moment I'm working with SQLAlchemy and I'm trying to figure out what the difference is between importing
from sqlalchemy import JSON
and
from sqlalchemy.dialects.postgresql import JSONB

[velvet coyote]

Why do I get this error

  File "C:\Users\user\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\client.py", line 333, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\user\Desktop\database\db.py", line 40, in on_member_join
    connect_to_db.execute("INSERT INTO joined_members VALUES (:id)", {':id': member.id})
sqlite3.OperationalError: table joined_members has 2 columns but 1 values were supplied

ync def on_member_join(member):

    connection = sqlite3.connect('member_info.db')
    connect_to_db = connection.cursor()

    try:

        connect_to_db.execute("""CREATE TABLE joined_members (
            id 

        ) """)

        with connection:
            connect_to_db.execute("INSERT INTO joined_members VALUES (:id)", {':id': member.id, })

        connection.commit()


 

    except sqlite3.OperationalError:
        with connection:
            connect_to_db.execute("INSERT INTO joined_members VALUES (:id)", {':id': member.id})

ik that sql is not the best choice for d.py ut ima learn postgresql later

but r, I will use sql

[wintry stream]

@velvet coyote sounds like you have made the table joined_members before, so it ignores everything in the try: bracket. After that it tries to insert the id, but the error says there are 2 columns to the table

[velvet coyote]

ohhhh ok

[wintry stream]

also

it's better to not have a create table like that in your code

you either want to check if the table exists using an if statement, or just create it once and write your code trusting the table already exists

[rapid herald]

Is it possible to store an instance of a python class in PostgreSQL without first serializing it with something like pickle?

I think that's a stupid question 😄 What I'm trying to achieve is relate Table_A - Column_A - x, to Table_B - Column_A - x. Such that if I try to retrieve Table_B - Column_A, I receive the data that is stored in Table_A - Column_A - x

[reef tartan]

SELECT owner FROM pet WHERE owner < 'E' AND

I am trying to create a query where the name of the person starts with and ends with first five letters of alphabet

any ideas?

[grand vector]

Don't know which database you're using but this should comply with ANSI SQL: SELECT owner FROM pet WHERE (LEFT(owner, 1) BETWEEN 'A' AND 'E' OR RIGHT(owner, 1) BETWEEN 'a' and 'e');

May need to use SUBSTR() depending on the DB you're using

oh, and replace or with and if both first and last need to be between

[reef tartan]

sqllite btw

& for the BETWEEN, would it be safe to use <'F' like i have done and maybe RIGHT on the otherside

[grand vector]

between is inclusive, you could do exclusive logic

e.g. LEFT(owner, 1) >= 'A' and LEFT(owner, 1) < 'F'

I don't remember what comes before 'A', if I recall it depends on whether you're using ANSI or UTF encoding

[reef tartan]

what is the reason for the >=A, I have seen a few people do that now

Surely <F would include the A, no?

[grand vector]

<= is an inclusive comparison wheras < is exclusive

meaning if LEFT(owner, 1) is A, then A >= A is True

[pseudo cove]

and

[grand vector]

perhaps I misunderstood the question, you're asking why compare to A at all?

[reef tartan]

yeah

If < 'F' is 'A,B,C,D,E'

Why include >=A

[grand vector]

oh, by that logic you'd be right, but if the name starts with anything that below 'A' like BLANK (aka space) then it may also be regarded as True if you don't limit the range

[reef tartan]

ahhh i see

[grand vector]

I'd have to look at a character chart but I seem to recall there are characters below A

also, A does not equal a

so depending on your use case you may want to use a LOWER() method on the owner field

[reef tartan]

Very good to know, slightly out of the scope of what Im doing, but helps to be reminded to think of these things when writing code, thank you

[grand vector]

cheers

[torn sphinx]

Ey, so this is kinda of a discord.py problem and a database problem. I will check if I can get some help here. So the problem is that it won't put anything into the database, it stays empty even after I executed the code. Here is the code:

    async with aiosqlite.connect("C:/Yoha/files/server_logs.db") as db:
        await db.execute("CREATE TABLE IF NOT EXISTS message_logs (guild_id str, channel_id str)")
        await db.execute("INSERT INTO message_logs (guild_id, channel_id) values (?, ?)",
                        (str(ctx.guild.id), str(channel.id)))

You didnt commit

OHHHH

Thanks

another discord.py/database problem. So I get the error:

  File "c:/Yoha/bot.py", line 98, in on_message_delete
    channel = await db.execute("SELECT channel_id FROM message_logs WHERE guild_id="+guild.id)
TypeError: can only concatenate str (not "int") to str

On this code:

    async with aiosqlite.connect("C:/Yoha/files/server_logs.db") as db:
        channel = await db.execute("SELECT channel_id FROM message_logs WHERE guild_id="+guild.id)

You;re trying to concatenate a str and int together, Also, guild.id returns and int, when you created the table you made the guild_id field of type str

Oh, alright. Lemme change that, one second.

Same with channel.id, that will also returns an int

Ye ye,

ty

lemme test it

@torn sphinx ey bro

i get the same error

lemme get the code

    async with aiosqlite.connect("C:/Yoha/files/server_logs.db") as db:
        await db.execute("CREATE TABLE IF NOT EXISTS message_logs (guild_id INTEGER, channel_id INTEGER)")
        await db.execute("INSERT INTO message_logs (guild_id, channel_id) values (?, ?)",
                        (guild.id, channel.id))
        await db.commit()

Same error as before? TypeError: can only concatenate str (not "int") to str ?

yes, sir

You're still trying to concatenate str and int

But I changed from str to INTEGER?

channel = await db.execute("SELECT channel_id FROM message_logs WHERE guild_id = ?", [guild.id])

oh

that looks smart 😳

imma try it out

1 sec

Traceback (most recent call last):
  File "C:\Users\Max stationära\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "c:/Yoha/bot.py", line 98, in on_message_delete
    channel = await db.execute("SELECT channel_id FROM message_logs WHERE guild_id = ?", guild.id)
  File "C:\Users\Max stationära\AppData\Local\Programs\Python\Python38\lib\site-packages\aiosqlite\core.py", line 160, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "C:\Users\Max stationära\AppData\Local\Programs\Python\Python38\lib\site-packages\aiosqlite\core.py", line 109, in _execute
    return await future
  File "C:\Users\Max stationära\AppData\Local\Programs\Python\Python38\lib\site-packages\aiosqlite\core.py", line 95, in run
    result = function()
ValueError: parameters are of unsupported type

i got this as an error

@torn sphinx u there brother?

Sorry, look at my edited answer from above.

oh

lemme try it out

@torn sphinx alr so

now i got another error

  File "c:/Yoha/bot.py", line 99, in on_message_delete
    await channel.send("yes")
AttributeError: 'Cursor' object has no attribute 'send'

the "yes" is just to see if it works

i'll create an embed later

but it aint workin

You're sending the cursor you need to fetch

fetch uhhhh.

how do i do that?

[pseudo cove]

ctx.send, no?

[torn sphinx]

was a long time ago i did this, and i wasn't really good at it when i did it

result = await channel.fetchone()

oh alr

ty

@pseudo cove u can't use ctx in a event, and it aint gonna send it into the log channel

@torn sphinx it says that

tuple has no attribute send

    await result.send("yes")
AttributeError: 'tuple' object has no attribute 'send'

[pseudo cove]

no

[torn sphinx]

Yeah it returns a tuple, if you py print result[0] it will give you the channel ID. You need to get the channel object

[pseudo cove]

you need to call send on a discord.py object

[torn sphinx]

hmmm

but i aint trynna print it tho

im trynna send it to a channel

🤔

You don't have to print it lol, result[0] is your channel ID. Now get the channel object from that ID using dpy py channel_obj = guild.get_channel(result[0])

ye ye

i found the answer

thanks bro

Then call send() on that channel object, ```py
await channel_obj.send("Whatever")

this will really help me

Np

God bless ya, have a great day.

You too

[torn sphinx]

@torn sphinx ey brother

u down to help me again?

i need to know how i can change the channel of the message logs, like it replaces the current channel_id if it was sent in a server that already has message logs setup.

please @ at an answer, imma head out for a sec

[proven arrow]

SQL has an update statement you can use

UPDATE some_table_name 
SET some_column_name = your_value 
WHERE some_condition;```

[potent sparrow]

SQLite3, how do I work with the data in Pandas/Numpy? What file do I load?

[glass gorge]

does anyone have experience with bcrypt?

I'm getting an invalid salt error, and from what I googled this is a difficult problem to solve, because it could be invalid for a lot of reasons

im not sure where to start with debugging

[solar gale]

in sqlalchemy, is my guess that i can use the following syntax to update a table valid?

query = my_table.update().where(my_table.c.column1 == "my val").values(**my_val_dict)

[shy viper]

i downloaded the mongodb in the bash as i get the 3 info type db name from the bash which are my self hosted databases

[minor venture]

SQL Help Please:
I am trying to find if there is a row that contains a discord_user_id and a guild_id the same to the variables in my code.
This is my code so far and yes i know i haven't added the guild_id part yet. I think i need to put that where the * is but im not sure how

    if len(cur.execute("select * from user_data where discord_user_id = %s", str(member.id),)) > 0:
        return

also please ping me

[proven arrow]

@minor venture The parameters need to be passed as a tuple. And after executing the query you need to fetch the result. Thereafter you can check if the query returned something or not.

And when checking if it exists you should just do if not result insteead of len() because if the database returns None then you will probably end up with an error trying to perform len on none type

[minor venture]

SELECT * from user_data where (discord_user_id, guild_id) = (%s, ???

[proven arrow]

So you would have something like this:

cursor = cur.execute("SELECT * FROM user_data WHERE discord_user_id=%s", (str(member.id),))
row = cursor.fetchone() # there are others like fetchall...

if not row:
    print("None found")

Do you want to have both those values in the where clause?

[minor venture]

ok got it

yes

wait

[proven arrow]

then you can just use and in your query to filter by the extra column and then add that value to the parameters list

cursor = cur.execute("SELECT * FROM user_data WHERE discord_user_id=%s AND guild_id=%s", (str(member.id), your_guild_id))

[minor venture]

thank you!

[proven arrow]

The * selects all the columns from that table.

If you want just some specific columns then replace the * with those col names

[minor venture]

So changing the star just changes what it returns to me. So * will retunr the whole row while guild_id will only return the guild_id value for that row?

[proven arrow]

Yes

[minor venture]

thank you!

i get AttributeError: 'NoneType' object has no attribute 'fetchone'

@proven arrow

[past widget]

Why is nothing happening?not even an error

                THIS_FOLDER = os.path.dirname(os.path.abspath(__file__))
                my_file = os.path.join(THIS_FOLDER, 'warns.sqlite')
                db = await aiosqlite3.connect(my_file)
                cursor = await db.cursor()
                else:
                    await cursor.execute("INSERT INTO warns (memid, reason, date, warner, warnid, warnnum) VALUES (?, ?, ?, ?, ?, ?)", (str(message.author.mention), "".join(str(reason)), str(datetime.datetime.utcnow().strftime("%b/%d/%Y, %X")), str(bot.mention), str(result[0] + 1), str(random.randint(9, 9999))))
                    await db.commit()
```Just hangs in the execute statement

[proven arrow]

@minor venture Can you show your code?

[minor venture]

cursor = cur.execute("SELECT * FROM user_data WHERE 
                      discord_user_id=%s AND guild_id=%s",        
                      (str(member.id), str(member.guild.id)))
    row = cursor.fetchall()
    if not row:
        return
    else:
        cur.execute("INSERT INTO user_data (discord_user,     
                     discord_user_id, whitelist, guild_id) "
                     "VALUES ('{}', {}, True, 
                     {});".format(member.name + "#" + 
                     member.discriminator, member.id, 
                     member.guild.id))
        DB_conn.commit()```

fixing indention one sec

sorry discord kinda butchuerd it @proven arrow

butchered*

[proven arrow]

How were you doing fetchone when you got the error?

[minor venture]

it was un commented

fixed

[proven arrow]

And you need to fetch from the cursor object. So for you it would be cur.fetchone()

[minor venture]

oh ok

[potent sparrow]

I’m trying to use my DB.sqlite with Pandas/Numpy. But it says “near” there’s a issue. Idk if there anything in the dB, I have migrations tho

[minor venture]

That worked now i just need to take out the not becouse i want the var to be full lol

[proven arrow]

@minor venture Also dont use string formatting for sql queries as you are doing in your else. Its unsafe and can lead to what is known as sql injection.

Instead used the parameterised query as you do in your select query.

[minor venture]

ok thanks

[potent sparrow]

This is the issue I’m having.

[proven arrow]

Im not too sure entirely how pandas does it, or requires it but generally SQL query will have a statement at the beginning that tells the query what to do. For example, SELECT, UPDATE, INSERT

[potent sparrow]

@proven arrow ok, but that’s not what tutorials show?

[proven arrow]

Yeah then as i said im not sure how pandas requires you to specify it. I was speaking from a SQL syntax POV. You might want to wait for someone who know has used what you are doing before.

[torn sphinx]

@client.command(aliases=['bribe', 'leave'])
async def escape(ctx):
    conn = await aiosqlite.connect('moneylist.db')
    id = ctx.author.id
    ids = await conn.execute("SELECT id FROM money")
    author = ctx.message.author
    role = discord.utils.get(ctx.guild.roles, name="TheGulag")
    values = await ids.fetchall()
    print(values)
    cursor = await conn.execute("SELECT cash FROM money WHERE id=?", (id,))
    records = await cursor.fetchone()
    print("Id: ",  id)
    print("Cash: ", records[0])
    usercash = records[0]
    if usercash > 1:
        if usercash >= 1000:
            await conn.execute("UPDATE money SET cash = cash - ? WHERE id=?", (1000, id))
            await author.remove_roles(role)
    else:
        await ctx.send("You dont got the funnds!")

    await ids.close()
    await conn.close()

would this work for ba buy command like if they dont have money for it it dosent buy it

[torn sphinx]

How can I work with databases in Python?
I've done it on Java before but I have no idea for Python
I have MySQL btw

[mossy sand]

Just import the relevant modules i.e mysql/postgres and send the queries as string

depends what you mean under "working"

[torn sphinx]

I mean like storing users id and their password so they can log in by entering them in text fields

[mossy sand]

https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html

mysql docs are pretty elaborate, if you find it confusing I suggest finding any decent looking python beginners DB guide

[pliant cliff]

How can i tuple unpack into an sql statement?

                while len(_.inventory) < 25:
                    _.inventory.append(None)
                
                await db.execute("""INSERT OR REPLACE INTO Inventory (tag, item1,item2,item3,item4,item5,item6,item7,item8,item9,item10,
                item11,item12,item13,item14,item15,item16,item17,item18,item19,item20,item21,item22,item23,item24,item25) VALUES (
                ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""", (_.tag, ))```
Plan was to make _.inventory into a tuple, and then pass that in...
ps: If you know of a more efficient way to do this entire thing then 🙇🏿‍♂️

[torn sphinx]

@client.command(aliases=['bribe', 'leave'])
async def escape(ctx):
    conn = await aiosqlite.connect('moneylist.db')
    id = ctx.author.id
    ids = await conn.execute("SELECT id FROM money")
    author = ctx.message.author
    role = discord.utils.get(ctx.guild.roles, name="TheGulag")
    values = await ids.fetchall()
    print(values)
    cursor = await conn.execute("SELECT cash FROM money WHERE id=?", (id,))
    records = await cursor.fetchone()
    print("Id: ",  id)
    print("Cash: ", records[0])
    usercash = records[0]
    if usercash > 1:
        if usercash >= 1000:
            await conn.execute("UPDATE money SET cash = cash - ? WHERE id=?", (1000, id))
            await author.remove_roles(role)
    else:
        await ctx.send("You dont got the funnds!")

    await ids.close()
    await conn.close()

This command should - 1k from the user If they have it else send a error msg and remove the role

mysql docs are pretty elaborate, if you find it confusing I suggest finding any decent looking python beginners DB guide
@mossy sand Thank you, I'll check it out

[hexed hatch]

Hi, i need to choose a database for a project with django(it's a notes app whit authentication system and i want that the notes get syncronized on every device that login on the account) which database is easy to use and also learn because i'm quite new to databases? I was thinking on firebase but i dont know if it integrates well with python

With*

[shell ocean]

Hi, i need to choose a database for a project with django(it's a notes app whit authentication system and i want that the notes get syncronized on every device that login on the account) which database is easy to use and also learn because i'm quite new to databases? I was thinking on firebase but i dont know if it integrates well with python
@hexed hatch Firebase isn't a database...

[hexed hatch]

Yes, i know but it has realtime databases, and also i need a hosting service so

Anyways i decided to use postgreSQL because i think it intragrates better with django

[torn sphinx]

i'm trying to create a database with mongoDB but it's not letting me import motor.motor_asyncio. I'm on the latest discord.py version and i've also tried to install motor but it's not working
and since it says motor isn't a module but installing it is a no-go for some reason

[brazen charm]

Motor is a little bit all over the place

What python version r u on

[quaint tiger]

How can i tuple unpack into an sql statement?
@pliant cliff (_.tag, *_.inventory) ?

[shy viper]

where can i find examples of discord.py commands with sqlrite database

[proven arrow]

@shy viper You probably wont as its quite a specific thing so only if people were to share their code online on some repository you can have a look. Or maybe search through the #discord-bots channel to see if anyone has posted something like that.

[shy viper]

i have fixxed thx for tried for help me

[lusty grail]

Hey guys. A question... I need to setup the sync of some tables in prod psotgres DB to the dev server DB. I'm looking into some solutions but I have some important constraint/limitations:

1. I can only connect from prod to dev DB, not vice versa (prod is gate behind terribly slow VPN)
2. I don't want to have sync runing every insertion but rather have batch sync every n inserts or x minutes (because connection from prod to dev is not the great either)

I didn't find any ready to use solution that seems to satisfy these 2, Hence I am thinking to write python script to do that for me. Do you think it's good solution, or perhaps I missed something in my searches for some free tools to do that?

[pliant cliff]

technically...
Python is a free tool to do that 😎

@pliant cliff (_.tag, *_.inventory) ?
@quaint tiger noted. thanks

[lusty grail]

technically...
Python is a free tool to do that 😎
@pliant cliff well yeah. I meant some ready to use solutons 🙂

[quaint tiger]

@quaint tiger noted. thanks
The asterisk is the unpack operator. You can also use ** for unpacking dicts to kwargs.

[pliant cliff]

ok thank you

[hexed hatch]

Hi! I'm making a web app with django and it consist on a user authentication and the user can create notes, i need to choose a database and i'm very new to this, which one should i choose if i want to store user data(username, password) and the notes and files that the user creates?

Hi! I'm making a web app with django and it consist on a user authentication and the user can create notes, i need to choose a database and i'm very new to this, which one should i choose if i want to store user data(username, password) and the notes and files that the user creates?
@hexed hatch
Sorry, i wrote that quickly i probably misspelled some words

[mossy granite]

If you are doing it for deployment, PostgeSQL is the go, but for practice SQlite is great and fast DB.

If you are doing it for deployment, PostgeSQL is the go, but for practice SQlite is great and fast DB.
@mossy granite

This is for Django

Hope this is what you were looking for @hexed hatch

[hexed hatch]

Hope this is what you were looking for @hexed hatch
@mossy granite
Yes, thanks!

[mossy granite]

Anytime!

[potent sparrow]

Is this how SQLite3 is? A dB of “django.db” was created, it’s off of the Django site. I want to implement using pandas/Numpy with my Django project to analys sales

[torn sphinx]

What python version r u on
@brazen charm latest, also discord.py is latest version

[brazen charm]

i doubt that considering 3.9 doesnt support d.py

specific version

[high geyser]

3.9 cannot be used for discord bots yeah

[torn sphinx]

hm

so i need to uninstall and install what version?

3.6 again?

@high geyser

[high geyser]

3.8 is stable

@torn sphinx

but 3.6 also works

I use 3.6 right now

[torn sphinx]

ok

and then still with the motor thingy, whats up with that

if you maybe know @high geyser

[high geyser]

what

monitor thingy?

[torn sphinx]

i'm trying to create a database with mongoDB but it's not letting me import motor.motor_asyncio. I'm on the latest discord.py version and i've also tried to install motor but it's not working
and since it says motor isn't a module but installing it is a no-go for some reason

monitor thingy?
@high geyser

is that just bc i was on a wrong version or?

[brazen charm]

Motor is very all over the place rn

Ik 3.8 works but that's about all I can say

[torn sphinx]

yeah im using 3.8 rn

[torn sphinx]

Hi there.
I have a question about connecting MySQL and Python.
I'm using the mysql-connector package to connect a python script to mysql workbench where I have a database.
I managed to connect my database and my python script on my local computer. But I actually would love to have this SQL database on Gitlab.

So my question is, how can I connect my python script to a sql database on gitlab.

[fair crescent]

Hi, can someone explain how you would write an Associative Entity from a ERD Diagram to become an SQL Table? My Associative Table has 2 foreign keys and no primary key

create table Joint_Acc(
Joint_type varchar2(255),
foreign key(Acc_ID) references Account(Acc_ID), 
foreign key(Cust_ID) references Customer(Cust_ID)
);

Am I missing something like delete CASCADE?

[shy viper]

how do i deselect a channel selected by the user and added in the sqlite database so is it done?

DELETE FROM channel.id WHERE {author.id}

[tight isle]

Hi, so im making an discord bot and i have this

    @commands.command()
    @commands.has_permissions(administrator=True)
    async def joinid(self, ctx, jid):
        await ctx.send('Successfully changed channel!')
        print(f'{message.author} changed join channel.')
    @joinid.error
    async def joinid_error(self, ctx, error):
        if isinstance(error, commands.MissingPermissions):
            await ctx.send('You do not have permission to use this command!')
            print(f'{message.author} tryed to change join channel without permission.')
        if isinstance(error,commands.MissingRequiredArgument):
            await ctx.send('Please, send all required arguemnts!')
            print(f'{message.author} didnt give all required arguemnts for joinid command')

    @commands.Cog.listener()
    async def on_member_join(self, member):
        print(f'{member} has joined a server!')
        try:
            channel = self.client.get_channel(jid)
            await channel.send(f'{member.mention} has joined a server!')
        except:
            await ctx.send('You have to use joinid command first to set a channel!')```

shortly if anyone joins - i take jid and send on channel with this id, idk waht to do if id is invalid but ima do it later, if there is no jid, then it tells u to make jid first - if it works...

how to save jid to database and how to do database

[shy viper]

how do i deselect a channel selected by the user and added in the sqlite database so is it done?

DELETE FROM channel.id WHERE {author.id}

I want that the bot no longer sends the welcome message just that it does not send it to any channel that I deselect the channel .welcome channel #none or .welcome channel reset

[long abyss]

SELECT user_id, xp FROM leveling_users GROUP BY user_id, xp ORDER BY SUM(xp) DESC LIMIT $1``` why do I still get duplicated rows

like I only want one unique user_id with his sum xp

[shy viper]

SELECT user_id, xp FROM leveling_users GROUP BY user_id, xp ORDER BY SUM(xp) DESC LIMIT $1``` why do I still get duplicated rows

@long abyss help me

I want that the bot no longer sends the welcome message just that it does not send it to any channel that I deselect the channel .welcome channel #none or .welcome channel reset

[long abyss]

<Record user_id=513792552755789825 xp=18557>
<Record user_id=513792552755789825 xp=7809>```
should get

<Record user_id=513792552755789825 xp=total_xp_here>```

[proven arrow]

@shy viper What are you trying to delete? Can you show the table structure

[tepid cradle]

@long abyss just put SUM() around xp in the select part

[long abyss]

fixed already

thx tho

[tepid cradle]

Hi there.
I have a question about connecting MySQL and Python.
I'm using the mysql-connector package to connect a python script to mysql workbench where I have a database.
I managed to connect my database and my python script on my local computer. But I actually would love to have this SQL database on Gitlab.

So my question is, how can I connect my python script to a sql database on gitlab.
@torn sphinx You can't have a database on Gitlab. Git services are for storing your code and versioning, they are not for any kind of hosting

Also, your database is not in MySQL Workbench. Your database is a separate service running independently on your computer. MySQL Workbench is just an interface which allows you to connect to the database.

[topaz glen]

hey guys, im using sqlite3 right, i was wondering if there was a way to except if a table is empty or a whole database is empty

just so i can tell a message if the table exists or not

[torn sphinx]

@torn sphinx You can't have a database on Gitlab. Git services are for storing your code and versioning, they are not for any kind of hosting
@tepid cradle Thanks for clarifying!

What does that mean for my project though? Can we still have a shared database somehow?

[tepid cradle]

Yes. You'll have to find a hosting service for mysql databases

You should be able to find a free hosting service if this is just a hobby project or a college project.
If you're looking at building an actual application, then you might want to look at paid services.
For the time being, you can also get a VPS and run your own server. Contabo's basic plan is 4 Euro per month.

@torn sphinx

[potent sparrow]

I got the hang a little bit of Django ORM, but why would you use it instead of Pandas/Jupyter?

Is ORM used to mainly add items/objects/products?..... but that’s what’s made by the using the back office tho

[torn sphinx]

You should be able to find a free hosting service if this is just a hobby project or a college project.
If you're looking at building an actual application, then you might want to look at paid services.
For the time being, you can also get a VPS and run your own server. Contabo's basic plan is 4 Euro per month.
@tepid cradle

Thanks for explaining!!
Do you have a recommendation for a server? It's for a college project.

[tepid cradle]

@torn sphinx, I haven't used any.
If you're comfortable switching to PostgreSQL, you can use Heroku.
But otherwise just ask a general question on this channel and someone should be able to suggest

[torn sphinx]

Thanks, yes I'm really curious if someone else has experience with shared/online databases. I'm using it for a project with friends where we want to store data generated in python into a SQL database which we can all modify, preferably using git or any other easy method. Currently I'm using the mysql-connector package to modify a database on my local computer but I would like that others can access the database too when running the python files.

I've also heard of SQLite. Maybe that's easier? idk
Would love to learn about your expertise?

[dusk oxide]

What are the key differences between MongoDB and SQL?

[shy viper]

you know how to do that instead of writing
.benveny reset #chan
.benveny reset
it does automatically sees the guilda's id is removes the respective channel id
db.execute("DELETE FROM main WHERE channel_id=?", (channel.id,))

[nocturne basin]

Hey, I'm trying to copy a db template from another to create a new database. I have closed all sessions but one, and not sure how to close the last session that would allow me to copy the template. I'm using PgAdmin to try and help me out here but I feel stuck.
what am I overlooking? does PgAdmin also count as a session? is that why I cannot use with TEMPLATE command?

[potent sparrow]

Django ORM or Jupyterlab. Which one do you prefer using?

[nocturne basin]

Jupyterlab

[brazen charm]

🤔

they're two completely different things though

[torn sphinx]

Can someone please help me?> Thanks, yes I'm really curious if someone else has experience with shared/online databases. I'm using it for a project with friends where we want to store data generated in python into a SQL database which we can all modify, preferably using git or any other easy method. Currently I'm using the mysql-connector package to modify a database on my local computer but I would like that others can access the database too when running the python files.
@torn sphinx

How can I make a shared db

Do I need to have an online server? It's just for amateur purposes

[brazen charm]

git is version control

it has nothing todo with how you'll have to interact with your database

you need to have the server running and exposed to the open in order for your friends to be able to connect to the database

[potent sparrow]

I’m trying to use a sqlite dB in a project, in a Jupyterlab, how do I import it? or do I create a jupyterlab inside of the Django project?

[torn sphinx]

you need to have the server running and exposed to the open in order for your friends to be able to connect to the database
@brazen charm that's exactly what I need. How do I fix that though?

[brazen charm]

well mysql already runs as a server

but you'll need to port forward your ip to expose the port on your ip

[torn sphinx]

but you'll need to port forward your ip to expose the port on your ip
@brazen charm
You mean if I fill these in correctly and my friends fill in the same, they get access to my database? This is a screenshot from my mysql workbench when I make a new connection

I'm a complete noob with this

[brazen charm]

127.0.0.1 is your local host

it would need to be your public ip

and then be port forwarded on your router

[torn sphinx]

So my personal IP?

[brazen charm]

yes

[torn sphinx]

And in that case, if I give that to my buddies, they will get access to my database?

[brazen charm]

if you portforward yes

[torn sphinx]

what does that exactly mean? Just giving the ip address right?

Sorry for frustrating you with dumb questions haha

[brazen charm]

https://www.lifewire.com/what-is-port-forwarding-2483494

[torn sphinx]

Thanks! I will read it thoroughly!

https://www.lifewire.com/what-is-port-forwarding-2483494
@brazen charm Thank you for the article. I've looked up more on this. (https://support.cloud.engineyard.com/hc/en-us/articles/205408088-Access-Your-Database-Remotely-Through-an-SSH-Tunnel) Here it says that I have to create an SSH tunnel to achieve it. So my question is:
If I now change my hostname to my IP, my port to 3307, my username to something unique together with a password, then my friends can do the same but with their own IP and the same username and password?

Does it work like that?

[brazen charm]

ehh i wouldnt really say you need a ssh tunnel todo what you want todo

you just need to make your db accessible from outside your network

[torn sphinx]

If I now change my hostname to my IP, my port to 3307, my username to something unique together with a password, then my friends can do the same but with their own IP and the same username and password?
@torn sphinx So I can do this then right?

I'm not sure if I understand it correctly

[brazen charm]

yeah

[torn sphinx]

ah wow, is it that easy?

[brazen charm]

if you've port forwarded your Ip yeah

[torn sphinx]

if you've port forwarded your Ip yeah
@brazen charm I see. I've looked up this video and it is quite useful. I'm going to try port forwarding out. Thanks. https://www.youtube.com/watch?v=jfSLxs40sIw

What do I have to fill in as hostname in my python file though?

because for my local host I just wrote hostname = "localhost"

but I guess it should be my IP address right?

[brazen charm]

same thing for you

for your friends it will be your public ip

the ports will be the same for you as for them

[torn sphinx]

Ah ok, so I can then use my ip address as hostname as well right?

[brazen charm]

only thing they'll need to change is instead of 127.0.0.1 they'll use your public ip

yeah

[torn sphinx]

ah sweet, I will try this out tomorrow 🙂

Thank you very much for your help!!
I really appreciate it

[brazen charm]

np

[potent sparrow]

Can you do what Jupyterlab does, but in Django ORM?

[brazen charm]

They're two entirely diffrent things

Comparing them is pointless

[potent sparrow]

@brazen charm ok, but how can I use a Sqlite.db from my Django project, in a Jupyterlab?

[brazen charm]

Why writing code using the sqlite module?

[potent sparrow]

@brazen charm I created a Django project, and sqlite is default as a DB.

[brazen charm]

Yes and?

[potent sparrow]

@brazen charm I want to analyse sales in real time. But, I can’t copy and paste the dB as even a test.

[brazen charm]

Why can't you just use django admin for that?

[potent sparrow]

@brazen charm I have been, but, I want to visually graph my sales.

[brazen charm]

Then make a Web endpoint and plot it on the website

Probably gonna be the easiest method

[potent sparrow]

@brazen charm so, I cant just use any BD from any project???

[brazen charm]

I mean you can but its your issue if you fuck up how the orm has set everything up

You're going to be manually messing with a dB system that does it all automatically in its way

If you randomly try change something or do some random connection when it can't things will go very pear shaped

[potent sparrow]

@brazen charm I’m not messing with the DB, I’m just wanting to plot the data/sales

[brazen charm]

100% will be easiest if you do it with html and js as a web page rather than some script

Also probably look nicer aswell

[potent sparrow]

@brazen charm I’m asking because, I’m focusing on Datascience and want to implement it, as examples to learn from as I go.

[shy viper]

someone can help me with sqlite that a command doesn't work

[proven arrow]

@torn sphinx You can only have one command per prepared statement

You can run the queries twice as separate

[rocky belfry]

need to use variables in sql queries, the %s one is not working

what do?

stmt = operation.encode(self._connection.python_charset)
AttributeError: 'tuple' object has no attribute 'encode'

[proven arrow]

@rocky belfry Can you show your code?

[rocky belfry]

can i dm you?

@proven arrow ^

[proven arrow]

You can ask here, but i am thinking your error is because you have passed a tuple into the execute function.

Since execute() would expect first argument to be string, hence why its asking for the encode method, which str object has but tuple wouldnt

[rocky belfry]

crt1 = "CREATE TABLE CURRENT_SHARES_FOR_%s (SHARE_ID INT(5) PRIMARY KEY, COMPANY_NAME VARCHAR(8), NUMBER_OF_SHARES INT(5), SHARE_BOUGHT_FOR DECIMAL(10,4))", (uss,)

i'm trying to execute the above statement^

sorry for the italics the underscore did it

can't i use the formatting for the table name or is it just for the insert into values?

@proven arrow

[proven arrow]

Just for values

[rocky belfry]

aah ok

[proven arrow]

But are you doing like cursor.execute(crt1) ?

[rocky belfry]

yes

[proven arrow]

That would give you the error yes

Because of the reason i said before

[rocky belfry]

what should i do then?

[proven arrow]

You can unpack the tuple, so you can use its elements as positional arguments for execute. Or pass the first arg into execute as a string, and the second as the tuple of values.

So you can do cursor.execute(*crt1)

[rocky belfry]

aah ok thanks

[proven arrow]

But in your current use case since you cant use the parameters like that for the names, you can just pass the string as the argument.

[versed sluice]

Can one define the CHECK constraints for a sqlite table through sqlalchemy when defining the model?
I can't find the syntax for it anywhere.

Or should I resort to another approach to do ENUMs in sqlite?

[wispy hazel]

        try:
            sqliteConnection = sqlite3.connect('database.db')
            cursor = sqliteConnection.cursor()
            print("Connected to SQLite")
            sqlite_insert_prefix_query = """ INSERT INTO prefix
                                    (guild_id, guild_prefix) VALUES (?, ?)"""


            cursor.execute(sqlite_insert_prefix_query)
            sqliteConnection.commit()
            cursor.close()

        except sqlite3.Error as error:
            print("Failed to insert blob data into sqlite table", error)
        finally:
            if (sqliteConnection):
                sqliteConnection.close()
                print("the sqlite connection is closed")```I am using this but it gives me the following error,

[torn sphinx]

hello guys so i want to switch from using json as my db for to an actual database.

which 1 should i switch to im so confused

[broken estuary]

need some help in creating modes:

class Project(db.Model):
    contract_id = db.relationship("Contract", backref="project", lazy=True)
    lots_id = db.relationship("Lots", backref="project", lazy=True)

class Lots(db.Model):
    project_id = db.Column(db.Integer, db.ForeignKey("project.id"))
    contract_id = db.relationship("Contract", backref="lots", lazy=True)

class Contract(db.Model):
    project_id = db.Column(db.Integer, db.ForeignKey("project.id"))
    lots_id = db.Column(db.Integer, db.ForeignKey("lots.id"))

so the context is: my Project may have Lots and may not have it. if it doesnt have the Contract will be pointed in the Project, but if Project have Lots the Contract will be pointed in the Lots and not in the Project.

is this how it should be? or is this they place that i should be asking for help in something like this?

[proven arrow]

@torn sphinx What is your use case?

I am using this but it gives me the following error,
@wispy hazel You are not passing any values, into your query. You tell the database to look out for 2 values VALUES (?, ?) which you never provide.

The ? are just placeholders, so you need to provide the parameters that will fit into here

[torn sphinx]

@torn sphinx What is your use case?
@proven arrow i want to know which db would be better and all

[tepid cradle]

@torn sphinx Which db is better depends on use case. Use case means what kind of data you'll be storing and what kind of application will be using it.
There's no best database that someone can just point to without knowing what you're trying to do. In fact, as a general rule, there's no best of anything, only the best for a particular use case.
That's why it always helps to give as much detail as possible about what you're trying to do while asking a question.

[torn sphinx]

im gonna store:

1. guild data
2. temprole data
3. giveaway stuff
4. roles stuff

[proven arrow]

How much data will you be storing, and how often?

[grim lotus]

I guess goo for Postresql since you are using it for a discord bot

postgresql simple fast efficient Database if you are just starting then it may be the choice

if i get you correctly all of the things you are trying to do will ezily be done in psql with right table schema

[torn sphinx]

Hey

I need help

So this is my code

and I am getting a syntax error near join

here is the error

anybody have any idea how to fix I can't find the syntax error at all

[wintry stream]

Hello, I'm moving my discordbot from psycopg2 to asyncpg, and i'm running into a small error

    @commands.command(help='Warns the user for the specified reason')
    @commands.has_permissions(manage_messages=True)
    async def warn(self, ctx, member: discord.User, *, reason=None):
        await ctx.message.delete()
        embed = discord.Embed(title=f'You have been warned in {ctx.guild.name}', color=discord.Color.green())
        if reason:
            embed.add_field(name='Reason:', value=f'{reason}', inline=False)
        embed.add_field(name='\u200b', value=f'If you have questions about this action, or would like '
                                             f'to appeal it. Please contact the staff team. '
                                             f'You were warned by {ctx.author.mention}', inline=False)
        try:
            await member.send(embed=embed)
        except discord.Forbidden:
            await ctx.send('Could not send DM to user')
        try:
            result = await self.bot.con.fetch("INSERT INTO warnings(uid, executor, timedate, reason) VALUES($1, "
                             "$2, CURRENT_TIMESTAMP(1), $3) RETURNING warnid", member.id, ctx.author.id, reason)
            warnid = result[0]
        except Exception as error:
            print(error)

gives this error

invalid input for query argument $2: 234649992357347328 (expected str, got int)

but the executor argument in the database is a bigint type

so i would expect it to take an int as the argument

@torn sphinx seems like your traceback goes back further, could you send the FULL traceback?

it will always start with Traceback (most recent call last):

[torn sphinx]

ok

@wintry stream

that's the traceback

Traceback (most recent call last):
  File "/home/container/.local/lib/python3.8/site-packages/discord/client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "bot.py", line 56, in on_member_join
    await client.pg_con.execute("INSERT INTO invites (userid, total, join, leave) VALUES ($1, $2, $3, $4)", str(inviter.id), 1, 0, 0)
  File "/home/container/.local/lib/python3.8/site-packages/asyncpg/pool.py", line 518, in execute
    return await con.execute(query, *args, timeout=timeout)
  File "/home/container/.local/lib/python3.8/site-packages/asyncpg/connection.py", line 297, in execute
    _, status, _ = await self._execute(query, args, 0, timeout, True)
  File "/home/container/.local/lib/python3.8/site-packages/asyncpg/connection.py", line 1445, in _execute
    result, _ = await self.__execute(
  File "/home/container/.local/lib/python3.8/site-packages/asyncpg/connection.py", line 1454, in __execute
    return await self._do_execute(query, executor, timeout)
  File "/home/container/.local/lib/python3.8/site-packages/asyncpg/connection.py", line 1466, in _do_execute
    stmt = await self._get_statement(query, None)
  File "/home/container/.local/lib/python3.8/site-packages/asyncpg/connection.py", line 351, in _get_statement
    statement = await self._protocol.prepare(stmt_name, query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 163, in prepare
asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "join"```

[wintry stream]

what type is the join column in your database?

[torn sphinx]

int

[hoary sonnet]

you putting cmd line args into it?

you may need to put it in quotes

[torn sphinx]

it is in quotes

[hoary sonnet]

then try not putting it in quotes?

[torn sphinx]

it won't work like that

[hoary sonnet]

what do the values $ 1, 2, 3 ,4 correspond to?

where are you inputting those?

[torn sphinx]

after the ""

[hoary sonnet]

I mean where are you assigning values to them

[torn sphinx]

not there

[wintry stream]

@hoary sonnet that's how it goes

[torn sphinx]

yeah where sebkuuip showed lol

[wintry stream]

in values you put $n and then after the query you put the values

[torn sphinx]

the top one worked fine and the bottom didn't

[hoary sonnet]

yeah that's to prevent the sql injection

sql arguments

parameters*

[wintry stream]

this is how the docs show it

[hoary sonnet]

I see it now.

[wintry stream]

yep

[torn sphinx]

yeah honestly I don't know what's wrong with the line

it's saying I have a syntax error near join

[hoary sonnet]

but what is 1, 2, 3, and 4? are you actually putting 1, 2, 3, and 4 into the database?

[wintry stream]

atrixs what if you try do to it exactly like the docs where you put each argument in ()

[hoary sonnet]

are those variable names?

[wintry stream]

@hoary sonnet $1 $2 are the arguments

then he puts these values in

[hoary sonnet]

cmd line args?

[torn sphinx]

ok so I just put each one in ()

[hoary sonnet]

oh

[wintry stream]

so $1 translates to str(inviter.id), $2 becomes 1, $3 becomes 0

etc.

[hoary sonnet]

i'll brb

[torn sphinx]

like this ?

[hoary sonnet]

i'm going to check something

[torn sphinx]

still didn't work

[hoary sonnet]

txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
db.Execute(txtSQL,txtNam,txtAdd,txtCit);

you want something like this...$ looks incorrect

@ looks correct for sql params

[torn sphinx]

I've used $ everywhere

I don't think its the problem

[wintry stream]

@hoary sonnet look at the asyncpg docs

asyncpg uses the default postgres syntax for arguments, namely $n

so $1, $2, $3

that's the correct way

[hoary sonnet]

so does it do the same thing?

I just got that off of w3schools though.

it's correct when it's on an official website like that.

[wintry stream]

but is that a w3schools on asyncpg?

[torn sphinx]

its even correct when its on the official docs

[wintry stream]

no it's not

[hoary sonnet]

ok then it must do the same thing then.

[wintry stream]

@hoary sonnet that's not a w3schools on asyncpg

[hoary sonnet]

just different syntax

[wintry stream]

as you can see from the Execute, it's not awaited

[torn sphinx]

It doesn't say its the issue with $1

[wintry stream]

aka not async, and thus cannot be asyncpg

[torn sphinx]

it says its the issue with "join"

[hoary sonnet]

ok

[torn sphinx]

or near it

[hoary sonnet]

just checking

[wintry stream]

@torn sphinx have you checked if you do your transaction correctly?

if you use transactions

[torn sphinx]

I am not sure what you mean

[hoary sonnet]

but it still does the same thing...it has the same functionality probably

so that's not the issue

case in point.

[torn sphinx]

I'll change the column name

[wintry stream]

do you know what a transaction is atrixs?

[torn sphinx]

maybe join is interferring with something

no I am not sure what you mean by that

[wintry stream]

a transaction is something known with all databases

so when you do execute it instantly applies to the database

[torn sphinx]

maybe if I see what it is I'll know

oh the connection?

yeah it works fine

[wintry stream]

but there is a way where you can make edits to the database, and then discard them if something's wrong

it's called a transaction

so you open a transaction, it creates a temporary version of the database and edits that instead

[torn sphinx]

oh I don't think I did that

[wintry stream]

and if you just commit normally, it will apply all changes

but if you do not commit, it will discard all changes

[torn sphinx]

yeah I didn't do that

[wintry stream]

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

[torn sphinx]

uhh I might have fixed it lol

[hoary sonnet]

why don't you use prepared statements?

https://magicstack.github.io/asyncpg/current/api/index.html#prepared-statements

[torn sphinx]

YEAH I DID

so "join"

is like interferring witih something

so I changed the column name to "stay"

and now it's work

working*

[hoary sonnet]

ok

[torn sphinx]

that's really strange though

but I guess I figured out the issue

[hoary sonnet]

prepared statements may be more efficient for what you are doing @torn sphinx

that is if you are going to be using the query more than once.

[torn sphinx]

ok

[wintry stream]

welp guess that wraps it up for today, join i guess is a built in for pg

[torn sphinx]

yeah

[wintry stream]

now i need to find a fix for my issue

[hoary sonnet]

bye

[torn sphinx]

well thanks a lot

[hoary sonnet]

thanks

[torn sphinx]

ok so ur issue is easy to fix sebkuip

[wintry stream]

lets move to #help-grapes as i opened the issue there i guess?

[torn sphinx]

all you need to do is change member.id to str(member.id)

ok

[rough hearth]

How would a website like reddit or stack overflow store who has upvoted and downvoted a given item and what their vote was?

It seems like having a table of users, posts, and votes would become immense, but I guess there's no way around that.

[rocky grove]

Link columns of tables

that's for your example

[tepid cradle]

I would assume it would be something like

+------------+----------+-------------+
|post_id     |user_id   |vote         |
+------------+----------+-------------+
|1234        |1245      |1            |
|1234        |1246      |-1           |
+------------+----------+-------------+

Could easily grow into billions of rows

[brazen charm]

i doubt you would ever deploy like that

you would probably put it in cache first and have scheduled saves

so if someone spam clicks or it changes rapidly it doesnt end up in loads of quires

cache first then save in x time intervals

[tepid cradle]

yeah, this was more about the table structure

Of course, I'm mostly guessing from what seems like a logical way of putting it in a regular SQL Db. I have no idea of how it's actually implemented

[rough hearth]

@rocky grove @tepid cradle @brazen charm thanks for your input

[ember marsh]

I don't think Reddit stores raw votes.

Well they do in the short term. They get archived after what, six months.

I bet vote tallies would be denormalized and processed from a queue instead of linked. You keep track of them for the user side, but not for calculating.

[gaunt reef]

Anyone know an sql discord for help on homework? or is this channel fine? ping me when answering tyty 😄

[ember marsh]

@gaunt reef what's your SQL question?

[gaunt reef]

Uh well simplified down its how would i get something like count(distinct(x, y)) working where x, and y are two columns that correspond to a composite primary key (using SQLite)

In context, im trying to count the number of votes of different posts, but the way that the tables are set up is:
posts(pid, posterID, pdate, primary key (pid))
votes(pid, vno, primary key (pid, vno), foreign key (pid) references posts)

So i want to make sure that im getting distinct votes without overlap, for each post

O and i need it to be no nesting cause of the homework spec

[ember marsh]

select post id and a count of posts then group by post id?

not sure what they mean by nesting

what is the vno field too?

[remote vessel]

umm so im learning dbs, but when i tried to pull from my db, it didnt work. how come?

[steep turret]

umm so im learning dbs, but when i tried to pull from my db, it didnt work. how come?
@remote vessel you're going to need to be a bit more verbose on the issue... code, expected result, actual result, etc

[remote vessel]

@remote vessel you're going to need to be a bit more verbose on the issue... code, expected result, actual result, etc
@steep turret eh i figured out i didnt .commit() so it wasnt saving it. thanks for the help

how do i create a database file with python? is it just open("filename.db", "w")

[weak tinsel]

hello!

so i have a question

i always get a sql syntax error when trying this code:

    dbCursor.execute(rawExeText)
    dbConnection.commit()

where curDate = d2020_10_12

this is in mysql btw

if you got an answer, ping me

[pseudo cove]

@weak tinsel that's not what alter table does

you want insert

also use parametrized queries

[weak tinsel]

parameterised?

i want to add a field

therefore, i used alter

[pseudo cove]

uh

are you dynamically creating columns?

that sounds very sketchy

[weak tinsel]

yes

[pseudo cove]

why

[weak tinsel]

cuz

its an attendance thingy

so everyday, a new column

[pseudo cove]

no no no

[weak tinsel]

?

[torn sphinx]

my mongo db isn't working :p

someone help

[pseudo cove]

You use 2 columns: one for name, one for date

[weak tinsel]

yes

[pseudo cove]

that's it

no extra columns

[weak tinsel]

huh?

i have ID too

[pseudo cove]

whatever

I'd name date

[weak tinsel]

wdym?

[torn sphinx]

how do i fix this

[pseudo cove]

so for each day

[weak tinsel]

yes..

[torn sphinx]

i did that, and when i do my command, my bot says nothing

[pseudo cove]

oh yeah plus a present bool

for each day do insert name,date,present

[torn sphinx]

Please help me sm1

[weak tinsel]

that will cause redundancy

[pseudo cove]

you don't need to create new columns

[weak tinsel]

i want name and id to be fixed

[pseudo cove]

one row for each person per day

that's the sql way

[weak tinsel]

uhh i did not fully understand

so

what you are sayin is

that each day i add name,id and date?

[pseudo cove]

plus whether they're present that day, yes

you can move id-name mappings to a separate table and use join

[weak tinsel]

so

i put name , id in parent

and dates in foreign table?

[pseudo cove]

wait why do you need a foreign table

[weak tinsel]

ok nvm

[pseudo cove]

but for each day, you add one row per person

[weak tinsel]

ohh

so i invert the table?

like field = date

and then values arethe dates

[pseudo cove]

yes

[weak tinsel]

ok

uhh

but how will name,id be then

i cannot invert them

@pseudo cove can you just like draw the layout

im confused

[raven tulip]

Can i get some help on a SQL Question for my homework?

Specify the integrity constraint that the price of any book must be less then 90$
My Book says the answer is
ALTER TABLE Book ADD CHECK (Price>90)

Yet in the book Table

Price isnt listed insted price is listed in the Copy Table

im trying to make this work in MS access and the book is no help.

[torn sphinx]

Hi

Say, I want to create a ranking based on some value.

How should I model my database?

I have lots of records and get millions of entries.

I believe querying each time would be a pain. But updating table each time may cost performance too right?

[dusky shore]

I have a postgres database and I'm trying to use upsert like this:

INSERT INTO permissions (serverid, role, bantime)
VALUES ($1, $2, $3)
ON CONFLICT (serverid, role) DO
UPDATE SET bantime=$3

basically it's a table of columns: serverid, role, bantime, mutetime, and locktime

serverid and role columns together should be unique (only 1 same role on 1 sever) that's why I'm using upsert like this, but upsert is usually used with just 1 conflict column and the syntax I've used doesn't seem to work:

asyncpg.exceptions.InvalidColumnReferenceError: there is no unique or exclusion constraint matching the ON CONFLICT specification

any ideas?

I was even thinking of something like this but I doubt that's the way it should be done

INSERT INTO permissions (serverid, role, bantime)
VALUES ($1, $2, $3)
ON CONFLICT (serverid) DO
INSERT INTO permissions (serverid, role, bantime)
VALUES ($1, $2, $3)
ON CONFLICT (role) DO
UPDATE SET bantime=$3

[proven arrow]

Did you add the unique constraint on those columns when making the table?

So you would enforce it on your table like so unique(serverid, role)

[dusky shore]

I didn't add UNIQUE because individual columns aren't unique only their combination, where should I put the unique(serverid, role)?

INSERT INTO permissions (serverid, role, locktime)
VALUES ($1, $2, $3)
ON CONFLICT unique(serverid, role) DO
UPDATE SET locktime=$3

when I do this I get another error:
asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "unique"

@proven arrow

[proven arrow]

You can still enforce 2 columns to be unique. So that would do what you want, which is where the combination of a given serverid, role role can exist only once

[dusky shore]

but how would I do that?

could you give me an example, I'm still fairly new to postgres

[proven arrow]

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (serverid, role);

That would be for you to alter the existing table, and add that constraint

[dusky shore]

oh, and how could I create it with that?

CREATE TABLE permissions (
    serverid NUMERIC(40) NOT NULL,
    role NUMERIC(40) DEFAULT 0,
    bantime INTEGER DEFAULT 0,
    mutetime INTEGER DEFAULT 0,
    locktime INTEGER DEFAULT 0
)

this is how I've created it

how could I do it with that unique constraint?

[proven arrow]

Yeah same way, you can add unique (serverid, role) at the end

[dusky shore]

CREATE TABLE permissions (
    serverid NUMERIC(40) NOT NULL,
    role NUMERIC(40) DEFAULT 0,
    bantime INTEGER DEFAULT 0,
    mutetime INTEGER DEFAULT 0,
    locktime INTEGER DEFAULT 0,
    UNIQUE(severid, role)
)

like this?

[proven arrow]

Yes

[dusky shore]

oh, okay and after I do that the upsert query will work without any adjustments?

[proven arrow]

Yes should do, but you can try it

[dusky shore]

alright it works, thanks a lot!

[torn sphinx]

Does someone have experience with failed connections between mysql workbench and heroku postgresql

Keep getting an error when trying to test the connection

[upbeat marsh]

I'm getting this error very often => http://sqlalche.me/e/13/2j85 what should I do if I already tried the pool_pre_ping=True ?

[torn sphinx]

Can someone help me! I'm trying to connect my SQL server on heroku with my python script!

I'm getting this error:
mysql.connector.errors.OperationalError: 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

[hot grove]

Anyone know how to fetch the newest data from MySQL select when using pools with mysql-connector?

I am committing everything, and auto commit is enabled anyways but I am fetching old data..

I get new data if I make a new connection each time I want to fetch the newest data but I dont really want to have to do that.

[minor ruin]

You need to look at MSSQL Service and what user is it running as

then grant that user full access permission to specific folder folder

[vale void]

A file activation error has occurred. The physical file name 'C: \ walid \ Ikram1.mdf' may be incorrect. Diagnose and fix any additional errors, then try the operation again.

i have this problem

A file activation error has occurred. The physical file name 'C: \ walid \ Ikram1.mdf' may be incorrect. Diagnose and fix any additional errors, then try the operation again.

[minor ruin]

I don't have MSSQL installed on any machine I can access

[vale void]

how can i resove the is

?

this

??

[minor ruin]

then grant that user full access permission to specific folder folder```

[vale void]

you can helm me

??

[minor ruin]

I am

I gave you guidance

I can't screenshot guide you, I don't have MSSQL installed on any personal machines

[vale void]

how can i give the pernission ?

[minor ruin]

In Windows Explorer

[vale void]

call me in my dm ?

you can call me ?

plzzz

[minor ruin]

Nope

[vale void]

how can i give the permision

?

[minor ruin]

that menu

[vale void]

annd what ??

[minor ruin]

MSSQL is pretty rough server to run

I'd strongly consider a different one

[vale void]

ohh ok

ty ❤️

[minor ruin]

it's enterprise server and requires enterprisy knowledge unfortunately

[vale void]

yes i now

[lavish flower]

I just started working with MongoDB about 16 hours ago, and my use case is pretty simple: I just want to check, with a loop, if an object exists in the Members objects, for each "guild" _id. If not, I want to add the dataset. I've got the adding down, but my check seems to be incorrect?
if mongo_guilds.find( {f'{guild.id}.Members': f'{member.id}'} ) != 0:

[lime echo]

What does it mean to dump a database? does it create a copy of it or does something else?

[proven arrow]

Yes essentially a backup/replication of all the data in the database. The file will contain a load of SQL statements that can be run again to get your tables and it's data back.

[torn sphinx]

AttributeError: module 'sqlite3' has no attribute 'connnect'```

why isnt it working

[proven arrow]

It says your module has no attribute connect

Do you have a file named that?

[torn sphinx]

m no

import sqlite3```

[proven arrow]

I meant the name of your python file. What is it called? Or do you have any files in your project called sqlite3

[torn sphinx]

main.py

i could just use db browser

also i want to ask people put TEXT where it even needs to me a int

[proven arrow]

Oh my bad I see it

[torn sphinx]

why?

[proven arrow]

You have an extra n inside connect

[torn sphinx]

OH

[proven arrow]

You spelled it incorrectly

[torn sphinx]

OMG

I AM SO BAD AT SPELLING

also i want to ask people put TEXT where it even needs to me a int
@torn sphinx

[proven arrow]

What?

[torn sphinx]

people use TEXT

but i need to be a int id

suppose i have a member id

why do people put there ids in TEXT instead of INTEGER

in the table

[proven arrow]

Because you must have specified the column type as text instead on integer

[torn sphinx]

so if i use text will it work

the user ids ans stuff

and*

[halcyon forum]

has anyone here used firebase?

pyrebase to be more specific?

i am getting an error while trying to write to it

my rules are the default

it says unathorized access

[torn sphinx]

hello i am really new at databasses

[proven arrow]

so if i use text will it work
@torn sphinx No if you specify the column as Text then it will only store and accept text data. If you say integer then itll do integer.

[torn sphinx]

i am making economy system

what shuld i use far wallet

[proven arrow]

@halcyon forum Did you enable read and write?

What is economy system?

[torn sphinx]

o

discord.py

so like make a wallet

numbers in int

wallet = 10 coins

[proven arrow]

Im not too sure what you mean

[torn sphinx]

lik that

wallet = 10 coins
@torn sphinx

like this

[proven arrow]

So every user has a wallet?

[torn sphinx]

yep

untill they do a command

[proven arrow]

What does the command do?

[torn sphinx]

!openaccount

and it creates a line for a specific user with a wallet

[proven arrow]

So if i understand correctly, then every user has an account with a balance?

[torn sphinx]

yeps

[proven arrow]

In that case you need to figure out what structure you want your table to have first

[torn sphinx]

i just want to ask do i have to store the discord user id as int or text

already done

and made the table

[proven arrow]

Yes you will have to store the user id, as they are static and dont change. And you can store as an Integer

[torn sphinx]

alrigh

and also what are those ai u pk nn

[halcyon forum]

rules_version = '2';

service cloud.firestore {

  match /databases/{database}/documents {

    match /{document=**} {

      allow read, write: if false;

    }

  }

}

i dont understand what this means

@proven arrow

is this allowing writes and reads

[proven arrow]

Not entirely sure, as I've not used it for sometime. I just took a guess as I remember having a similar problem when using it with Android. But if i remember you need it as allow read, write: if {condition}; @halcyon forum

If you want to allow all you can just do allow read, write: if true;

You have false, so itll deny it

@torn sphinx AI (Auto_Increment), PK (Primary Key), NN (Not Null), U (Unique),

[torn sphinx]

so i dont need to check any of these?

[proven arrow]

You will need most of them, at least once in your table. You should have a read up on what each one does, so you get a better understanding of what they do.

[tepid cradle]

I am trying to connect from python to my Redis cloud account. This is my code

import redis
host_address = 'https://redis-obfus.cat.ed.cloud.redislabs.com'
r = redis.StrictRedis(host=host_address, port=12345, password='mypasswordgoeshere')
r.keys()

However, I'm unable to connect, it keeps giving this error:
ConnectionError: Error 11001 connecting to https://redis-obfus.cat.ed.cloud.redislabs.com:12345. getaddrinfo failed.
I also tried with http instead of https, same error.
If anyone knows why this is happening, please help.

OK, resolved. I actually had to remove the https:// part completely

[charred jetty]

def check_registered(db):
    cursor = db.cursor()
    query = 'SELECT key_value, data_value FROM wisebox WHERE key_value = "registered"'
    cursor.execute(query)
    records = cursor.fetchone()
    cursor.close()

    print("RECORDS:" + records[1])

    # key doesn't exists, add as false
    if records == None:
        query = "INSERT INTO wisebox (key_value, data_value) VALUES (%s, %s)"
        values = ("registered", 0)
        cursor.execute(query, values)
        db.commit()
        return False

    if records[1] is "0":
        return False
    
    return True

I have this inside a While loop, but it always returns only what happens in the first iteration. If I manually change the table, the code doesnt get the changes, any idea why? Do I need to close the connection?

oh I think the issue is with autocomit?

aha, it was the issue, thanks! 😄

[torn sphinx]

Hello, I'm messing with sql last two days and that's what I want to ask.

If I subclass MySQLConnection and make it work with with statement, I have to connect each time I want to access my database.
However, if I save conn variable somewhere outside main functions I see this after 5 minutes of idle.

The thing is, is it happening everywhere if I choose second variant or that's a problem on the server side?

And how do I avoid such weird and uncomfortable thingies?

[teal sparrow]

Can someone please take a look at my SQL query to see if it's correct?

The question is at the bottom of the image.

SELECT Inventory.StoreStock, Store.StoreDescription, Store.Location, Store.ProductCode
FROM Inventory INNER JOIN Store
ON inventory.StoreCode = Store.StoreCode
ORDER BY
Inventory.Date DESC

Its an image, I don't have the chance to try if it works

[tender citrus]

is it possible to show records for a particular month? where user inputs the month

[analog shadow]

                                CREATED BY HUDLEZ
                        ---------------------------------
                        TEMPERATURE; °-64.966
                        HORIZONTAL WIND; 6.025 m/s
                        SEASON; FALL
                        PRESSURE; 754.148 pascal or 0.109379 psi```

prolly my coolest project yet

although simple

[rich plume]

Anyone knows why do we need reference tables?

reference tables like this

I mean why not just put job_id into the account table where user_id is the primary key

[woven dove]

I'm using DB Browser for SQLite
Is there any way to view multiple tables at the same time? I don't want to go back and forth between tables just to check on the foreign keys for each table

[clever nimbus]

@woven dove I don't think there is a way. But I would script the tables to a file and reference the text there for fk's

[nocturne basin]

 sql = f'''SELECT t1.id, cast(t1.enterprisevalue as money), ROUND(t1.enterprise_value_revenue, 2),
       ROUND(t1.revenuepershare, 2), 
       ROUND(t1.debt_to_equity, 2), 
       ROUND(t1.profitmargin, 2), 
       ROUND(t1.price_to_sales, 2), 
       ROUND(t1.price_to_book, 2), 
       ROUND(t1.put_call_ratio, 2), 
       t2.employees, 
       cast(ROUND(t1.revenue_per_employee, 2) as money)
        FROM security_advanced_stats as t1
        cross join (
        select *
        from security_stats as t2
        WHERE id IN ({placeholders})
        order by id
        limit 1) as t2;''' 

I have a query that selects a handful of columns from table 1, aka t1. I would like to cross join this query with another column from table 2, t2, labelled as employees

I use placeholders for my parameter. For my case, a list of companies for the query to pull for.

The query doesn't throw errors and does almost everything right, except it's pulling every company in my dB for t1 data and ignoring the placeholders parameter. Could use some evaluation here to help me fix this query. Maybe I'm overcomplicating it somehow...

[hasty hinge]

I have a MySQL table with users, every user has an XP amount. What would be the best way to get the index of a user ordered by XP?

[torn sphinx]

I'm using the python databases library to store large numbers in an sqlite database

765401478671499324 - What I should be getting
765664041104048188 - What I am getting

CREATE TABLE IF NOT EXISTS RoleMap (EmojiID BIGINTEGER, RoleID BIGINTEGER)

await self.database.fetch_one('SELECT * FROM RoleMap WHERE EmojiId=:id', {'id': event.emoji.id})

Why is it not returning the correct number?

I remember this occurring to me once in Java, and I solved it by changing INTEGER to BIGINTEGER and retrieving the column value into a long

But this doesn't seem to solve my issue in python as I don't believe long is a valid data type

[surreal charm]

if i create a temp table like CREATE TEMPORARY TABLE x (LIKE y) is there a way to make sqlalchemy generate a Table object based off the new temp table x ?

[torn sphinx]

Hi, Im new to sql and was wonderig if there is some kind of list or set for datatype. For example I would like to make a table of foods and the type of those could be either 'fruit', 'veggie' or 'dairy'. Is there a way to ensure that every new entry has a type of one and only one of those? Or just use varchar() and hope there will be no typos?

[harsh pulsar]

@torn sphinx postgresql supports array data type, and sqlite supports json

however, often array/set data is a sign that your database could be designed better

especially for basic applications

[surreal charm]

are there any python libraries that will generate a bulk insert for you based on a list of dictionaries? not talking about sqlalchemy as sqlalchemy seems to want you to use their ORM and table metadata etc

ideally like... db.bulk_insert('table_name', [dict(a=1),dict(a=2),dict(a=3)])

[torn sphinx]

WITH c_insert AS (
    INSERT INTO "public"."client" ( "client_id", "tag", "name") 
    VALUES ($1, $2, $3)
    ON CONFLICT (client_id) DO UPDATE
    SET "client_id" = EXCLUDED.client_id
    RETURNING "client_id"
)...#rest of the query

This works fine, unless all of the values are NULL. How can I make a conditional and do not insert if $1 is NULL?

[torn sphinx]

if not $1, no?

Inside the query

[torn rain]

Python2.7 : So i have this .db file. If i open it in DB Browser there is a table with events. This events table has columns: type, timestamp and data. If i view the column data in the db browser i can see there is some sort if dictionary stored. I try to read this data aswell in my python script. Iterating and printing the timestamp and types goes fine, these are datatypes int. The Data is BLOB. And i cant get it to show the dict in characters. It only prints out like this: ['Array[Byte]((<System.Byte object at 0x000000...[123] <- which translates in unicode to the right first character.

I was wondering if anyone can help me out translating the binary file to readable data

[woven dove]

@clever nimbus wouldnt that be a bit counterproductive? im still new to databases but id imagine that people will need to look at a ton of tables to make sense of where goes where
Or do people use another program to visualize it?

[jagged flint]

class A:
  def __init__(self, b: B, c):
    self.b = {key: instance of B(), ...}
    self.c = c
``` how would i save this custom class in a mongodb document? normally i'd use `__dict__` but instances of B() would still be a problem.

[hidden osprey]

    db = sqlite3.connect('database.db')
    cur = db.cursor()
    cur.execute(f'SELECT username FROM login WHERE username = {username}')
    res=cur.fetchall()

    if res != []:
        print("Taken")
        main()
    elif res is None:
        print('Sooncl')``` How do i make it so if it doesnt find it it returns None

[proven arrow]

You just need to do:

if not res:
  # no results
else:
  ...

The inverse if res also will work. @hidden osprey

[hidden osprey]

/Code/python/Gui/passman/hashtest.py", line 19, in signup
    cur.execute(f'SELECT username FROM login WHERE username = {username}')
sqlite3.OperationalError: no such column: ssd```

@proven arrow

[proven arrow]

You should use a parameterised query instead of f string.

execute("SELECT username FROM login WHERE username=?", (username,))

[torn sphinx]

Can someone help me with getting started on unittest mocking my postgresql database? I have functions written in python that add and remove records from the database. But I would like to test those without actually affecting the database. Anyone experience with mocking?

[clever nimbus]

@clever nimbus wouldnt that be a bit counterproductive? im still new to databases but id imagine that people will need to look at a ton of tables to make sense of where goes where
Or do people use another program to visualize it?
@woven dove yeah, but you were asking about that program. I think dbbeaver might have more options. I spend most of my time in ms-sql so their tools have all that. But pretty sure you can't open a slqlite db there. Although maybe I'll try today to test that out

[woven dove]

ohh yea i understand, thanks

[trim knoll]

Hey guys, I am using postgresql along with asyncpg library. I want to use IN clause to delete all the values in the table along with a python list.
Lets call it

user_list = [1, 2, 3]

And I am executing

    async def foo(self, user_list):
        async with self.bot.pool.acquire() as con:
            await con.execute(f"delete from table where userid in $1", user_list)

userid is a numeric value in the table
But it raises this error, do any of you have any idea what am I doing wrong here?

[brazen charm]

@trim knoll need to say its a array

IN ANY($n::type[]) i think it was

[trim knoll]

hmm, so something like this?

    async def remove_ban_requests(self, user_list):
        async with self.bot.pool.acquire() as con:
            await con.execute(f"delete from banrequest where userid in ANY($1::type[])", user_list)

[brazen charm]

you'd replace type with the actually data type

but yeah

[trim knoll]

  async def remove_ban_requests(self, user_list):
        async with self.bot.pool.acquire() as con:
            await con.execute(f"delete from banrequest where userid in ANY($1::list[])", user_list)

I tried it but it still gives me a similar error

did i understand you wrong?

@brazen charm

[brazen charm]

[] indicated that its an array

theyre sql types

not python types

so if its a list of strings it would be TEXT[] to signify a array of strings

[trim knoll]

ahhhhh

so in my case NUMBERIC[]

got it thanks!

[brazen charm]

generally the same type as your table column

[trim knoll]

yup

[brazen charm]

usually big int for discord ids

[trim knoll]

hmm i was using numeric for them, is that bad

[brazen charm]

dont think do

mostly its just about how much data the db will set asside for each column

[trim knoll]

(there was a typo i just saw 😓 )

[brazen charm]

aha

[trim knoll]

ugh

its still there

[brazen charm]

hmmm

1 sec

[trim knoll]

    async def remove_ban_requests(self, user_list):
        async with self.bot.pool.acquire() as con:
            await con.execute(f"delete from banrequest where userid in ANY($1::NUMERIC[])", user_list)

[brazen charm]

wait

try using = not in

might be my bad memory

[trim knoll]

it did work now!

hmm do you have any clue why the in clause was not working

[brazen charm]

if i remember its because it expects it to be a hardcoded tuple

e.g x IN (var1, var2, var3) where it knows the exact length of the tuple

not sure if ($1, $2, $3) would work but its a pretty hacky method for something that can be diffrent lengths

[trim knoll]

ahh

if you dont mind i have another question

[brazen charm]

sure

[trim knoll]

is using any($n::type[]) same as looping over something like this:

async with pool.acquire() as con:
  for value in values:
    await con.execute("delete from table where column = $1", value)

like is any looping over the array?

[brazen charm]

sorta but not really

ANY will go until it finds a match, generally will be much quicker than anything you could do with a For loop

[trim knoll]

hmm alright thanks a lot! i learned a ton

[fleet kiln]

hi i installed python 3.9 and now i am trying to install mysql and it is showing it has dependency only upto python 3.8

[brazen charm]

downgrade

[rough hearth]

@torn sphinx wants to know how to write unit tests for a program that uses a database. I have no experience with this but my intuition is to create an in-memory database with the same schema at the start of the tests.

[rotund badge]

How should I handle migrations in Python? Specifically, a Discord bot using psycopg2

[eternal wadi]

guys

[flat ibex]

anyone a sqlalchemy god

[rough hearth]

@flat ibex go ahead and ask your question. If someone knows, they'll answer.

[vague haven]

why doesn't this line work?

mycursor.execute(f"INSERT INTO servers (serverid, name) VALUES (%s, '%s')", (guild.id, guild.name))

[foggy sand]

@vague haven Depends on what you mean with "doesn't work" but normally, you should leave quoting of parameters to the interface, i.e. try with ...VALUES (%s, %s)...

[brazen charm]

@rotund badge well generally they're gonna have to be manual and I would recommend changing them often

Also a side note: use asyncpg not psycopg2

[torn sphinx]

i finished installing postgre and its telling me to download stackbridge, what is that?

[proven arrow]

i finished installing postgre and its telling me to download stackbridge, what is that?
@torn sphinx Do you mean stackbuilder?

Ive never used it but i think its just a GUI to install any extensions to go with your postgres installation. You can probably leave it out.

[high geyser]

how can I generate an unique id for each row?

in a psql db

[torn sphinx]

yeah stackbuilder

oh ok

[high geyser]

pls welp

[proven arrow]

do you mean auto generating PK for each row?

[high geyser]

no

like a unique id

except a pk

like when u insert into a column called keyword

then an id for it should be unique and created like 19283733737373

[proven arrow]

If you want to add a unique constraint to a column you can add the UNIQUE constraint keyword

[high geyser]

nono

like when I insert into a column

that column should have an auto generated unique id

like 13485999606

and it should be like at least 6 digits

@proven arrow

and it should be like unique among two tables. Is that possible?

[proven arrow]

You can probably have create a function for that which will generate you the IDs. Alternatively, you can use SERIAL or BIGSERIAL to increment them automatically.

[high geyser]

thanks!

You can probably have create a function for that which will generate you the IDs. Alternatively, you can use SERIAL or BIGSERIAL to increment them automatically.
@proven arrow can I set the minimum value

like it should be atleast six digits

can u also pls give me an example pls?

[naive sandal]

What would be the fastest way for text search in sqlite?

[high geyser]

ORDER BY and LIKE?

[proven arrow]

You can alter sequence to start with a value, from the docs. https://www.postgresql.org/docs/9.1/sql-altersequence.html
Ive never done it myself, so you would have to try.

[high geyser]

thanks

[proven arrow]

But its probably something like, ALTER SEQUENCE name RESTART WITH number;

[high geyser]

how can I create a sequence btw

is it minvalue

    ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]```

https://www.postgresql.org/docs/9.5/sql-createsequence.html

thanks I found this

thanks a lot!

[glass gorge]

hello, I'm looking at the pymysql documentation

I'm trying to update some values in my table, but the query structure is a bit confusing

update_sql = "UPDATE users SET password_hash = %s where email =%s"
                    cursor.execute(update_sql, (new_unhashed_password,), (new_pw_hash,))```

ahhhh clearly my syntax is off

but the pymysql documentation is so sparse

ive seen days to handle the values that you're trying to update

[proven arrow]

Provide 1 tuple with the args

cursor.execute(update_sql, (new_unhashed_password, new_pw_hash))

[glass gorge]

ahh ok

ill try

should I be looking at mysql docs

or pymysql?

[proven arrow]

pymysql docs to interact with the db from python.

And use MySQL for database/sql reference

[glass gorge]

ok thx

syntax worked

[deep dove]

hey can someone tell me how am i suppose to edit an existing table in mysql workbench? I'm a mac user

[glass gorge]

am i supposed to close the db connection every time I make a query or something?

[proven arrow]

Well, that would be a waste of resources, and end up being an expensive operation. You can rather keep a single connection across your application.

[glass gorge]

yeah that was what i thought

just trying to debug

it seems it isn't updating the table

even after i use connection.commit()

so i thought maybe i need to close the connection afterwards as well.

[glass gorge]

that's weird, it didn't keep the changes to the db until i restarted my script

[grizzled dagger]

can anyone tell me how I can find rank/index of a row.? with or without where clause

[bronze escarp]

Hello, I try to connect aiohttp and aiopg without using sqlalchemy, could you help me ? Here is my code and it throws an error:

"RuntimeWarning: coroutine 'main' was never awaited main()
RuntimeWarning: Enable tracemalloc to get the object allocation traceback"

from aiohttp import web
import aiopg
import routes


async def main():
    dsn = 'dbname=test user=user1 password=123 host=127.0.0.1'

    async with aiopg.create_pool(dsn) as pool:
        app = web.Application()
        app["db_pool"] = pool

        routes.setup_routes(app)

        web.run_app(app, port=80, host='127.0.0.1')

main()

[mortal nymph]

u need to run it with asyncio

asyncio.run(main())

because its an asynchronous function

@bronze escarp

[bronze escarp]

@mortal nymph thanks, I'll try

[eternal raptor]

https://hasteb.in/vofoxugo.py

I have server, database, tables, columns

[mystic cloud]

So the .execute() in asyncpg.Pool does an .acquire() if i do .acquire() before running the .execute() will that get 2 connections or just one?

[proven arrow]

pool.acquire() will get single connection from the pool

[proven arrow]

@eternal raptor It seems your issue is because at the time of creating your table you must have double quoted the column name like "UserID"

So when you make the query you will also have to do this

[eternal raptor]

I don't understand...

@proven arrow

[proven arrow]

Yeah as i said, you used the double quotes for your column name " " when making the query

You did something like:

CREATE TABLE users_general ("UserID" BIGINT ...)```
When instead you should have done:
```py
CREATE TABLE users_general (UserID BIGINT ...)```

So either you can change the table name, and remember this for the future or in your query add the "" to your column name.

[eternal raptor]

now will be ok?

[proven arrow]

Do you understand the mistake you made from what i said?

[eternal raptor]

but tables was created without " "

earlier i created tables in this option

[proven arrow]

I was referring to column name, same as your error.

[eternal raptor]

sorry, but my Engkish is not so good...

I'm from Poland

aaa in query]

not in Server

so: sql1 = "UPDATE users_general SET eco_ilosc = (eco_ilosc + $1) WHERE "UserID" = $2 AND "GuildID" = $3 "

@proven arrow

yes?

or sql1 = "UPDATE "users_general" SET "eco_ilosc" = ("eco_ilosc" + $1) WHERE "UserID" = $2 AND "GuildID" = $3

[proven arrow]

Yeah but you cant have quotes like that. That will give you syntax error. Use single quotes for the query string and double quotes for the column name.

[eternal raptor]

sql1 = 'UPDATE users_general SET eco_ilosc = (eco_ilosc + $1) WHERE "UserID" = $2 AND "GuildID" = $3 '

this ok?

[proven arrow]

Try it

[eternal raptor]

?tag tias

?rtfm tias

!tag tias

/tag tias

oops.

not this server 😄

output was be: try it and see

" " for column names, not table names ?

[proven arrow]

yeah

[somber isle]

does anyone know if sqlite3 can store objects in a table instead of strings, ints, etc

nvm i found it its a yes/no

https://stackoverflow.com/questions/2047814/is-it-possible-to-store-python-class-objects-in-sqlite

[ember marsh]

@somber isle You can def store serialized Python objects in a DB. This wouldn't be very useful unless you're looking them up a key though, since you lose a lot of the DB features.

They'd just be dumb blobs.

[stark olive]

does sqlalchemy have something that does close to "insert if not exists"?

[ember marsh]

Ah, the upsert?

@stark olive are you using the ORM or declarative?

[stark olive]

ORM

@ember marsh

i am putting together a quickstart quick for sqlalchemy in a github repo for myself to reference, and so far, i have this: ```python
def run_insert_into_table():
for username in _usernames:
if not session.query(exists().where(User.username == username)).scalar():
session.add(User(username=username))

[ember marsh]

I think session add supports that, according to my stackover sleuthing.

[stark olive]

well there is this in the docs, might be what you are refering to Repeated calls to ``add()`` will be ignored

seems i will just need to mess around with it some and see

[ember marsh]

Hmm, I guess if you add it with the same object it will be?

b/c it doesn't do anything until you flush the session

[stark olive]

the complication for my usage of it usually is that i use a auto-increment int as the primary key, so even of there is functionality built in to do a upsert, it probably would not work with a auto incr primary key

i think a exists() scalar is probably my base bet

[torn sphinx]

Hello, has anyone worked with MongoDB before, here?

I need help as I am new to it

[burnt turret]

What's your question?

[torn sphinx]

Is it possible to translate BSON to JSON so i can recluster it?

@burnt turret

[boreal crag]

hi are any1 know mongodb

i have a problem about this

{'user_id':751751736875548682, 'server_id':751755633128243291}

is not in database

basically it runs else code

who can solve it

[modern mulch]

I think it's running code in the if? Do you want it to run code in else?

[torn sphinx]

why is everything in enchantment table language