databases

[torn sphinx]

and in the right side, give it a value of 0

and click apply

then write changes

[clear reef]

okio

[torn sphinx]

once you do that in db structure, right click warns, and modify tavke

add a default for cleared

ok

write changes

close and open as readonly

[torn sphinx]

give cleared a default value

[clear reef]

Ohhhh

there

[torn sphinx]

write changes

close and open as readonly

[clear reef]

yes!

did

[torn sphinx]

:>

try your warn command again

[torn sphinx]

:3

[clear reef]

now

question

i want staff to be able to do like "p.warnings @clear reef" and check their warnings

[torn sphinx]

yup

[clear reef]

what is the variable for the warning amount

[torn sphinx]

there isnt one

you fetch them

and count them,

[clear reef]

oh

hooow

such advanced

[torn sphinx]

    async def get_warn_records(self, user_id: int) -> Optional[List[Record]]:
        results = await (await self.conn.execute("select * from warns where user=?", (user_id,))).fetchall()
        if not results:
            return None
        return [Record(staff=r[2], user=r[1], id=r[0], timestamp=r[3],
                       cleared=r[5], reason=r[4], typ="W") for r in results]

in my case, Record is a dataclass but you dont need it if you dont wanna make one ;p

you'd have something like

records = []
async with aiosqlite.connect(...) as db:
  async with db.execute("select * from warns where userid=?", (user_id,) as cursor:
    async for row in cursor:
      records.append(row)
for warn in records:
  # warn[0] = warn id
  # warn[1] = user
  # warn[2] = staff
  # warn[3] = reason

[clear reef]

[torn sphinx]

edited

[clear reef]

still confused

[torn sphinx]

thats the code that would get warnings for a user

user_id being the user's id

[clear reef]

eh

okay i guess

[torn sphinx]

well is there anything specific you dont get about it?

[clear reef]

id like to just simply put the numbers in an embed

such as {len(bot.guilds)} would do

[torn sphinx]

ok so in this case, len(records)

[clear reef]

okio

this part confuses me

[torn sphinx]

thats if you wanted to iterate thru your records

to show individual warnings

[clear reef]

ok so in this case, len(records)
@torn sphinx would this be len(member.records) ?

[kind edge]

I am currently trying to work with microsoft access via python using the pyodbc library, and for some reason I am getting: Could not update; currently locked by user 'admin' on machine
Despite the fact that I am the admin of the db and there is no password on it

any ideas

[torn sphinx]

nope, just records

[clear reef]

to the mentioned person?

[torn sphinx]

you'd have something like

records = []
async with aiosqlite.connect(...) as db:
  async with db.execute("select * from warns where userid=?", (user_id,) as cursor:
    async for row in cursor:
      records.append(row)
for warn in records:
  # warn[0] = warn id
  # warn[1] = user
  # warn[2] = staff
  # warn[3] = reason

you define records here

this code goes inside your command to view warns

@kind edge not familiar with the lib, but that seems like you have the db open somewhere else

[clear reef]

heeh?

[torn sphinx]

the code i sent goes inside the command to view your warns

so like

[kind edge]

The only place it could be open is in access and I don't have the program operating

[clear reef]

it goes inside my DB

[torn sphinx]

huh

no like

@bot.command()
async def warns(ctx, member: discord.Member):
  records = []
  async with aiosqlite.connect(...) as db:
    async with db.execute("select * from warns where userid=?", (member.id,)) as cursor:
      async for row in cursor:
        records.append(row)
  last = records[-1]
  timestring = datetime.datetime.utcfromtimestamp(last[4]).strftime('%b %d %y %H:%M:%S')
  await ctx.send(f"{member.mention} has {len(records} warnings. The last warning was {last[3]} given by <@{last[2]}> at {timestring}")

@clear reef

[clear reef]

hm

lets try

"as" isnt a syntax

[torn sphinx]

change userid in the query to whatever your column is (iser)

and oop forgot a )

there

[clear reef]

change userid in the query to whatever your column is (iser)
@torn sphinx ?

[torn sphinx]

(user)*

whatever column in your table that holds the user's ID

in the execute

[clear reef]

i see

uh huh

my console said nothing

its the time

[torn sphinx]

hm

i might have the timestamp wrng

[clear reef]

oh?

[torn sphinx]

print(last)

as soon as you assign it

[clear reef]

?

[torn sphinx]

last = records[-1]

do\

  last = records[-1]
  print(last)

[clear reef]

remove the timestring?

[torn sphinx]

no

add the print

i think when i did last[4]

[clear reef]

still gives the same error

[torn sphinx]

the 4 should be changed

yea but what did it print

[clear reef]

ohhh

[torn sphinx]

ook

so

use last[3] in timestring

and use last[2] instead of last[3], and so on in the ctx.send

[clear reef]

oko

Now just make the embed cleaner

[torn sphinx]

:D

[clear reef]

How do I display all the warnings

for the current user

[torn sphinx]

welp

https://pypi.org/project/disputils/

this lets you page thru warnings

[clear reef]

o

[torn sphinx]

btw you should probably order by timestamp desc in the execute

[clear reef]

eh

[torn sphinx]

well that way you get the warnings in newest-first order

[clear reef]

a hold on let me make the embed tidy

[torn sphinx]

alr

[clear reef]

where is the bad indent its telling me i have @torn sphinx

[torn sphinx]

embed.set_author

and anything after

[clear reef]

how

oh

wait

yeah how

[torn sphinx]

embed.set_author should be at thesame level as embed= ...

[clear reef]

its telling me there is a invalid f string

tf

thats just to confirm that my stuff loads

[torn sphinx]

nah thats not invialid

it's just vsc freaking out about the spelling

[torn sphinx]

:o

idk

[clear reef]

does it look correct now

[torn sphinx]

ye

[clear reef]

strange....

now its just

loafing with me here

[torn sphinx]

restart vsc?

uh

not sure

[clear reef]

oh ffs

same thing

[torn sphinx]

you might need to go ask in #tools-and-devops if its a vsc issue

[clear reef]

wow annoying aaaaa

[ripe glacier]

probably a dumb question, but I created an application that uses a sqlite3, it works as intended on my pc, but when I transferred it to a google cloud VM instance it just doesn't want to read data from it

I'm probably doing something stupid aren't I?

[torn sphinx]

well @clear reef i have to go, but imma leave you with
https://github.com/united-minecrafters/kaede/blob/master/libs/utils.py#L67
https://pypi.org/project/disputils/
https://www.geeksforgeeks.org/break-list-chunks-size-n-python/ (first link does this)
https://github.com/united-minecrafters/kaede/blob/master/cogs/administration/moderation.py#L84

that contains everything you need to make fancy pages of warnings

[clear reef]

oki thanks!

[torn sphinx]

np :3

[clear reef]

now to doing everything again.... on my raspberry pi....

wait no

not possible

[wanton storm]

Hello all, I am trying to think what is the best approach to solve this problem. I need to create a table that upon selecting some items will refer to other tables of those specific items to generate some calculations to be displayed on the main table. Furtheremore, if wanted these other tables used to calculate the main table should be accessible and show calculations performed on a data base or the lower heriarchy table. so It should be something like main table->table 1-> table2-> table 3. each table + some other data will be used to calculate the table above. I did some research and using the datatable library seems to do it but if there are other ways I am happy to hear. also let me know if this makes sense.

[tough sluice]

@wanton storm
You can link your tables by using foreign key.

[wanton storm]

@wanton storm
You can link your tables by using foreign key.
@tough sluice Thank you

[broken night]

!close

[earnest parcel]

[upbeat lily]

I'm on postgres 8.5, is there a simple query I can do to insert multiple rows and ignore duplicates?

[torn sphinx]

Hey can I get some help?

[upbeat lily]

just ask

and hope someone will come along and answer

[torn sphinx]

How exactly would I go about calling certain values from a row and displaying it in an embed.add_field

My example code is something like this: ```py
for row in rows:
ab = row.id
abc = row.ect
ect = row.ect

embed.addfield(title=f"{row.itemname}", description=f"Description: {row.description}, Buying Price: {row.bprice} | Selling Price: {row.sprice} ")```

Can I do something like

for row in rows:
 ab = sql.execute("select ProductID from row")
 ab = ab[0]
 ``` ?

[upbeat lily]

from a high level, what you'd want to do is run some SQL to select all the products you currently want. Something like

SELECT * FROM products WHERE on_sale=True

Then in Python, grab the result of that query, and iterate through it.

Here's a page with the basic SQL you'd want https://www.w3schools.com/python/python_mysql_select.asp

[torn sphinx]

Thank you

I worked ito ut

for anyone else: You can refer to a column in a row using row[0] (thats the first column) and you can keep going like row[1] for itemname and ect.

[pearl heath]

I can feed flask-sqlalchemy a key:value for a lookup instead of a query in the form

    pubchem_search_types = {"cid","iupac_name","cas"}
    if id_of_record in pubchem_search_types:
        kwargs  = { id_of_record : entity}
        lookup_result  = Compound.query.filter_by(**kwargs ).first()

instead of :

lookup = Compound.query.filter_by(cid = cid_passed).first()`

and it will work right? I can't seem to find any documentation on it but it works for cid but not iupac_name with the following values { 420 : 'cid' } and { 'methanol' : 'iupac_name' }

[torn sphinx]

What should be my database model for a check-in system. At the end of the week, I'd like to know if a person checked-in during a week-day or not. Any tips would be highly appreciated

[upbeat lily]

Probably at least two tables. Something like
guest(customer_number, name, address)
check_ins(cutomer_number*, date, room_number, length_of_stay)

[modest ledge]

can anyone pls help me to set up sqllite

i need examples/tutorials

[hazy mango]

https://www.tutorialspoint.com/sqlite/sqlite_python.htm this is for py2 but the actual sqlite stuff is the same, lemme see if I can find a more recent one

[modest ledge]

thanks :)

