torn sphinx Aug 17, 2020, 7:21 PM

#

it would be the buy

#

not pass

#

will that work

scarlet bluff Aug 17, 2020, 7:26 PM

#

hey
so how do i use cookies in general ?
sorry but i am new to web-development
and i heard i can store the user info from discord in cookies so i can define that this info belongs to this user

torn sphinx Aug 17, 2020, 7:26 PM

#

@torn sphinx a csv file is a "stream" of rows, you cant easily insert or delete in the middle
@harsh pulsar is there anyway to do it?

#

@client.command()
async def test(ctx):
    id = ctx.message.author.id
    conn = await aiosqlite.connect('money.db')
    cursor = await conn.execute("SELECT cash FROM money WHERE id=?", (id,))
    records = await cursor.fetchone()
    print("id: ", id)
    print("Cash: ", records[0])
    if id in records[0] < 10:
        callablecash = records[0]
        callablecash - 10
        await conn.execute("UPDATE money SET cash = cash - ? WHERE id=?", (callablecash, id))
        await ctx.send("TEST")
        await conn.commit()    
    else:
        await ctx.send("Cannot not buy")
``` this is my buy command 

error:
`discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: argument of type 'int' is not iterable`

normal igloo Aug 17, 2020, 8:04 PM

#

if id in records[0] < 10

#

explain to me what this is doing

shadow iris Aug 17, 2020, 8:39 PM

#

welllllll

#

lol, i think it's saying if some numeber "id" , i'm guess in a list "records" , the first number is less than 10.. than do something.

scenic fern Aug 17, 2020, 10:07 PM

#

cur.execute("SELECT * FROM besitary_of_demons WHERE Name = " + self.framename)

is there a way i can surround self.framename in quotes.

i figured when i was testing this in the actual database to get the exact row i want to display i have to surround the very last thing in quotes. but i cant figure out how to do that in python.

warped frigate Aug 17, 2020, 10:10 PM

#

are you using psycopg2?

scenic fern Aug 17, 2020, 10:10 PM

#

yes

warped frigate Aug 17, 2020, 10:10 PM

#

the proper syntax for psycopg2 would be cur.execute("SELECT * FROM besitary_of_demons WHERE Name = %s", [self.framename]) iirc

#

i just started using psycopg2 and postgres about two weeks ago, and I was confused at first about this

#

you may be able to do self.framename without the list brackets, but i'm pretty sure you need the list brackets if you were to put two or more %ss in the query

harsh pulsar Aug 17, 2020, 10:28 PM

#

this is called a parameterized query and its a lot less mysterious than that

#

the "brackets" indicate that you're using a list

#

a list is a collection of things

#

like... a list, on a piece of paper

#

a shopping list, etc.

#

each %s is a placeholder

#

the database engine then takes each thing in the list, correctly/safely quotes it, and then fills it in where the placeholder is

#

the first arument to execute is the query, the 2nd argument is the list of parameters

#

the search terms here are "parameterized query", and this is part of python's "DBAPI specification" which is defined in PEP 249

#

!pep 249

delicate fieldBOT Aug 17, 2020, 10:30 PM

#

**PEP 249 - Python Database API Specification v2.0**

Link

Status

Final

Type

Informational

harsh pulsar Aug 17, 2020, 10:30 PM

#

@warped frigate @scenic fern see above ^

scenic fern Aug 17, 2020, 10:33 PM

#

thanks

tropic fable Aug 17, 2020, 10:57 PM

#

hello everyone

idle sand Aug 18, 2020, 12:07 AM

#

hey, having issues with sqlite3

#

I can see the data in the database, but python can't for some reason

#

#help-croissant

torn sphinx Aug 18, 2020, 12:38 AM

#

@idle sand what's up?

idle sand Aug 18, 2020, 12:40 AM

#

@torn sphinx i outlined the problem in #help-croissant, but here it is again

#

I've managed to write to the database and I can see the data there using a browser, but when I do cur.fetchone() or fetchall() it returns None/[]

#

# Load database
# Store teams
# Have a function to calculate MMR and update in database

import sqlite3


def initialise_team_file(filepath):
    conn = sqlite3.connect(filepath)
    conn.execute('''CREATE TABLE IF NOT EXISTS teams (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    mu REAL NOT NULL,
    sigma REAL NOT NULL
);
''')


def load_team_file(filepath):
    conn = sqlite3.connect(filepath, isolation_level=None)
    return conn


def add_new_team(name, conn):
    try:
        conn.execute('''INSERT INTO teams (name, mu, sigma)
    VALUES(?,?,?);
    ''', (name, 25, 8.33))
        conn.commit()
    except sqlite3.IntegrityError:
        print("Team with that name already exists")


def get_team_by_name(name, conn):
    cur = conn.cursor()
    #conn.execute(f'''SELECT * FROM teams WHERE name=?''', (name,))
    conn.execute(r'''SELECT * FROM teams''')
    team = cur.fetchall()
    return team


def main():
    initialise_team_file("teams.db")
    conn = load_team_file("teams.db")
    add_new_team("paradox", conn)
    test = get_team_by_name("paradox", conn)
    conn.close()
    print(test)

main()

#

here's the record written in the database

#

📎 unknown.png

#

which i can get via the query

#

but then the result of running the python script is

#

📎 unknown.png

#

okay, fixed it by changing

#

def get_team_by_name(name, conn):
    cur = conn.cursor()
    #conn.execute(f'''SELECT * FROM teams WHERE name=?''', (name,))
    conn.execute(r'''SELECT * FROM teams''')
    team = cur.fetchall()
    return team

#

to

#

def get_team_by_name(name, conn):
    #conn.execute(f'''SELECT * FROM teams WHERE name=?''', (name,))
    cur = conn.execute(r'''SELECT * FROM teams''')
    team = cur.fetchall()
    return team

#

I don't understand why former doesn't work, everything i can see suggests it should

#

https://stackoverflow.com/questions/52815376/how-to-fetch-data-from-sqlite-using-python

Stack Overflow

How to fetch data from sqlite using python?

I am using this sample sqlite database and my code is

import sqlite3

conn=sqlite3.connect('chinook.db')
conn.execute("SELECT * FROM tracks")
rows= conn.cursor().fetchall()

for row in rows:
...

#

ah, i should have done cursor.execute

#

not conn.execute

sly cedar Aug 18, 2020, 5:09 AM

#

Hey peeps; im struggling with an SQLAlchemy query and looking for some guidance.
Basically, I have a users (User) table, and companies (Company) table, with a many-to-one relationship (users->company). I want to write a query that gets all users within a list of companies.
I tried: User.query.filter(User.company.in_(u.managed_companies)).all()
(Note: u.managed_companies is a list of companies)
However, I got a NotImplementedError. I currently iterate through the managed_companies list, and collect all the users, but am looking for a more efficient method

#

oh got it: User.query.filter(User.company_id.in_(list_of_company_ids)).all()

whole surge Aug 18, 2020, 5:38 AM

#

im making a plagiarism checker for my final year .Like its a digital library , the user uplloads any text file ,system compares it with exisitng ones.
for document similarity lll use python nltk.
tf-idf and cos similarity, but i have no idea about the database, what would be the optimal db ,any suggestions?

tepid cradle Aug 18, 2020, 7:52 AM

#

If all the computation is going to be done in Python, then you can store it in any database. Simply storing text files will also work. You can just load all the text files when needed. However, if you do want to store in database, you can use sqlite as it is the easiest to get started with. There is absolutely no setup required. You can just import the sqlite3 library and it will create the database automatically on connecting to the Db for the first time

whole surge Aug 18, 2020, 8:05 AM

#

okay thx ll check it out

left scaffold Aug 18, 2020, 8:50 AM

#

Hi I'm new to databases but just want to know are my INSERT commands always in "" ?

tepid cradle Aug 18, 2020, 8:57 AM

#

What?

left scaffold Aug 18, 2020, 9:07 AM

#

nvm

torn sphinx Aug 18, 2020, 11:50 AM

#


@client.command()
async def test(ctx):
    id = ctx.message.author.id
    conn = await aiosqlite.connect('money.db')
    cursor = await conn.execute("SELECT cash FROM money WHERE id=?", (id,))
    records = await cursor.fetchone()
    print("id: ", id)
    print("Cash: ", records[0])
    callablecash = records[0]
    if callablecash > 10:
        callablecash - 10
        await conn.execute("UPDATE money SET cash = cash - ? WHERE id=?", (callablecash, id))
        await ctx.send("TEST")
        await conn.commit()    
    else:
        await ctx.send("Cannot not buy")
``` issuse it drains all of your cash i only want it to drain 10 or what `callablecash - 10`

#

it - all of you cash it should only do -10

proven arrow Aug 18, 2020, 12:40 PM

#

Because you are deducting by the same value that is returned by the DB

#

x = 100
x-100
print(x)

This will output 100, and this is what you are doing since you don’t update the variable, and end up using the one returned by the DB.

harsh pulsar Aug 18, 2020, 2:06 PM

#

@torn sphinx you have callablecash - 10 on a separate line

#

python doesnt work like that

#

did you mean to write callablecash -= 10 and then do SET cash = ??

torn sphinx Aug 18, 2020, 2:07 PM

#

i just needed to fetch the users cash then - it by 10

#

so i think

#

..

harsh pulsar Aug 18, 2020, 2:16 PM

#

callablecash - 10 by itself doesnt do anything

#

the expression callablecash - 10 is evaluated, then discarded

#

    if callablecash > 10:
        await conn.execute("UPDATE money SET cash = cash - 10 WHERE id=?", (id,))

you could just write this if you really wanted to

torn sphinx Aug 18, 2020, 2:18 PM

#

@client.command()
async def test(ctx):
    id = ctx.message.author.id
    conn = await aiosqlite.connect('money.db')
    cursor = await conn.execute("SELECT cash FROM money WHERE id=?", (id,))
    records = await cursor.fetchone()
    print("id: ", id)
    print("Cash: ", records[0])
    callablecash = records[0]
    if callablecash > 10:
        callablecash -= 10
        await conn.execute("UPDATE money SET cash = ? WHERE id=?", (callablecash, id))
        await ctx.send("TEST")
        await conn.commit()    
    else:
        await ctx.send("Cannot not buy")
``` this works fine

spark ravine Aug 18, 2020, 2:40 PM

#

is a SELECT attr FROM blah WHERE id=lots of IDs more inefficient than just selecting one ID (without taking latency/time between the db and server into consideration)?

tepid cradle Aug 18, 2020, 2:59 PM

#

You mean selecting one ID at a time when you need many of them?

#

@spark ravine

spark ravine Aug 18, 2020, 3:00 PM

#

Na I meant id=1, id=2, id=3 in one SQL query.

tepid cradle Aug 18, 2020, 3:00 PM

#

No, it doesn't matter. The syntax for that is select attr from table where id in (1, 2, 3)

spark ravine Aug 18, 2020, 3:01 PM

#

👍

tepid cradle Aug 18, 2020, 3:03 PM

#

This is the Execution plan for IN query

📎 unknown.png

#

And this is the execution plan for all 5 IDs written one by one

📎 unknown.png

#

As you can see, the cost is same in both @spark ravine

spark ravine Aug 18, 2020, 3:07 PM

#

I see, thank you! I'm implementing a very... well something that could probably crash the server. I have a table that has a self-referencing FK. I just worried that the execution cost would be huge against the database once the list of IDs get to the hundreds and sometimes thousands.

tepid cradle Aug 18, 2020, 3:08 PM

#

I use DBeaver to view the query plan as shown above. If you work with Databases often, I would recommend this software. It's quite feature rich

harsh pulsar Aug 18, 2020, 3:19 PM

#

@spark ravine would it make more sense to do some kind of join

#

but yeah +1 for reading query plans

spark ravine Aug 18, 2020, 3:20 PM

#

Definitely +1. Currently using Django, so I get something similar with Postgres explain+analyze (flag). Will check out DBeaver; never heard, but it sounds like it can help with this data-driven application.

#

Mmm I don't think it would be in my case. My foreign key is pointed towards the same table (a self FK?).

tepid cradle Aug 18, 2020, 3:26 PM

#

I tried explain analyse as well. And it seems like an IN query will actually be more efficient

ancient abyss Aug 18, 2020, 4:08 PM

#


    cursor.execute("SELECT items FROM bought WHERE user_id = %s AND guild_id = %s AND items = %s",
                   (user_id, guild_id, item))

    account = cursor.fetchone()
    if cash is None:
        await ctx.send("You don't have a bankacc, how are you supposed to even have items?")
    if account is not None:
        result = account[0]

        if item in result:
            if item == 'tomato':
                await ctx.send('You ate a tomato. Yum')
        else:
            await ctx.send("You don't have this item")

#

Why does the else not send?

#

also, item is a string param for the main command

#

@client.command()
async def use(ctx, *, item: str):

tepid cradle Aug 18, 2020, 4:24 PM

#

where is item defined before this line if item in result:

wanton tulip Aug 18, 2020, 6:51 PM

#

Remove the invite

torn sphinx Aug 18, 2020, 6:52 PM

#

my pc is lagging remove the message

#

please i cant my pc is lagging

wanton tulip Aug 18, 2020, 6:52 PM

#

What message

torn sphinx Aug 18, 2020, 6:53 PM

#

the invite

wanton tulip Aug 18, 2020, 6:53 PM

#

@client.command()
@commands.cooldown(1, 5, commands.BucketType.user)
async def buy_advert(ctx, *, advert):
    id = ctx.message.author.id
    item = 'Advert'
    cost = '200'
    conn = await aiosqlite.connect('money.db')
    cursor = await conn.execute("SELECT cash FROM money WHERE id=?", (id,))
    records = await cursor.fetchone()
    print("id: ", id)
    print("Cash: ", records[0])
    callablecash = records[0]
    if callablecash > 200:
        callablecash -= 200
        await conn.execute("UPDATE money SET cash = ? WHERE id=?", (callablecash, id))
        await ctx.send(f"You Bought {item} for: {cost} your advert has been hosted in <server invite>")
        advert = client.get_channel(745348904421294110)
        await advert.send(f".{advert}")
        await conn.commit()    
    else:
        await ctx.send(f"Cannot buy `{item}`")

from @torn sphinx

torn sphinx Aug 18, 2020, 6:56 PM

#

^^^^^ Why dosent there advert not send to the channel i get no erro

#

nvm

#

Consider using some sort of lock when decrementing the balance

brave bridge Aug 18, 2020, 7:37 PM

#

I have 3 (relevant) tables: posts, comments, users. Each post and each comment have an author_uid which referes to the author in users. Each comment has a post_uid to indicate which post it's attached to. Is there a way to fetch all information about a post in one query?
The only way I can think of is making a giant join, but it will repeat the post metadata (post.name & post.contents) and the post author data for each comment.

SELECT
    p.uid AS post_uid, p.name AS post_name, p.content AS post_text,
    up.uid AS post_author_uid, up.name AS post_author_name, up.is_admin AS post_author_is_admin,
    c.uid AS comment_uid, c.content AS comment_text,
    uc.uid AS comment_author_uid, uc.name AS comment_author_name, uc.is_admin AS comment_author_is_admin
FROM posts p
LEFT JOIN users up ON p.author_uid = up.uid
FULL OUTER JOIN comments c ON c.post_uid = p.uid
LEFT JOIN users uc ON c.author_uid = uc.uid
WHERE p.uid = 1;

remote burrow Aug 18, 2020, 8:32 PM

#

Hey guys. I've been using Mongo's Free Atlas Clusters for maintaining a database for a small project, but I've been wanting to expand. Trouble is I'm not sure what vps specs are suitable for running a database and scraping data at the same time.

I plan on running a Mongo Database with roughly 1gb growth every month. For updating and accessing, I'm multithreading locally with roughly 20-30 threads at once. At the same time I'll also be scraping data off yahoo finance in 5 minute intervals.

I was thinking a VPS with 16gb ram 60gb storage 3 core vcpu for the price of $16/m (location shouldn't matter too much right?) would be a nice budget choice for a few months depending on how long I plan on running my project for. Only issue I was thinking was that CPU could be an issue if I'm bottlenecked by database read/writes + while serializing the data I'm getting from yahoo finance.

Any thoughts/opinions would be much appreciated!

brazen charm Aug 18, 2020, 8:33 PM

#

@remote burrow https://contabo.com/?show=vps

Cheap Windows & Linux VPS hosting on high-end servers - Contabo.com

Powerful VPS hosting – SSD storage, snapshots, virtual machines equipped with brand new hardware, 100 mbit/s unlimited traffic, starting at just €3,99 EUR / month.

#

Personally my favourite VPS provider for the pricing and their support

#

the None SSD versions are a little week but the price is such that you can just get a SSD one which is more powerful

#

for the same price you'll get 8 cores, 800GB storage and 30GB ram

remote burrow Aug 18, 2020, 8:40 PM

#

thanks for the link! with this i don't think I'll have any issues trying to micromanage my resources anymore lmao
i think I have another project on the side that I was working on, but for my original question, what vps specs do you think would be
most appropriate for my project?

brazen charm Aug 18, 2020, 8:42 PM

#

Ehh You wouldnt need much, more cores would let you load balance better across cores tho

inland mango Aug 18, 2020, 9:47 PM

#

Hi, I'm trying to do a lot of batch processing from an api to a sql table. Would this be a job for multi threading or multiprocessing?

drifting folio Aug 18, 2020, 10:11 PM

#

depending on the api possibly. multithreading can help if you need to make a ton of requests (where the majority of your code is I/O bound). but loading into a sql table you may be better of running a bulk insert after all the data has been pulled, as many DBMSs are optimized for that style of insert.

#

so that answer really depends on the api (where multitheading may or may not help), the processing you need to do on the data first (where multiprocessing may or may not help), and the db you're using

dim thistle Aug 19, 2020, 12:18 AM

#

hey all! tl;dr would anyone be able to point me to a good example a discord bot using sqlalchemy with a postgresql database (or similar)?

I've been storing data in a json up until now, but because I host my bot on heroku, it keeps getting reset which is not good for the functionality of my bot. So, I'm attempting to migrate to a postgresql database. However, while I do have some idea of the libraries I want to use, I'm not sure where to start beyond that. So, any help would be very much appreciated!

restive stone Aug 19, 2020, 1:19 AM

#

Code ```py
@commands.Cog.listener(name='on_message')
async def mention_prefix(self, message, guild=None):
if message.content.startswith('@cloud mortar'):
guild = message.guild.id
prefix = self.client.cur.execute(f"SELECT prefix FROM prefix WHERE guild={guild}").fetchone()[0]
if prefix is None:
self.client.cur.execute(f"INSERT INTO prefix(guild, prefix) VALUES({guild}, '-')")
self.client.con.commit()
embedd = discord.Embed(
colour=0x0ffff,
title=f'The Prefix For This Server Is -'
)
await message.channel.send(embed=embedd)
return
if message.content.startswith('@cloud mortar'):
guild = message.guild.id
prefix = self.client.cur.execute(f"SELECT prefix FROM prefix WHERE guild={guild}").fetchone()[0]
embed = discord.Embed(
colour=0x0ffff,
title=f'The Prefix For This Server Is {prefix}'
)
await message.channel.send(embed=embed)
return
else:
prefix = self.client.cur.execute(f"SELECT prefix FROM prefix WHERE guild={guild}").fetchone()[0]
embed = discord.Embed(
colour=0x0ffff,
title=f'The Prefix For This Server Is {prefix}'
)
await message.channel.send(embed=embed)
return