[hazy mango]

I mean there's a mini-tutorial on the actual docs https://docs.python.org/3/library/sqlite3.html

https://www.sqlitetutorial.net this seems to be a pretty good tutorial for sqlite itself

@modest ledge

[modest ledge]

thx

[stoic cipher]

Hello guys

how do i get the computer to get to say something

pls

help

[modest ledge]

wdym

[stoic cipher]

I mean the print function

[modest ledge]

print()?

not something to be on this channel

import sqlite3
db = sqlit3.connect("data.db")```

i have to save the data.db in the same directory right?

and what are these

# Create table
c.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

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

[clear reef]

Now I'm quite new to Linux. And I'm unsure if ```py
os.chdir(os.path.dirname(sys.argv[0]))

works on Linux.

I suppose it doesnt?

[modest ledge]

no such file or directory @clear reef

make sure the file name you put actually exists

[clear reef]

im very sure it exists when its digitally infront of me

[unique gulch]

Are you trying to get the dirname that the current executing script is in?

If so you should probably call dirname on the absolute path of __file__

The working directory where Python is running in is probably not what you are thinking it is

[keen sundial]

@upbeat lily

I'm on postgres 8.5, is there a simple query I can do to insert multiple rows and ignore duplicates?
no. you can upsert to change existing rows, but either the whole operation succeeds or the whole operation fails. having individual rows failing is not delightful. 🙂

at least that's what i think; maybe it has a feature to do this. 🤔

[upbeat lily]

I've solved my problem now - but thanks for trying to help. Problem was that I was on Redshift, and Redshift has no real concept of constraints

[strong fable]

I want to get the 10 highest values using mongodb

In my discord bot, I assign levels to everyone

And users level up according to levels

I am creating a function that will show the top 10 users with the highest levels

[round isle]

Guys, what is the command for generating all the tables in the database from models in django

[upbeat lily]

@round isle you need to run the migrations. The actual command is python manage.py migrate

but

you're probably missing some step or other

https://docs.djangoproject.com/en/3.0/topics/migrations/ there's the relevant page of the docs

[coarse dust]

I'm using SQLAlchemy with alembic for migrations - how do i generate a new database from the migrations? that always seems to throw me off a bit.

using sqlite in this case

[blazing void]

a whole new database?

I'm not sure, as I've not done this with sqlite before, with regular databases you usually don't end up with a whole new database

I usually stick with updating my ORM and then doing an alembic revision --autogenerate -m "blah"

and then the CI/CD does alembic upgrade head on this

I suppose if you're trying to create a whole new SQLite file, maybe you can duplicate/backup the sqlite file first, and then on one of the duplicates run the upgrade head

[coarse dust]

@blazing void yeah, i'd need the whole new database. but i guess if it doesn't exist i can generate it from the tables first - but then i'd be lacking the alembic revision in the database

this is an application that gets deployed to multiple devices, so new devices will generate the database table on the first time they're run. but i guess i could ship the application with the initial revision of the database maybe?

hah. nevermind

alembic upgrade head generates it anyways it seems like

[blazing void]

yep

[cursive ibex]

I'm making a search thing (PostgreSQL). I want to search my database for any entries where a specific column's value is similar to a provided string. I tried using LIKE, but it doesn't work very well... it's even case sensitive. Any ideas how I can do this with PostgreSQL?

The closest thing I can find is ~* which is pretty great, wondering if there was anything better?

[blazing void]

if you're looking for text search, something like Elasticsearch is better

but more often than not, you'd use the two together - postgresql for you relational data, and elasticsearch for your full text searching

[torn sphinx]

hello im doing with sqlite3

c.execute("UPDATE guild SET guildID = ?, privateCategoryID = ? WHERE guildID = ?", (guildID, new_category.id, guildID))
``` im trying to update the database

but the privateCategoryID not updating

i tried to print the new_category.id and its works fine

nvm i forgot to commit the changes

[torn sphinx]

    Customer_ID int(4) not null auto_increment, 
    CustName char(100) not null, 
    CustAddress char(100) not null, 
    CustEmail char(50) not null,
PRIMARY KEY(Customer_ID));

CREATE TABLE Orders (
    id INT(4) NOT NULL auto_increment,
    Customer_ID INT(4) NOT NULL,
    Stock_ID INT(4) NOT NULL,
    Orders_date DATE NOT NULL,
    Orders_status CHAR(10) NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID),
    FOREIGN KEY (Stock_ID) REFERENCES Customers(Stock_ID)
);```

i keep getting cannot add foreign key constraints

[mossy imp]

just a stupid question from my side Kradux

should Stock_ID not be defined in your Customers table?

@torn sphinx

[torn sphinx]

Assuming stock ID is the product ID the initial "id" should be enough.

Unless he means the amount of stock

[mossy imp]

I am not sure here because with a foreign key you make a reference key to a key in another table

The first foreign key has a reference to a Customer_ID defined in the Customers Table

whereas the second foreign key is referring to what?

[tepid cradle]

I'm making a search thing (PostgreSQL). I want to search my database for any entries where a specific column's value is similar to a provided string. I tried using LIKE, but it doesn't work very well... it's even case sensitive. Any ideas how I can do this with PostgreSQL?
@cursive ibex postgresql supports full text search. here's a guide on how to use it https://www.compose.com/articles/mastering-postgresql-tools-full-text-search-and-phrase-search/

You can also use regex or even fuzzy matching within Postgres. I have implemented a search function on my project and was able to achieve very good results using these tools. So I'd recommend trying these out before moving to something like elastisearch.

Also, the keyword for case-insensitive like is ilike

[midnight laurel]

would anyone familiar with asyncpg be willing to do a code review for part of a wrapper i've written? i'm trying to get better at writing a good wrapper for my own user but i'm not exactly sure how to construct it or write it overall

https://pastebin.com/fyiHv9DR

this is the code i've written so far

don't have the whole thing implemented, but it should give a general idea

[feral spruce]

any good site to start learning database???

ty

[brazen charm]

@midnight laurel it's okay, I would however say you need to wrap everything in transactions because at the moment from the looks you have no safe guard and roll backs implemented if something goes back

[delicate bluff]

are databases just code that auto generates data to store in the base or is it just code and does not require extra storage for each user.

for a dpy database

[torn sphinx]

hello

i have a question about database anyone willing to help me

[grim lotus]

!ask

[delicate field]

Asking good questions will yield a much higher chance of a quick response:

• Don't ask to ask your question, just go ahead and tell us your problem.
• Don't ask if anyone is knowledgeable in some area, filtering serves no purpose.
• Try to solve the problem on your own first, we're not going to write code for you.
• Show us the code you've tried and any errors or unexpected results it's giving.
• Be patient while we're helping you.

You can find a much more detailed explanation on our website.

[round isle]

Yes, that's the command I was looking for! thanks @upbeat lily!

[torn sphinx]

im trying to make a discord bot with a lotto command of joining a lottory

when i open my database i dont see anything nothing at all

[gloomy pilot]

    edit_code = int(input("Enter the product code of the item you would like to edit:"))
    edit_cat =  input("Enter the category of the item you would like to edit:")
    edit_val =  int(input("Enter the new value"))
    edit = """UPDATE products SET %s = %s where prod_code = %s"""
    cur.execute(edit,(edit_cat,edit_val,edit_code,))
    connector.commit()

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' prod_code' = 1 where prod_code = 1231' at line 1

This is the error I get

[indigo dawn]

do i need to make a class for every table i want to query using sqlalchemy?

[runic pilot]

you don't need to, though it's nice to have a python class represent that

you can use sqlalchemy to connect to your db and execute statements directly, though I don't know why you would use sqlalchemy for that instead of the db driver alone

[indigo dawn]

ahh phew https://stackoverflow.com/questions/60258576/connect-to-existing-tables-in-database-using-sqlalchemy

i'll give the metadata a go

wait if you're making classes do you need to define the KVPs?

[runic pilot]

what's a kvp?

[blazing void]

I assume key-value pairs, but I feel that may be the wrong term to use for sqlalchemy and relational databases

[torn sphinx]

which is the easy of converting asyncpg Record results into a dict?

For example, i get this when I do conn.fetch(...)

[
<Record level_id=13 guild_id=2343243243245345 user_id=2343243243245345 xp=6300 level=7 messages=575 timestamp=datetime.datetime(2020, 7, 15, 3, 52, 48, 16060)>, 

<Record level_id=14 guild_id=111111111111111111 user_id=11111111111111111 xp=1248 level=3 messages=156 timestamp=datetime.datetime(2020, 7, 13, 0, 57, 43, 35675)>
]

It is list of record entries from query, but how to convert to a dict easily?. My goal is to try to make local cache of this data and update this instead of make db query each time.

[torn sphinx]

how

do you calculate IOPS for a database, and what does it depend on

[torn sphinx]

Can anyone explain what are nested queries in MySQL?

[blazing void]

you can make a SELECT query, and then the results of that becomes a temporary table that you can use inside other queries

[torn sphinx]

hello how do i check if a table or database exists

is there a cod

[midnight laurel]

@brazen charm okay, thanks!

[mossy imp]

@torn sphinx He man, some updates on your problem with the foreign key?

[hallow umbra]

hi peep @placid hare pythoners, i am using flask_mysqldb to do a small project , can someone assist me?

cant seem to be able to submit, receiving bad request error

[wanton storm]

Hello guys, what is the best way or library to construct a table GUI where the user inputs values on the table and I it outputs calculations based on those input values in another table?

[torn sphinx]

Hi
is sqlite3, that comes by default in django, good for building websites?
i know that it isnt optimal if multiple users are going to use the site to write in the db, but in my case there is only going to be one maybe 2 persons using it as admins, so i imagine it should be ok.
What's your opinion, are there any other drawbacks with sqlite3? Are other dbs better for this situation?

[dry stump]

@torn sphinx Sqlite3 is a good choice for that application. Do you know where you will be hosting the app? That may change your requirements.

[torn sphinx]

i havent decide for that yet

[dry stump]

@wanton storm look into PySimpleGui.

@torn sphinx I would start thinking about it. If you are using Heroku, you would go with Postgres

[torn sphinx]

are there good ones that support sqlite3 as well?

[dry stump]

@torn sphinx PythonAnywhere would be a good choice. If you use an ORM I wouldn't worry too much about the choice of database.

[torn sphinx]

i am :))

so it doesnt matter as long as i use orm, okay thanks

[dry stump]

@torn sphinx You may run into minor issues depending on column types.

But nothing you wouldn't be able to work around.

[torn sphinx]

what do you mean by minor issues?

[dry stump]

@torn sphinx I have ran into issues with how Sqlite handles datetime.

[torn sphinx]

hmm

[dry stump]

Just do quick reading in the ORM docs when choosing.

it most likely wont be an issue,

[torn sphinx]

i used briefly datetime in my first project and it was fine

idont think i will be needind it this time

you used PythonAnywhere for hosting ?

[dry stump]

I have. It worked fine for my application. It is using a MySQL db

but they have documentation on using sqlite.

[torn sphinx]

okay thanks a lot, i will be looking in to it

[wanton storm]

@wanton storm look into PySimpleGui.
@dry stump Thanks, I am trying to do it using plotly dash

[dry stump]

@wanton storm Is that a requirement?

@wanton storm Do you have a db behind the app? If not, UI channel might be a better place.

[wanton storm]

@wanton storm Do you have a db behind the app? If not, UI channel might be a better place.
@dry stump At this point no, but later on I need to use data bases and other tables to update the calculations

[earnest parcel]

how can i insert a document with all ids of a servers text channel's as objects in mongodb? I tried this: "channels": { channel.id for channel in guild.text_channels } but it gives me cannot encode object: {730806008699420713, 729426765121847358}

[dry stump]

@wanton storm So a web app would be a good idea

[wanton storm]

@wanton storm So a web app would be a good idea
@dry stump I would appreciate your help with something else tho. I am trying to update the table rows based on a multi dropdown selection but when I do it all the rows have all of the selections from the dropdown and I want each selection to have its own row.
my update code looks something lke this @app.callback(

dash.dependencies.Output('output', 'children'),

[dash.dependencies.Input('select-prd', 'value')])

def update_output(value):

value_list=[]

value_list.append(value)