This is a Discord.py bot. MySQLite3

#

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Users\Admin User\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\Admin User\Breeze Folders\Breeze\cogs\Help.py", line 73, in mention_prefix
    prefix = self.client.cur.execute(f"SELECT prefix FROM prefix WHERE guild={guild}").fetchone()[0]
sqlite3.OperationalError: no such column: None

#

no such column: None what does this error mean? And what are the steps to fixing it?

torn sphinx Aug 19, 2020, 1:43 AM

#

so im using AioSQLITE for my pc i moved to linux in did
pip install aiosqlite and it said it installed but im getting a Unable to import aiosqlite error im using raspberry pi 4 i was using windows

dim thistle Aug 19, 2020, 3:26 AM

#

when using asyncpg, what does a NULL value from the table become in fetchrow?

dense kindle Aug 19, 2020, 3:35 AM

#

i sure hope it would be None

dim thistle Aug 19, 2020, 4:00 AM

#

thanks hahah I shall assume it's None for now

torn sphinx Aug 19, 2020, 4:36 AM

#

@torn sphinx try installing sqlite3 first

tepid cradle Aug 19, 2020, 4:59 AM

#

thanks hahah I shall assume it's None for now
@dim thistle why don't you just run a query and check, what is the need to assume

dim thistle Aug 19, 2020, 5:00 AM

#

I would... but admittedly, I don't have the database set up just yet

#

I'm very very new to all this database stuff so I'm not really comfortable with it yet--going to attempt creating a database right now though!

dense kindle Aug 19, 2020, 5:03 AM

#

💯

tepid cradle Aug 19, 2020, 5:41 AM

#

@dim thistle If you are going to just set up the database, I'd recommend first setting it up and using it with a database software like pgadmin or mysql workbench or Dbeaver. That will get you some comfort with the Db. Directly starting work with a Db from inside python is not the best way to learn about Dbs imo

eternal raptor Aug 19, 2020, 8:32 AM

#

   @commands.Cog.listener()
    async def on_guild_join(self, guild):
        gildia = guild.id
        async with aiosqlite.connect("zonnerty.db") as db:
            await db.execute("CREATE TABLE bany_? (UserID NUMERIC NOT NULL UNIQUE, reason REAL NOT NULL, PRIMARY KEY(UserID))", (gildia,))
            await db.commit()
            print("ok 1.1")
            await db.execute("CREATE TABLE ekonomia_? (UserID NUMERIC NOT NULL UNIQUE, Ilosc INTEGER NOT NULL, Znak TEXT NOT NULL, PRIMARY KEY(UserID,Ilosc,Znak))", (gildia,))
            await db.commit()
            print("ok 1.2")
            await db.execute("INSERT INTO free_prefix (ID) VALUES (?)", (gildia,))
            await db.commit()
            print("ok 1.3")
            await db.execute("INSERT INTO free_waluta_char (ID) VALUES (?)", (gildia,))
            await db.commit()
            print("ok 1.4")
            print(gildia)

eternal raptor Aug 19, 2020, 8:48 AM

#

returning exception near "?": syntax error
Traceback (most recent call last):
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\aiosqlite\core.py", line 171, in run
    result = function()
sqlite3.OperationalError: near "?": syntax error
Ignoring exception in on_guild_join
Traceback (most recent call last):
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\user\Desktop\Zonnerty\cogs\bot_jal.py", line 14, in on_guild_join
    await db.execute("CREATE TABLE bany_? (UserID NUMERIC NOT NULL UNIQUE, reason REAL NOT NULL, PRIMARY KEY(UserID))", (gildia,))
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\aiosqlite\core.py", line 236, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\aiosqlite\core.py", line 185, in _execute
    return await future
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\aiosqlite\core.py", line 171, in run
    result = function()
sqlite3.OperationalError: near "?": syntax error

Could you help me, plz?

eternal raptor Aug 19, 2020, 9:13 AM

#

plz

eternal raptor Aug 19, 2020, 9:29 AM

#

problem solved.

tepid cradle Aug 19, 2020, 11:16 AM

#

@unborn pawn don't think this is the right channel for your question

unborn pawn Aug 19, 2020, 11:17 AM

#

isnt SQLAlchemy database related?

tepid cradle Aug 19, 2020, 11:17 AM

#

Oh, I thought the listener was a flask feature. My bad

unborn pawn Aug 19, 2020, 11:17 AM

#

🙂

rain star Aug 19, 2020, 11:50 AM

#

    conn = await aiosqlite.connect("database/prefix.db")
    c = await conn.execute(f"SELECT * FROM Prefix WHERE guild_id = ?",[ctx.guild.id])
    prefix = await c.fetchone()
    await conn.close()
    return prefix```

 
```async def formention(message):
    conn = await aiosqlite.connect("database/prefix.db")
    c = await conn.execute(f"SELECT * FROM Prefix WHERE guild_id =?"[message.guild.id])
    prefix = await c.fetchone()
    await conn.close()
    return prefix
 how can i connect to the database only one time and use it in the bot functioons``` i want to connect to the database at one time use it in these to functions

tepid cradle Aug 19, 2020, 11:54 AM

#

You can create the connection outside the function, it will work

#

You could, if you want, explicitly pass the connection object as a parameter as well

rain star Aug 19, 2020, 11:56 AM

#

like

#

con = await aiosqlite.connect("database/prefix.db")

#

or async with aiosqlite.connect("database/prefix.db") as conn

reef crag Aug 19, 2020, 12:06 PM

#

hoping someone can take a look at these errors and advise on how to correct please. Much appreciated.
NotADirectoryError Traceback (most recent call last)
<ipython-input-7-1cb0e413c5bf> in <module>
48
49 # Main
---> 50 generateURL("y2019", 5)

<ipython-input-7-1cb0e413c5bf> in generateURL(startYear, numYears)
32 directory = "all_stats_html/%s" % stat.replace('/', ' ') #need to replace to avoid
33 if not os.path.exists(directory):
---> 34 os.makedirs(directory)
35 years = []
36 for option in html.find("select", class_="statistics-details-select").find_all("option"):

~\AppData\Local\Programs\Python\Python38-32\lib\os.py in makedirs(name, mode, exist_ok)
221 return
222 try:
--> 223 mkdir(name, mode)
224 except OSError:
225 # Cannot rely on checking for EEXIST, since the operating system

NotADirectoryError: [WinError 267] The directory name is invalid: 'all_stats_html/SG: Tee-to-Green'

whole gale Aug 19, 2020, 12:30 PM

#

Guys anyone tell me how to print a SQL table in a sheet, I have dynamic no of tables in SQL server and it has to be printed as one table per A4 sheet

is there any way to finish it ?
using python reports

tepid cradle Aug 19, 2020, 12:35 PM

#

@reef crag use a codeblock to post error messages and code, and always show the part of the code which is generating the error, very difficult to tell the reason without seeing the code.

#

@whole gale just look up how to fetch list of tables in MS SQL Server. Usually each Db has a different way of storing this information. After that, you can just fetch the data using a for loop

whole gale Aug 19, 2020, 12:40 PM

#

@tepid cradle yes, but how to design a report in A4 sheet for printing in python

#

that is to create report

tepid cradle Aug 19, 2020, 12:42 PM

#

No idea about that. I was answering for the Db part

whole gale Aug 19, 2020, 12:45 PM

#

thanks bro

torn sphinx Aug 19, 2020, 1:28 PM

#

@torn sphinx dident work

leaden kestrel Aug 19, 2020, 1:40 PM

#

hello ! everyone

#

i m new to learn programming can anyone help me in this!

harsh pulsar Aug 19, 2020, 1:54 PM

#

!ask @leaden kestrel

#

!ask

#

oh bot is dead?

#

anyway... we have a guide to asking good questions

#

just ask your question, don't "ask to ask"
post any code you already wrote, and post full error messages

rose parrot Aug 19, 2020, 6:03 PM

#

I need advice on Modeling using SQLAlchemy
I'm trying to build a Real Estate Property (Realty) class and link it to an address.

It'll be useful if I could later filter Realties by Country/State/City/District to display on my front-end stuff, so users can apply filters to find what they want and I also want to retrieve the Country/State/City/District a certain Realty belongs to, as I want to also display it on that Realty Landing Page.

Relationship-wise: Realty/RealtyAddress and PostalCode/RealtyAddressis One-to-One, Country-State/State-City/City-District is One to Many.

PostalCode may be useful to validate administrative division data or for auto-filling purposes.

Not sure where I would bind the Administrative Levels though (to Realty or to RealtyAddress)

Here's what I've got so far: https://pastebin.com/cBrbRsPL

I'm still not sure if that will work out and if there's any better/simpler way to achieve the functionality that I want as I'm not directly creating a relationship between the RealtyAddress and the Administrative Divisions (state, city, district...).

Should I create a relationship between each class using Realty as the parent class?
Or just link from top to bottom? Country-States, States-City, City-District?
What I'd like to query in the future is stuff like:

# All realties in a certain country
Realty.query.filter(Realty.country.name == 'United States').all()
# Or
Realty.query.filter(Realty.address.country.name == 'United States').all()
# All realties within a certain city
Realty.query.filter(Realty.city.name == 'New York City').all()
# Or
Realty.query.filter(Realty.address.city == 'New York City').all()

# Or if I have a certain realty object
some_realty = Realty.query.get(1)

# One of the two options bellow, whichever is easier to implement and maintain, if that doesn't screw performance
some_realty_country = some_realty.country.name # ?
some_realty_country = some_realty.address.country.name # ?

Appreciate any help on that

Pastebin

Realty Model - Pastebin.com

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

pure cypress Aug 19, 2020, 6:41 PM

#

In my opinion, it is unnecessary and not very beneficial to split up the address into multiple tables.

tepid cradle Aug 19, 2020, 6:45 PM

#

I agree with mark. You can have a few master tables for cities , states, etc. But keep the address in one table

#

You can do all your filtering in one table as well

rose parrot Aug 19, 2020, 7:04 PM

#

@pure cypress how can I avoid people writing different names for the same city though? EG. : NYC and New York city. Should I do manual filtering?
My idea was to build a drop-down so people can pick from a pre-defined list to avoid duplicates

#

As soon a person chooses a country from a drop-down it will only display states from that country, then a state would filter which cities are within that state and so on

somber matrix Aug 19, 2020, 7:05 PM

#

How can i find the closest number to an input within a range in an SQLAlchemy database?

#

out of a column of float values just @ me if u have any advice

torn sphinx Aug 19, 2020, 7:16 PM

#

@client.command()
async def pay(ctx, member : discord.Member, num : int):
    conn = await aiosqlite.connect('money.db')
    author = ctx.message.author
    id = member.id
    conn = await aiosqlite.connect('money.db')
    cursor = await conn.execute("SELECT cash FROM money WHERE id=?", (id,))
    records = await cursor.fetchone()
    print("id: ", id)
    print("Cash: ", records[0])
    callablecash = records[0]
    if callablecash > num:
        callablecash -= num
        await conn.execute("UPDATE money SET cash = ? WHERE id=?", (callablecash, id))
        await asyncio.sleep(2)
        await conn.execute("UPDATE money SET cash = cash + ? WHERE id=?", (num, id))
        await ctx.send(f"You gave {author.mention} `{num}$`")
        await conn.commit()
    else:
        await ctx.send("You dont have the money!! :x:")

why dose this not work it should remove num from the authors account and add it to the mentioned users account

pure cypress Aug 19, 2020, 7:27 PM

#

@rose parrot Yeah, that's a good point. It's a bit of a complicated issue. I think what most things do is using some sort of address normalisation library. Sometimes there are technically multiple correct cities to use e.g. using the name of a metropolitan area instead of the city/county inside it. I believe such tools/libraries would fix that, so you can be more sure you're not getting only partial results from a query.

torn sphinx Aug 19, 2020, 7:27 PM

#

YO mark remember me you told me about DB's

pure cypress Aug 19, 2020, 7:30 PM

#

hello

rose parrot Aug 19, 2020, 7:40 PM

#

@somber matrix maybe

# https://www.geeksforgeeks.org/python-find-closest-number-to-k-in-given-list/
def closest(lst, K):    
    return lst[min(range(len(lst)), key = lambda i: abs(lst[i]-K))] 

closest(Numbers.query.all(), K)

somber matrix Aug 19, 2020, 8:00 PM

#

Oh god

#

I barely know what any of that means :0

#

tf is a lambda

#

but thanks 😄

agile scaffold Aug 19, 2020, 8:06 PM

#

lambda is just a function you can type in one line

#

func = lambda x: x + x

def func(x):
  return x + x

#

these do the same thing

thick fog Aug 19, 2020, 9:27 PM

#

Hey I just got into using python for my work, and was wondering what database platform and format should I use to easily study the data

bleak crown Aug 19, 2020, 9:57 PM

#

Hi is it possible to do such a thing like this in sqlite3 library "INSERT INTO the_table() IF NOT EXISTS"?

torn sphinx Aug 19, 2020, 9:58 PM

#

@client.command()
async def start_miner(ctx):
    conn = await aiosqlite.connect('money.db')
    id = ctx.message.author.id
    author = ctx.message.author
    await ctx.send("Starting miner ...")
    await asyncio.sleep(2)
    await ctx.send("Miner started")
    await asyncio.sleep(2)
    await ctx.send("Now mining for money `ile dm you if we hit anything` !")
    ran = random.randint(1, 10)
    for i in range(1, ran):
        await asyncio.sleep(150)
        rancash = random.randint(1,35)
        await conn.execute("UPDATE money SET cash = cash + ? WHERE id=?", (rancash, id))
        await author.send(f"Your miner made `{rancash}$` | {author.mention}")
        print(i)
    else:
        await ctx.send(f"Oh, No your `miner` broke please rerun the `+start_miner` command! | {author.mention}")

why dose this not work there was no error it just dosent save there money

radiant elbow Aug 20, 2020, 12:13 AM

#

you're probably missing a call to commit the transaction

#

await conn.commit() I'm guessing

shrewd linden Aug 20, 2020, 12:14 AM

#

yah because you need to “commit” the changes i guess

torn sphinx Aug 20, 2020, 12:24 AM

#

omegaroll

#

your joking

minor ruin Aug 20, 2020, 12:38 AM

#

no, standard database procedure

torn sphinx Aug 20, 2020, 2:11 AM

#

how do i change column data types in postgreSQL?

discord_statistic=> ALTER TABLE user_status
discord_statistic-> ALTER COLUMN desktop TYPE SMALLINT;
ERROR:  column "desktop" cannot be cast automatically to type smallint
HINT:  You might need to specify "USING desktop::smallint".

discord_statistic=> ALTER TABLE user_status
discord_statistic-> ALTER COLUMN desktop TYPE SMALLINT USING desktop::SMALLINT;
ERROR:  cannot cast type boolean to smallint
LINE 2: ALTER COLUMN desktop TYPE SMALLINT USING desktop::SMALLINT;
                                                        ^

dense kindle Aug 20, 2020, 2:15 AM

#

try this:
alter table user_status alter column desktop using case when desktop = true then 1 else 0 end

#

it's just not sure what true should become, you gotta tell it

torn sphinx Aug 20, 2020, 2:16 AM

#

discord_statistic=> alter table user_status alter column desktop using case when true then 1 else 0 end;
ERROR:  syntax error at or near "using"
LINE 1: alter table user_status alter column desktop using case when...

dense kindle Aug 20, 2020, 2:16 AM

#

:\ maybe that does not work in this context

torn sphinx Aug 20, 2020, 2:17 AM

#

what does that statement do actually

#

the case when true then 1 else 0 end

dense kindle Aug 20, 2020, 2:17 AM

#

ah yes, sorry it may need to be desktop = true?

#

the idea is to return 1 when desktop = true otherwise 0;; since you need a smallint to be returned

torn sphinx Aug 20, 2020, 2:18 AM

#

the table has an empty row

#

so i dont think i would need to do that

dense kindle Aug 20, 2020, 2:22 AM

#

i don't think it matters. i just tested it, that syntax should work if you say when desktop = true

torn sphinx Aug 20, 2020, 2:26 AM

#

actually i just solved it after some digging, apparently it's this

discord_statistic=> ALTER TABLE user_status
discord_statistic-> ALTER COLUMN desktop TYPE SMALLINT
discord_statistic-> USING desktop::integer;

dense kindle Aug 20, 2020, 2:28 AM

#

lol i like how the HINT was completely off

torn sphinx Aug 20, 2020, 2:28 AM

#

yea idk why it gives the wrong hint lol

dense kindle Aug 20, 2020, 2:28 AM

#

for type conversions where you don't have the ability to cast, though, you can use a case statement

torn sphinx Aug 20, 2020, 2:28 AM

#

i see, i'll read about it

empty vortex Aug 20, 2020, 7:15 AM

#

Hey guys !

Can someone give me a good example of using class when connecting to a database using mysqlconnector ? I want to make my code more abstract, not just defining a function in my main ..

#

def set_database(user, password, host, db):
    """
        Permet de se connecter à la base de donnée
        - @user: Utilisateur de la base de données  
        - @password: Mot de passe de l'utilisateur  
        - @host: Serveur Mysql  
        - @db: Nom de la base de donnée
        @return: une connexion
    """
    try:
        config = {
            'user': user,
            'password': password,
            'host': host,
            'database': db,
            'raise_on_warnings': True
        }
        return mysql.connector.connect(**config)

    except mysql.connector.Error as err:
        print(err)

brave bridge Aug 20, 2020, 1:49 PM

#

I don't think making your code more abstract is a good end goal.

vocal moon Aug 20, 2020, 3:13 PM

#

I've just moved house and im unable to connect to my databases I keep getting:

asyncpg.exceptions.InvalidAuthorizationSpecificationError: no pg_hba.conf entry for host "myip", user "postgres", database "rologging", SSL off

tepid cradle Aug 20, 2020, 3:29 PM

#

I'm assuming you changed a router or some connection or something. You must have allowed only a set of IPs and because of the change in IP, you're not able to connect

#

You need to add an entry for your new IP address in pg_hba.conf

rose parrot Aug 20, 2020, 3:56 PM

#

SQLAlchemy modeling question

Suppose I have

from app import db
# [...]
class Environment(db.Model):
  __tablename__ = 'environments'
  id = db.Column(db.Integer, primary_key=True)
  name = db.Column(db.String(25))
  description = db.Column(db.String(256))
  has_multiple = db.Column(db.Boolean, default=False)
  quantity = db.Column(db.Integer, default=1)

And I want to create a relationship between Environment and other classes such as House, Apartment or Building.

Should I create other classes HouseEnvironment, ApartmentEnvironment and BuildingEnvironment, eg:

# [...]
class House(db.Model):
  # [...]
  environments = db.realtionship("HouseEnvironment", back_populates="house")
  # [...]
class HouseEnvironment(db.Model):
  # devires from Environment, but not sure how to do that, but also contains the relationship below:
  house = db.relationship("House", back_populates="environments")
``` or can I do it some other way with just the `Environment` class?

Is that what `backref` is for?

Can I just:
```Python
  class Apartment(db.Model):
    #[...]
    environments = db.Relationship("Environment", backref="belongs_to")
  #[...]
```?
And it will automatically place `belongs_to` to my `Environment` class and I can just repeat the `environments = db.Relationship("Environment", backref="belongs_to")` for House, Building, etc.? So `SomeEnvironment.belongs_to` will be able to return House, Apartment or Building, depending on where that environment is in?

vocal moon Aug 20, 2020, 4:50 PM

#

@tepid cradle i did

#

my ip is the last one

📎 unknown.png

tepid cradle Aug 20, 2020, 4:55 PM

#

@vocal moon did you restart postgres service after changing the configuration?

vocal moon Aug 20, 2020, 4:55 PM

#

oh i forgot

#

thanks

cinder dome Aug 20, 2020, 8:10 PM

#

so Im a noob with sqlite/aiosqlite, and Im having trouble setting things up

The database part of the main file: https://mystb.in/DatesPuertoIslam.swift

cogs/datab/database.py: https://mystb.in/AppreciatedInductionMiscellaneous.python

#

Then getting this error

📎 unknown.png

#

nvm forget that

#

Im just gonna have the create_db function in my main file instead

#

Instead, in here at the conn.execute() how can I include multiple tables?

📎 unknown.png

#

Like a prefix table, how would I add another table in there

rustic surge Aug 20, 2020, 8:22 PM

#

Would it be appropriate to ask someone to write me up a learning path

#

to accomplish a very specific task

velvet girder Aug 20, 2020, 8:43 PM

#

Hello, i have weird problem with my postgres query. I have an fetch function:

        print(table,column_name,name)
        query = f'SELECT {",".join(columns)} FROM {table} WHERE {column_name} = "{name}";'
        print(query)
        result=await self.db.fetchrow(query)
        print(result)
        return #result```
And usage of function:

value=await db.fetch_table_columns("guild_settings","parameter","pref","value")

I get following error:
```asyncpg.exceptions.UndefinedColumnError: column "pref" does not exist

From my understanding of created query: ```SELECT value FROM guild_settings WHERE parameter = "pref";

velvet girder Aug 20, 2020, 9:06 PM

#

Hello, i have weird problem with my postgres query. I have an fetch function:
        print(table,column_name,name)
        query = f'SELECT {",".join(columns)} FROM {table} WHERE {column_name} = "{name}";'
        print(query)
        result=await self.db.fetchrow(query)
        print(result)
        return #result```
And usage of function:
value=await db.fetch_table_columns("guild_settings","parameter","pref","value")
I get following error:
```asyncpg.exceptions.UndefinedColumnError: column "pref" does not exist
From my understanding of created query: ```SELECT value FROM guild_settings WHERE parameter = "pref";

@velvet girder Well, with enough trial/error i found an error, i've used "pref" except of 'pref'. Problem solved

cinder dome Aug 20, 2020, 9:07 PM

#

If anyone replies ping me please #databases message

cinder dome Aug 20, 2020, 10:27 PM

#

Traceback (most recent call last):
  File "C:\Users\1andana\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\aiosqlite\core.py", line 95, in run
    result = function()