return [

   

    dash_table.DataTable(

        id='prd-mix',

        columns=[{'id':'product-group', 'name':'Product Group'},

                 {'id':'mix','name':'Product Mix'}],

        data=[{'product-group':value for value in value_list} for j in range(len(value)) ],

        editable=True

       

        )

looks like this

nvm I got it, I wasn't accessing the list correctly

[round isle]

Guys, is this basically what the django workflow:
Make a class
Migrate
Link up in database
Continue

Is this it?

[orchid needle]

guys hello

i have deployed a data analysis heroku app

however

it has a static csv file

[glad rune]

hello I have deployed a (minimal version) staging app to heroku with a database and lost my connection to the local. How to continue testing & development locally after migration?
the app is at https://nlp-nltk-stage.herokuapp.com/. When I test on heroku, it creates a worker job w results url... this no longer works on localhost, since sqlalchemy throws an error... what to do here?

[orchid needle]

how can i get a way to get an updatable database in the cloud?

[glad rune]

firebase is really simple if you are just doing a static site

[wanton storm]

I have a table with two columns and I want to append values of columns to a list of dictionaries. I am trying to do it by [{'key1':i} for i in table['column1}, {'key2':j} for j in table['column2']] but its giving me a sysntax error, any ideas?

[somber isle]

anyone with mongodb experience plz @ me

[pure cypress]

@fast burrow A simple select with a where clause can do it

[torn sphinx]

mysql.connector error.internal.error
Unread result found
https://hastebin.com/ebimukafas.py

How to fix this error?

[delicate bluff]

with open('badwords.csv', 'w', newline='') as csvfile:
    spamwriter = csv.writer(csvfile, delimiter=' ',
                            quotechar='|', quoting=csv.QUOTE_MINIMAL)
    spamwriter.writerow([arg","])```
idk what spamwriter is but i modified the example off of docs.python.org for csv databasing, will this write `ARGUMENT,`?
eg: running 3 times with different ones becomes for example:
`first argument, second argument, third argument` exactly in the csv file?

[frozen charm]

I need help with the Rollback function in sqlite 3
As an example, even if i raise an error in the middle of the execution the first Table drops anyway o.o
Does anybody know why.

    if ask == 1:
        try:
            cur.execute("DROP TABLE Projects")
            raise sqlite3.OperationalError
            cur.execute("DROP TABLE Temp")
            print("Droping Database Tables")
        except sqlite3.OperationalError:
            conn.rollback()
            messagebox.showinfo(title="Datenbank Reset", message="Anscheinend sind keine Daten vorhanden.")
        else:
            conn.commit()

[torn sphinx]

In MYSQL (ye ik) How can i return a string in a field that appears more than once

when a field has multiple strings or names in it

[brazen charm]

@frozen charm last time i used Sqlite it didnt have a rollback ability

or atleast not a proper one

[frozen charm]

i looked in the documentation it looks like it has one

hm weird

[torn sphinx]

which is the best way to update/insert many queries from a dict into postgres with asyncpg? Would the below be efficient?

[torn sphinx]

records = self.bot.levels
        async with self.bot.pgdb.acquire() as conn:
            async with conn.transaction():
                for record in records:
                    guild_id = record
                    for user in records[guild_id]:
                        await conn.execute("INSERT INTO levelling (guild_id, user_id, xp, level, messages) "
                                           "VALUES ($1, $2, $3, $4, $5)", 
                                           guild_id, user, 
                                           records[guild_id][user]['xp'],
                                           records[guild_id][user]['level'], 
                                           records[guild_id][user]['messages'])

[brazen charm]

no the above would not be efficent

[torn sphinx]

no the above would not be efficent
@brazen charm so which way is efficient?

[brazen charm]

 async with self._db.pool.acquire() as conn:
     await conn.copy_records_to_table(
           table_name=self._table,
           columns=[list, of, column, names],
           records=[(list,), (of,), (tuples,), (to,), (fill,), (columns,)]
    )```

[torn sphinx]

but i would still have to convert the dict into tuples using the for loop right?

[brazen charm]

you could just use map() to generate the lists that way

[torn sphinx]

My dict is look like this:

{
    654644556456546576: {
        11111111111111111: {
            'xp': 540
        }
    }, 

    5464567657657657: {
        6666666666666666: {
        'xp': 1248
        }
    }
}

[brazen charm]

you can map that probably

[torn sphinx]

Each guild (unique) has many sub users as sub dicts

[torn sphinx]

@brazen charm Sorry for ping, but do you have any example or doc link of what i can use?

[brazen charm]

eh yeah lemmy get asyncpg up

[torn sphinx]

Thanks 🙂

[brazen charm]

https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.connection.Connection.copy_records_to_table

der we go

[torn sphinx]

And how to convert the dict to the list of tuples?

[brazen charm]

look at dict.values() or dict.items() etc...

[torn sphinx]

> items = records.items()
dict_items([(667670942326980608, {233362445240070414: {'xp': 6785, 'level': 8, 'messages': 608, 'timestamp': datetime.datetime(2020, 7, 15, 17, 45, 50, 735457)}})])

> values = records.values()
dict_values([{613362435860070414: {'xp': 6785, 'level': 8, 'messages': 608, 'timestamp': datetime.datetime(2020, 7, 15, 17, 45, 50, 735457)}}])```
This is what i get?

[brazen charm]

you can use map() to apply a function to each item

[gaunt meadow]

how to change username in postgresql?

[torn sphinx]

@brazen charm so we have been trying in #help-chocolate but we get stuck i think

[torn sphinx]

So i used like this to add to my db which is working:

 async with self.bot.pgdb.acquire() as conn:
    async with conn.transaction():
        result = await conn.copy_records_to_table(table_name='levelling', columns=['guild_id', 'user_id', 'xp', 'level', 'messages',   'timestamp'], records=records_list)

Its working, but if item already exists, it will add the new copy instead of overwrite. Can you do on conflict with copy_records_to_table or not?

with asyncpg

[brazen charm]

erm, i have no idea

[torn sphinx]

I think execute many is the only way

But problem then is i feel on conflict will not operate because there is no unique column

unless it is possible to combine column guild_id and user_id to be used in onconflic since same combination cant exist twice.

[brazen charm]

you could probably make a postgre function

[torn sphinx]

Or I may have to redesign the table,so it has (u_id) which is the guild id and user id combined together as one value

[hasty juniper]

@commands.command()
    async def addGem(self, ctx, member: discord.Member, arg: int = None):
        con = sqlite3.connect('user.sql')
        cur =  con.cursor()
        channel = self.bot.get_channel(733122438090588201)

        cur.execute(f"UPDATE users SET UserCash = UserCash + {arg} WHERE UserID = {member.id}")
```hm no errors

[somber falcon]

if i want to add another database do i just copy paste this command with diff names?

or do i make another function called create_db_pool2

and then add it in here

im gonna do the latter bec that sounds more reasonable

[somber falcon]

ok i guess this approach doesn't work bec idk

can someone explain how do i use 2 diff databases?

since client.pg_con is the same variable it overwrites the other one so it doesn't work

[proven arrow]

Well you don't have to name it the same variable. And why not just have the tables inside the single DB?

Also i feel that the run_until_complete will be blocking like that.

@somber falcon

[somber falcon]

well hmm what do i name it except pg_con?

wait or do u mean name the bot smth else?

instead of client name it bot

well i just created another table for it @proven arrow so hmm thanks i guess

[proven arrow]

You can name it anything you like

And i meant the pg_con part

[craggy arrow]

has anyone worked with Prometheus database?

[rustic umbra]

no I dont think so.

[alpine ibex]

Can someone help me with ElasticSearch? Im tryna use it to search through a PostgreSQL database also using Flask? Thanks in advance!

[native vapor]

Hmm, we have some problems with our Postgresql Autovacuum not turning on/finishing up enough. I activated the logs to see whats happening, but I don't see anything after two days. Is there a specific locations the autovacuum logs are stored i'm missing or something i could have overlooked?

[steady epoch]

https://paste.pythondiscord.com/gadeponuye.sql

this error i am getting

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

my main bot file

this error i am getting since i changed my db from sqlite3 to aiosqlite

[steady epoch]

        cursor = await db.cursor()
        await cursor.execute("""
            CREATE TABLE IF NOT EXISTS player(
            discord_id integer Not NULL, 
            player_tag text UNIQUE)
        """)
        await cursor.execute("""
            CREATE TABLE IF NOT EXISTS clan(
                discord_id integer NOT NULL,
                clan_tag text,
                UNIQUE(discord_id,clan_tag))
        """)```

this will create db and close it right

if condition matches

when bot starts

[final plinth]

Hi !! Need a quick help. Im running a python (3.6.10) code in Aws EC2 but due to some columns i cant fetch the mysql query result and if i exclude those columns, its working.
But the same code is running perfectly in my local machine but not doing anything in ec2

[golden warren]

Hello, do you know how to create a db sqlite in a specific folder ?

Mmm @final plinth sorry i don't know i don't know how to help you.

[gaunt meadow]

import asyncio
import asyncpg
import datetime

async def main():
    conn = await asyncpg.connect('postgresql://postgres@localhost/testDB')
    await conn.execute('''
        CREATE TABLE users(
            id serial PRIMARY KEY,
            name test, 
            dob date
        )
    ''')

    await conn.execute('''
        INSERT INTO users(name, dob) VALUES($1, $2)
    ''', 'bob', datetime.date(1984, 3, 1))

    row = await conn.fetchrow(
        'SELECT * FROM users WHERE name = $1', 'Bob')
    
    await conn.close()

asyncio.get_event_loop().run_until_complete(main())```

what makes this error

[rose parrot]

What's the type of relationship (SQLAlchemy context) that makes a class that derives from another, but with its own extra attributes?

e.g.:

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    # backpopulates from SpecialUser?
    # [...]


class SpecialUser(db.Model): # has every attribute from User, but contains its own extra stuff
    __tablename__ = 'special_users'
    id = #? links to user? has to have its own primary key?
    # user_id = ? foreign key?
    special_attribute = db.Column(db.String(120), unique=True)    
    # [...]

Assuming SpecialUser is a User, but contains attributes that a User doesn't have.

[harsh pulsar]

i think in database terms you'd have a 1:1 relationship between User instances and SpecialUser instances

that's how it would work in django

not sure about sqlalchemy

[golden warren]

Hello, do you know how to create a db sqlite in a specific folder ?

[harsh pulsar]

in django that code would create a users table and a specialusers table, the primary key of the latter would also be a foreign key linking to the primary key of the former

@golden warren the filename in sqlite3.connect can be a full path

info_conn = sqlite3.connect('/var/www/myapp/info.db')

[golden warren]

Yep thx ;)

Like that @harsh pulsar

            self.connection = sqlite3.connect(
                "/app/data" + self.db_logs, isolation_level=None)

[rose parrot]

@harsh pulsar you mean like:

# [...]
class SpecialUser(db.Model): 
  id = Column(Integer, ForeignKey('user.id'), primary_key=True)
  # [...]

?
(if that syntax makes any sense to you)

[lime echo]

events = c.execute("SELECT MAX(tickets) FROM temp_tickets WHERE role, user=?", ("event", ctx.author.id,)).fetchone()

role, user=? syntax isn't correct

how do I solve it?

[harsh pulsar]

@rose parrot what if you make SpecialUser a subclass of User?

@lime echo what do you want the query to do?

that is indeed incorrect sql syntax

@golden warren check out os.path.join and also pathlib.Path 🙂

but yes

[lime echo]

events = c.execute(f"SELECT MAX(tickets) FROM temp_tickets WHERE role={733697779934822481} AND user={ctx.author.id}").fetchone()

even this doesnt work

[harsh pulsar]

do not use f strings

for sql queries

did you forget to write role = ?

[lime echo]

even with that

it doesn't work

brb 1 min

[harsh pulsar]

c.execute("select max(tickets) from temp_tickets where role = ?, user = ?", (role_id, user_id))

@lime echo like this?

[lime echo]

let's me try that

operational error near ,

@harsh pulsar

[harsh pulsar]

oh sorry

, should be and

c.execute("select max(tickets) from temp_tickets where role = ? and user = ?", (role_id, user_id))

[lime echo]

it worked man, thank you!

[harsh pulsar]

does my code make sense?

please don't just copy and paste, try to learn from it 🙂

[golden warren]

Okay thx :)

[torn sphinx]

I am writing a configuration database for my discord bot and when ever I dump a list into the json file the list goes to multiple lines but I only want it on 1 single line.

i use asyncpg, but the serial auto increment value just jumps randomly by very big number

like it was 6, and then next one is 440

then it went to 465 for the one after

[harsh pulsar]

That's postgres doing it

[torn sphinx]

I recreate table and still similar

That's postgres doing jt
@harsh pulsar sorry what this mean?

[harsh pulsar]

Typo, jt->it

[torn sphinx]

oh xD

do you know why and is this bad?

query is like this

CREATE TABLE levelling (
    level_id SERIAL PRIMARY KEY NOT NULL,
    guild_id BIGINT NOT NULL ,
    user_id BIGINT NOT NULL ,
    xp BIGINT NOT NULL ,
    level BIGINT NOT NULL ,
    messages BIGINT NOT NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    UNIQUE (guild_id, user_id)
);

I feel it is because of the unique constraint i add?

[harsh pulsar]

https://www.postgresql.org/docs/12/datatype-numeric.html#DATATYPE-SERIAL

Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still "used up" even if a row containing that value is never successfully inserted into the table column. This may happen, for example, if the inserting transaction rolls back. See nextval() in Section 9.16 for details.

[torn sphinx]

i see so is nothing to worry about, but is very strange because never happen in other tables and only this

[lime echo]

@harsh pulsar it made sense, thanks

[harsh pulsar]

@torn sphinx yeah i dont know why it's happening in your specific case. but it's technically correct behavior

you could also not make the level_id

and use guild_id and user_id as a composite primary key

[languid seal]

I'm not sure if this fits as a database question, but its my best guess lol. So, I'm trying to import some parsers I have in this project, but its giving me an error that I can't see replicated on Google. So, the line is:

class Parser(Structure):
  _pack_ = None 
  ...

The import fails at the line with the _pack_ and gives me an error: ValueError: _pack_ must be a non-negative integer
There's nothing I can see in the code that would require that value to be anything else other than None, so I have no idea why the error is being thrown. Has anyone seen any error likes this before?

[runic pilot]

it's a reserved word https://docs.python.org/3/library/ctypes.html#ctypes.Structure._pack_, I'd suggest picking something else

[languid seal]

ah, I see now. Thank you!

[cinder dome]

How can I start a database with visual studio code?

Since I cant install a db browser on my pc

[merry mirage]

Hey guys, how can I identify (or authenticate?) an user with email and password using Firebase? I couldn't find it here https://firebase.google.com/docs/reference/admin/python

[runic pilot]

I don't think that exists on the admin functionality

The Admin SDK lets you interact with Firebase from privileged environments to perform actions like:

Read and write Realtime Database data with full admin privileges.
Programmatically send Firebase Cloud Messaging messages using a simple, alternative approach to the Firebase Cloud Messaging server protocols.
Generate and verify Firebase auth tokens.
Access Google Cloud Platform resources like Cloud Storage buckets and Cloud Firestore databases associated with your Firebase projects.
Create your own simplified admin console to do things like look up user data or change a user's email address for authentication.

it exists in the normal module though https://firebase.google.com/docs/reference/js/firebase.auth.Auth#signinwithemailandpassword

[merry mirage]

What do you mean by normal module?

[runic pilot]

the non-admin module

[merry mirage]

Does it have a python wrapper?

[runic pilot]

you linked to the firebase admin sdk

doesn't look like it from their docs, but it has a rest api interface https://firebase.google.com/docs/reference/rest/auth#section-sign-in-email-password

[merry mirage]

I see. Thank you

Maybe I can write a basic wrapper to do this API request stuff

[runic pilot]

I'm sure somebody else also had that idea, check around for some python wrappers

like this https://pypi.org/project/firebase/

[cinder dome]

https://discordapp.com/channels/267624335836053506/342318764227821568/733771349394194462

[runic pilot]

starting a database from a text editor confuses me, what are you trying to do?

[blazing void]

I encountered exactly this issue - Python's firebase SDK doesn't have logins

it's a weird omission, but you can create users, and you can check their tokens, but you can't login users

so our web app uses firebase on python for practically everything, but the web app has to use firebase frontend libs to do the logging in

[merry mirage]

@blazing void Yeah exactly. I just want to do a basic email and password auth for a basic flask app but it's seems like pain

[blazing void]

yeah, it's weird

I'm annoyed about it too

I don't have any solutions sorry, I gave up and just had the frontend do the login

I think maybe if I were to revisit this, I'd run a separate node.js microservice just to handle the logins, but that just seems so convoluted

[merry mirage]

I see. My situation is a UI-less REST API. I just want to use JWT for basic auth and security. I want to implement an endpoint like /get_token which would first authenticates the user with an email and password. If valid, returns a JWT token for further requests

Does it seem like correct? Sometimes I ask "why we bother with tokens if we get the email and password from the user. It's an UI-less API anyway, users won't need to type their email and password every time"

I would like to hear your opinions @blazing void

[cinder dome]

starting a database from a text editor confuses me, what are you trying to do?
@runic pilot make tables and all that

So I can connect it with my other commands and stuff

[blazing void]

my opinion on that is if you are implementing microservices, you don't want to have every service have to be able to do authentication

but if you're not implementing microservices, using JWTs is probably less of problem, though it does avoid users having to keep transmitting their passwords, and also it allows not storing passwords on the frontend, and having a token that can be later invalidated

in my opinion, not storing the password on the frontend, and only storing the JWT is a better option - it's safer because no password is stored; and it's easier for the user to manage their account as they can invalidate the token if they lose a device or accidentally leave their account logged in on a machine they can no longer access

[merry mirage]

my opinion on that is if you are implementing microservices, you don't want to have every service have to be able to do authentication
Yea of course

@blazing void I see. So do you think getting email and password from a user via rest api like once in a day is correct, right? Anly problem with that?

[blazing void]

I think that's fine. I would say whether the email and password was actually stored in the browser is not the backend's concern, however asking for the email/password every day is probably ok

often frontends have a "remember me on this machine" checkbox that asks the backend to please use a longer timout on the token

[merry mirage]

Yea there is no a browser at all. My customer is going to just use the API

Completely no user interface

[blazing void]

yep ok

one day seems fine

[merry mirage]

Okay, Thank you very much. I was concerned. There are no one to ask in my office

[ripe helm]

how do i get all tables in a database

in sqlite3?

ping me plz

[runic pilot]

.tables in the sqlite3 console

[ripe helm]

like

within python

@runic pilot

[runic pilot]

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()
c.execute('''.tables''')

[ripe helm]

ah thanks

@runic pilot sorry for bugging you but i dont think im doing this right-

      conn = sqlite3.connect('tags.db')
        c = conn.cursor
        c.execute(".tables")
        tables = c.fetchall()
        conn.close()

        print(tables)```

[runic pilot]

I'm not sure honestly, I've never programmatically asked sqlite what tables it has

[ripe helm]

nvm i got it

its this

c.execute("SELECT name FROM sqlite_master WHERE type = 'table';")
tables = c.fetchall()```

[potent sparrow]

With using flask, why use flask for data? When Vue and React has this.name, this.email, ect??
You can have a entire JS sheet just for information to call on.

What is a reason for Flask? Or Django?

[calm moon]

@potent sparrow Flask is a very popular framework, and React can't do what Flask does, it is frontend framework, Flask is backend (if you mean JS in total, than I am not talking about NodeJS, such a rubbish)

[potent sparrow]

@calm moon whats the difference between a flask object/class vs a react class/object? .... thank you

[calm moon]

React is a frontend framework, it works on client side.

Flask is server side

[potent sparrow]

@calm moon, do it’s about location??

[calm moon]

Man I wont argue with you

[vestal geyser]

Whats the best way to fetch one row per table from multiple tables with two columns and the same value.
But without joining since I simply want all the rows seperate

[gaunt meadow]

How to change the server host places in postgresql?

[zealous sluice]

Hello, Just want to seek an advice on how to structure database for e commerce with roles. ex. Customer / Supplier.

have a separate table? or single user table with roles column. which one would be preferred

[harsh pulsar]

If you have special behavior for each role you can have table per role

[thorny musk]

did anyone experience performance problem with django rest saving modelserializer

[brazen charm]

Django isnt really a 'performance' framework in general

[thorny musk]

when i try to save 1000 users it executes in 3 seconds

in serializer it executes in 5 minutes

[rancid forge]

hello

anyone good with json files that wants to help

[sick dragon]

!ask @rancid forge

!ask

!ask

well done

!ask

!ask

meh I stop
this will mute me

!ask

[delicate field]

Asking good questions will yield a much higher chance of a quick response:

• Don't ask to ask your question, just go ahead and tell us your problem.
• Don't ask if anyone is knowledgeable in some area, filtering serves no purpose.
• Try to solve the problem on your own first, we're not going to write code for you.
• Show us the code you've tried and any errors or unexpected results it's giving.
• Be patient while we're helping you.

You can find a much more detailed explanation on our website.

[cinder dome]

How would I create a database like creating the tables and everything if I cant install Db browser? For SQLite

[rich trout]

python's sqlite3 module has a .execute() method that runs arbitrary SQL

including that required to create and view tables if you wish

[cinder dome]

Is there any videos on that

[rich trout]

I'm sure there are, I prefer text guides so I'm unfortunately not familiar

[cinder dome]

What text guides do you have?

[rich trout]

Python's official one is pretty good, https://docs.python.org/3/library/sqlite3.html

digitalocean provides a similar one here https://www.digitalocean.com/community/tutorials/how-to-use-the-sqlite3-module-in-python-3

since you're working on a bot aiosqlite is a good choice too, since it fits with the async format of discord.py better. It's nearly the same as builtin, so despite the lack of docs you can probably make everythying work just by following the standard sqlite3 ones and adding await where appropriate

You might find this useful too, although it is aimed at mysql most if not all of it translates pretty well to sqlite https://www.digitalocean.com/community/tutorials/introduction-to-queries-mysql

[solar gale]

is this just invalid syntax in sqlite3?
DELETE FROM table1, table2 WHERE criteria=?

[upbeat lily]

I don't think you can delete from multiple tables like that @solar gale

you might be able to - but even if you could, it's not the most readable

[solar gale]

nah, i get a syntax error at the comma.

i found this though, so now im figuring out how to use triggers and transactions.
https://stackoverflow.com/questions/39397417/sqlite-query-to-delete-from-multiple-tables

[upbeat lily]

yeah, that's the one I was about to link

assuming you have a parent/child FK relation. The easiest thing is to just delete from the child table first, then the parent table

[solar gale]

yeah, i think a trigger would be more appropriate in this case because i do have a parent/child relationship in this situation

ok wait lol im trying to make sense of this statement from that SO post i linked:
create table addresses ( id INTEGER PRIMARY KEY, address TEXT, person REFERENCES people(id) ON DELETE CASCADE );

in particular, the person REFERENCES people(id) ON DELETE CASCADE bit

does that basically create a trigger that deletes the record where ID in the addresses table == the ID in the people table?

[upbeat lily]

When the parent table gets deleted, all children referencing that parent.id will also get deleted, yeah

[solar gale]

ok, that makes more sense lol

i can also get rid of some code i wrote on my end too lol

thanks

and im guessing that i need to do pragma foreign_keys = on; prior to making that trigger because this bit person REFERENCES people(id) ON DELETE CASCADE wouldn't work then, right?

[cinder dome]

How would I edit this economy system so when I do !buy it'll take a certain amount of balance I set it to in the shop: https://mystb.in/ControllerCutsJokes.python

[solar gale]

@hallow mango idk what you think we're supposed to do with a bunch of code just pasted into the chat, but you def shouldn't be using an f-string for the sql statement.

these are the formats you should use

# This is the qmark style:
cur.execute("insert into people values (?, ?)", (who, age))

# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

ref: https://docs.python.org/3/library/sqlite3.html

[dusky siren]

So I am currently working with sqlite3. The line of code giving me issues is:
c.execute("INSERT INTO cards(ownerid, collectionid, name, set, rarity) VALUES (?, ?, ?, ?, ?)", (str(ctx.author.id), i["id"], i["name"], i["set"], i["rarity"]))
All values are properly named, and all values are defined. The goal of the code is to create a new record inserting those values into the specified columns (all other columns have defaults). However, I get the following error:
sqlite3.OperationalError: near "set": syntax error
Anyone got any ideas on how I can fix this?
(let me know if further information is required)

[proven arrow]

@cinder dome You can use the UPDATE statement to set the new balance. You can perform subtraction on the column value as well.
Also use a async db, since what you are using currently will end up blocking and break your bot. And you can clean that code up a lot, since you are making unnecessary queries. For example, you are doing the same query twice in your buy command, and what is the point of this.

[cinder dome]

async db?

Wdym

[proven arrow]

There is synchronous (sync), and asynchronous (async).
When you do something with sync you have to wait for it to complete before moving on to the next part (this waiting can cause what is known as blocking). Whereas if you execute something with async, you can start doing other tasks whilst it finishes.
So when writing bots which mostly rely on async code, its important to make sure things like db requests are using async, because if not then it can end up blocking. In other words the blocking will kind of nuke your bot especially if you block for too long. Therefore always use an async library for your DB.
https://discordpy.readthedocs.io/en/latest/faq.html#what-does-blocking-mean @cinder dome

[cinder dome]

So what would I change in the sql code?

Updated link: https://mystb.in/ReportersDecidedConsiderations.python

[proven arrow]

Well first I recommend changing to the new library: https://pypi.org/project/aiosqlite/

[cinder dome]

Oh sqlite3 isnt good

Anymore

[proven arrow]

No sqlite3 is sync based. aiosqlite the one I linked above is async

[cinder dome]

Aight thanks

[proven arrow]

The queries and principles will be the same.

[cinder dome]

So the only difference is just async and awaiting right

[proven arrow]

Yes

[cinder dome]

Aight thanks

[proven arrow]

Take a look at that link, it has some good examples.

[rich trout]

@dusky siren the word "set" is a reserved word in SQL, you'll need to quote it to use it as a table column

[dusky siren]

ah alright, that explains a lot. Thanks!

[cinder dome]

@proven arrow would it look like this? https://mystb.in/BabeOrdinanceRabbit.python

I didn’t change the other commands yet just how I connect it and stuff

[somber isle]

anyone here with mongodb experience? @ me

[solar gale]

is this valid syntax because im:

1. creating a column definition with a trigger tied to it (the trackartist column)
2. since the foreign key doesn't seem to be made implicitly after making the trigger, then i can explicitly state the foreign key in my foreign key statement
   ?

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY,
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT,
  trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

im not sure if this is technically redundant or not

[proven arrow]

Not really no. When you connect it’s fine. But the other stuff you should remove. The code examples in that link are to demonstrate how the lib can be used. You don’t want to be doing things like db.close() as that will end your database connection. And you should only be fetching rows once after you execute a query.

In short just have the, db = await aiosqlite.connect(....) line and remove the others below it. @cinder dome

[cinder dome]

So the only thing here I should keep is DIR, db =, and thats it

[proven arrow]

Yes that should do.

[cinder dome]

And wdym by fetching rows? Is it like how I have it setup in the commands?

[proven arrow]

Yes so you can only fetch data once you execute a query. Like the one in your commands.

[cinder dome]

Aight thanks

[proven arrow]

Also if you want to use the DB in other cogs if you have any then you should assign the connection to a bot var, and use that across your code. So keep a single connection which you refer to each time.

[rancid forge]

unknown module "os"

[cinder dome]

Also if you want to use the DB in other cogs if you have any then you should assign the connection to a bot var, and use that across your code. So keep a single connection which you refer to each time.
@proven arrow I have a decent amount of cogs, I just put my testing commands in a regular file first, but if I need anything am I able to ping you?

[proven arrow]

Umm, I will go sleep now since it’s almost 5am. But you can ask on here or the other relevant channels and someone else should be able to help. Or if not then tomorrow.

[cinder dome]

Aight thanks, but you should go to bed earlier bruh lol

[grave carbon]

Can I shut my postgresql dev server down when I am not actively trying to work with it

[tepid cradle]

You definitely can. But how you do it will depend on where it's hosted

[rancid forge]

could anyone help me with sqlite3

[tribal dome]

did u try importing it

[craggy mirage]

so umm im kind of stuck
how do i make a database with SQLite

for discord.py

@rancid forge at the very top add a line that says import sqlite3

[rancid forge]

yeah but now its going crazy when i try to run

[delicate bluff]

is csv a good idea for a py bot?

[rancid forge]

like drug store??

[craggy mirage]

how do i make a database with SQLite

lmao

[torn sphinx]

Assuming you are using aiosqlite (for a discord.py bot since that is where you first asked) db = await aiosqlite.connect("mydatabase.db") will create a database if one doesn't exist with the name given and connect to it

[craggy mirage]

ok

[torn sphinx]

you can then act upon db for database interactions

[delicate bluff]

Comma Seperated Values for a d.py bot. good idea?

[torn sphinx]

n o

I mean, csv and json are both fine for small data it really just depends on your use for it

key word

small

yes, definitely

[craggy mirage]

db = await sqlite3.connect("money.db")
^
SyntaxError: 'await' outside function

[torn sphinx]

and they arent scalable, so once they get too big, you have to copy to a db

[craggy mirage]

hooters thats what came up

[torn sphinx]

you need to create the connection inside an async function

since it has to be awaited

[craggy mirage]

sorry im kind of new to this but how do you do that?

[torn sphinx]

either do it in your on_ready

or

[craggy mirage]

oh ok

[torn sphinx]

db = None
async def connect():
  global db
  db = await aiosqlite.connect(...)
asyncio.run(connect())

that also works

[craggy mirage]

@client.event
async def on_ready():
    db = await sqlite3.connect("money.db")

i just did this?

[tribal dome]

hi is anyone familiar with mongo that can assist me?

[delicate bluff]

Which type of a database would be if csv isn’t? SQL?

[torn sphinx]

sqlite is good for beginners from what i hear

use the aiosqlite lib

[craggy mirage]

im a begginer

beginner

ok ty crazygmr

it worked with a few tweeks to adjust to SQLite

[delicate bluff]

so SQLite is good for beginners? cuz i can’t even write in db yet, not to mention read.

[torn sphinx]

you uh

what

write in db yet, not to mention read

but yea from what i heard it is

i use it

there are faster db's tho

[grim lotus]

Wha

[torn sphinx]

since you are using this for a discord.py bot jabaited, you should probably try to migrate over to aiosqlite, since it's basically the asynchronous version of sqlite (which is a must, since d.py is asynchronous) and almost nothing is changed

[grim lotus]

if you looking to fast up your Database use redis

yes first of all you should async libs

for d.py like platform

[gaunt meadow]

How to change the server host places in postgresql?

as i dont want to set localhost

[solar gale]

is doing something like this in sqlite3 considered redundant? im not sure if the foreign key constraint gets created implicitly when making the trigger.

            GameMasterID TEXT NOT NULL REFERENCES ActiveGames(GameMasterID) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY(GameMasterID) REFERENCES ActiveGames(GameMasterID)

[solar gale]

damn, i thought that a foreign key would be able to enforce not being able to insert a record where the foreign key in the parent table doesn't exist

using sqlite, does anyone know of a way to enforce not being able to insert a record if the foreign key in the child table doesn't exist in the parent table?

omg lmao i forgot to do PRAGMA foreign_keys = ON; before doing any other sql statements 🤦‍♂️

[balmy egret]

I'm using a noSQL and each document has a list of nested dicts, each with multiple properties. Some of the items in the list may have duplicate names with variances in other properties. Should I turn each property into a list because duplicate item properties can be appended to them, or keep the properties as non-list values except for in the case of a duplicate?

The all-list option seems more uniform, but an added burden since the duplicates are rare and a mongodb query looking for a value will return that item where the value is alone or in a list.

[late jacinth]

I got a question

How do i compare the first -1 to the next row down number?
The conditions are: the first number needs to be -1, and if the next row down number is +1 then the count of True goes up by one.
Then the comparison starts on the second number to the third number with the same conditions

with using Pandas

[boreal oak]

Sorry if this question doesn't belong here. I am planning to get in Data science, so want to learn database systems. Where should I get start? Can anyone point to credible guide?

Also, I don't come from CS background. I am an electrical engineer.

[high bone]

I really like https://selectstarsql.com for SQL

[dim shuttle]

I have a really weird issue with sqlite3 and a small flask application

everything runs fine without errors.....but nothing gets written to the database

https://termbin.com/fc6r

anyone have any idea what might be going on? I don't have any error whatsoever when the function that should insert data is run

but the .db file just doesn't get changed

[solar gale]

because you don't commit the changes after executing

once you're done making changes to your db, you need to call commit(), or else none of the changes ever get committed.

[dim shuttle]

ohhhhh okay

do you know if there's a way to do that with the context manager (short of writing my own context manager?)

[runic pilot]

it comes with one https://docs.python.org/3.8/library/sqlite3.html#using-the-connection-as-a-context-manager

[torn sphinx]

Does anyone know how I could access my mongodb database using a class. I've been using just normal python code and i feel that connecting using a class would be better

[lime echo]

xp=c.execute("SELECT MAX(user_xp) FROM KindeoXp").fetchone() sqlite3.OperationalError: no such table: KindeoXp

[cinder dome]

I keep getting a bunch of undefined variable errors for the db https://mystb.in/RegardingMariahFor.python

[lime echo]

mine worked btw!

@cinder dome can I see the code?

[cinder dome]

Its in that link

If not here's the git: https://github.com/AndrewNunnes/Andrew-s-Bot

Everything with the db is under bot.py

[lime echo]

@cinder dome they are defined out of the scope.

Either redefine them inside each function, or make them global (outside of your main.)

[cinder dome]

Wdym make them global?

You mean just backspace them

Or I mean put them after asyncio.run(main())

@lime echo

[lime echo]

Put them after asyncio.run(main())

@cinder dome

[arctic acorn]

Helo

I'm new to python and discord.py

And I was going to make a database using sqlite

Is thst the correct way or

Shud I do something else

[cinder dome]

@lime echo but what would I put after as db:

@arctic acorn use aiosqlite

better than sqlite3

[arctic acorn]

What is the diff

[cinder dome]

sqlite3 causes blocking, everyone told me to use aiosqlite instead

[arctic acorn]

So while making the db rather tha using import sqlite3

I'll write import aiosqlite?

[lime echo]

@cinder dome the problem with your db is on line 46 not on line 24.
Make

    global SQL
    async with aiosqlite.connect(os.path.join(DIR, "BankAccounts.db")) as db:
        SQL = await db.cursor()
        START_BALANCE = 100
        C_NAME = "Basketballs"``` global (put it below `asyncio.run(main())` and it shall be fixed.

[cinder dome]

line 46?

in here?

[lime echo]

@cinder dome that's where the problem is.

And yes, in there.

[wooden wraith]

Lol

[cinder dome]

so what would I make it instead

[lime echo]

https://discordapp.com/channels/267624335836053506/342318764227821568/734473134073184256

[wooden wraith]

What are you guys learning python for

[cinder dome]

inside of that command right?

And that goes for all the others too

[lime echo]

@wooden wraith I am not learning python, I am just making some stuff, and those stuff require python, so..

[wooden wraith]

Oh ok

I dont really know why I'm learning python
No objective

[lime echo]

@cinder dome cut:

    global SQL
    async with aiosqlite.connect(os.path.join(DIR, "BankAccounts.db")) as db:
        SQL = await db.cursor()
        START_BALANCE = 100
        C_NAME = "Basketballs"```
paste it below:
`asyncio.run(main())`

[wooden wraith]

Ah sql

[wooden wraith]

I still cannot connect mysql to vscode

[lime echo]

good, now delete async def main(): to make your stuff global rather than inside the scope of main()

@wooden wraith do you have some code yet?

[wooden wraith]

No

I'm finishing a course

[craggy mirage]

Hey

so i wannted to use asyncio

[cinder dome]

main is undefined

[wooden wraith]

So like it's a self learning course

[lime echo]

1. Import sql
2. db = sqlite3.connect("db_name.sqlite") (if you are using sqlite3...)
   there you go

[tribal dome]

hi would anyone be able to help with a mongodb question? i cant update my collection properyl

[cinder dome]

!tag ask

[delicate field]

Asking good questions will yield a much higher chance of a quick response:

• Don't ask to ask your question, just go ahead and tell us your problem.
• Don't ask if anyone is knowledgeable in some area, filtering serves no purpose.
• Try to solve the problem on your own first, we're not going to write code for you.
• Show us the code you've tried and any errors or unexpected results it's giving.
• Be patient while we're helping you.

You can find a much more detailed explanation on our website.

[cinder dome]

@craggy mirage ^^

[craggy mirage]

all i need to import is discord, from discord.ext import commands and asyncio right?

[cinder dome]

@lime echo Im using aiosqlite

[craggy mirage]

import discord
from discord.ext import commands
import asyncio

or do i need to import more things?

[normal igloo]

There are like 5 people in here, some of you should go to the help: available channels

[cinder dome]

yeah

[lime echo]

@cinder dome yes good.
now, what's the utility of asyncio.run(main()) if you don't have main() in the first place? it doesn't have any, so we should delete it.

[wooden wraith]

1. Import sql
2. db = sqlite3.connect("db_name.sqlite") (if you are using sqlite3...)
   there you go
   @lime echo
   Oh thx

When i do that tho

[lime echo]

@craggy mirage asyncio isn't necessary.

[cinder dome]

got no errors

thanks kranthos

[lime echo]

np!

[cinder dome]

Ima work on the actual commands another day

time*

global SQL
SyntaxError: name 'SQL' is assigned to before global declaration```

@lime echo

[craggy mirage]

If someone could help me out in #help-apple that would be great i need help with asynco

[tribal dome]

same.. in #help-lollipop

[lime echo]

@cinder dome send me the code from line 0 to line 22

[cinder dome]

import discord
from discord.ext import commands
import random
from random import randint
import platform
import logging
import asyncio
import datetime
import typing
import sqlite3
import aiosqlite
import os
from discord.ext import commands
from discord.utils import get

client = commands.Bot(command_prefix = '!', case_insensitive=True)
client.remove_command('help')

DIR = os.path.dirname(__file__)
SQL = None

global SQL
async with aiosqlite.connect(os.path.join(DIR, "BankAccounts.db")) as db:
    SQL = await db.cursor()
    START_BALANCE = 100
    C_NAME = "Basketballs"```

[lime echo]

@cinder dome

SQL = None

global SQL``` ❌
```python
global SQL
SQL = None``` ✅

[cinder dome]

oh ok

[lime echo]

Reverse their declaration order.

[cinder dome]

aight thanks

[lime echo]

np

[cinder dome]

async with aiosqlite.connect(os.path.join(DIR, "BankAccounts.db")) as db:
SyntaxError: 'async with' outside async function```

You sure I had to remove that async function though?

async def main:

@lime echo

[lime echo]

Since you don't have any main() you definitely don't need an async.main(), I am sure about this.

Oki, so the problem is that you need, async with aiosqlite.connect(os.path.join(DIR, "BankAccounts.db")) as db: to be inside an async method.

[cinder dome]

Yeah

[lime echo]

Your problem can be solved by using classes

or you can repeatedly redefine

global SQL
async with aiosqlite.connect(os.path.join(DIR, "BankAccounts.db")) as db:
    SQL = await db.cursor()
    START_BALANCE = 100
    C_NAME = "Basketballs"``` on each function.

In this case, you would need to redefine main()

[cinder dome]

Id rather use clases

Ig ima put it in a cog

[lime echo]

Mhm, or just use a class inside your current file.

Just add back async main() and its async.main() thing.

[cinder dome]

Im gonna switch it into a cog anyways later on

[lime echo]

Then, define a class,

def __init__(self):
async with aiosqlite.connect(os.path.join(DIR, "BankAccounts.db")) as db:
    self.SQL = await db.cursor()
    self.START_BALANCE = 100
    self.C_NAME = "Basketballs"```

then replace all your SQL, START and C_NAME with self.SQL, self.START and self.C_name respectively.

Your code shall work then.

[cinder dome]

https://mystb.in/NonPharmaciesBelongs.python

@lime echo

[lime echo]

@cinder dome what is this

oh oki I see

[cinder dome]

Wait Im fixing it

[lime echo]

class Economy(commands.Cog):
    def __init__(self, bot):
        self.bot = bot

DIR = os.path.dirname(__file__)

global SQL
SQL = None
async with aiosqlite.connect(os.path.join(DIR, "BankAccounts.db")) as db:
    self.SQL = await db.cursor()
    self.START_BALANCE = 100
    self.C_NAME = "Basketballs"``` ❌

[cinder dome]

How would I do it?

[lime echo]

class Economy(commands.Cog):
    def __init__(self, bot):
        self.bot = bot
        global SQL
        self.SQL = None
        async with aiosqlite.connect(os.path.join(DIR, "BankAccounts.db")) as db:
        self.SQL = await db.cursor()
        self.START_BALANCE = 100
        self.C_NAME = "Basketballs"
DIR = os.path.dirname(__file__)```

[cinder dome]

oh nvm

I indented it wrong

wait no

[lime echo]

yes you indented it wrong

self.SQL... should also be indented

[cinder dome]

🤦‍♂️ myb

But Im still getting the undefined variable “db”

In my other commands

Also “Using global for ‘SQL’ but no assignment is done”

https://mystb.in/SphereFlameCompetitors.python

[lime echo]

which line

[cinder dome]

10, 9

global SQL

[lime echo]

@cinder dome delete global SQL

[cinder dome]

K that worked, but what about the db variables

That are “undefined”

[solar gale]

do users of PostgreSQL like it because of its implementation of multiversion concurrency control?

[lime echo]

async with aiosqlite.connect(os.path.join(DIR, "BankAccounts.db")) as self.db:
    self.SQL = await self.db.cursor()```

add self. to each db variable @cinder dome .

And it shall work.

[cinder dome]

Oh ok

Ill lyk if anything else happens

Can I ping you?

[lime echo]

Sure, anytime.

[cinder dome]

File "/home/container/.local/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 596, in _load_from_module_spec
spec.loader.exec_module(lib)
File "<frozen importlib._bootstrap_external>", line 779, in exec_module
File "<frozen importlib._bootstrap_external>", line 916, in get_code
File "<frozen importlib._bootstrap_external>", line 846, in source_to_code
File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
File "/home/container/cogs/database/database.py", line 11
async with aiosqlite.connect(os.path.join(DIR, "BankAccounts.db")) as self.db:
SyntaxError: 'async with' outside async function
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "bot.py", line 29, in <module>
client.load_extension(ext)
File "/home/container/.local/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 653, in load_extension
self._load_from_module_spec(spec, name)
File "/home/container/.local/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 599, in _load_from_module_spec
raise errors.ExtensionFailed(key, e) from e
discord.ext.commands.errors.ExtensionFailed: Extension 'cogs.database.database' raised an error: SyntaxError: 'async with' outside async function (database.py, line 11)
ERROR:asyncio:Unclosed client session
client_session: <aiohttp.client.ClientSession object at 0x7fd116f155b0>```

@lime echo

[proven arrow]

Your trying to use Async with inside a non Async func

You should consider having a single database connection, maybe in your main file. And then refer to this connection each time.

[cinder dome]

How would I do that

and how would I fix my issue

[proven arrow]

async def setup_db(bot):
    await bot.wait_until_ready()

    bot.my_db = await aiosqlite.connect(...)

bot.loop.create_task(setup_db(bot))

So you can put this in your main file, and this would assign the database connection to the bot instance. Now you no longer need to make a new connection to the DB each time. You make it once here and then access it each time through: bot.my_db in your commands or wherever you want to use it.

[wooden wraith]

@lime echo um so im trying to connect MySql client to my python project using the method from w3schools but it doesn't work
Btw I'm using vscode

[cinder dome]

@proven arrow this in my main file?

Also ping me

[proven arrow]

No, you just need to make the connection, like in the example I sent @cinder dome

[cinder dome]

Like this? @proven arrow

[proven arrow]

Yes

[cinder dome]

And what would I do with my database.py file? https://mystb.in/CelebrationCaliforniaGuarantees.rust

[cinder dome]

@proven arrow

[proven arrow]

bot.my_db holds your database connection. You would refer to this now to acquire the connection, and then make your queries

[cinder dome]

So bot.my_db = what?

@proven arrow

Like that?

[proven arrow]

No. Connect will make and return you a connection to the database. You have already made your DB connection and stored it in bot.my_db. So why are you making another connection and overwriting the existing one.
You just refer to this connection each time.
The same way you make your bot once in your main file, like bot = discord.Client(), and you never recreate it again, you shouldn’t do this here.

For example,

row = await cursor.fetchone() ```

@cinder dome

[rich trout]

I'm too tired to remember how I'm supposed to do this, but in django I've got the following model relationships:

class Spell(models.Model):
  name = models.CharField(max_length=255)
  cast_text = models.TextField(max_length=512)

class Spellslot(models.Model):
  spell = models.ForeignKey(Element, related_name="spells")
  spellbook = models.OneToOneField('Bookpage', related_name="slots")

class Bookpage(models.Model):
  rarity = models.PositiveIntegerField(default=0)  

class Spellbook(models.Model):
  page = models.ForeignKey(Bookpage, related_name="spellbooks")

I have a Spellbook, I want to query for a list of spells

self.page.slots works, but I can't seem to remember how to query through Spellslot to get Spell

I know [i.spell for i in self.page.slots.select_related('spells').all()] only does one query, which is kind of what I want, but doesn't feel like the right syntax

[cinder dome]

@proven arrow like this?

Ping if you reply

[proven arrow]

@cinder dome I feel you should brush up on some OOP. At this point I’m kind of just repeating the same things. If you know OOP concepts then try to read your code and try to understand the logic and see if it makes sense.

[cinder dome]

Oop?

Wdym

[somber isle]

anyone with mongo db experience plz @ me

[shell drum]

Is it possible to store objects of custom classes into a database without serializing them? I've heard tools like Redis and Mongo are able to do something like that but I'm not sure...

[brazen charm]

not really no

everything has to be serialized in some way to store it

[shell drum]

hmm... thanks

[brazen charm]

Redis is a cache database which lets it be a bit more fluid but that serializes it still and so does mongo

[shell drum]

great! merci.

[onyx aspen]

Is it possible to count the occourence of a certain string?

[torn sphinx]

Hey there, i'm moving my sqlite code to aiosqlite so it isn't blocking, and ive run into this issue

    async def addInfraction(self, guild, user, type, reason):
        print("All is swell")
        try:
            sqliteConnection = await aiosqlite.connect('./Guild_Databases/{0}'.format(str(guild.id)),
                                                        detect_types=aiosqlite.PARSE_DECLTYPES |
                                                                     aiosqlite.PARSE_COLNAMES
                                                        )
            cursor = await sqliteConnection.cursor()
            print("Connected to SQLite DB")

            insert_param =  """INSERT INTO infractions
            (userid, type, reason, date)
            VALUES (?, ?, ?, ?);"""

            data_to_add = (user.id, type, reason, datetime.datetime.now())
            await cursor.execute(insert_param, data_to_add)
            await sqliteConnection.commit()
            print("success?")

            await cursor.close()

        except aiosqlite.Error as e:
            print(e)

        finally:
            if (sqliteConnection):
                await sqliteConnection.close()
                print("beep boop")```

now the thing is

detect_types=aiosqlite.PARSE_DECLTYPES | aiosqlite.PARSE_COLNAMES```

those parse_ things dont exist in aiosqlite seemingly

How can i get around this, as i need it for my datetime

[rich trout]

aiosqlite's .connect() method should pass those directly to sqlite3's connect method

Oh I see

use sqlite3's version of the constants, but pass them to aiosqlite

[arctic acorn]

hi there

i have recently started programming a bot on discord.py

and needed a database

a lot of people have reccomended me to use aiosqlite

ratehr than sqlite3

due to certian blocking issues of code

since discord.py is an async lib

and i couldnt understand how to code aiosqlite

and neither could i find any tutorials on how to use the above mentioned

[rich trout]

Aiosqlite is almost identical to sqlite3, it just requires await in front of (I think?) everything

[arctic acorn]

yes

thats what ive been told as well

its smply and async module for sqlite3 standard

@naive sandal

so how am i supposed to apply all the code to my discord.py bot?

[naive sandal]

Fairly easy, all you need to do is store the data correctly.

lets start with storing the welcome message

[arctic acorn]

ok

shud i send my welcome code?

[naive sandal]

each guild can only have 1 welcome message, which means we'll need to store the guild_id to keep track of which guild has what message. And of course, we need to stor the message itself.

shud i send my welcome code?
@arctic acorn yes

[arctic acorn]

ok

its not cog btw already saying since this is simply a testing bot

async def on_member_join(member):
    channel = client.get_channel(734312787752386621)
    await channel.send(f"Hello {member} we hope u enjoy ur stay")```

async def on_member_remove(member):
    channel = client.get_channel(734312787752386621)
    await channel.send(f"Hello {member} we are sorry to see you go")```

[naive sandal]

@arctic acorn now add a command to change the guild's welcome message

[arctic acorn]

how to do that?

im complete nub

[naive sandal]

@client.command()
async def changeWelcomeMessage(ctx, *, expression): # the ctx, *, expression means that the variable 'expression' is of variable length
  ...

[arctic acorn]

ok

[naive sandal]

after that, create a table to store guild settings

[arctic acorn]

so i simply copy paste that?

i mean not the tag part

[naive sandal]

to store the welcome message, you'll need to store the guild_id (Primary key) and the message

[arctic acorn]

so will that be my table?

guild_id channel_id and msg?

does that work

since i already have a table with that function

[naive sandal]

what are you using that table for though?

[arctic acorn]

rn i want it for welcome messages and goodbye messages

so shud i make one column

msg_welcome

and the other msg_leave

?

with a leave message command change?

[naive sandal]

I think you are confused about your Database schema. Why are you storing channel id?

[arctic acorn]

is that not nessesary?

i thought that it would need to store diff channel ids on which the msg is sent to

[naive sandal]

ah makes sense

[arctic acorn]

based on diff guilds

yes

so ill just make a new table

[naive sandal]

alright, then you can use that table

[arctic acorn]

ok

wait

actually ill just add msg_leave

to the table for my own reference

ok

ive added it

now what shud i do?

wait i need to make a leave msg change command

one sec

ok done

now?

also thx a lot for helping a newbie like myself

[naive sandal]

now you need to query your database to store data

if you are using aiosqlite:

[arctic acorn]

meaning?

[naive sandal]

await db.execute('INSERT INTO Tablename (guild_id, welcome message) VALUES (?,?)', (ctx.guild.id, welcmsg))

you need code like this to write to your database

[arctic acorn]

forgot to install aiosqlite lmao

to pycharm

also

[naive sandal]

you should probably use REPLACE INTO and not INSERT INTO

[arctic acorn]

what is the "values"

supposed to mean?

you should probably use REPLACE INTO and not INSERT INTO
@naive sandal ok

[naive sandal]

I suggest you read the aiosqlite and the sqlite3 docs

[arctic acorn]

since they are left as qn mark

oh ok

[naive sandal]

I can't tell you about all the features

[arctic acorn]

lemme go thru em for some time

[naive sandal]

but basically ? are placeholders for variables

[arctic acorn]

then ill b back for some more

oh

ok

lemme grab a tad more knowledge for it

ok

ive sorta understood some stuf

so lemme put in that code u sent zeffo

cursor = await db.execute('SELECT * FROM DB_FOR_JOIN_AND_LEAVE')
row = await cursor.fetchone()
rows = await cursor.fetchall()
await cursor.close()
await db.close()


await db.execute('REPLACE INTO Tablename (guild_id, welcome message) VALUES (?,?)', (ctx.guild.id, welcmsg))```

does this seem okay?

@naive sandal

[naive sandal]

why do you need cursor = await db.execute('SELECT * FROM DB_FOR_JOIN_AND_LEAVE') row = await cursor.fetchone() rows = await cursor.fetchall() await cursor.close() await db.close()?

[arctic acorn]

some one said i needed to put it into code

that someone being crazy gamer

im not suremyself tbh

so shud i delete that part @naive sandal

[naive sandal]

yes, you should really look into the sqlite3 docs. They have everything you need

[arctic acorn]

i did read some of it

but got lost halfway thru

oh wait also

i forgot to edit "tablename"

shud i write my table name with spaces

or with underscores @naive sandal

[naive sandal]

WriteItLikeThis

[arctic acorn]

ok

[naive sandal]

column_names_should_be_like_this

[arctic acorn]

AH OK

await db.execute('REPLACE INTO DBFORJOINANDLEAVE (guild_id, welcome message) VALUES (?,?)', (ctx.guild.id, welcmsg))```

BETTER?

oops sorry for caps

@naive sandal