sqlite3.Warning: You can only execute one statement at a time.
ERROR:asyncio:Task exception was never retrieved
future: <Task finished name='Task-1' coro=<create_db() done, defined at c:/Users/1andana/Desktop/VSC Discord Bot/bot.py:45> exception=Warning('You can only execute one statement at a time.')>
Traceback (most recent call last):
  File "c:/Users/1andana/Desktop/VSC Discord Bot/bot.py", line 53, in create_db
    await cursor.execute("""
  File "C:\Users\1andana\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\aiosqlite\cursor.py", line 36, in execute
    await self._execute(self._cursor.execute, sql, parameters)
  File "C:\Users\1andana\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\aiosqlite\cursor.py", line 30, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "C:\Users\1andana\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\aiosqlite\core.py", line 109, in _execute
    return await future
  File "C:\Users\1andana\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\aiosqlite\core.py", line 95, in run
    result = function()
sqlite3.Warning: You can only execute one statement at a time.```

Code: https://mystb.in/FunnyRequiringAssists.properties

#

also Im using aiosqlite

#

this is in my main file if that helps too

cinder dome Aug 20, 2020, 11:28 PM

#

Nvm got it

torn sphinx Aug 21, 2020, 12:56 AM

#

could anyone help me with pg8000?

heady galleon Aug 21, 2020, 1:38 AM

#

Anyone here have experience with MongoDB?

#

Basically I would like to search within a database in a column named 'synonyms' in which is listed a variety of alternatives of an item name

#

ie an entry may be 'itemname ItemName item name Item Name ItNm itnm'

#

and if the search has any one of those names it would find the entry. Is this possible?

tepid cradle Aug 21, 2020, 2:53 AM

#

@heady galleon mongodb does not have columns.
Anyway, you can probably use regex to do that.

heady galleon Aug 21, 2020, 3:10 AM

#

sorry, my terminology is wrong, i wasnt sure which word to use

#

i mean like in my excel file from which i make the csv files to populate the database, i have a column named synonyms

#

so each item has an entry type synonym

#

Im not sure how regex works, as Im a total noob to databases, but I'll look it up thanks

#

ok, I've found the relevant info. Thanks gkrou

#

and I guess the word to use was 'field' rather than'column'

tepid cradle Aug 21, 2020, 3:39 AM

#

Yes, field.
And regex is not specific to databases, you can use it anywhere

misty ibex Aug 21, 2020, 3:43 AM

#

So I'm trying to pull data from a DB and upload a csv to S3. The problem I keep running into is that I keep getting memory errors. I'm trying to read the data into a pandas dataframe and then write the csv to a StringIO buffer to use to load to S3. This works if I set a limit on how much data to query (setting for 200k rows works just fine). But when I open it to include all for a day (~6million rows) it (understandbly) fails with the MemoryError. I thought I could use chunksize to help solve this, and it works, but doesn't solve the MemoryError issue, even if I choose chunksize=150k or something smaller than what has worked. Anyone run into issues like this with large datasets and python memory and know how to work around them?

tepid cradle Aug 21, 2020, 3:47 AM

#

@misty ibex what database are you using?

misty ibex Aug 21, 2020, 3:48 AM

#

snowflake

#

I can query just fine it seems, and even get the first 3-4 chunks to process before I hit the memory error (but again I'm sort of expected ~20-30 chunks)

tepid cradle Aug 21, 2020, 3:50 AM

#

I have never used this Db, but a quick Google search tells me that, like other Dbs, it also provides a way to directly export a CSV file without bringing in Pandas in between.
See if this helps https://community.snowflake.com/s/question/0D50Z00008xAUHr/how-to-export-large-amounts-of-data-from-snowflake-to-local

Snowflake Community

Join our community of data professionals to learn, connect, share and innovate together

misty ibex Aug 21, 2020, 3:53 AM

#

hmm.. yeah I've done this for large files manually for snowflake (that allows you to load a large query that you can't download from UI to download, but it requires using their command line tool to actually download the file, so I'll have to see if they have a python workaround for that since this needs to be an automated script

#

actually I might be able to load directly to S3 as the "stage"

limber trail Aug 21, 2020, 3:59 AM

#

Has anyone else had this error? I'm using asyncpg. The code works fine on my PC but not on my vps?

Traceback (most recent call last):
  File "main.py", line 22, in <module>
    client = Bot()
  File "/root/bot/__init__.py", line 137, in __init__
    self.loop.run_until_complete(create_connection())
  File "/usr/lib/python3.8/asyncio/base_events.py", line 616, in run_until_complete
    return future.result()
  File "/root/bot/__init__.py", line 92, in create_connection
    self.db = await asyncpg.create_pool(
  File "/usr/local/lib/python3.8/dist-packages/asyncpg/pool.py", line 398, in _async__init__
    await self._initialize()
  File "/usr/local/lib/python3.8/dist-packages/asyncpg/pool.py", line 426, in _initialize
    await first_ch.connect()
  File "/usr/local/lib/python3.8/dist-packages/asyncpg/pool.py", line 125, in connect
    self._con = await self._pool._get_new_connection()
  File "/usr/local/lib/python3.8/dist-packages/asyncpg/pool.py", line 468, in _get_new_connection
    con = await connection.connect(
  File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1718, in connect
    return await connect_utils._connect(
  File "/usr/local/lib/python3.8/dist-packages/asyncpg/connect_utils.py", line 663, in _connect
    con = await _connect_addr(
  File "/usr/local/lib/python3.8/dist-packages/asyncpg/connect_utils.py", line 642, in _connect_addr
    await asyncio.wait_for(connected, timeout=timeout)
  File "/usr/lib/python3.8/asyncio/tasks.py", line 483, in wait_for
    return fut.result()
asyncpg.exceptions.ConnectionDoesNotExistError: connection was closed in the middle of operation

brave bridge Aug 21, 2020, 5:24 AM

#

@limber trail can you show the code?

brave bridge Aug 21, 2020, 6:23 AM

#

Could someone show a more complex query using postgresql's WITH RECURSIVE?
Suppose I have two tables: apples and bananas. Each banana has zero or more apples attached to it (don't ask...).

bananas
    uid BIGSERIAL PRIMARY KEY
    links_to BIGINT REFERENCES bananas
    brand VARCHAR(64)

apples
    uid BIGSERIAL PRIMARY KEY
    links_to BIGINT REFERENCES apples
    color VARCHAR(32)
    brand VARCHAR(64)

banana_apples
    uid BIGSERIAL PRIMARY KEY
    banana_uid BIGINT REFERENCES bananas
    apple_uid BIGINT REFERENCES apples

bananas and apples have a links_to column. If links_to is not null in a row, that row describes an object as a shallow copy of another object (and all of its content rows are null)
So, for example

-- BANANAS
uid  links_to     brand
  1    NULL      "CISCO"
  2    NULL      "MICROSOFT"
  3     1          NULL --  links to `1 CISCO`
  4     2          NULL --  links to `2 MICROSOFT`
  5     4          NULL --  links to `2 MICROSOFT` via banana no.4

#

So if I do this

WITH RECURSIVE rec_bananas AS (
    SELECT uid, brand, links_to FROM bananas WHERE uid = $1
UNION
    SELECT
        b.uid, b.brand, b.links_to
    FROM bananas b
    INNER JOIN rec_bananas r ON b.uid = r.links_to
    WHERE r.links_to IS NOT NULL
)

SELECT rb.uid AS banana_uid, rb.brand AS banana_brand
        a.uid AS apple_uid, a.color AS apple_color, a.brand AS apple_brand
FROM rec_bananas rb
JOIN banana_apples ba ON ba.banana_uid = rb.post_uid
INNER JOIN apples a ON a.uid = ba.attachment_uid

I get a result containing banana $1 with all of its apples.

given `apples`:
uid  links_to   color      brand
1      NULL       red    KALASHNIKOV
2      NULL     green       COLT
3      NULL     yellow    WINCHESTER

given `banana_apples`:
uid    banana_uid    apple_uid
1          2             1
2          2             3


banana_uid | banana_brand | apple_uid | apple_color | apple_brand
-----------+--------------+-----------+-------------+--------------
    5      | MICROSOFT    |    1      |    red      | KALASHNIKOV
    5      | MICROSOFT    |    3      |  yellow     | WINCHESTER

The problem is that it doesn't handle apples which don't contain anything but only link to another apple. How do I obtain data for each apple in the same way I obtain the banana's data?

dull scarab Aug 21, 2020, 6:54 AM

#

Why don't you use a relationship table?

#

Seems like an odd way to do it, imo

brave bridge Aug 21, 2020, 6:59 AM

#

@dull scarab what do you mean by 'relationship table'?

#

do you mean that links between objects should be specified in a separate table like apple_links, and then I should just make a join in a recursive query?

dull scarab Aug 21, 2020, 7:01 AM

#

a 3rd table that lists all relations

brave bridge Aug 21, 2020, 7:03 AM

#

alright, I'm going to take a break and do something easier for a while..

broken estuary Aug 21, 2020, 7:28 AM

#

aprvd_budget = db.Column(db.Numeric(10, 2), nullable=False)

this is my code for storing a numeric value in database. but the sqlalchemy doesnt like using numeric in sqlite. it throws me this error "SAWarning: Dialect sqlite+pysqlite does not support Decimal objects natively"
what would be the best practice to store currency?

brave bridge Aug 21, 2020, 8:45 AM

#

maybe you can store the number of cents (i.e. currency * 100)?

#

assuming you have an integer part and a decimal part of 0..99

eternal raptor Aug 21, 2020, 9:56 AM

#

        @commands.command(pass_context=True)
        @commands.has_permissions(ban_members=True)
        async def ukaraj(self, ctx, member: discord.Member, reason = None):
                async with aiosqlite.connect("zonnerty.db") as db:

                        gildia = ctx.guild.id
 
                        if reason == None or member == None:
                                embed = discord.Embed(title="Błąd!", description="Podaj powód lub użytkownika i spróbuj ponownie!", color=discord.Color.dark_red())
                                await ctx.send(embed=embed)
                        else:
                                
                                await db.execute(f"INSERT INTO bany_{gildia} (UserID, reason) VALUES (? , ?)", (member.id , reason,))
                                await member.ban(reason=reason)
                                embed = discord.Embed(title='Sukces!', description=f'Ukarano użytkownika {member}, powód: {reason}', color=discord.Color.green())
                                await ctx.send(embed=embed)
                                await db.commit()

no such table: bany_711941542671024179

gaunt meadow Aug 21, 2020, 10:21 AM

#

how to add column if not exist in sqlite?

gaunt meadow Aug 21, 2020, 10:43 AM

#

?

modest ledge Aug 21, 2020, 11:52 AM

#

is there any good tutorials or videos of postgresql asyncpg basics?

cinder dome Aug 21, 2020, 12:06 PM

#

What can I do so that I don’t have to connect to the database every single time

📎 image0.png

#

aioSQLite

#

Can I do this in my main file?

📎 image0.png

merry mirage Aug 21, 2020, 12:43 PM

#

Hello. I have deleted many documents from my mongodb cluster. But logical size stays the same; it does not decrease. I have deleted non important documents to free storage for important ones. Why is this number is not decreasing?

📎 unknown.png

cinder dome Aug 21, 2020, 12:45 PM

#

Nvm I got it

limber trail Aug 21, 2020, 1:02 PM

#

@brave bridge I actually managed to fix it. Turns out the server didnt like having the same server host. I changed it to 127.0.0.1 on the vps and it worked xD

plucky crystal Aug 21, 2020, 1:03 PM

#

Hi, how can I extract integers from a sqlite database to a list?

weary quarry Aug 21, 2020, 1:18 PM

#

does anyone know how to read orm diagrams?

tulip pier Aug 21, 2020, 2:20 PM

#

create table car (
    id BIGSERIAL NOT NULL,
    make VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    price VARCHAR(50) NOT NULL
);

create table person (
    id BIGSERIAL NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    date_of_birth DATE NOT NULL,
    email VARCHAR(50),
    country_of_birth VARCHAR(50),
    car_id BIGINT REFERENCES car(id), 
    UNIQUE(car_id)
);

#

ERROR: there is no unique constraint matching given keys for referenced table "car"

#

i am getting this error, ping me if you come up with a solution

keen zodiac Aug 21, 2020, 2:37 PM

#

Simple question:

I am trying to create query to only grabs items from the last N days via sql. So, I want to get all events within the last 7 days and sort that by the updated_at column from a table. How would I do that?

cinder dome Aug 21, 2020, 3:08 PM

#

Traceback (most recent call last):
File "/home/container/.local/lib/python3.8/site-packages/aiosqlite/core.py", line 95, in run
result = function()
sqlite3.OperationalError: near "user_id": syntax error
ERROR:asyncio:Task exception was never retrieved
future: <Task finished name='Task-1' coro=<create_db() done, defined at /home/container/cogs/datab/tables.py:8> exception=OperationalError('near "user_id": syntax error')>
Traceback (most recent call last):
File "/home/container/cogs/datab/tables.py", line 16, in create_db
await cursor.executescript("""
File "/home/container/.local/lib/python3.8/site-packages/aiosqlite/cursor.py", line 48, in executescript
await self._execute(self._cursor.executescript, sql_script)
File "/home/container/.local/lib/python3.8/site-packages/aiosqlite/cursor.py", line 30, in _execute
return await self._conn._execute(fn, *args, **kwargs)
File "/home/container/.local/lib/python3.8/site-packages/aiosqlite/core.py", line 109, in _execute
return await future
File "/home/container/.local/lib/python3.8/site-packages/aiosqlite/core.py", line 95, in run
result = function()
sqlite3.OperationalError: near "user_id": syntax error``` What does this error mean for `aiosqlite`?

tepid cradle Aug 21, 2020, 4:06 PM

#

it means there is a syntax error in your code. Since you've not shown your code, it's not possible to tell what is the error.

vocal moon Aug 21, 2020, 4:43 PM

#

Do I need to open this port or smth?

#

@tepid cradle

rain apex Aug 21, 2020, 5:17 PM

#

Is there a way to use sqlite's built-in auto increment ti have multiple threads of incrementation based in the value of a column for that record. Fir example if you insert a record with a name fred it will increment and have a value if 1. Done again, and the value will be 2. But if you insert Mary as thr name the increment column will have a different count and start at 1 since the name is different.

torn sphinx Aug 21, 2020, 5:52 PM

#

With MySQL with email and passwords on how would I send keys in an email field and repeat it

cinder dome Aug 21, 2020, 5:59 PM

#

it means there is a syntax error in your code. Since you've not shown your code, it's not possible to tell what is the error.
@tepid cradle I already figured it out a while ago

tepid cradle Aug 21, 2020, 6:04 PM

#

Do I need to open this port or smth?
@vocal moon what are you referring to?

harsh pulsar Aug 21, 2020, 6:25 PM

#

@rain apex sqlite autoincrementing is guaranteed to be monotonic but not sequential

#

oh

#

never mind i misunderstood

rain apex Aug 21, 2020, 6:25 PM

#

k

harsh pulsar Aug 21, 2020, 6:25 PM

#

i thought you were asking about mulithreading...

rain apex Aug 21, 2020, 6:25 PM

#

no

harsh pulsar Aug 21, 2020, 6:25 PM

#

i dont know if there is a way to do what you're asking for automatically

rain apex Aug 21, 2020, 6:25 PM

#

I didn't really know a better word

#

I was doing research and the only way I could find was to select the lowest avaiable id and do it in a trigger

harsh pulsar Aug 21, 2020, 6:26 PM

#

maybe you can do it with a trigger

#

yeah

rain apex Aug 21, 2020, 6:27 PM

#

I guess I will just do it that way

harsh pulsar Aug 21, 2020, 6:27 PM

#

https://stackoverflow.com/a/2406528/2954547

Stack Overflow

Use trigger for auto-increment

I'm trying to solve the problem that composite keys in sqlite don't allow autoincrement.

I don't know if it's possible at all, but I was trying to store the last used id in a different table, and ...

#

it looks like this solution uses a 2nd table to keep track of the max value per group

rain apex Aug 21, 2020, 6:30 PM

#

I found a different query to get the minimum unused value

#

https://stackoverflow.com/questions/30944315/search-missing-rowid-in-sqlite

SELECT id + 1
FROM (SELECT 0 AS id
      UNION ALL
      SELECT rowid
      FROM MyTable
     ) AS ids
WHERE NOT EXISTS (SELECT 1
                  FROM MyTable
                  WHERE rowid = ids.id + 1)
LIMIT 1

Stack Overflow

Search missing rowId in sqlite

In my table there are rows that have been deleted. How can I find the first missing id?

Ex:

ID DATA
1 row1
2 row2
4 row3
8 row4
How can I find that Id 3 is missing?
This needs to ...

crisp ledge Aug 21, 2020, 6:36 PM

#

How to fix this error

import discord
from discord.ext import commands
from discord.ext.commands import Bot

from pymongo import MongoClient

bot = commands.Bot(command_prefix = '<>')

'The error is located on this line ↙'
client = MongoClient("mongodb+srv://FortyArt:<password>@microbot1.a627n.mongodb.net/<dbname>?retryWrites=true&w=majority")
db = client["MicroBot1"]
collection = db["users"]

@bot.command()
async def my(ctx):
    if collection.count_documents({"_id": ctx.author.id}) == 0:
        collection.insert_one({
        "_id": ctx.author.id,
        "name": ctx.author.name,
        "rep": 0,
        "lvl": 0,
        "xp": 0
        })

    name = collection.find({"_id": ctx.author.id})
    await ctx.send(name["name"])

@bot.event
async def on_ready(): 
    print("-----")
    print(f'{bot.user} в сети!')
    print('ID:', bot.user.id)
    print('Bot работает.')

token = open('token.txt').readline()
bot.run(token)

📎 unknown.png

harsh pulsar Aug 21, 2020, 6:38 PM

#

@rain apex ```sql
CREATE TABLE person (
firstname text,
n integer
);

CREATE TRIGGER n_inc AFTER INSERT ON person
BEGIN
UPDATE person
SET n = (
SELECT CASE WHEN n IS NULL THEN 0 ELSE n+1 END
FROM person
WHERE firstname = NEW.firstname
ORDER BY n DESC
LIMIT 1
)
WHERE ROWID = NEW.ROWID;
END;

INSERT INTO person (firstname) VALUES ('frank');
INSERT INTO person (firstname) VALUES ('lisa');
INSERT INTO person (firstname) VALUES ('frank');
INSERT INTO person (firstname) VALUES ('lisa');
INSERT INTO person (firstname) VALUES ('frank');

SELECT * FROM person;

this worked for me

#

@rain apex rowid is not the same as what you are looking for

#

rowid is the internal autoincrementing integer primary key for the row

#

% sqlite3 --header data.db < s1.sql
name|n
frank|0
lisa|0
frank|1
lisa|1
frank|2

this is what i got from my script

rain apex Aug 21, 2020, 6:46 PM

#

I will use some of that, but I was going to make it reuse deleted ids. Thank you

#

The query I showed before is just copy pasted from stackoverflow so I will have to modify it of course

warped frigate Aug 21, 2020, 8:07 PM

#

In Postgresql, I have a table that I'm trying to insert into with psycopg2. When I use execute_values, it gives me this error:

psycopg2.errors.InvalidTextRepresentation: invalid input syntax for integer: "A"
LINE 1: INSERT INTO IV_working VALUES (ARRAY[0,'20200814','A','20200...

However, the third column of the table is of the data type text! It shouldn't be trying to convert it to an integer! This is the code I used to create the table, so you can see the data types in order:

        CREATE TABLE IV_working(
        row_id integer,
        date_id date,
        ticker text,
        expiration text,
        strike real,
        type text,
        iv double precision,
        symbol text,
        PRIMARY KEY(row_id, date_id)
        )

#

oh and these are my arguments to execute_values:

    execute_values(cur, "INSERT INTO IV_working VALUES %s ON CONFLICT DO NOTHING", rows)

rows is a list of tuples, each with the same length (8 values, as there are 8 columns). you can see the beginning of rows in the error message in the previous thing i sent

#

(and I'll be switching the primary key to be just row_id after I solve this problem, as someone told me earlier there's no use for a composite primary key here)

frank anchor Aug 21, 2020, 9:05 PM

#

i have an entire discord bot to migrate from using pickle to store data to using aiosqlite tomorrow :| it will be a bit of a pain, but will be fun, and its about time i learned to use the SQL libraries for python because theyre so popular 😅

#

plus SQL itself is very popular and i need to learn how to properly use it. not that anyone asked lmao just wanted to share

tepid cradle Aug 22, 2020, 1:21 AM

#

@frank anchor would recommend just playing around with SQL first, maybe on w3school, before you start the migration. Build a little bit of familiarity, work with random data, you'll get an idea about how to structure the database.

pure cypress Aug 22, 2020, 4:02 AM

#

Wait no, @pure cypress for the function can I do py async def execute(self, sql, param); await self.db.execute(sql, (param))
@cinder dome

Are you trying to create a wrapper function to avoid doing self.db? That seems kind of redundant.

cinder dome Aug 22, 2020, 4:02 AM

#

It works fine though

pure cypress Aug 22, 2020, 4:02 AM

#

Well yes, it may work, but it's pointless is what I mean.

#

Since you use a single parameter to take in all the arguments, you don't need to convert it to a tuple.

cinder dome Aug 22, 2020, 4:03 AM

#

Just easier for me

#

Apparently I do, because the function itself only takes sql, and param, and so I wanted to have it so when Im making a different function like my add_warns, it’ll be able to take multiple args (values)

pure cypress Aug 22, 2020, 4:04 AM

#

You defined your function like this execute(self, sql, param) Which means it can only take 2 arguments at most. So execute("SELECT ..", 1, 2, 3) would be too many arguments. Hence, you'd need to turn the args into a tuple before you call the function: execute("SELECT ..", (1, 2, 3))¨.

cinder dome Aug 22, 2020, 4:04 AM

#

If that makes sense

pure cypress Aug 22, 2020, 4:04 AM

#

You need to convert it to a tuple before you call the function rather than inside the function.

#

By the time you're inside the function, params should already be a tuple

cinder dome Aug 22, 2020, 4:05 AM

#

so I should only change the functions I have then

#

All those values I have

#

That makes sense then ig

pure cypress Aug 22, 2020, 4:05 AM

#

I don't know what you mean

cinder dome Aug 22, 2020, 4:06 AM

#

Its exactly what you just said, my add_warns has those 4 values

pure cypress Aug 22, 2020, 4:06 AM

#

You can define it like this to avoid turning them into tuples yourself:

async def execute(self, sql, *param);
    await self.db.execute(sql, param)

#

Notice the star there

cinder dome Aug 22, 2020, 4:06 AM

#

Yeah

pure cypress Aug 22, 2020, 4:06 AM

#

So that means it can take a variable amount of arguments

#

And it will automatically store them in a tuple named params

cinder dome Aug 22, 2020, 4:07 AM

#

oh damn

pure cypress Aug 22, 2020, 4:07 AM

#

So you'll be able to pass that on directly to db.execute()

cinder dome Aug 22, 2020, 4:07 AM

#

async def execute(self, sql, *param);
    await self.db.execute(sql, *param)

#

Or leave it as param

#

No *param, inside that

pure cypress Aug 22, 2020, 4:08 AM

#

Leave the second one without the star

cinder dome Aug 22, 2020, 4:08 AM

#

alr

pure cypress Aug 22, 2020, 4:08 AM

#

If you have the star there, it unpacks the tuple into individual arguments again

cinder dome Aug 22, 2020, 4:08 AM

#

Yeah I read that on google

#

I just didnt think it was for my situation

#

But lemme try it rn

#

Also how did I end up in the databases channel? Thought we were in the discordpy

pure cypress Aug 22, 2020, 4:09 AM

#

I pinged you here

#

Cause, like I said, this channel is more appropriate

cinder dome Aug 22, 2020, 4:09 AM

#

Oh I just didnt notice then

#

So if anything happens can I ping you?

#

Ima try it out rn

pure cypress Aug 22, 2020, 4:10 AM

#

In the short term, yes I suppose

#

I'm getting off soon though

#

But surely others can help too

cinder dome Aug 22, 2020, 4:10 AM

#

Aight

#

@pure cypress
Hopefully you’re still on
Error: https://mystb.in/EnsureSweetCelebration.sql
Tables.py: https://mystb.in/EnsureSweetCelebration.sql

#

Oops

pure cypress Aug 22, 2020, 4:17 AM

#

The links are the same

cinder dome Aug 22, 2020, 4:18 AM

#

Wrong link lmao

#

Error: https://mystb.in/PositiveReceptorComplexity.sql

pure cypress Aug 22, 2020, 4:19 AM

#

Did you add the column later?

cinder dome Aug 22, 2020, 4:20 AM

#

To what?

pure cypress Aug 22, 2020, 4:20 AM

#

You use CREATE TABLE IF NOT EXISTS, so the table wouldn't have been recreated if you added or renamed the column after it was first created

cinder dome Aug 22, 2020, 4:20 AM

#

Oh well yeah

pure cypress Aug 22, 2020, 4:20 AM

#

To warns

cinder dome Aug 22, 2020, 4:20 AM

#

I did

#

Why, I cant change it?

pure cypress Aug 22, 2020, 4:20 AM

#

You have to use alter table to edit an existing table

#

Or drop the table and let it create it again, but you'll lose data by doing that

cinder dome Aug 22, 2020, 4:21 AM

#

I dont have any data in the table set yet

#

There’s no warns or anything

#

Nothing in those column rows

pure cypress Aug 22, 2020, 4:21 AM

#

well you can run drop table warns then

cinder dome Aug 22, 2020, 4:21 AM

#

Where?

pure cypress Aug 22, 2020, 4:22 AM

#

Before you create the table

#

It's SQL

#

And you can remove that line once it runs one time

cinder dome Aug 22, 2020, 4:23 AM

#

📎 image0.png

#

Like this?

pure cypress Aug 22, 2020, 4:23 AM

#

don't forget the semicolon

cinder dome Aug 22, 2020, 4:23 AM

#

Yeah, but is that really it?

#

I dont have to change anything in my functions

pure cypress Aug 22, 2020, 4:23 AM

#

Yes?

#

You just need to drop it so that it'll get recreated with the columns you added

#

Then you can delete the line since you only need to drop it this one time due to editing your table

cinder dome Aug 22, 2020, 4:24 AM

#

Alr

#

And for the future

#

With ALTER TABLE, do I just copy paste all those columns from my CREATE TABLE IF NOT EXISTS

#

Like just replace CREATE TABLE IF NOT EXISTS with ALTER TABLE

pure cypress Aug 22, 2020, 4:25 AM

#

No, you should look up documentation for that statement

cinder dome Aug 22, 2020, 4:25 AM

#

alright

pure cypress Aug 22, 2020, 4:25 AM

#

You use it to add 1 column

#

Well, it can do more than add columns too

cinder dome Aug 22, 2020, 4:25 AM

#

Ill see when I have to use it later on

#

Lemme try running this

#

Aight I tried running it

#

Same error still @pure cypress

#

I dont think it dropped

#

Like I did !warn @user test to see if it would drop the table

#

Same error

#

📎 image0.png

pure cypress Aug 22, 2020, 4:28 AM

#

Did you restart your bot?

cinder dome Aug 22, 2020, 4:29 AM

#

Yeah

pure cypress Aug 22, 2020, 4:29 AM

#

Are you sure create_db ran?

cinder dome Aug 22, 2020, 4:29 AM

#

I dont think it did

#

I have nowhere to connect to that

#

All I have in my main file is ```py

#Function to make our life easier
async def connect_db():
bot.db = await aiosqlite.connect('main.db')

#Make the database
asyncio.get_event_loop().run_until_complete(connect_db())
#client.loop.create_task(connect_db(bot))```

#

Thats what self.bot.db from database.py refers to

#

So self.db = self.bot.db is for that

pure cypress Aug 22, 2020, 4:32 AM

#

You do that before the bot starts?

cinder dome Aug 22, 2020, 4:33 AM

#

Im not sure

#

Wait

#

Lemme send the main file

#

https://mystb.in/ComplicationsMaskScope.python

#

You’ll see it in line 46

pure cypress Aug 22, 2020, 4:34 AM

#

Well, I guess you do bot.run() somewhere else cause I don't see it here

#

Anyway

cinder dome Aug 22, 2020, 4:34 AM

#

Its at the bottom

pure cypress Aug 22, 2020, 4:34 AM

#

Why don't you call create_db inside connect_db?

cinder dome Aug 22, 2020, 4:34 AM

#

All the way at the bottom

#

I honestly should

#

Should I just move everything I have into that?

pure cypress Aug 22, 2020, 4:35 AM

#

Yeah, you can move creation inside it

#

You don't need the wait until ready thing

cinder dome Aug 22, 2020, 4:35 AM

#

Alr, how much longer will you be on for?

pure cypress Aug 22, 2020, 4:35 AM

#

Getting off now

cinder dome Aug 22, 2020, 4:36 AM

#

Damn

#

Thanks for helping

pure cypress Aug 22, 2020, 4:36 AM

#

You're welcome

cinder dome Aug 22, 2020, 9:45 AM

#

I’m using aiosqlite, and whenever I invoke the !warns @user command, it throws errors everytime pointing to the get_warns function

Getting this error: https://mystb.in/RanchMysteriousConfident.php

Database.py: https://mystb.in/IdleNerveAntibody.python

Main File (Where I set up tables and connections): https://mystb.in/RadiationAccuratelyNeighbors.sql

#

Ping if you reply

modest ledge Aug 22, 2020, 11:03 AM

#

if i need to update information on a column of my db, i'd wanna check first if that column exists or not if exists then i'd update it, but doing a SELECT * FROM and fetchall() then UPDATE takes 2 quiries, is there any easier way to do that with 1 quiry? (using sqlite3)

#

basically a better way of doing this

db.execute("""SELECT * FROM authors WHERE last_name="user" """)

list = list(db.fetchall())

if list == []:
    # Column doesn't exist
    # create a column
else:
    # Column exists
    # update information```

torn sphinx Aug 22, 2020, 12:36 PM

#

Alright, so I am trying to make a warn command for my discord bot.

I am using aiosqlite.

I will probably need a lot of help with this, but the thing I need help with rn is the case number. AKA the number that just increases for every warning.

Now my question is:

How can I make the number increase everytime someone warns someone?

sick dragon Aug 22, 2020, 12:39 PM

#

@torn sphinx useful info https://www.sqlitetutorial.net/sqlite-autoincrement/

SQLite Tutorial

Understanding the SQLite AUTOINCREMENT

This tutorial helps you understand SQLite AUTOINCREMENT attribute and explain when you should use it in the primary key of a table.

pale river Aug 22, 2020, 12:39 PM

#

figure out how to query

#

take the queried case number and iterate by one

#

send it back

#

🙂

sick dragon Aug 22, 2020, 12:39 PM

#

why do that when there is autoincrement

torn sphinx Aug 22, 2020, 12:40 PM

#

@sick dragon

📎 unknown.png

#

It's that simple? 😳

sick dragon Aug 22, 2020, 12:40 PM

#

yes

torn sphinx Aug 22, 2020, 12:40 PM

#

Damn

#

Thanks a lot

sick dragon Aug 22, 2020, 12:40 PM

#

when you insert something the number will increase automatically

pale river Aug 22, 2020, 12:40 PM

#

setting case number as primary key sounds sus

#

LOL

sick dragon Aug 22, 2020, 12:41 PM

#

?

torn sphinx Aug 22, 2020, 12:42 PM

#

@sick dragon What does

#

"PRIMARY KEY"

#

Do?

pale river Aug 22, 2020, 12:42 PM

#

that's SQL syntax

#

probably wanna learn that

sick dragon Aug 22, 2020, 12:42 PM

#

A primary key is a... primary key

torn sphinx Aug 22, 2020, 12:42 PM

#

Yeah buit

#

If I make my case number the primary key

pale river Aug 22, 2020, 12:42 PM

#

you don't have to

torn sphinx Aug 22, 2020, 12:42 PM

#

Does it affect my bot?

#

Rlly?

pale river Aug 22, 2020, 12:42 PM

#

CASENUMBER INT AUTOINCREMENT

sick dragon Aug 22, 2020, 12:42 PM

#

It uniquely identifies a record in a table @torn sphinx

pale river Aug 22, 2020, 12:43 PM

#

should be a valid solution

#

the example just showed it as primary key

torn sphinx Aug 22, 2020, 12:43 PM

#

Btw

sick dragon Aug 22, 2020, 12:43 PM

#

infraction_id INTEGER PRIMARY KEY AUTOINCREMENT

torn sphinx Aug 22, 2020, 12:43 PM

#

📎 unknown.png

pale river Aug 22, 2020, 12:43 PM

#

although I don't really think you're going to be creating tables using traditional SQL syntax

torn sphinx Aug 22, 2020, 12:43 PM

#

What do I do here?

sick dragon Aug 22, 2020, 12:44 PM

#

First of all, don't create a table every time you execute warn command

torn sphinx Aug 22, 2020, 12:44 PM

#

Like do I have to make a variable called caseid and set it to 0

#

Yeah

#

I will just make it a comment line

#

After I run the command the first time

#

Since IF NOT EXISTS is not a thing in aiosqlite

sick dragon Aug 22, 2020, 12:44 PM

#

Second, don't insert into case_id

torn sphinx Aug 22, 2020, 12:44 PM

#

ohhh

#

Alright

pale river Aug 22, 2020, 12:45 PM

#

this is more of a design question, what do you want as the primary key?

sick dragon Aug 22, 2020, 12:45 PM

#

The primary key should be the case id

torn sphinx Aug 22, 2020, 12:45 PM

#

I have no idea, because I don't know what setting the primary key means.

#

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "AUTOINCREMENT": syntax error

#

Btw I got that error

pale river Aug 22, 2020, 12:45 PM

#

do you know how hashmaps work?

sick dragon Aug 22, 2020, 12:45 PM

#

The primary key is what uniquely identifies a row

pale river Aug 22, 2020, 12:45 PM

#

this isn't really a 1:1 analogy

torn sphinx Aug 22, 2020, 12:45 PM

#

do you know how hashmaps work?
@pale river No idea.

pale river Aug 22, 2020, 12:46 PM

#

uh

#

you know how in a dictionary

#

we have a b c d e f g h

#

on the side?

#

so we don't have to search through everything?

sick dragon Aug 22, 2020, 12:46 PM

#

The primary key is what uniquely identifies a row
btw

pale river Aug 22, 2020, 12:46 PM

#

primary key basically helps with that

torn sphinx Aug 22, 2020, 12:46 PM

#

Oh yeah, like cordinates

pale river Aug 22, 2020, 12:46 PM

#

and it identifies each row as unique

#

yea

#

atie's explanation is fine and 100% correct

#

if you know that there will never be another entry with the same case_id

torn sphinx Aug 22, 2020, 12:47 PM

#

   A   B   C   D   E
1 |    1B
2 |
3 |
4 | 
5 |

pale river Aug 22, 2020, 12:47 PM

#

or there should never be another one

torn sphinx Aug 22, 2020, 12:47 PM

#

Like that

#

Right?

pale river Aug 22, 2020, 12:47 PM

#

autoincrement is fine

#

nah

#

not a coordinate map

#

sorry for confusing you

torn sphinx Aug 22, 2020, 12:47 PM

#

._.

sick dragon Aug 22, 2020, 12:47 PM

#

why do you need a hash map at all?

pale river Aug 22, 2020, 12:47 PM

#

just using it as analogy

#

do not make a hash map

#

its a unique identifier

#

that's all it is

sick dragon Aug 22, 2020, 12:48 PM

#

just create the table first, commit, then insert the data every time you record an infraction without inserting into case_id and commit

torn sphinx Aug 22, 2020, 12:48 PM

#

(Just a quick comment that I don't know a lot at all about databases, so yeah)

pale river Aug 22, 2020, 12:48 PM

#

u should

#

probably learn something about DB before using it

#

if you want to get your code pushed out asap

#

just use a JSON

#

it doesn't sound like you're working on scaled software or anything that needs SQL

sick dragon Aug 22, 2020, 12:48 PM

#

sql is like human language

torn sphinx Aug 22, 2020, 12:48 PM

#

I mean yeah

#

JSON is simple

sick dragon Aug 22, 2020, 12:48 PM

#

learn the keywords and how to put them correctly

torn sphinx Aug 22, 2020, 12:48 PM

#

I just like aiosqlite more

pale river Aug 22, 2020, 12:48 PM

#

what the fuck LOL

#

yeah up to you

sick dragon Aug 22, 2020, 12:49 PM

#

don't use json as a db lol

pale river Aug 22, 2020, 12:49 PM

#

probably worth learning a little bit of SQL syntax and concepts like primary, secondary key, and SELECT * FROM etc.

#

hey fuck it, I do it

#

https://tinydb.readthedocs.io/en/stable/ pretty good lib

torn sphinx Aug 22, 2020, 12:49 PM

#

I will set the caseid as my primary key

pale river Aug 22, 2020, 12:49 PM

#

yeah

torn sphinx Aug 22, 2020, 12:50 PM

#

holy shit i think it works

#

Oh yeah

#

This works

#

Yay!

#

@pale river Yo, quick thing

#

Sorry for the pingh

#

Just uhhhh

#

How can I print out the case id?

pale river Aug 22, 2020, 12:57 PM

#

are you querying it?

torn sphinx Aug 22, 2020, 12:57 PM

#

Oh wait

#

I think I know how I can do it

#

Let me try this

#

Just one question

pale river Aug 22, 2020, 12:57 PM

#

sweet

torn sphinx Aug 22, 2020, 12:57 PM

#

How do I print out the thing I did "SELECT from"?

sick dragon Aug 22, 2020, 12:57 PM

#

Use aiosqlite instead

torn sphinx Aug 22, 2020, 12:57 PM

#

Like if I do SELECT case_id FROM warn_logs

#

How can I print case_id?

#

Use aiosqlite instead
@sick dragon I am?

pale river Aug 22, 2020, 12:57 PM

#

is it returning a class or a string?

#

i'm not too familiar with that lib

torn sphinx Aug 22, 2020, 12:58 PM

#

Integer

sick dragon Aug 22, 2020, 12:58 PM

#

I think you need to fetch, as many other sql modules

torn sphinx Aug 22, 2020, 12:58 PM

#

await db.execute("SELECT case_id FROM warn_logs")

#

Yeah

#

I did that

#

Right?

pale river Aug 22, 2020, 12:59 PM

#

res = await ...
print(res) and lmk what happens

#

or you can read doc and figure out what .execute() returns

torn sphinx Aug 22, 2020, 1:00 PM

#

oh

#

yeah

#

that should work

#

lemme try

#

I got <aiosqlite.cursor.Cursor object at 0x00000150A513A490> as an output

#

😳

pale river Aug 22, 2020, 1:06 PM

#

lmfao fuck me

📎 unknown.png

haughty flame Aug 22, 2020, 1:06 PM

#

How do I check if a user is not in a guild?

pale river Aug 22, 2020, 1:06 PM

#

#discord-bots

torn sphinx Aug 22, 2020, 1:06 PM

#

@haughty flame Use #discord-bots I think

haughty flame Aug 22, 2020, 1:07 PM

#

Do they accept sql?

torn sphinx Aug 22, 2020, 1:07 PM

#

What u asked is not about sql

#

Lol

pale river Aug 22, 2020, 1:07 PM

#

you probably need

#

!resources

delicate fieldBOT Aug 22, 2020, 1:07 PM

#

Resources

The Resources page on our website contains a list of hand-selected learning resources that we regularly recommend to both beginners and experts.

pale river Aug 22, 2020, 1:09 PM

#

a little tired rn, but it looks like you get a callback from that cursor and you'd use .fetchone()

#

https://aiosqlite.omnilib.dev/en/latest/api.html?highlight=cursor#cursors

torn sphinx Aug 22, 2020, 1:13 PM

#

Uhhh

#

Alr

sick dragon Aug 22, 2020, 2:45 PM

#

@haughty flame
ctx.guild if false if dms

haughty flame Aug 22, 2020, 2:46 PM

#

I already fixed it thanks.

proven arrow Aug 22, 2020, 5:11 PM

#

DELETE FROM some_table WHERE some_condition=some_value; or do you mean something else?

#

But isnt that your PK? Do you want to reuse deleted primary keys?

#

Im not too sure if even Postgres allows this, and even if so, it would be an expensive operation.

#

You can manually manage the keys, although is not recommended. What is the reason for reusing the keys?

#

Well I don't see how reusing PKs will help with that. You can just make a new column for slot_number, and allow the user to delete by the value in this.

#

Or since user_ids are unique, you can delete the slot by deleting the record by user_id and guild_id

#

That would be the best way, since it allows users to delete only their own records

lime echo Aug 22, 2020, 8:38 PM

#

games_check=c.execute("SELECT games FROM games WHERE (userID, games) in values(?,?)", (ctx.author.id,games,)).fetchall()

#

what's wrong with this code?

#

syntax error near "values" is says

sinful condor Aug 23, 2020, 1:35 AM

#

can someone help me with this https://pastebin.com/UUxJppnG 1st of all I am getting this error I know it is because of the second thing but its still an error that I thought id say

Traceback (most recent call last):
  File "C:\Users\runne\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "main.py", line 135, in on_message
    await add_xp(message.author, 1)
  File "main.py", line 168, in add_xp
    exp1 = result1 + exp
TypeError: unsupported operand type(s) for +: 'NoneType' and 'int'```
 and second I think the error works with this problem it makes a new column every time it is run.

Pastebin

@client.eventasync def on_message(message): if message.guild is ...

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

limber trail Aug 23, 2020, 1:42 AM

#

Is there anyway to detect that the insert statement had a conflict and did not fully insert a new record from the rowcount variable that I'm defining?

Currently rowcount returns values like this but I'm not sure how to read them

Like it's always either 0 0 or 0 1 ?
I'm using postgresql and asyncpg btw

Code:

📎 unknown.png

#

The rowcount atm prints out this as shown but I'm not sure what it really means

📎 unknown.png

cinder dome Aug 23, 2020, 1:56 AM

#

@sinful condor use aiosqlite

sinful condor Aug 23, 2020, 1:56 AM

#

yeah someone already told me that I am trying to change it to that

#

@cinder dome someone told me this is how you do it when making the database async with aiosqlite3.connect(database_path) as db: await db.execute("""CREATE TABLE IF NOT EXISTS level (userguild INTEGER, xp INTEGER, level INTEGER, PRIMARY KEY (userguild, xp, level) ON CONFLICT REPLACE)""") but database_path is not defined what am I supposed to put there

#

ohhhh

#

nevermind

#

wait

#

how do I make a database in the first place

cinder dome Aug 23, 2020, 1:59 AM

#

If you want to make it simpler, make functions in some file for the database, then make the connection to the database in a main file

#

Check my github if you wanna see examples

#

Look at bot.py, database.py

sinful condor Aug 23, 2020, 2:00 AM

#

ok

sinful condor Aug 23, 2020, 2:21 AM

#

@cinder dome ok I have problem here is my code ```@client.event
async def on_message(message):
if message.guild is None:
return
else:
await update_data(message.author)
await add_xp(message.author, 1)
await level_up(message.author, message.channel)
await client.process_commands(message)

async def update_data(user):
userguild = f'{user.id}/{user.guild.id}'
async with aiosqlite3.connect("C:/Users/runne/Desktop/mysqllvl/level.db") as db:
async with db.execute("SELECT userguild FROM level WHERE userguild = ?", (userguild)) as cursor:
result = await cursor.fetchone()
sxp = '0'
slevel = '1'
if user == client.user or result is not None:
return
else:
await db.execute("INSERT INTO level(userguild, xp, level)", (userguild, sxp, slevel))
db.commit()the bottom part is the only stuff that matters reallyIgnoring exception in on_message
Traceback (most recent call last):
File "C:\Users\runne\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\client.py", line 312, in _run_event
await coro(*args, **kwargs)
File "main.py", line 129, in on_message
await update_data(message.author)
File "main.py", line 137, in update_data
async with db.execute("SELECT userguild FROM level WHERE userguild = ?", (userguild)) as cursor:
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 37 supplied.``` I dont know where I am giving 37 'bindings' when it only wants 1

dusky siren Aug 23, 2020, 3:06 AM

#

@sinful condor using round brackets () causes it to create a list if you only pass one variable.
For example if I did (12345), it would create a list of [1, 2, 3, 4, 5] automatically.

#

In other words, replace (userguild) with [userguild] if you only are using 1 variable

modest ledge Aug 23, 2020, 5:03 AM

#

im getting a database is locked error

proven arrow Aug 23, 2020, 5:20 AM

#

Something else is using your database then. Either writing to it or you didn’t close it.

modest ledge Aug 23, 2020, 5:45 AM

#

I'm adding a row to the db everytime a user joins the server and like if 2 user joins at the same time it inserts for one user and gets database locked for the second user or sometimes gets database locked for both users

torn sphinx Aug 23, 2020, 6:32 AM

#

hi

#

I don't understand why Hashed values are considered an anti-pattern when designing row keys

#

📎 unknown.png

#

I understand domain names and sequential IDs would have hotspotting issues..

#

and frequently updated identifiers would overload the tablet due to frequent updates

#

but I don't understand why not hashed values, when salting is done with hashed values all the time

torn sphinx Aug 23, 2020, 7:36 AM

#

I made a db on mongo and I’m having a trouble inserting data into the db. I’m using discord.py to insert this data. Can someone help?

import pymongo
from pymongo import MongoClient

# Connections
CONNECTION_URL=os.environ.get("mongo")
cluster = MongoClient("CONNECTION_URL")
db = cluster["UserData"]
collection = db["User"]

# Data i want to insert
@client.event
async def on_message(ctx): 
  if "python" in str(ctx.content.lower()):
    post = {"_id": ctx.author.id, "score": 1}
    collection.insert_one(post)
    await ctx.channel.send('accepted!')

#This is my .env file
# I hashed out my password and username
mongo=mongodb+srv://user****:pass*****@cluster0.zouqc.mongodb.net/UserData?retryWrites=true&w=majority

Error:

📎 image0.png

hazy mango Aug 23, 2020, 7:37 AM

#

Oof I don’t know pymongo

torn sphinx Aug 23, 2020, 7:37 AM

#

ow

hazy mango Aug 23, 2020, 7:37 AM

#

Only the sql databases

torn sphinx Aug 23, 2020, 7:37 AM

#

ok

#

np

hazy mango Aug 23, 2020, 7:37 AM

#

Although honestly you shouldn’t be using pymongo for a discord bot

#

MongoDB’s async driver is honestly terrible

torn sphinx Aug 23, 2020, 7:37 AM

#

Do they not have an async wrapper?

#

i mean it works...

hazy mango Aug 23, 2020, 7:38 AM

#

You’d be much better off using PostgreSQL

torn sphinx Aug 23, 2020, 7:38 AM

#

mhm

#

i’ll switch to better dbs later on when i’m familiar with the basic stuff

#

I am sure there is some 3rd party async wrapper for pymongo

#

No harm in using those

#

ight thx

#

https://motor.readthedocs.io/en/stable/

hazy mango Aug 23, 2020, 7:39 AM

#

MongoDB and PostgreSQL are completely different by the way @torn sphinx

torn sphinx Aug 23, 2020, 7:39 AM

#

Motor: Asynchronous Python driver for MongoDB

#

o

hazy mango Aug 23, 2020, 7:40 AM

#

i’ll switch to better dbs later on when i’m familiar with the basic stuff
Saying this is like saying “I’ll learn java after learning basics of Python” kinda thing

torn sphinx Aug 23, 2020, 7:40 AM

#

bruh

hazy mango Aug 23, 2020, 7:40 AM

#

All the SQL databases are relatively similar (sqlite, mysql, postgresql, etc.)

#

But mongodb is completely different

torn sphinx Aug 23, 2020, 7:40 AM

#

the error says it could not connect to the db or is it something else?

#

MongoClient("CONNECTION_URL")

hazy mango Aug 23, 2020, 7:41 AM

#

The error is saying you gave it an invalid database link

torn sphinx Aug 23, 2020, 7:41 AM

#

"name or service unknown¡

#

You are supposed to have the actual url there

#

huh

#

Not litrally "CONNECTION_URL"

#

@torn sphinx i am having that. I imported from .env

#

But you are not using it, you are passing it the litral string

#

MongoClient("CONNECTION_URL")

#

MongoClient(os.environ.get("CONNECTION_URL"))
``` would be the right way

#

CONNECTION_URL=os.environ.get("mongo") cluster = MongoClient("CONNECTION_URL")

hazy mango Aug 23, 2020, 7:43 AM

#

Remove the “”

#

In the bottom one

torn sphinx Aug 23, 2020, 7:43 AM

#

Thats right but remove the "

hazy mango Aug 23, 2020, 7:43 AM

#

Right now you’re passing the string, not the variable

#

!e py var = "hello" print("var") # what you’re doing print(var) # what you should be doing

delicate fieldBOT Aug 23, 2020, 7:44 AM

#

@hazy mango :white_check_mark: Your eval job has completed with return code 0.

001 | var
002 | hello

torn sphinx Aug 23, 2020, 7:44 AM

#

i did remove the " on the second line. bUt it gave me an error saying its not defined

#

i’ll try again

#

In that case you dont have the environment var set

#

@torn sphinx i am having that. I imported from .env
...?

#

You prolly did that part wrong

#

#This is my .env file
# I hashed out my password and username
mongo=mongodb+srv://user****:pass*****@cluster0.zouqc.mongodb.net/UserData?retryWrites=true&w=majority

#

Bruh the variable name is "mongo"

#

Not CONNECTION_URL

#

cluster = MongoClient("CONNECTION_URL")
# main.py```

#This is my .env file

I hashed out my password and username

mongo=mongodb+srv://user****:pass*****@cluster0.zouqc.mongodb.net/UserData?retryWrites=true&w=majority

#

Yet the same mistake

#

CONNECTION_URL=os.environ.get("mongo")
cluster = MongoClient(CONNECTION_URL)

#

Remove the "

#

yes, i’ll try

#

thx

#

Also I dont see your code loading the env file

#

Is it somewhere else or you just dont have it written?

#

It works now.

#

i removed the "

#

and it said to install dnspython

#

@client.event
async def on_message(ctx): 
  if "python" in str(ctx.content.lower()):
    post = {"_id": ctx.author.id, "score": 1}
    collection.insert_one(post)
    await ctx.channel.send('accepted!')
  await client.process_commands(ctx)

@torn sphinx this is how i insert data right?

#

correct me if i’m wrong

#

I've never used mongo, will have to reference docs give me a min

#

import pymongo
from pymongo import MongoClient

# Connections
CONNECTION_URL=os.environ.get("mongo")
cluster = MongoClient("CONNECTION_URL")
db = cluster["UserData"]
collection = db["User"]

# Data i want to insert
@client.event
async def on_message(ctx): 
  if "python" in str(ctx.content.lower()):
    post = {"_id": ctx.author.id, "score": 1}
    collection.insert_one(post)
    await ctx.channel.send('accepted!')

#

oh np then. i’ll see

#

ye it works

#

cool.

#

📎 image0.png

#

:D

#

thanks

modest ledge Aug 23, 2020, 8:31 AM

#

um even after using aiosqlite im still getting "database is locked" how do I fix it

still pulsar Aug 23, 2020, 8:32 AM

#

Hello, is there a way to insert your own code into a running process using Python?

#

I'm trying to make it so that you press a button which triggers a function, causing code to be injected into a specific process.

#

And the code is compiled in DLL.

torn sphinx Aug 23, 2020, 9:10 AM

#

That question would perform better in some other channel.

#

um even after using aiosqlite im still getting "database is locked" how do I fix it
Dump the entire traceback here.

still pulsar Aug 23, 2020, 9:21 AM

#

Where should I ask this then? (Sorry, I'm kinda new to Python.)

modest ledge Aug 23, 2020, 9:28 AM

#

@torn sphinx

Traceback (most recent call last):
  File "C:\Users\User\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiosqlite\core.py", line 95, in run
    result = function()
sqlite3.OperationalError: database is locked```

torn sphinx Aug 23, 2020, 9:40 AM

#

This error code occurs when the user tries to perform two inappropriate operations on a database at the same detail and on the same database connection. This error code shows that an operation can’t be continued due to encounter with a transaction that uses the same database connection or the transaction that uses a distinct database connection by using a shared cache.

#

You'll have to aquire a Lock when making changes to the db.

#

!d asyncio.Lock

delicate fieldBOT Aug 23, 2020, 9:41 AM

#

`asyncio.Lock`

class asyncio.Lock(*, loop=None)```
Implements a mutex lock for asyncio tasks. Not thread-safe.

An asyncio lock can be used to guarantee exclusive access to a shared resource.

The preferred way to use a Lock is an [`async with`](../reference/compound_stmts.html#async-with) statement:

```py
lock = asyncio.Lock()

# ... later
async with lock:
    # access shared state
```  which is equivalent to:

```py
lock = asyncio.Lock()

# ... later
await lock.acquire()
try:
    # access shared state
finally:
    lock.release()
```   Deprecated since version 3.8, will be removed in version 3.10: The *loop* parameter.

*coroutine *`acquire`()[](#asyncio.Lock.acquire "Permalink to this definition") Acquire the lock.

This method waits until the lock is *unlocked*, sets it to *locked* and returns `True`.

When more than one coroutine is blocked in [`acquire()`](#asyncio.Lock.acquire "asyncio.Lock.acquire") waiting for the lock to be unlocked, only one coroutine eventually proceeds.... [read more](https://docs.python.org/3/library/asyncio-sync.html#asyncio.Lock)

torn sphinx Aug 23, 2020, 9:42 AM

#

Do not confuse between the locked state of the db and the aquired asyncio Lock

#

That error occurs when you are trying to make multiple changes to db at the same time

#

A lock makes sure that only one async task uses the db at a time

#

#Keep a permanent lock instance
db_lock = asyncio.Lock()
...
# When modifying the db aquire it
with db_lock:
    db.execute(...) # execute a query
    db.commit()

# Lock gets released

modest ledge Aug 23, 2020, 9:50 AM

#

i did this

lock = asyncio.Lock()

await lock.acquire()
try:
  #connecting
finally:
  #release```

#

@torn sphinx

Traceback (most recent call last):
  File "C:\Users\User\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\tasks\__init__.py", line 98, in _loop
    await self.coro(*args, **kwargs)
  File "g:\Omnisphere\TAHRIB\DJ T\test.py", line 64, in update_board
    lock = asyncio.lock()
AttributeError: module 'asyncio' has no attribute 'lock'
returning exception database is locked```

torn sphinx Aug 23, 2020, 9:51 AM

#

Lock

#

L

#

not l

modest ledge Aug 23, 2020, 9:52 AM

#

lock?

#

so lower case or upper case

torn sphinx Aug 23, 2020, 9:52 AM

#

upper

modest ledge Aug 23, 2020, 9:52 AM

#

lock = asyncio.Lock()

torn sphinx Aug 23, 2020, 9:52 AM

#

yes.

modest ledge Aug 23, 2020, 9:52 AM

#

exactly what i did

torn sphinx Aug 23, 2020, 9:52 AM

#

No I see a lower case l in the error

#

lock = asyncio.lock()

#

read the error

modest ledge Aug 23, 2020, 9:53 AM

#

lemme check

torn sphinx Aug 23, 2020, 9:53 AM

#

to make it more clear/readable/obvious, use db_lock for variable name, just a suggestion

modest ledge Aug 23, 2020, 9:53 AM

#

still this

  File "C:\Users\User\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiosqlite\core.py", line 95, in run
    result = function()
sqlite3.OperationalError: database is locked```

torn sphinx Aug 23, 2020, 9:54 AM

#

Show me where you are aquiring lock

#

and modifying the db

#

the code

modest ledge Aug 23, 2020, 9:54 AM

#

    if not message.author.bot:
        if message.channel.id != 712605489279533106:
            lock = asyncio.Lock()

            await lock.acquire()
            try:
                async with aiosqlite.connect("level.db") as db:

                    try:
                        await db.execute("""INSERT INTO users VALUES ("{}", "1", "0") """.format(message.author.id))
                        await db.commit()
                    except:
                        await db.execute("""UPDATE users SET exp = exp+1 WHERE user = "{}" """.format(message.author.id))
                        await level_up(message.author.id)
                        await db.commit()
            finally:
                lock.release()```

torn sphinx Aug 23, 2020, 9:55 AM

#

You are creating a new Lock object every time so it wont really have any effect

#

lock = asyncio.Lock() move this out in a more global scope

#

ideally a member of some class or even the global scope of the file

modest ledge Aug 23, 2020, 9:56 AM

#

ye i just did global

torn sphinx Aug 23, 2020, 9:56 AM

#

does it work? Can I see your code if not?

modest ledge Aug 23, 2020, 9:57 AM

#

i just put lock = asyncio.Lock() somewhere on the top of the script that should work right?

#

and no its not working

torn sphinx Aug 23, 2020, 9:58 AM

#

that should work unless you are creating another lock object and assigning it

#

Or maybe you've got sqlite already open on that db somewhere (can be some other process)

modest ledge Aug 23, 2020, 9:59 AM

#

📎 Capture.JPG

#

also, as much as i know aiosqlite should auto close connection everytime?

torn sphinx Aug 23, 2020, 9:59 AM

#

On linux you can use killall sqlite3 to make sure no other process of sqlite is using it

#

yes.

modest ledge Aug 23, 2020, 9:59 AM

#

im on windows

torn sphinx Aug 23, 2020, 10:00 AM

#

Open task manger and kill sqlite3

modest ledge Aug 23, 2020, 10:01 AM

#

i dont see sqlite3 there

torn sphinx Aug 23, 2020, 10:01 AM

#

try taskkill /IM sqlite3.exe or taskkill /IM sqlite.exe

modest ledge Aug 23, 2020, 10:01 AM

#

on command prompt?

torn sphinx Aug 23, 2020, 10:01 AM

#

ofc

modest ledge Aug 23, 2020, 10:02 AM

#

ERROR: the process "sqlite3.exe" not found.

torn sphinx Aug 23, 2020, 10:02 AM

#

so no other process it locking it, I'm running out of ideas to be honest

modest ledge Aug 23, 2020, 10:03 AM

#

ik this is so weird

#

do we get the same "database is locked" problem on MySQL or postgres?

torn sphinx Aug 23, 2020, 10:03 AM

#

are you closing db?

#

Not quite sure about that

modest ledge Aug 23, 2020, 10:04 AM

#

are you closing db?
@torn sphinx no cause docs said aiosqlite auto closes connection

torn sphinx Aug 23, 2020, 10:04 AM

#

no mysql and postgres is very more heavy duty

modest ledge Aug 23, 2020, 10:04 AM

#

should i try using mysql then?

torn sphinx Aug 23, 2020, 10:04 AM

#

postgres is better imo

modest ledge Aug 23, 2020, 10:04 AM

#

although it said mysql's performance is not as good as sqlite

#

i tried postgres but im having troubles

torn sphinx Aug 23, 2020, 10:05 AM

#

sqlite this is for lite performance

#

But I dont see why you wouldn't be able write to the db.

modest ledge Aug 23, 2020, 10:05 AM

#

ik thats weird

torn sphinx Aug 23, 2020, 10:05 AM

#

Are you using a with to open the db in aiosqlite?

modest ledge Aug 23, 2020, 10:05 AM

#

yes

#

async with

torn sphinx Aug 23, 2020, 10:06 AM

#

right.

modest ledge Aug 23, 2020, 10:06 AM

#

is that a problem?

torn sphinx Aug 23, 2020, 10:06 AM

#

nope

#

The other way would be a problem

#

it is probably open or used by other place inside code

#

and db lock is not released

modest ledge Aug 23, 2020, 10:06 AM

#

its used in many places inside the code but i also made the lock

torn sphinx Aug 23, 2020, 10:07 AM

#

you made asyncio.lock

#

db has own lock

#

They did Synchronize it though

#

Yes

modest ledge Aug 23, 2020, 10:07 AM

#

o

#

i guess ill just try postgres again, is there any docs or tutorials? also can we access postgres from a different computer?

torn sphinx Aug 23, 2020, 10:07 AM

#

I asked them to use a Synchronization lock to avoid multiple writes at the same time

#

what is use case if i can ask?

#

like what you are using for?

#

and how many write per second or minute

modest ledge Aug 23, 2020, 10:08 AM

#

experience

torn sphinx Aug 23, 2020, 10:08 AM

#

experience?

modest ledge Aug 23, 2020, 10:08 AM

#

about 2-3 per second

torn sphinx Aug 23, 2020, 10:08 AM

#

i guess ill just try postgres again, is there any docs or tutorials? also can we access postgres from a different computer?
Postgres is a server bassed db, yes you do it from "a different computer"

modest ledge Aug 23, 2020, 10:08 AM

#

like you get exp when you send a message

torn sphinx Aug 23, 2020, 10:08 AM

#

oh

#

i think now this is because you are experiencing more concurrency than sqlite can handle. So one call had lock, and another call like time out waiting for the lock the be released, which it didnt

modest ledge Aug 23, 2020, 10:09 AM

#

should i download postgres 13 BETA or 12.4

torn sphinx Aug 23, 2020, 10:09 AM

#

12.6

#

is fine

modest ledge Aug 23, 2020, 10:10 AM

#

i think now this is because you are experiencing more concurrency than sqlite can handle. So one call had lock, and another call like time out waiting for the lock the be released, which it didnt
@torn sphinx maybe

#

12.4*

#

theres no 12.6 my bad

torn sphinx Aug 23, 2020, 10:10 AM

#

This is still very bizzare in my opinion, I've never had such problems with sqlite, yes I've used aiosqlite

modest ledge Aug 23, 2020, 10:10 AM

#

and 13 is BETA so idk

torn sphinx Aug 23, 2020, 10:10 AM

#

also still even when you use postgres do not be writing to databse every 2/3 second for every experience. because is unncessary. you can instead make like batch writing.

#

Maybe you can do this also with aiosqlite as well

modest ledge Aug 23, 2020, 10:11 AM

#

batch writing?

#

lmao tbh ive been using JSON so far and it worked far better than sql

torn sphinx Aug 23, 2020, 10:11 AM

#

It will work with postgres, but is it real necessary for you to write every 2/3 second? Instead keep data change inside memory and make update for example every minute

modest ledge Aug 23, 2020, 10:11 AM

#

ye i can do that ig

torn sphinx Aug 23, 2020, 10:13 AM

#

Would that waste a lots of memory, in case of huge guild with thousands of members esp

modest ledge Aug 23, 2020, 10:13 AM

#

json is fine and fast, only it becomes a little bit slow when you try to sort using sorted considering there are around 10k+ users

torn sphinx Aug 23, 2020, 10:13 AM

#

json is not database

modest ledge Aug 23, 2020, 10:13 AM

#

ik

torn sphinx Aug 23, 2020, 10:13 AM

#

is not for storing data

modest ledge Aug 23, 2020, 10:13 AM

#

ye

torn sphinx Aug 23, 2020, 10:13 AM

#

but more for represent data

modest ledge Aug 23, 2020, 10:14 AM

#

ye that is the main reason im trying to move away from json

torn sphinx Aug 23, 2020, 10:14 AM

#

json is very bad choice here and very error prone

#

but you have 10k users so postgres is good. just make sure use asyncpg library with python

modest ledge Aug 23, 2020, 10:14 AM

#

basically when i started making this bot in the first place i didnt know how to use sql and didnt even bother to learn

#

but now i think i should move to sql so started learning sqlite but now all these happened lol

#

also question, by server based db that means if my db server is on my computer i have to keep my computer running to access it?

proven arrow Aug 23, 2020, 10:22 AM

#

Well that applies for any DB/program/data. To access it the computer system must be on.

torn sphinx Aug 23, 2020, 10:34 AM

#

Yo, how do I use fetchone in aiosqlite? I've seen the docs but I don't really understand.

proven arrow Aug 23, 2020, 10:36 AM

#

Execute your query and then do fetchone() on it?

#

db = await aiosqlite.connect(...)
cursor = await db.execute('SELECT * FROM some_table')
row = await cursor.fetchone()
await cursor.close()
await db.close()

From their docs

torn sphinx Aug 23, 2020, 10:36 AM

#

Hmmm

#

Wait

#

Maybe that is not what I am trying to do then

#

Let me show u

#

So

#

It's for my discord bot

#


@bot.command()
async def warn(ctx, member : discord.Member, *, reason=None):
    async with aiosqlite.connect("C:/Yoha/files/mod_logs.db") as db:
        #await db.execute("CREATE TABLE warn_logs (user_id integer, mod_id integer, reason str, case_id integer PRIMARY KEY AUTOINCREMENT)")
        await db.execute("INSERT INTO warn_logs (user_id, mod_id, reason) values (?, ?, ?)",
                        (member.id, ctx.message.author.id, reason))
        await db.commit()
        getid = await db.fetchone("SELECT case_id FROM warn_logs")
        await ctx.send(f"{getid}")

#

I am trying to print out the case id

proven arrow Aug 23, 2020, 10:38 AM

#

You would need to execute the query again, then fetchone

torn sphinx Aug 23, 2020, 10:38 AM

#

Alright

#

Can you help me doing that?

#

Oh wait

#

Execute the query

#

What's a query?

proven arrow Aug 23, 2020, 10:39 AM

#

SELECT case_id FROM warn_logs is your query. It is when you are querying the database

torn sphinx Aug 23, 2020, 10:39 AM

#

Right

#

Alright so

#

I put this

#

Or I put that

#

Into a variable

#

And then I do await db.fetchone(variableName)

#

?

proven arrow Aug 23, 2020, 10:40 AM

#

See the example i sent https://discordapp.com/channels/267624335836053506/342318764227821568/747041604614881383

torn sphinx Aug 23, 2020, 10:41 AM

#

Oh yeah

#

@proven arrow uhhhh

#

It kinda works

#

So uhhh

#

It prints out the case number 8 the first time I run it

#

Which is what it's supposed to do

#

But when I do it again

#

It prints 8 again

#

When it's supposed to print 9

#

And it says 9 in my db

proven arrow Aug 23, 2020, 10:47 AM

#

Because in your query your just saying SELECT case_id FROM warn_logs. This will return all the records from warn_logs. And then you are doing fetchone which gets the first row from these results.

torn sphinx Aug 23, 2020, 10:47 AM

#

OHHHHHH

#

So I use fetchall

#

And I do

#

[0]+1

#

?

#

Or sum?

proven arrow Aug 23, 2020, 10:48 AM

#

What data do you want?

torn sphinx Aug 23, 2020, 10:48 AM

#

Start off with the case id

proven arrow Aug 23, 2020, 10:49 AM

#

How do you mean? Which case ID?

torn sphinx Aug 23, 2020, 10:52 AM

#

Oh

#

Uhhh

#

The one of the warning

#

Like

#

The db looks like this

📎 unknown.png

#

And if I make a warning

#

The case id is 1

#

And if I make another one

#

It is 2 for that warning

#

3, 4, 5

#

And so on

#

So I want it to print out

#

The case id

#

Of the warning

#

I just did

proven arrow Aug 23, 2020, 10:55 AM

#

If you mean the ID of the INSERT you in your command, then you can use execute_insert which "inserts and get the last_insert_rowid". https://aiosqlite.omnilib.dev/en/stable/api.html#aiosqlite.Connection.execute_insert

Or you can use the Cursor.lastrowid property which will give you this ID as well. https://aiosqlite.omnilib.dev/en/stable/api.html#aiosqlite.cursor.Cursor.lastrowid

And then use this data to make your query.

#

Is case_id your PK?

torn sphinx Aug 23, 2020, 11:16 AM

#

@proven arrow Sorry, I totally forgot about this

#

Someone DMed me

#

And I just forgot

#

Sorry

#

Yes, case_id is my PK

modest ledge Aug 23, 2020, 12:29 PM

#

is mongoDB a good library?

tepid cradle Aug 23, 2020, 12:39 PM

#

MongoDB is not a library, it's a DBMS

#

It's probably the most popular NoSQL Db right now, so it definitely has some good qualities

#

@modest ledge What's your usecase? The suitability depends a lot on that

modest ledge Aug 23, 2020, 12:48 PM

#

like giving users "exp" based on the messages they send and store them

harsh pulsar Aug 23, 2020, 2:09 PM

#

i always just recommend sqlite as the default database

eternal raptor Aug 23, 2020, 2:12 PM

#

I have correct database and table.

Ignoring exception in on_guild_join
Traceback (most recent call last):
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\client.py", line 312, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\user\Desktop\Zonnerty\cogs\bot_jal.py", line 17, in on_guild_join
    await db.execute("INSERT INTO guilds_general (GuildID, eco_char, prefix_char, powitanie, pozegnanie, pow_ch_id, poz_ch_id) VALUES (?, NULL, NULL, NULL, NULL, NULL, NULL) ", (gildia,))
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\aiosqlite\core.py", line 236, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\aiosqlite\core.py", line 185, in _execute
    return await future
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\aiosqlite\core.py", line 171, in run
    result = function()
sqlite3.OperationalError: no such table: guilds_general

    @commands.Cog.listener()
    async def on_guild_join(self, guild):
        BASE_DIR = os.path.dirname(os.path.abspath(__file__))
        db_path = os.path.join(BASE_DIR, "zonnerty.db")
        gildia = guild.id
        async with aiosqlite.connect(db_path) as db:
            await db.execute("INSERT INTO guilds_general (GuildID, eco_char, prefix_char, powitanie, pozegnanie, pow_ch_id, poz_ch_id) VALUES (?, NULL, NULL, NULL, NULL, NULL, NULL) ", (gildia,))
            await db.commit()
            print("ok 1.1")
            print(gildia)

tepid cradle Aug 23, 2020, 2:13 PM

#

Yeah, for a simple usecase like this, sqlite is better

like giving users "exp" based on the messages they send and store them
@modest ledge

harsh pulsar Aug 23, 2020, 2:15 PM

#

heck you could even use a key-value database for that, just mapping (userid, guildid) -> number of messages

#

but yeah hard to go wrong with sqlite

eternal raptor Aug 23, 2020, 2:16 PM

#

Is it to me?

harsh pulsar Aug 23, 2020, 2:16 PM

#

no, the other person

eternal raptor Aug 23, 2020, 2:16 PM

#

ok, sorry

harsh pulsar Aug 23, 2020, 2:16 PM

#

your question i dont have an answer for. clearly the table doesn't exist in that database

eternal raptor Aug 23, 2020, 2:16 PM

#

if you could help me too, i would be grateful 🙂

harsh pulsar Aug 23, 2020, 2:17 PM

#

can you open the database file with the sqlite3 command line tool and confirm that the table indeed exists with that name?

eternal raptor Aug 23, 2020, 2:17 PM

#

how?

harsh pulsar Aug 23, 2020, 2:18 PM

#

where is the database file stored?

eternal raptor Aug 23, 2020, 2:18 PM

#

C:\Users\user\Desktop\Zonnerty\zonnerty.db

harsh pulsar Aug 23, 2020, 2:19 PM

#

oh you're on windows

#

are you using a virtualenv/venv?

#

(you should be, one for each project)

eternal raptor Aug 23, 2020, 2:20 PM

#

no, i don't use this.

harsh pulsar Aug 23, 2020, 2:20 PM

#

oh. you should.

#

do you use pip install --user at least?

eternal raptor Aug 23, 2020, 2:20 PM

#

em... no xD

harsh pulsar Aug 23, 2020, 2:20 PM

#

alright. for your own sanity, use a venv.

#

anyway for now just do pip install litecli

#

actually wait

#

do you have DB Browser?

eternal raptor Aug 23, 2020, 2:21 PM

#

yes, i have it

harsh pulsar Aug 23, 2020, 2:21 PM

#

https://sqlitebrowser.org/

DB Browser for SQLite

#

ok, open the file with dbbrowser

#

and see if the table is actually there

#

e.g. maybe you forgot to commit when you created the table

eternal raptor Aug 23, 2020, 2:22 PM

#

📎 1f13412f4.PNG

harsh pulsar Aug 23, 2020, 2:22 PM

#

hm

#

import sqlite3
conn = sqlite3.connect(r'C:\Users\user\Desktop\Zonnerty\zonnerty.db')
print( conn.execute('select * from guilds_general').fetchone() )

if you run this in python, what does this show?

eternal raptor Aug 23, 2020, 2:24 PM

#

None

harsh pulsar Aug 23, 2020, 2:24 PM

#

but it doesnt give an error right?

eternal raptor Aug 23, 2020, 2:24 PM

#

because in this i have nothing

harsh pulsar Aug 23, 2020, 2:24 PM

#

but no error?

eternal raptor Aug 23, 2020, 2:25 PM

#

📎 2r33g5f2f3.PNG

#

PS C:\Users\user> python -u "c:\Users\user\Desktop\Zonnerty\sa.py"
None

#

it's an output from the terminal.

#

sorry, but i must change on mobile.

harsh pulsar Aug 23, 2020, 2:26 PM

#

ok, good

#

that means the table does exist

#

i dont know why your discord bot thinks the table doesnt exist

#

make sure you spelled the filename correctly

eternal raptor Aug 23, 2020, 2:29 PM

#

Sorry, but i will be active for 2 hours.

cinder dome Aug 23, 2020, 2:30 PM

#

Basically whenever I invoke the !goodbye text <my_text> it sends the embed saying it set the new text, but then when I do !goodbye current it just shows the default message I set it to, and not the new message

It’s supposed to update the msg column from the goodbye table, which is what I made it do in the goodbye_text but for some reason its not showing the new message (current message) when I do !goodbye current So it’s either the msg column not updating or the current command isn’t done right, thats what Idk

The function for goodbye_text and get_g_text is around line 107, and the two text commands start at line 89

Code: https://mystb.in/PeripheralsStoppingTwins.python
database.py: https://mystb.in/PlaysYesCorrected.python

modest ledge Aug 23, 2020, 2:34 PM

#

unlike sqlite, can mysql handle multiple

Yeah, for a simple usecase like this, sqlite is better
@modest ledge
@tepid cradle sqlite is fucking up and im getting database is locked

tepid cradle Aug 23, 2020, 2:35 PM

#

Yeah, if you can setup mysql, that should work better. Go with Mariadb, the setup is really simple

modest ledge Aug 23, 2020, 2:35 PM

#

i got a hosting for mysql and seems like it takes a 1-2 seconds just to connect, is that normal?

tepid cradle Aug 23, 2020, 2:36 PM

#

No. That's a problem with the host I'd say

#

Maybe try installing on your local machine, just to give it a shot

#

Which OS do you use?

cinder dome Aug 23, 2020, 2:36 PM

#

If anyone replies ping me

modest ledge Aug 23, 2020, 2:37 PM

#

um im not quite sure what OS it is tbh but i think Linux

tepid cradle Aug 23, 2020, 2:38 PM

#

No, I meant your local machine

modest ledge Aug 23, 2020, 2:38 PM

#

oh windows

#

its actually a site giving free hosting for mysql

#

not sure if thats a good idea but my credit card is fucked up rn so i cant get a paid one

tepid cradle Aug 23, 2020, 2:41 PM

#

Then install Mariadb on your PC to get started. Use the hosted service only when you need.
In any case, you don't have to connect every time, once connected, you can just get a cursor for each transaction

modest ledge Aug 23, 2020, 2:42 PM

#

thats also a problem that i cant self-host the db

#

so i guess the best idea is to like connect once when the bot starts up and stay connected also idk if that'll consume too much RAM or if it even has to do anything with ram

tepid cradle Aug 23, 2020, 2:43 PM

#

That shouldn't be a problem. You can keep the connection alive

modest ledge Aug 23, 2020, 2:43 PM

#

ye awesome, and it takes like only 2 seconds to connect

#

also unlike sqlite i believe mysql can handle multiple writings at the same time?

tepid cradle Aug 23, 2020, 2:44 PM

#

Yes, it can. Shouldn't be a problem.

modest ledge Aug 23, 2020, 2:45 PM

#

awesome

tepid cradle Aug 23, 2020, 2:45 PM

#

Although, if your DB is more write, less read, PostgreSQL is a better choice, technically

#

But MySQL should be fine for a bot

modest ledge Aug 23, 2020, 2:46 PM

#

technically yeah but i couldnt find a free hosting for postgreSQL

tepid cradle Aug 23, 2020, 2:47 PM

#

Heroku is there, but not very useful, only 10000 rows

modest ledge Aug 23, 2020, 2:50 PM

#

nah not useful, my server has 10k+ users

#

theres a free one for postgre but it only offers 20MB for free, would that be good enough?

torn sphinx Aug 23, 2020, 2:54 PM

#

What's the best library to connect 2 mongod ?

cinder dome Aug 23, 2020, 2:57 PM

#

Basically whenever I invoke the !goodbye text <my_text> it sends the embed saying it set the new text, but then when I do !goodbye current it just shows the default message I set it to, and not the new message

It’s supposed to update the msg column from the goodbye table, which is what I made it do in the goodbye_text but for some reason its not showing the new message (current message) when I do !goodbye current So it’s either the msg column not updating or the current command isn’t done right, thats what Idk

The function for goodbye_text and get_g_text is around line 107, and the two text commands start at line 89

Getting this error: https://mystb.in/CorrectedAccessedSandra.sql
Code: https://mystb.in/PeripheralsStoppingTwins.python
database.py: https://mystb.in/PlaysYesCorrected.python

#

Ping if you reply please

sinful condor Aug 23, 2020, 3:06 PM

#

Hey I need some help I have this line giving me an error ```await db.execute("INSERT INTO level(userguild, xp, level)", (userguild, sxp, slevel))`````` Ignoring exception in on_message
Traceback (most recent call last):
File "C:\Users\runne\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\client.py", line 312, in _run_event
await coro(*args, **kwargs)
File "main.py", line 129, in on_message
await update_data(message.author)
File "main.py", line 145, in update_data
await db.execute("INSERT INTO level(userguild, xp, level)", (userguild, sxp, slevel))
File "C:\Users\runne\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiosqlite3\utils.py", line 147, in await
resp = yield from self._coro
File "C:\Users\runne\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiosqlite3\connection.py", line 126, in _execute
future = yield from self._loop.run_in_executor(
File "C:\Users\runne\AppData\Local\Programs\Python\Python38-32\lib\concurrent\futures\thread.py", line 57, in run
result = self.fn(*self.args, **self.kwargs)
sqlite3.OperationalError: incomplete input

harsh pulsar Aug 23, 2020, 3:18 PM

#

what database library is this?

#

oh aiosqlite

modest ledge Aug 23, 2020, 3:18 PM

#

how do i know if a row exists or not

harsh pulsar Aug 23, 2020, 3:18 PM

#

@sinful condor ```python
await db.execute("INSERT INTO level (userguild, xp, level) VALUES (?, ?, ?)", (userguild, sxp, slevel))

modest ledge Aug 23, 2020, 3:19 PM

#

like if i have a row with someone's user ID i'd wanna update it and if not then insert that data

harsh pulsar Aug 23, 2020, 3:19 PM

#

@modest ledge if you want to know if the row exists you can use a SELECT query. but you don't have to explicitly check for existence, you can use a technique called "upsert" ("update" + "insert")

#

what database are you using?

modest ledge Aug 23, 2020, 3:20 PM

#

mysql

harsh pulsar Aug 23, 2020, 3:20 PM

#

ugh, mysql

#

ok let me see how to do it in mysql

modest ledge Aug 23, 2020, 3:20 PM

#

thanks lol

harsh pulsar Aug 23, 2020, 3:25 PM

#

@modest ledge https://www.db-fiddle.com/f/guvL7XAVBUrtHknCCU6n2a/0

DB Fiddle - SQL Database Playground

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

#

as long as the id column has a UNIQUE constraint, this will work

#

in this case i used PRIMARY KEY

modest ledge Aug 23, 2020, 3:25 PM

#

ye its a primary key

harsh pulsar Aug 23, 2020, 3:26 PM

#

im not exactly sure if you can do the SET from python, you might just have to use a parameterized query with duplicated parameters

#

what mysql library are you using?

modest ledge Aug 23, 2020, 3:26 PM

#

mysql.connector

#

what does set do

harsh pulsar Aug 23, 2020, 3:28 PM

#

query = '''
INSERT INTO users VALUES (%(id)s, %(display_name)s)
ON DUPLICATE KEY UPDATE display_name=%(display_name)s;
'''

data = {
    'id': user_id,
    'display_name': user_display_name,
}

cursor.execute(query, data)

#

@modest ledge ^

#

(updated)

modest ledge Aug 23, 2020, 3:29 PM

#

i did like this before


c.execute("""INSERT INTO users (user, exp, lvl) VALUES ("123", "34", "32") ON DUPLICARE KEY UPDATE (SET exp = exp + 1 WHERE user="123") """)```

#

the values rn i just directly used strings cause im still testing will format them later on the main file

#

also thats not working lmao

harsh pulsar Aug 23, 2020, 3:30 PM

#

you have the wrong syntax

#

you spelled DUPLICATE wrong, and mysql doesn't use the "SET" like postgres does

#

the fiddle link i sent is correct

modest ledge Aug 23, 2020, 3:32 PM

#

c.execute("""INSERT INTO users (user, exp, lvl) VALUES ("123", "34", "32") ON DUPLICATE KEY UPDATE exp = exp + 1""")```

#

theres no error but it didnt update it

harsh pulsar Aug 23, 2020, 3:34 PM

#

hm