databases

[shell ocean]

if it's a hobby project, nothing much you need to worry about

yes, strings take more space

you can index string columns

but indexing integers is faster

there's also a more conceptual thing

you can look up natural vs surrogate keys

which, again, is part of the relational model of data

knowing the theory is quite helpful IMO

[terse shale]

It's not my project, and the owner makes a big thing out of performance

[shell ocean]

🥴

[shell ocean]

this is a bigger reason

[terse shale]

yee I'll look into it

thanks

[shell ocean]

not to have clan tag as the PK

[terse shale]

I think I understood it-

maybe

well thanks a lot

[quartz island]

When I query my MongoDB Atlas db using pymongo

import pymongo
cluster = pymongo.MongoClient('mongodb+srv://Yash:<password>@cluster0.pfcfw.mongodb.net/<dbname>?retryWrites=true&w=majority')
db = cluster['dbname']
coll = db['drinks']
coll.find()

It's return back a cursor object and not the documents in my collection. :-
<pymongo.cursor.Cursor at 0x13d47bc5a60>
What's the problem here? I don't even know what a cursor object is, I'm just getting started with MongoDB

[jaunty galleon]

I want to make a message track level up bot, i need database right? Is it SQL? If it is, what is the best place for learning it for the level up system?

[burnt turret]

The cursor object is iterable and contains all the documents which matched your query, you can loop over it with a for loop:

for document in coll.find():

[jaunty galleon]

How can i download it?

Anyone knows?

[torn sphinx]

Has anybody tried async support of sqlalchemy 1.4 (beta)?
I would try fastapi on a new project, but:
In the fastapi's documentation "databases" + "sqlalchemy" is the suggested combo,
But sqlalchemy's own asnyc api solution is under development.
So which solution would be better?

databases + sqlalchemy VS sqlalchemy 1.4 (beta)?

https://fastapi.tiangolo.com/advanced/async-sql-databases/
https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html

[restive pilot]

Make sure folder is not containing any spaces in the filename
Roght the folder --> clik rename
Remove all the text, type in sqlite
Hit the enter key.

Now open cmd, type in cd c:\sqlite and hit the enter key.

Actually wait, ur issue is a ACL issue.

[jaunty galleon]

No spaces in file name

[jaunty galleon]

what is it?

[restive pilot]

Move the sqlite folder to:
C:\users\student\

Then open cmd
And type:
cd sqlite

[jaunty galleon]

And where's that

[restive pilot]

If you still get access denied, you can fix the permsssions manually or download sqlite from their website again

[jaunty galleon]

Is this ok?

[torn sphinx]

You don’t need download sqlite?

It comes already with python

[jaunty galleon]

I try to download sqlite and it won't launch

The app

[torn sphinx]

What is the extension of the file?
Is it a zip file?

You can download a portable version (zip) and executable installer

https://github.com/pawelsalawa/sqlitestudio/releases

[jaunty galleon]

It won't let,e see

let me*

[jaunty galleon]

I downloaded the first one

[torn sphinx]

Probably you need this:
SQLiteStudio-3.2.1.zip

or this:
InstallSQLiteStudio-3.2.1.exe

[jaunty galleon]

ok

Which is better>

[torn sphinx]

zip -> You don't need to install it on your machine. Just extract it and run the exe...

[torn sphinx]

I never used this tool... 🙂

[jaunty galleon]

Oh

So i downloaded the zip

what should i do next? move it to the sqlite folder?

[torn sphinx]

Try to extract the content of the zip and run the exe file.

[jaunty galleon]

What is extract?

i mean how can i do i

it

[torn sphinx]

https://support.microsoft.com/en-us/windows/zip-and-unzip-files-f6dde0a7-0fec-8294-e1d3-703ed85e7ebc#:~:text=Right-click the file you,> Compressed (zipped) folder.&text=Open File Explorer and find,zipped folder to open it.

Check the "To unzip files" section

[jaunty galleon]

nvm it is too hard this database

[torn sphinx]

Hey guys, lets say I have 3 tables A B C in database. Each entry in B can have multiple Entries in C. How can A have B and C foreign keys and ensure that C is an option for that B entry
A B C x has a or b as options , y has c as an option.
1 x a
x y b
a c
If A had x and c for example it should error or something

like how do I set up the relationships and constraints, or if there is a better way to set up the tables in the first place

[torn sphinx]

hehe

sec I'll give an example

A = Accounts
B = Account_Type
C = DiscountOptions or something

account types lets say can be Business or Individual
Businesses can have a discount of Null, Dealer, Super Dealer
Individuals can have a discount of Null, Loyalty1, Loyalty2 or whatever they are called

How would you set the tables up for such a requirement

[proven arrow]

@torn sphinx How many discount types can an account have?

[torn sphinx]

Uhmmm, I don't know a handful I guess

[proven arrow]

Then you can have something like this:

accounts

• id
• account_type (FK to account_types table)

account_types

• id
• type

account_discounts

• id
• account_id (FK to accounts table)
• discount_id (FK to discounts table)
  unique constraint on both (account_id, discount_id)

discounts

• id
• name

[torn sphinx]

hmm so basically a many to many relationship between accounts and discounts. but that doesn't ensure that a discount belongs to that account type

[autumn epoch]

@proven arrow
Why am I getting this error:

syntax error at or near "$1"

The code that the error is coming from is this:

await self.client.db.execute("UPDATE users SET ($1) = ($1) + ($2) WHERE userid = ($3)", item, amount, ctx.author.id)

[proven arrow]

You cant use parameters for column names

[autumn epoch]

You can't

[autumn epoch]

I am creating a column for each item, and I am using the item variable as the item

[proven arrow]

One thing you can do is create a trigger

[proven arrow]

What do you mean you can’t?

[autumn epoch]

I meant:
You can't?

[proven arrow]

Yeah not possible with parameterised queries as the query is sent separately from the values to the database

[autumn epoch]

What could I use instead, because one column for each item would be too long

And would extend past the bigint range

[proven arrow]

I don’t understand clearly what you mean

[autumn epoch]

So I am making a column for the items a playing has

[proven arrow]

A column for each item?

[autumn epoch]

Ya

[proven arrow]

That’s not a really good idea.

[autumn epoch]

Then what should I do?

[proven arrow]

It would be better to have an items table where you store all the items. In this table you just need 2 columns (minimum), id and item name.
Then another table that sits in the middle where you store each item a user has. The table in the middle will have 2 columns minimum, (user_id, item_id) where they both are foreign keys to the user and items table.

Does that make sense or did I lose you? 😆

[autumn epoch]

You can have 2 columns in a column?

[proven arrow]

No, 2 columns in the table.

[autumn epoch]

Wait so a table just for items

[proven arrow]

That’s the minimum you need for it to work like that. Obviously you can store more for extra data.

[autumn epoch]

Ok

But if the player has multiple items how would that work

[proven arrow]

Yes, that way you can dynamically add new items/remove

[proven arrow]

So what I said previously with the creating extra tables is a many to many relationship (you can look it up) but that is what you need. This is because a user can have many items, and each item can belong to many users.

[autumn epoch]

Well I have a table just for their userid, balance and items

[proven arrow]

Where’s your users table?

[autumn epoch]

Could I add a dict to it like this?
{itemid: 1, amount: 1}

[proven arrow]

Sure you can, but it might also mean you have more processing to do. And you data would better fit and easier to manage when it is separated into tables/columns like I said.

[indigo mason]

Hi. Would anyone be able to explain to me how sqlite3 transactions work. The docs are a little confusing and contradictory to what I thought.
Does anyone also know of any good (async) sqlite3 libs/wrappers? aiosqlite also doesn't seem to support controllable transactions and asqlite (a wrapper made by the d.py creator seems to be broken in new versions)

I understand sqlite is fairly fast so I am not dead set on the lib being async, however it would definitely be a plus

[autumn epoch]

So like:
Column 1:
Food

Column 2:
Sword

[white geode]

uMongo again

new_submission = {
    "code": in_map_code,
    "creator": in_creator,
    "map_name": new_map_name,
    "desc": in_desc,
    "posted_by": ctx.author.id,
    "type": in_map_type
}
MapData(new_submission)

Last line produces an error: "TypeError: init() takes 1 positional argument but 2 were given"

This is the class:

@instance.register
class MapData(Document):
    code = StringField(required=True, unique=True)
    creator = StringField(required=True)
    map_name = StringField(required=True)
    posted_by = IntegerField(required=True)
    type = StringField(required=True)
    desc = StringField(required=True)

    class Meta:
        collection_name = "MapData"```

How am I giving MapData two arguments?? I am only using one dict.

I can't get a stack trace for some reason so I assume its the MapData class causing this. But could it be something else?

[white geode]

I guess I had to unpack it? **

But I don't know why

[rain plank]

"TypeError: init() takes 1 positional argument but 2 were given"

keyword arguments are not positional arguments

and when you initialize a class it implicitly passes self along with other ags you pass in

[mellow trench]

How do I go about changing DBMS for my sqlalchemy application?

I feel like there should be an automated way to move data from one database to another, if they are supported by SQLAlchemy and I have the table definitions

[hearty nebula]

!resources

[delicate field]

Resources

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

https://pythondiscord.com/pages/resources

[heady hatch]

If I'm trying to query a postgresql database with sqlalchemy, How do I find all rows of a number column that contains a number?

For example, lets say that I want to look for a number 266 and there are other numbers like 26 and 2666 and so I want all of them returned with it's closest match.

I have tried...

Locations.query.filter(Locations.site_id.like(266)

but it always appends the _1 after site_id Like so site_id_1?

[parameters: {'site_id_1': '266'}]

operator does not exist: integer ~~ unknown LINE 3: WHERE locations.site_id LIKE '266'

[proven arrow]

Your error is because your comparing an integer to string which Postgres doesn’t allow unlike some other databases. With pure Sql the way to fix this would be to cast your int column to text type.

So you should try an find a way to cast it with your orm .

[heady hatch]

Thanks, that helps a lot!

[grave eagle]

hi ^^
i'm using sqlite3, how do i check if data exists in the database and if not instert it? or i have to query in for every data?

[torn sphinx]

hi

for a full discord message, what mysql data type should I use?

I think it only supports a maximum of 8mb.

[rain plank]

json maybe?

[torn sphinx]

It is for a tag command, I don't think it needs more than 2000 characters.

TINYTEXT – 255 Bytes (255 characters)
TEXT – 64KB (65,535 characters)

[rugged violet]

Hi, I'm wondering if it is okay to use variables for table names in SQL query. I've googled it up and some says using variables for SQL queries make it vulnerable to SQL injection and some says it's fine

which left me confused

[rain plank]

you said for a "full discord message"

I thought you were talking about a Message object

[torn sphinx]

@rain plank discord limits to 2000 characters per message, I would like to know a way to put a data type close to 2000 characters.

[rain plank]

VARCHAR(2000)

[torn sphinx]

thanks

[half kettle]

Hey! I am trying to make a custom discord.py cooldown system through MySQL. I have 2 tables; cooldownTypes and cooldowns. cooldownTypes contains the name of the cooldown, the length it lasts, the number of times it can be used during that time period and the bucket effected. cooldowns contains a nullable user_id and server_id then a non-nullable name (reference to the cooldown type), expiry and usages. Each function in my Discord bot (commands or events) is given a list of cooldown types that effects it. I am using aiomysql.
So, what would be the preferred way of querying and updating the cooldowns table every time a command/event is issued? I can keep a copy of cooldownTypes in my code if needed as it won't be updated while the bot is online if this would be faster.
The best way I see would be to iterate through each cooldown that effects the current command, SELECT bucket,number FROM cooldownTypes WHERE name = %s and then, based on the result of that query (say it's member) do SELECT * FROM cooldowns WHERE user_id = %s AND server_id = %s AND name = %s AND expiry > now() AND usages > %s then, if anything is returned, display an error to the user, else test against the next cooldown. If no cooldowns are returned at all, I then have to iterate through them all again and SELECT usages FROM cooldowns WHERE user_id = %s AND server_id = %s AND name = %s and if one exists, I have to UPDATE to either add 1 to the usages or renew it. If one doesn't exist, I have to INSERT one. I feel like this is too many requests though; on a command with 3 cooldowns, I end up having to make 12 requests every time it is used successfully! Any ideas on how I can improve this?

[knotty rivet]

Your well-written paragraph might be better for a help channel.

[half kettle]

Alright I'll try

[burnt turret]

I'm curious, what are the custom bucket types you make? Discord.py provides built-in buckets that imo can meet almost every use-case imaginable

[half kettle]

I’ve already asked a few times if some of the things I’m wanting are possible with discord.py’s implementation of it, and every time I’ve got something along the lines that it’ll be easier to just design my own version. But, my buckets are server, member and user which, yes, I believe discord.py provides all of (might not do user seperate to member, not sure)

Stuff like being able to not add to the cooldown if the command returns a sertain thing (for example, I’m wanting it so for my crime commands in my economy, one of my cooldowns is only added to if the crime is unsuccessful), having commands share cooldowns, being able to buy yourself out of a cooldown, cooldowns staying active after the bot restarting etc.

[burnt turret]

Except for the cooldowns staying active part, the rest can be easily achieved with the built-ins really

[inner sentinel]

this is all stuff you'd have to add

How long is the cooldown?

[half kettle]

Some of my cooldowns are to last multiple days

[inner sentinel]

use a database for your cooldowns

don't make them actual 'cooldowns'

[half kettle]

That was the plan, yes... lmao

Eh?

[inner sentinel]

like check in the command body for the cooldown

[burnt turret]

Not adding the cooldown can be done with something like after_invoke/reset_cooldown

[inner sentinel]

or in a cog_check if its all of the ones in the cog

[half kettle]

But, say a cooldown allows a command to be used 3 times and it has been used 2 times, this would allow someone to intentionally get an error or want a failure as i would reset their cooldown to 0 instead of just staying as it is

[burnt turret]

true

[half kettle]

There is a cooldown that effects all the commands in a cog, yes. I still don’t understand your point about using a database and not making them actual ‘cooldowns’. I thought a cooldown was just anything that stopped you from doing something more than x times in y time

[inner sentinel]

well yeah

I just mean don't use the @commands.cooldown()

[spiral meadow]

if iwant to add a login system that remember all users and password would i need to use a database?

[proven arrow]

Yes

[bronze tiger]

is it true that while scaling your foreign key is moved to a separate table and formed a relationship with the main table later on? On ORM based dbs

here i want to make a table out of visit_place by removing foreign key place_id, is this what scalling means or im doing wrong?

[proven arrow]

You don’t need to move the foreign key out to it’s own table. If you did then then there is no longer a foreign key and so then how would you define the relationship?

[bronze tiger]

is this worthy?

[proven arrow]

Sure you can do something like that (pick what works best for your app), but what’s the purpose of the extra table. A user can visit many places?

[bronze tiger]

yes

[proven arrow]

So then you don’t need the visits table. You can have a table for users, a table for places. And then have a junction table in the middle like the one your posted which has foreign keys for both.

The junction table is your pivot table, and you can store extra columns in that table if you like.

Three tables are fine, there’s no need for extra complexity.

[bronze tiger]

a user has a option to visit place or just write review without associating the place and only add pictures

and i made separate table since it creates many null values when tried to accomplish within single table

can i dm just to clear my confusion?

[proven arrow]

What do you mean without “associating” the place?

[proven arrow]

If it helps

[bronze tiger]

🙂

[real bough]

mad libs game == choices based game : rights ?

[hollow abyss]

guyes how can i increment an integer value from a sqllite database

[half kettle]

Well ye that eas the point that was why I was asking here lol. That’s why I was confused

[inner sentinel]

.... This is not #discord-bots no wonder I confused you

[torn sphinx]

for messages in all:
    try:
        if date != messages["date"]:
            print(f" Today - {messages['time']}")
        else:
            print(f" {messages['date']} - {messages['time']} ")
        print(f" From: ", messages['id'])
        print(f" Message: ", messages['message'])
        print("\n \n")

    except:
        print("something went wrong")
``` what is wrong with this message :\

[torn sphinx]

nobody answered :\

[half kettle]

Just be patient. If nobody answers, either nobody is currently online looking at this channel or they don't understand your question. Try clarifying. Saying things like What is wrong with this message :\ isn't very detailed

[torn sphinx]

Its worked btw

[wild robin]

Quick MySQL naming convention question, would it be reasonable to name my tables as 2021_week1, 2021_week2, etc...?

If not, any other suggestion?

[torn sphinx]

hi, How do I change the hypesquad?

class discord.HypeSquadHouse

I dont know how to use it.

[inner sentinel]

Time to learn how to work with databases now, anyone got any tips on how to get started?

[jovial cedar]

creating the database?

learning database query?

[inner sentinel]

I've created it, postgresql, but how to query

also trying to set up pgadmin4, if I can

[jovial cedar]

i never touch postgresql with python or not with python

[inner sentinel]

ah, why not?

[jovial cedar]

i always use sqlite

😐

python3 + sqlite db

if you want to create db with sqlite use db browser for sqlite or the command line

i prefer db browser for sqlite

[inner sentinel]

hm

may I ask why sqlite?

the ppl I'm working with have had postgresql experience but I'm curious to know why sqlite

[jovial cedar]

it outputted as .db files

[inner sentinel]

wdym

starts dowloading sqlite

[jovial cedar]

you can access the database with .db files

and to create the database itself is easier with db browser for sqlite

[inner sentinel]

reading about it, it looks like you can create a database per user or stuff as well

[jovial cedar]

yea

[inner sentinel]

like, user who's accessing via an api

I'm learning a database for making an api

yes I know its going to be hard

[jovial cedar]

good to know

[jovial cedar]

yea inner join and join sucks especially if you have a lot of data

gotta normalize then denormalize it 😄

[inner sentinel]

wdym?

[jovial cedar]

database normalisation and database denormalisation

[inner sentinel]

I was just talking about the overall thing is gonna be hard. Containers in docker on wine in linux with FastAPI and a database, either postgresql or sqlite.

[inner sentinel]

…what that, and why did boss music just start playing?

[jovial cedar]

google it

[inner sentinel]

https://www.tutorialspoint.com/difference-between-normalization-and-denormalization

oh geez sounds fun /s

[jovial cedar]

depends

[jaunty sentinel]

sqlite is also the best c project i have ever seen

[inner sentinel]

lets see if I can install it.…..

using a raspberry pi for development 😅

[jovial cedar]

you can

btw

you might need this

https://stackoverflow.com/questions/16658880/sqlite-user-password-security/16659301

[jaunty sentinel]

if you're using a raspberry pi for development, why do anything locally?

just set up an aws rds instance

ez

[inner sentinel]

don't you have to provide some payment info before you can do that?

[jaunty sentinel]

yes

actually, i forgot if small rds instances fall under free-tier

so it might not cost anything

(it probably doesn't)

but you still need a credit card

[inner sentinel]

I don't have one

I'm still 17 (un)fortunately

[jaunty sentinel]

uh

[inner sentinel]

what?

[jaunty sentinel]

what are you working with databases for

i don't understand then

[inner sentinel]

discord bot and learning?

[jaunty sentinel]

sqlite + flask running in its own docker container

[inner sentinel]

also a whole other thing I'm doing with someone else

[jaunty sentinel]

there's probably a billion blog posts on this

i would skip the docker probably on the raspberry pi as you're resource-limited as it is

[inner sentinel]

yeah

for now I'm just trying to learn databases

rest assured it won't be running on the pi

i suppose I could do sqlite locally and not on the pi.…

[inner sentinel]

@jovial cedar @jaunty sentinel what lib do you use in python for sqlite?

[jaunty sentinel]

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

it's built in

[jovial cedar]

sqlite3

[inner sentinel]

oh

yknow, that makes sense.

[pure mortar]

sqlite3

i used that for a spotify api thingie

[hasty juniper]

how can i append data

[autumn epoch]

How would I delete some information from a table. I am making it so when the player leaves, it deletes their information.

[hasty juniper]

DELETE FROM table_name WHERE condition

[autumn epoch]

Ok

[hasty juniper]

how i can add in db if datatype is bigint[]

await self.bot.db.execute("UPDATE blacklist SET BlockedIdList = ($1) WHERE EventerId = ($2)", user.id, ctx.author.id)
``````cmd
 File "asyncpg\protocol\prepared_stmt.pyx", line 171, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions.DataError: invalid input for query argument $1: 499559069842014219 (a sized iterable container expected (got type 'int'))

[brazen charm]

https://www.postgresql.org/docs/current/functions-array.html, so much for just using my suggestion

[hasty juniper]

Thx

[wooden garden]

https://github.com/KetanIP/dbjson

Guys, I made a literal document database.

If you like it then don't forget to star 🌟 it.

I was not sure that it is the right place to share it, but as it is related to databases I decided to share it.

Let me know your thoughts on it.🙂

[jaunty sentinel]

all the functions are in camelcase

i rate this 3/10

[earnest parcel]

Why should anyone use that and not simply json.dump

[hasty juniper]

camelcase

[slender atlas]

camelCase*

[torn sphinx]

json.dump doesn’t have as much features as what they have in that package

[torn sphinx]

Good work

[earnest parcel]

Such as? I'm really just wondering what the benefits are.

[bright hound]

Anyone here have experience using databases inside docker containers? I have set up an instance of PostgreSQL in docker container, and I have a container running Pgadmin4, and I have added them to the same docker network, but when I try to add my db server to pgadmin I'm getting an error that says my password is incorrect

[ripe quiver]

hi! i don't know if this is the right place to ask this question, but is there something in astropy that holds the value of 1 AU (astronomical unit)?

[torn sphinx]

Hey guys.

mydb = mysql.connector.connect(
    host = "localhost",
    user = "something",
    passwd = "something",
    database = "something",
    auth_plugin = "caching_sha2_password"
)```

How come I get a problem on the auth plugin

NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

I searched it up and it should work.

I am using MySQL

[bright hound]

I was able to connect. I had to change my username to postgres

[torn sphinx]

help

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'case BIGINT PRIMARY KEY)' at line 2")

await temp.execute("""CREATE TABLE cases
        (case BIGINT PRIMARY KEY)""")

[burnt turret]

I'm not sure, check if "case" is a reserved SQL keyword for your database

[torn sphinx]

well

[torn sphinx]

I've been trying to setup a database to log warns, ban information, kick information, etc. I've been recommended PostgreSQL, would you guys say it's a good first choice?

[hazy mango]

For a discord bot PostgreSQL is the standard database

But if you're really new to databases and SQL then it might be a bit confusing

sqlite is a minimalistic SQL language designed more for beginners

But because it's minimalistic there's some things you can't do with it (should be good enough though)

Also make sure you're using the async library of whatever database you choose

[torn sphinx]

Ahh, I understand. I'll have a go at sqlite then, it'll probably be enough for what I need. Maybe once I get better I'll upgrade to something like PyMongo or Postgre :D

[hazy mango]

PyMongo is pretty terrible for discord bots so I wouldn't go near it for that

[burnt turret]

You wouldn't switch from SQLite to mongodb (mongodb is the database, pymongo is the driver)

[torn sphinx]

Oh, erm what would that be called?

[hazy mango]

Aiosqlite and asyncpg

[torn sphinx]

Ahhh

[burnt turret]

mongodb stores data very differently from SQL so switching to it would be an unnecessary pain

[torn sphinx]

Thank you very much TizzySaurus and anand!

I see

Just before I head off, there aren't too many tutorials on aiosqlite compared to SQLite3.

Can I use the stuff I learn in the normal SQLite videos and use them with aiosqlite?

[burnt turret]

I think aiosqlite just needs you to await everything

[burnt turret]

most probably, just add an await before any database call

[torn sphinx]

Ooh, sounds good!

[wooden garden]

@torn sphinx Thanks

@earnest parcel It was just a fun side project that I thought would be cool to make as I wanted to learn more about handling files in Python.

[torn sphinx]

I have a roles table, which I want migrate to a new roles table. How I can do it with a single statement in the insert. So to select data from old table and insert into new.
Old table called roles_user is like:
id
role_id
user_id

New table called user_roles is like:
role_id
model_name
user_id

The model_name will always be the same value of User.

[autumn epoch]

How would I use a variable to update a database with asyncpg?

[burnt turret]

what is it you're trying to do?

[autumn epoch]

item = await self.client.db.fetchrow("SELECT ($1) FROM users WHERE userid = ($2)", item, id)

[burnt turret]

oh

i don't think that will work does it

[autumn epoch]

Also how would I use a dictionary?

[burnt turret]

wdym

like in general?

[autumn epoch]

No for a database

Is there a certain way to do it

[burnt turret]

i can't say without understanding what you want to do

[autumn epoch]

Well I am trying to create the inventory like this:

items = {"1": "3", "2": "5"}

The 1 is the item id and the 3 is the value.

[burnt turret]

right

what do you want to be done with your table

[autumn epoch]

Would that be a text thing

https://www.postgresql.org/docs/9.1/datatype-character.html

[burnt turret]

sure you can dump it into a text column

[autumn epoch]

Ok

Thx

[burnt turret]

@autumn epoch you'd want to put it in a TEXT field, and when you retrieve it you can do json.loads on the data to get back the dictionary form

i'd explained this approach to someone else let me see if i can find it

[autumn epoch]

Ok

[burnt turret]

#bot-commands message the example holds for your dictionary too

[autumn epoch]

Ok

[autumn epoch]

Is a list a dictionary?

Nvm

[zealous parcel]

Hi,
Do you have a kind of easy way to arrange statistics, a specific thing in the database (:

[kindred crow]

why does it show error?

    if menu_prompt==2:
        mycursor.execute('select * from food_type')
        rows=mycursor.fetchall()
        for row in rows:
            print(row)
        sel=eval(input('Enter your choice:'))
        if sel==1:
            mycursor.execute('select * from vegetarian')
            rows=mycursor.fetchall()
            for row in rows:
                print(row)
            val=eval(input('Select sno to alter price:'))
            cs=eval(input('Input new price'))
            ds="update vegetarian set rate='%s' where sno=%s" % (cs,val)
            mycursor.execute(ds)
            rows=mycursor.fetchall()
            for row in rows:
                print(row)
        else:
            mycursor.execute('select * from non_veg')
            rows=mycursor.fetchall()
            for row in rows:
                print(row)
            val=eval(input('Select sno to alter price:'))
            cs=eval(input('Input new price:'))
            ds="update non_veg set rate='%s' where sno=%s" % (cs,val)
            mycursor.execute(ds)
            mydb.commit()
            mycursor.execute('select * from non_veg')
            rows=mycursor.fetchall()
            for row in rows:
                print(row)
        ```

this is the code

[torn sphinx]

hey how to get connection uri for a databae?

[pure dirge]

Lets say I have a sqlite database, how would I display it in a webpage for people to see, using graphs etc

[pure dirge]

at me when you respond please im leaving to general

[honest marsh]

seems quite clear as a working database

[inner sentinel]

thanks, I was just about to ask for this.

[inner sentinel]

if you say so.…

https://cdn.discordapp.com/attachments/772916674076344340/811043054264778792/firefox_wTqRQCP3YQ.png

but I really think you aren't version 13.

[formal flicker]

how do i get the index of an object with json? For example, heres my json file. ```json
{
"users": [
{
"name": "test",
"password": "password"
},
{
"name": "test2",
"password": "pass2"
},
{
"name": "test3",
"password": "pass3"
}
]
}

[celest zodiac]

json_obj["users"][2]

If you don't know the index ahead of time, there's no way to know without iterating through each of the elements in users

[formal flicker]

ok

so im thinking maybe i should do something like this:

{
    "users": [
        {
            "test": {
                "name": "test",
                "password": "password"
            }
        },
        {
            "test2": {
                "name": "test2",
                "password": "password2"
            }
        },
        {
            "ethanedits": {
                "name": "ethanedits",
                "password": "password3"
            }
        }
    ]
}

so if im doing it like this, how can i retrieve the password of a user if i have the name of it. For example if i have the name ethanedits, how would i find that the password is password3

so far, this is what i tried

import json

with open('users.json') as f:
  data = json.load(f)

for user in data['users']:
  print(user['ethanedits'])

but this doesnt seem to work

i just get an error: KeyError: 'ethanedits'

[willow totem]

Is it okay to ask database help here?

[white geode]

Should I be concerned about injection with uMongo? All items in my database are available through discord commands. Is there something I am missing?

There is nothing "confidential" or "sensitive"

What would be the best way to protect against injection, if I do need to be concerned?

[tough root]

@client.command(aliases=['bal', 'BAL'])
async def balance(ctx, *, member: discord.Member = None):
  if member == None:
     member = ctx.author
  await open_account(ctx.author)
  user = ctx.author

  users = await get_bank_data()

  wallet_amt = users[str(user.id)]["wallet"]
  bank_amt = users[str(user.id)]["bank"]

  em = discord.Embed(title=f"{ctx.author.name}'s' Balance" ,color = ctx.author.color)
  em.add_field(name="Wallet Balance", value=wallet_amt)
  em.add_field(name='Bank Balance',value=bank_amt)
  em.set_thumbnail(url=ctx.author.avatar_url)
  await ctx.send(embed= em)

It doesn't show the bal of other user if he is mentioned, it just shows our on >>bal and when we mention still it shows our where it is supposed to show the mentioned members... HeLp!

[torn sphinx]

Yes, you get this error, because you should reference the "name" field

'ethanedits' field doesn't exist in your json

[formal flicker]

i figured it out btw, thank you though!

[torn sphinx]

Ok, cool! 🙂

[young gyro]

How to use mysql with sqlalchemy in fastapi?

[austere portal]

Can I have a auto increasing value which I don't need to supply in sqlite

[austere portal]

like a id for each row but I dont need to manually input it, it will automatically increase after each input

[torn sphinx]

still need answer 😬

[torn sphinx]

aha nice name mango db

[torn sphinx]

for this you can use auto increment value

[austere portal]

I have auto increment to the Id column but when I use my function to append the input to the db I get this error:
sqlite3.OperationalError: table learning has 2 columns but 1 values were supplied

[austere portal]

Thanks

[torn sphinx]

Show code

[austere portal]

import sqlite3

def append_to_db(name):
    with sqlite3.connect("db2.sqlite3") as conn:
        command = "INSERT INTO learning VALUES(?)"
        conn.execute(command, (name,))
        conn.commit()

append_to_db("Test")

[torn sphinx]

After leaning you have to specify the columns still

But just only the column you want to insert

[austere portal]

So, I need to pass the Id as None and it will handle it?.

[torn sphinx]

No just you have to say which column you want to insert in

INSERT into leaning (column_name) values (?)

Like this

[austere portal]

Ok, Thanks I'll try that

Thanks it works.
So if I am inserting values to multiple columns it should be
INSERT INTO table_name (value1, value2, value3) values (?,?,?) right?

[torn sphinx]

Yeah like that

Do you know how to fix my issue?

[austere portal]

Ok, Thanks

[austere portal]

I'll try to

[austere portal]

You can select all the values and insert it to the new table by looping through the values.

[torn sphinx]

I need to do with sql so can’t loop

[austere portal]

can't use a python for loop?

[torn sphinx]

No

[austere portal]

I'll think about another way then

[torn sphinx]

This is my aiosqlite database warn function:

      # Create table
      await connection.execute(
      """CREATE TABLE IF NOT EXISTS warn_log (
        ident integer,
        executor integer,
        victim integer,
        description text,
        guild integer,
        at text
      )""");
      
      id_num = random.randint(1,1000000000)
      await connection.execute(f"INSERT INTO warn_log VALUES ('{id_num}', '{ctx.author.id}', '{member.id}', '{reason}', '{guild.id}', '{ny_date}')")

There's a bit more above and below, but that part seems to work just fine. I'm just so confused on why I'm getting 'column has 5 values but 6 were supplied'

[celest sleet]

sqlite3.OperationalError: table Player has 29 columns but 1 values were supplied how can i force it to ignore the other columns i just want 1 value to begin with

i want the other rows to use what was set to be Default etc

[austere portal]

I think sql has while loops

[torn sphinx]

yeah but issue is i dont know how to use it in a single statement, or what is most efficient way to do this

[torn sphinx]

you do it like here #databases message

just say which columns you want to enter into

[celest sleet]

@torn sphinx it gives me that error

sqlite3.OperationalError: table Player has 29 columns but 1 values were supplied

[torn sphinx]

show code

[celest sleet]

c.execute("INSERT INTO Player VALUES (?)", (discord_id))

[torn sphinx]

yeah so i said what to do

read again the message

[celest sleet]

i dont understand

@torn sphinx ```c.execute("INSERT INTO Player (Discord_Id) VALUES (?)", (discord_id))
ValueError: parameters are of unsupported type

ok now works

had to make tuple

[pure dirge]

Lets say I have a sqlite database, how would I display it in a webpage for people to see, using graphs etc. at me when you respond please im leaving to general

[austere portal]

What about doing something like this:
INSERT INTO table2 SELECT value1,value2 FROM table1;

[torn sphinx]

Thanks i will try like that

How do i create a datbase?

@opal timber.command()
async def userinfo(ctx , member:discord.Member):

roles = [role for role in member.roles]

embed=discord.embed(colour=member.colour ,timestamp = ctx.message.created_at)

embed.set_author(name = f"User info - {member}")
embed.set_thumbnail(url = member.avatar_url)
embed.set_footer(text = f" requested by {ctx.author}", icon_url = ctx.author.avatar_url)

embed.add_field(name = "ID:" , value=member.id)
embed.add_field(name = "Guild name:", value=member.display_name)

embed.add_field(name = "Created at:" , value=member.created_at.strftime("%a,%d %B %Y %I:%M %p:UTC"))
embed.add_field(name = "Joined at:" , value=member.joined_at.strftime("%a,%d %B %Y %I:%M %p:UTC"))

embed.add_field(name=f"Roles ({len(roles)})" , value=" ".join([role.mention for role in roles]))

embed.add_field(name="Top Role:",value=member.top_role.mention)
embed.add_field(name="Bot?",value=member.bot)

await ctx.send(embed=embed)

is this correct?

for userinfo?

@dense belfry
async def userinfo(ctx , member:discord.Member):

roles = [role for role in member.roles]

embed=discord.embed(colour=member.colour ,timestamp = ctx.message.created_at)

embed.set_author(name = f"User info - {member}")
embed.set_thumbnail(url = member.avatar_url)
embed.set_footer(text = f" requested by {ctx.author}", icon_url = ctx.author.avatar_url)

embed.add_field(name = "ID:" , value=member.id)
embed.add_field(name = "Guild name:", value=member.display_name)

embed.add_field(name = "Created at:" , value=member.created_at.strftime("%a,%d %B %Y %I:%M %p:UTC"))
embed.add_field(name = "Joined at:" , value=member.joined_at.strftime("%a,%d %B %Y %I:%M %p:UTC"))

embed.add_field(name=f"Roles ({len(roles)})" , value=" ".join([role.mention for role in roles]))

embed.add_field(name="Top Role:",value=member.top_role.mention)
embed.add_field(name="Bot?",value=member.bot)

await ctx.send(embed=embed)

[solemn cipher]

Does anyone here know mongo because I need some quick help
How would I insert a list into mongo await warnsys.insert_one({'id_': member.id, 'guild': ctx.guild.id, 'warns': []})
doing [] just makes the value of warns null

[pure dirge]

hey i have a bit of a stupid question maybe

[autumn epoch]

@burnt turret
Why is this coming back null?
This is the code:

dic = {"1": "0", "2": "0"}
await self.client.db.execute("INSERT INTO users VALUES(($1), 0, ($2))", id, str(dic))
inventory = await self.client.db.fetchrow("SELECT inventory FROM users WHERE userid = ($1)", id)
inventory = inventory[0]
print(inventory)

The inventory column is a text column

[velvet fable]

I'm getting an error of 'NoneType' object has no attribute 'fetch' on line, result = await client.db.fetch(f"SELECT prefix FROM prefix WHERE guild_id = {ctx.message.guild.id}").
I have client.db defined as client.loop.run_until_complete(create_db_pool())
create_db_pool() is defined as client.pg_con = await asyncpg.create_pool(database="Acer", user="postgres", password="(not my real pass)")
I was told my client.loop.run_until_complete is returning none, but how would I fix that?

[burnt turret]

huh i can't see a reason for that to be None when you're just inserting something there

why are you putting () around the $n though

[autumn epoch]

Dont you have to do that?

[burnt turret]

no

[autumn epoch]

Ok

[burnt turret]

ALTER TABLE notes ADD CONSTRAINT not_blacklisted CHECK (user_id NOT IN (SELECT user_id FROM blacklist));

Is something like this possible? i.e I want to make a check so that no data can be inserted into the notes table if the user_id exists in the blacklist table

(the above query fails, I want to know if something with the same function is available)

🤔 Do i fkey the user_id and then while INSERTing i check for ON CONFLICT

[rain plank]

What fails

[burnt turret]

in the above query?

sub-queries can't be used in checks

[rain plank]

Oh right

[burnt turret]

Solved; used CREATE TRIGGER

[dense belfry]

bruh

[inner sentinel]

I think I just corrupted my first database! 😃

postgresql

[white geode]

How do you properly use ListField() from umongo?

@instance.register
class MapData(Document):
    """MapData"""

    code = StringField(required=True, attribute='_id')
    creator = StringField(required=True)
    map_name = StringField(required=True)
    posted_by = IntegerField(required=True)
    type = StringField(required=True)
    desc = StringField(required=True)

    class Meta:
        collection_name = "MapData"```
The above works, but if I change _type_ to a `ListField(required=True)`, my bot fails

[inner sentinel]

nope, something is misconfigured

(un)fortunately I did not, whew

[torn sphinx]

Hey I use SQLAlchemy for my SQLite3 db and I don't seem to fully understand how to get data from the query when not using .all() method in the end.
Shouldn't I get only one element from the first query in message exists? How do I do it right?

 def message_exists(self, api_message):
        """Checks whether the message exists or not. TODO: Maybe filter by time first and then by user?"""
        query = self.db_session.query(User).filter(User.user_id == api_message.author_id)
        print(query)

        messages = self.get_messages_by_user_num(query.users_num)
        for message in messages:

            if message.content == api_message.content and message.time == api_message.time:
                return True
        return False
``` What `print(query)` shows
```sql
SELECT users.num AS users_num, users.user_id AS users_user_id, users.user_name AS users_user_name 
FROM users 
WHERE users.user_id = ?

Error message:
AttributeError: 'Query' object has no attribute 'users_num' in line messages = self.get_messages_by_user_num(query.users_num)

Looking at debugger right now, and still not sure how to get my user_num.

Hope it's fine for you that I ask for help here, admin said it's is fine when it's very specific for DB

[inland stone]

doc1:

{    '1': 'a', 
    '2': [    {'20': 'b', '21': 'c'}, 
                    {'20': 'd', '21': 'f'},
                    {'20': 'e', '21': 'c'}
                ]
}

doc2:

{    '1': 'g', 
    '2': [    {'20': 'h', '21': 'k'}, 
                    {'20': 'i', '21': 'j'},
                    {'20': 'l', '21': 'c'}
                ]
}

say i have these documents in mongodb. and I want to list all items that have '2.21': 'c'

i.e.
{ '1': 'a', '2': [ {'20': 'b', '21': 'c'} ] },
{ '1': 'a', '2': [ {'20': 'e', '21': 'c'} ] },
{ '1': 'g', '2': [ {'20': 'l', '21': 'c'} ] }

is that possible?

probably not

I guess I have to fetch them all matching docs and then screen the sub-elements in python

[coarse anchor]

hey how do i use excel cell value as input for driver.get(cellValue) to vist that url in cell. I have already imported the cell value and workbookwith openpyxl

[inner sentinel]

asyncpg or psycopg2?

I've heard of both and I want to know the advantages, disadvantages if you use them

for info: I'm working on an async api.

But still want to know the pros/cons of each for most applications

[chilly creek]

@inner sentinel on the with for psycopg2 - I am not sure if asyncpg also supports it as I have not used it. So I guess you would need to do the research there.
But just juding on the name - I would say asyncpg is better than sync (psycopg2) if you work on an asnyc api ^^

[inner sentinel]

ah okay

[burnt turret]

yep, go with asyncpg for an async api

[wheat raven]

How to create pymongo database

[burnt turret]

pymongo is just the driver, mongodb is the database

in the mongo shell you can just do use dbname and it'll create the database if it didnt exist already

if you're doing it through pymongo, i assume doing client["dbname"] will create that db if it didnt already exist (in this example client is an instance of pymongo.MongoClient)

[half kettle]

I know how to do it in an UPDATE statement, but unsure how to replicate this in an event. This is what I would do in an UPDATE statementsql UPDATE economy.members AS m INNER JOIN economy.inventories AS inv ON m.user_id=inv.user_id AND m.server_id=inv.server_id INNER JOIN economy.items AS i ON i.item_id=inv.item_id SET m.bank = m.bank + m.cash, m.cash = 0 WHERE inv.item_id = i.item_id AND i.name = 'bank'So how would I replicate this in an event? This is what I have so far```sql
DELIMITER $$

CREATE TRIGGER ebanking
BEFORE UPDATE
ON members
FOR EACH ROW
BEGIN
IF 0 < new.cash THEN
new.bank = new.bank + new.cash
new.cash = 0
END IF;
END $$

DELIMITER ;```

[torn sphinx]

i am saving user ID in a database i want to do it like
it still says the user's name

[burnt turret]

if this is for a discord bot, just do bot.get_user after you retrieve the id from the db

storing names are kinda pointless

[torn sphinx]

Hiya,
Anyone else store money/currency values for their application not as a integer?

I have always been recommended to store currency it as an integer, but i saw some other software use varchar or float.

[solemn ridge]

Hey, do you know if there is a module that has the same behaviour as Flask-SQLAlchemy but works on FastAPI?

[torn sphinx]

@solemn ridge dont the fastapi docs show example of sql alchemy?

or is flask sql alchemy totally differnt?

[torn sphinx]

Hey, I've got a warn command that uses aiosqlite to upload some information on a warn (reason, executor, victim, time, guild id, etc). I'd like to be able to pull up this information upon searching for a warn victims ID

This is my best shot at it:

@commands.command()
    await ctx.send(results)
  @commands.has_permissions(manage_messages = True)
  async def warns(self, ctx, user : discord.Member):
    guild = ctx.guild
    connection = await aiosqlite.connect("warn_logs.db")
    cursor = await connection.cursor()
    await cursor.execute("SELECT * FROM warn_logs")
    results = "SELECT member, executor FROM warn_logs\
    WHERE member = user.id;"

The problem with this is that results is just a string lmao. Doesn't take any info out of the database :/

@torn sphinx what is the columns of the databese of yours?

like in the warns table what are the column names?

      # Create table
      await connection.execute(
      """CREATE TABLE IF NOT EXISTS warn_logs (
        executor integer,
        victim integer,
        description text,
        guild integer,
        at text
      )""");

There ya go!

lol

ok

They are my columns :D

which is the user id

Victim

ok let me try

2 mins

Victim (user being warned)
Executor (user warning the victim)
Guild (ID to make sure that you don't pull up a users warns from another guild)
at (Time)
description (reason for warn)

you can do like this

async def warns(self, ctx, user : discord.Member):
    guild = ctx.guild
    connection = await aiosqlite.connect("warn_logs.db")
    cursor = await cursor.execute("SELECT * FROM warn_logs where victim = ?", (member.id,))
    results = cursor.fetchall()

results is a list of all the worm items in the database for that member id

you can make loop through this to print it however you like

Ohh

Hmm, not entirely sure how to do that.

where do you want to print it?

I'd get it to print in an embed

For e.g.
I pull up your warns in my server and it checks that the guild the warn was set in == the one it was requested in
It would then display who warned you, when, why, etc

embed = discord.Embed()
for warn in results:
    executor = warn[0],
    victim = warn[1],
    description = warn[2],
    guild = warn[3],
    at = warn[4]
    embed.add_field(here add details)

await ctx.send(embed=embed)

you can loop like this

each time you loop it is 1 row from the results

understand?

Ooh, I think so

Why are there [0], [1], etc at the end of the variables?

because you have 5 columns in the table

0 is the value of first column, 1 is value of second like this

Ohh, understood.

Apologies, I'm quite new to databases. But thank you so much for the help

Been looking to find a proper answer for days now lmao

yeah no problem man ahaha we are all new, lmao i also had a question posted before above as well still wait for reply

[torn sphinx]

let me just bump it in case anyone missed it

[solemn ridge]

It is kind of different, flask-sqlalchemy has a simple setup and it helps u get coding immediately

[torn sphinx]

how can i prevent sql injection attack in mysql.connector

c.execute('INSERT INTO ranks(id,xp,messages) VALUES (?,?,?)',
          (5555, 5555, 5555,))```
keeps giving 
```mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

[burnt turret]

i think the placeholder you use with mysql connector is %s?

[torn sphinx]

isnt that also string formatting

[burnt turret]

it is if you used it like

"string %s" % arg1
``` or something along those lines

its been a while since i've used mysql-connector but i think it uses the same %s as the placeholder

but passes the arguments separately in a tuple as you've done

[torn sphinx]

so i wud

[burnt turret]

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html the doc seems to confirm

[torn sphinx]

c.execute('INSERT INTO ranks(id,xp,messages) VALUES (%s,%s,%s)',
          (5555, 5555, 5555,))```

[burnt turret]

i think that should work

[torn sphinx]

sudi i use it like that

it worked

thanks

[burnt turret]

👍

[novel oak]

making like a DB with txt files is considered a DB?

[torn sphinx]

probably yeah if is organised data

[novel oak]

ok i am doing one, when i finish i will send it to see if theres any trick or easiest way to do it

[torn sphinx]

is postgres soutable for large bots?

[burnt turret]

yes

[torn sphinx]

ooh

can I transfer my db info form sqlite to that?

likethe info?

When designing the data layer using something like repository services, which use abstract classes, how can the connection be abstracted?

like should service implementation be aware of how the database connection is made or do we just pass a connection object to the class?

[torn sphinx]

yeh

[torn sphinx]

how?

i dont know how exactly but i know you can 😅

i think you can dump a sql file of the data and then import it into postgres

maybe you have to modify it a little because i think postgres has some different syntax

[burnt turret]

for outputs from a pg_dump, doing something like psql databasename < output.sql works i think

but then pg_dump outputs SQL queries i suppose

[torn sphinx]

Posted more detail in #software-architecture if someone wants to see take a look

[gaunt garden]

can you set two things in an sqlite UPDATE SET? like

db = sqlite3.connect('mydb.sqlite')
c = db.cursor()
c.execute("UPDATE table SET thing1 = ? and thing2 = ? WHERE key = ?", (thing1Var, thing2Var, keyVar,))
db.commit()
c.close()
db.close()

[torn sphinx]

yeah

[gaunt garden]

sweet, thanks.

[torn sphinx]

can I cache stuff with postgres?

[thorn geode]

Hello .

Let's say I want to commit something, is it expensive (as in does it take time)?

If it is, would it be better to have a task that commits every now and again or would it be best to commit after every modification?

This is for sqlite btw

[torn sphinx]

Can I ask questions about json here?

[paper wigeon]

hi guys, im trying to get a jsonb string, but i want to select null if list is default value of this column, how can i do this, im using postgresql

[mild fable]

Any good database resources

[tropic pilot]

if [z for z in maindb.find({"link": {"$exists": True}})]:
    print("hello, world")
``` @torn sphinx i think this is what you're asking, not completely sure what you mean tho

[thorny locust]

Hello Everyone Whats The Craic

[smoky summit]

does anyone know how to unlock an sqlite database?

[finite lynx]

I am scraping items 22500 of them, could someone recommend a time efficient way to check if their id would be already inside my db?

[acoustic shard]

Hey data nerds, If anyone could provide me some help in the #help-cherries channel I'd appreciate it #help-cherries message

[torn sphinx]

how do I cache stuff into memory with postgresql

[rugged parrot]

I'm having trouble with import pyodbc. can anyone help?

[gaunt agate]

what is the error message?

[restive dust]

Hi, I'm using mongo and am trying to list all items in a collection, I can't iter through them though, what do I use?

Please ping me when responding

[lapis wind]

you can iterate through collection.find()

[restive dust]

Oh I see, thanks

[lapis wind]

np

[twilit hinge]

anyone know if their is a mypy plugin / something related that checks if sql strings are valid, either based on its own syntax validation or (even better if its out there) against a running database?

like sqlx in rust, which checks if sql query strings are well-formed at compile-time by validating them against the database reached at a certain URL

[torn sphinx]

this keeps happening it says inf by None

 @commands.command(aliases=['inf'])
    @commands.has_permissions(kick_members = True)
    async def infractions(self ,ctx , user : discord.Member = None):
    

if user is None:
                    user = ctx.author
                '''
                Displays the person's infractions similar to Modlogs
                '''

                async with asqlite.connect('modlogs.db') as conn:
                    async with conn.cursor() as cursor:
                    
                            

                        
                            query = f"SELECT * FROM Modlogs WHERE user_id = ? AND guild_id = ?"


                            await cursor.execute(query , (str(user.id), (str(ctx.guild.id))))
                            
                            
                            result = await cursor.fetchall()
                            localem = discord.Embed(name = f"Infractions Of {user}" , color=discord.Color(0x2FBDFF))
                            
                            localem.add_field(name =f"All infractions of {user.name}" , value = "All the infractions of the given user")
                            for row in result:
                                infid , guildthing ,mod_name,reasonthing, use_namer, time , command = row
                                user = self.client.get_user(mod_name)
                                localem.add_field(name =f"Infraction By {user} ID : {infid}" , value = f"Type: {command} \n Reason: {reasonthing} \nAt: {time}" , inline=False)
                                
                                await ctx.send(f"**{len(result)} Logs Found**")
                                await ctx.send(embed = localem)
                            if len(result) == 0:
                                
                                
                                localem.add_field(name = "Empty Void" , value = "Its all Empty" , inline=False)
                                

                                await ctx.send(embed = localem)

code

[inner sentinel]

... What are your commas

Have you logged what your user id is before the if?

[near thicket]

I have two separate applications and one of them is constantly updating a value and I need the other application to retrieve that value. Is there any quick way to do this without going through the whole mySQL setup.

[slender atlas]

let those two apps communicate maybe

[near thicket]

They're separate unfortunately

one is a google extension and another is a server

[slender atlas]

that server is yours?

[near thicket]

yes

[slender atlas]

genuinely asking how is this related to databases

[near thicket]

so like I could set up a mysql database for the two applications to talk to each other

but I would have to watch a 30 minute video on how to do that + download mysql

I was wondering if there was some alternative to this

[chilly estuary]

Hi, i just started django. After migrating the db, this was all generated by django, do i need all of this? I just want to have my own personal table

im using mysql btw

[burnt turret]

I believe Django needs all this

[chilly estuary]

dang, i feel like im making this too hard on myself instead of using php as back-end?

[burnt turret]

You don't really have to manage those tables though I think? Django just needs those to keep track of migrations and other internals I think

You can just use the models you make and not really bother about these is what I think (I may be wrong though)

[teal phoenix]

the three columns exists still i am getting this error

any idea why this is happening

[chilly estuary]

aight thanks for answering ❤️

[burnt turret]

It's supposed to be in one set of parentheses (don't wrap each item in a (), put all three in one)

[teal phoenix]

okay

[lyric junco]

    async def notify_user(self, tags, embed):
        db_user = sqlite3.connect('./cogs/database/users.sqlite')
        cursor_user = db_user.cursor()
        authors = set()
        results = []

        for tag in tags:
            cursor_user.execute(f"SELECT AUTHOR_ID FROM follow WHERE TAG_NAME = {tag}")
            result_user = cursor_user.fetchall()
            if not result_user:
                db_user.commit()
                db_user.close()
                return
            results.append(result_user)

        for i in range(len(results)):
            for j in range(len(results[i])):
                for k in range(len(results[i][j])):
                    authors.add(results[i][j][k])

it says no column i in line 36 which is cursor_user.execute(f"SELECT AUTHOR_ID FROM follow WHERE TAG_NAME = {tag}")

[cyan junco]

Can I somehow do UPDATE [...] SET [...] WHERE [...] but if entry doesn't exist it will do INSERT INTO [...]

?

[lyric junco]

how to fix this?

[burnt turret]

On postgres, that (upserting) can be done with INSERT .... ON CONFLICT DO UPDATE ...

[burnt turret]

That error is being raised because there are no quotes around your {tag}, so SQL thinks it is a column

BUT- don't go adding quotes now

[lyric junco]

lol

[burnt turret]

#databases message

f-strings are not a good idea for SQL queries

The message I linked explains why

[lyric junco]

                                db_user = sqlite3.connect('./cogs/database/users.sqlite')
                                cursor_user = db_user.cursor()
                                cursor_user.execute(f"SELECT AUTHOR_ID FROM follow")
                                result_user = cursor_user.fetchall()
                                authors = set()
                                for i in range(len(result_user)):
                                    for j in range(len(result_user[i])):
                                        authors.add(result_user[i][j])
                                for tag in tags:
                                    for author in authors:
                                        author = await self.client.fetch_user(int(author))
                                        try:
                                            channel = await author.create_dm()
                                            await channel.send(embed=box)
                                        except discord.DiscordException:
                                            print(f"Can't dm {author}")

ok so here after I edit it it works but sends a message each time it finds tag in db so instead of sending it once to each user each time it find a tag it sends message (is this better in anyway)?

i'll remove f string

[torn sphinx]

Hey, I've been using a warn command, but when I warn someone for the second time, it rewrites a row in a table. This is my code for context:

      # Define connection and cursor
      connection = await aiosqlite.connect("warning_log.db")

      # Create cursor
      cursor = await connection.cursor()
      
      # Create table
      await connection.execute(
      """CREATE TABLE IF NOT EXISTS warning_log (
        warnid integer,
        executor integer,
        victim integer,
        description text,
        guild integer,
        at text
      )""");

      sqlStuff = """
      INSERT INTO warning_log (warnid, executor, victim, description, guild, at)
      VALUES (?, ?, ?, ?, ?, ?)
      """
      warn_id = random.randint(1, 1000000000)
      await cursor.execute(sqlStuff, (warn_id, ctx.author.id, member.id, reason, guild.id, ny_date,))

      await connection.commit()
      await connection.close()

[cyan junco]

Im using MySQL. Can i do UPDATE [...] ON CONFLICT DO INSERT [...] ?

[burnt turret]

Ah I'm not familiar enough with mysql to answer

Try googling "MySQL upsert" (sorry, kinda busy rn or would've tried finding an answer myself)

[prisma girder]

Are you sure that there are no UPDATE anywhere?

[carmine totem]

i have nice tkinter + mysql login page
how can i make sure it secured

im not going to run it but i want to practice security

[prisma girder]

You can check some OWASP guidelines with SQL injection

[carmine totem]

ima check it thank you 🙂

[prisma girder]

Good luck!

[cyan junco]

ok I will just check if it exist

[torn sphinx]

Apologies for the late reply. No there are no 'UPDATES'

Oh I think I know why

It can only display one variable

lol

Oh dear, how will I fix that

[prisma girder]

Don't worry

[prisma girder]

I don't have any experience with aiosqlite 😦

[wind trench]

    @commands.Cog.listener()
    async def on_ready(self):
        async with aiosqlite.connect("eco.db") as db:
            async with db.execute("SELECT * FROM eco") as cursor:
                row = await cursor.fetchone()
                for guild in self.bot.guilds:
                    for member in guild.members:
                        if await db.execute(f"SELECT id FROM eco WHERE id =?", member.id):
                            if row is None:
                                await db.execute(f"INSERT INTO eco(name, id, cash) VALUES ('{member}', {member.id}, 0)")
                            else:
                                pass
                            await db.commit()
                            print('database online')

sqlite3.OperationalError: no such table: eco

help me 😦

[burnt turret]

Are you sure you have a table called eco

The error tells you there's no table like that

[wind trench]

yes

[burnt turret]

That's the database

Not the table

[wind trench]

Seriously..

So I have the whole table called ECO

[surreal cape]

hi

[torn sphinx]

hi

[surreal cape]

k

[indigo smelt]

How would I set up a database for a user's money/currency (For discord.py)

[past stag]

It depends on the type of database you choose and how many servers your bot is in. I would recomment sqlite for something like that. There are plenty of youtube tutorials on how to use it with python.

[indigo smelt]

My bot is probably just going to be using one server, and I am probably going to use the replit db, it's just I don't know where to start.

[frigid glen]

@wind trench if you want easier way to use aiosqlite, consider using https://github.com/codemation/aiopyql

[past stag]

I've never used that before, so I don't have a direct answer, but I'd start with figuring out exactly which values you need to store and creating columns for them. For a currency bot, you would at least need a column with an identifier for each user (their id is a good option) and a column for their balance. If you want to have a wallet seperate from a bank account or something, that would also need to be included. Then learn how to create new rows in the db and how to edit existing ones. I think repl has a guide for their db with python.

[haughty ravine]

PyMongo:
I want to update my one document. I want to set a key with a value (ban, time) after unset all keys with their values except for _id and ban.
I am noob and dumb, pls help.
collection.update_one({"_id":object.id}, {"$set":{"ban":expiration}}, {"$unset":{"$nin"["_id":1,"ban":1]}})

eck

[haughty ravine]

help pls

[carmine totem]

I have database called database and table called info in the table there is
Id username password privilege

Now 2 questions

Can I add new column called teachers

And 2 can I assign multiplie teacher to the column

[torn sphinx]

yeah

[torn sphinx]

maybe? what do you mean can you give example

hey

i need help

for upgrading my ai

can any one help me?

[carmine totem]

as example i have a user called mark his password is mark123 his privilege is student and the teachers that learn him are michal,moran,eti

[torn sphinx]

and is michal, moran, eti also users in your database?

[carmine totem]

may i dm you and explain my idea

its realy long for here

[torn sphinx]

ok but my english is not so good for explaining 😅

[haughty ravine]

I am creating my discord.py bot with PyMongo. I want to create a block system.
I want to update my one document. I want to set a key with a value (ban, time) after unset all keys with their values except for _id and ban.
I am noob and dumb, pls help.
collection.update_one({"_id":object.id}, {"$set":{"ban":expiration}}, {"$unset":{"$nin"["_id":1,"ban":1]}})

[haughty ravine]

help pls

plsss

How do i unset all keys with their values except for some keys in one document MongoDB?

[burnt turret]

can you try

collection.update_one({"_id":object.id}, {"$set":{"ban":expiration}}, {"$unset":{"$nin"["_id","ban"]}})

[haughty ravine]

TypeError: string indices must be integers @burnt turret

[burnt turret]

missing a : after the $nin

i just copy pasted your query and edited a bit haha

[haughty ravine]

TypeError: upsert must be True or False

[haughty ravine]

XD

[neat forge]

SELECT playerid FROM participants where 92 BETWEEN start and end
So, this query works fine for any number over 100..
But when it's between 1 and 100 , it doesn't return any rows? Why?

[burnt turret]

huh you don't seem to be passing an upsert though, are you?

[haughty ravine]

i dont lol...

[burnt turret]

that's odd

pass upsert=False as a keyword-argument

[neat forge]

Any help

[haughty ravine]

collection.update_one({"_id":object.id}, {"$set":{"ban":expiration}})
collection.update_one({"_id":object.id}, {"$unset":{"$nin":["_id","ban"]}})

this works

but it doesnt remove other keys

{"$unset":{"$nin":["_id","ban"]}} doesnt work

[burnt turret]

huh

do you know what the rest of the fields are?

[haughty ravine]

they may be custom

[burnt turret]

ah dammit

[haughty ravine]

thats the problem

i want to delete each key except for those two

😦

[burnt turret]

i'm not sure how you'd do that, i haven't used unset in that manner before

if you find no other way i guess you could just delete and then insert instead of an update

but that's worst-case scenario

[haughty ravine]

yeah...

[neat forge]

Appreciate if anyone can help me understand this

[dawn hill]

So first off all the playerids are the same

you should get 1 record back.

[carmine totem]

classes
- class_id  = int ai
- class_name = varchat 255

users
- user_id = int ai
- username = varchat 255
- password = varchat 255
- role = varchat 255

user_classes
- id = int ai
- user_id = same as in users
- class_id = same in classes

i did something like this
ai means like random number it grows by one everytime
i have some questions.
1 how do i make the user_id in user_classes to be same as in users
2 how do i add the msg table
3 how do i add the grade table

its for school-login-register-msg-grade system

[neat forge]

I am not getting output..

[dawn hill]

how about select * ? do you get anything?

[neat forge]

1-100 any number, no output
others i do get it

[dawn hill]

do you want the overlap?

[dawn hill]

also whats the datatype of start/end

[neat forge]

TEXT

[dawn hill]

WELL YEAH

[dawn hill]

text isn't an Int

you can't do between on VARChar/Text

[neat forge]

but it works in some cases...

[dawn hill]

you need to parse it as an int

because text can compute to a number, but not the way you think

[carmine totem]

i need help pls

[neat forge]

This doesnt work
SELECT playerid FROM participants where 92 BETWEEN start and end

This works
SELECT playerid FROM participants where 250 BETWEEN start and end

Don't think it can be a data type issue

[mellow scroll]

is there anyway to change the default value of _id so its a smaller string?

so for example,

if each value within the database will have an ID which relates to its number

so

first piece of data inserted will be 1, second will be 2 etc etc

[burnt turret]

you can add your own _id while inserting

but afaik mongodb doesn't have a simple way to autoincrement the way you've described

[mellow scroll]

dang, alright. Thanks a bunch 🙂

I guess ill just leave it and if i ever get to 500mb ill decide what to do

I was trying to host a mongodb locally on my raspberrypi but couldnt figure it out

[floral oxide]

hello, trying to create a pen and papper rpg game with discord.py and was thinking about which database to use and thought about sqlite3

easy to handle database is what I was looking for

[hazy smelt]

MySQL easy too

Also

Speaking of MySQL This statement gives me error of syntax
Alter table Guess Add(Lock int Default 0)

[pure dirge]

How would you reference your main table that ties to all other tables say in a stock database or something

Just curious what term to use in my mind

[hazy smelt]

Uh

[pure dirge]

Key table? Legend table?

[hazy smelt]

Just Name of the bot

Or name of the program

Or database

Btw rusty

Alter table Guess Add Lock int Default 0

[pure dirge]

uwotm8

[hazy smelt]

Can u help @pure dirge this thing giving me syntax error

Alter table Guess Add Lock int Default 0;

[pure dirge]

Oh I'm a noob I can't help sorry lol

[hazy smelt]

;-;

[pure dirge]

I'm just learning how to make databases

[hazy smelt]

It's correct syntax

But still

Its giving error

[carmine totem]

any improvement?

[carmine totem]

@mortal scarab

[mortal scarab]

yes

[carmine totem]

it suppse to be some kind of login/msg/grades system

do you think those table will do?

[mortal scarab]

i will need some more info on that

[carmine totem]

ok one second

main screen
than 3 buttons
then 3 screens(admin login,teacher login,student login
if you login as admin then you move to admin_sesh screen which means you have 3 buttons (insert new user,delete existing user, logout)
insert new user is inserting user to db
delete existing user delete user from db
logout moves to main screen again
for here that whats working

than i wanna make:
teacher login(already working the login) that if your information is right you are moving to teacher_sesh screen which has 3 button(send msg to student,update grades,logout)
send msg to student should have some kind of form with, name of student(from drop menu),text of msg, SUBMIT button. the msg need to be stored on database in new table called msg and i would save a var of the id of the msg so i cant later print it in the student_sesh screen
update grades which i havent think of yet
logout - you know what

student login(already working login system) that if your information is right you are moving to student_sesh screen which has 4 buttons
check grades: you move to new screen that have your grades and class name on the side
check new msg: you move to new screen that have teachers name and msg (if you have)
send msg to teacher: same as send msg to student but upside down.
logout - you know what

---

im trying to think on a way for the msg and grade system as my login,delete,insert already working

btw all this screen stuff in on tkinter

[mortal scarab]

@carmine totem u will need a table with the UID, CID, role,

[carmine totem]

so like move role from table users to users_classes ?

if you can maybe explain not only say the answer i would like to

[mortal scarab]

as teachers wont have classes thy wont need CID so null but students will have so u place values

admim will also be not assigned CID

[carmine totem]

class for teacher will be what class they are teaching

[mortal scarab]

ohh but that will be multiple classes

or just single

[carmine totem]

so like if there is username=Eti123,role=teacher,class=math that means she TEACHES math
but if there is username=mark1,role=student,class=math,arabic,english

then he is in 3 classes

students can be in multiple and teacher only teach one

[mortal scarab]

ohh so rather remove the CID and implement a ClassAssigned column

[carmine totem]

wait what

can u like maybe overwrite on the db design i sent

cause i cant understand

[mortal scarab]

ok

[carmine totem]

thank you very much 🙏🏻

[mortal scarab]

ok wath data fields do u have gimme tat

[carmine totem]

what

[mortal scarab]

UID and stuff

[carmine totem]

when registering new user on admin menu
u need
username
pass
role (drop menu on teacher/student)
class(check box)

that all

[mortal scarab]

the mag and grade stuff

[carmine totem]

mmm have you read this

like it has everything explained there

[carmine totem]

when you login as admin you can insert new user which i said here

[mortal scarab]

i like need exact fieldes u need

[carmine totem]

when u login as teacher u can either send msg to all student in your class(insert msg_content to db & select from role=student, class=class name of the teacher)

also you have grades which is basicly the same thing

but for each person

not to all student

its like private msg of grade

also when ur a student you can send msg to teacher

and watch grades with print your grades

[mortal scarab]

@carmine totem how many classes do u have

[carmine totem]

sorry i do not understand what ur askin

[mortal scarab]

@carmine totem uk classes each student takes

[carmine totem]

uk?

[mortal scarab]

You know

[carmine totem]

so like each student takes idk 3 classes

math,english,arabic

each teacher teaches one

[mortal scarab]

ok

[carmine totem]

so like math teacher should be able to send msg to mark(cause he learns math,english,arabic)
then he should see

it

for that to happend u need to store student classes, msg content,msg sender,username of msg getter

right?

and also id of sender cause y not

[mortal scarab]

ok

@carmine totem

[carmine totem]

uid = user id
suid = sender id
r = reciver id

[mortal scarab]

sender reciever

[carmine totem]

ok let me try and understand it

thank you ofcourse

you dont save the username?

i think it should be added in UserTable

as he need to login with his username

[mortal scarab]

its easy u have a main user table that stores unique users,
thers a grade table with FK uids and stores grades and subjects corresponding to them ,
hten theres the message log that u will fill up \

@carmine totem u can add that in user tble but dont use username as a PK

[carmine totem]

FK? hten?

pk

?

[mortal scarab]

Foreign key

[carmine totem]

ok oh

primary key

yeah makes sense

[mortal scarab]

yes

u should handle conditional logic with the queries rather than implementing them in DB design

and my suggestion will be encoding the classes like a 4 digit number 0111 it from 4 subjexts u use 3

and for UID use (A, T, S)-9 digit number

so u elimanate the role column

[carmine totem]

and then as example user id 1 username mark password mark123 role student classes math arabic
then user id 2 username moran password 1604 role teacher classes math
if she wanna send msg then what will be sql code?
selece from usertable where role=student class=math (how class going to work?[maybe if i do 'math in class?] entry for msg content that stores on msg then suid is userid(cause she is logged ez) and ruid(how we gonna know this?)
then when student logs in we check if there is msg with his rUID if so print on screen

please answer my question if you know how 🙂

[mortal scarab]

so u asking U1 will send msg to U2

INSERT INTO msg (rUID, sUID, msg, time) VALUES ( select UID from UserTable where username == 'U2', U1, "HELLO", timestamp)```

@carmine totem

[carmine totem]

but can i make sUID aka man that is logged in choose rUID by his name?

then i will add full_name to db

then when send msg u have entry:
text = place to type your text
reciver name = list of names then you can choose which one you sending to

by list of name i mean dropmenu

so you have option

u choose

it stores rUID by recviver name, it stores text by msg and that it?

@mortal scarab

[mortal scarab]

@carmine totem yeah that query does that

[carmine totem]

ok and for the grade

it needs to be same thing

[pure dirge]

How can I insert variables into sqlite execute commands>
c.execute('''CREATE TABLE {varname} (

[carmine totem]

help meee

are you here @mortal scarab

i started working on the gui for it

[torn sphinx]

What’s question ?

[carmine totem]

why i cant import onvalue from Checkbutton to mysql column

as data

[torn sphinx]

What is Checkbutton?

[carmine totem]

in gui

tkinter

i did something like this

[torn sphinx]

So first make sure you are getting the value in correct way

[carmine totem]

im not

[torn sphinx]

Try printing value you get

[carmine totem]

i did something realy dumb

ok ima try

[torn sphinx]

First you need to work out how to get the value then you can proceed to insert in database

[carmine totem]

this is my db ok

im also having this gui

in order to know wheter someone in those class or not

i did onvalue='yes' offvalue='no'

then i saved it as var

https://paste.pythondiscord.com/gikaqedaju.apache

code

we talking bout add_user_to_db

and add_user

so my error is why am i not getting any no/yes in the last 3 colums in db

[carmine totem]

Please tag me when answering

[indigo smelt]

How could I check if a db key doesn't exist

Please tag me when answering as well 👍

[fallen bone]

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="test111",
  password="test"
)

print(mydb)

mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'localhost:3306' (10061 No connection could be made because the target machine actively refused it) ???

[inner sentinel]

so I've looked at asyncpg, but is there a similar library that is sync

[half kettle]

TL;DR: What's the equivalent of an INNER JOIN for use in a event/trigger?

I know how to do it in an UPDATE statement, but unsure how to replicate this in an event. This is what I would do in an UPDATE statementsql UPDATE economy.members AS m INNER JOIN economy.inventories AS inv ON m.user_id=inv.user_id AND m.server_id=inv.server_id INNER JOIN economy.items AS i ON i.item_id=inv.item_id SET m.bank = m.bank + m.cash, m.cash = 0 WHERE inv.item_id = i.item_id AND i.name = 'bank'So how would I replicate this in an event? This is what I have so far```sql
DELIMITER $$

CREATE TRIGGER ebanking
BEFORE UPDATE
ON members
FOR EACH ROW
BEGIN
IF 0 < new.cash THEN
new.bank = new.bank + new.cash
new.cash = 0
END IF;
END $$

DELIMITER ;```

[bitter ingot]

Why is this throwing this error?

[half kettle]

Try INTEGER instead of INT

[bitter ingot]

oh, it didn't throw an error

let me try add something

[half kettle]

👍

[bitter ingot]

it works, thanks very much!

[mellow scroll]

Anybody here have any experience in local mongodb databases? Having quite a few issues:

[royal torrent]

what Bd?

[carmine totem]

finally working

it took me some time

but eventually yessss

@torn sphinx that what i did to determine wheater someone in class or no

i did something so dumb

but eventually it worked

[torn sphinx]

Nice

[carmine totem]

   global math
    global english
    global arabic
    math = ttk.Checkbutton(screen5,text='math',variable='math_yes_or_no',onvalue='yes',offvalue='no')
    math.place(x=100,y=147)
    english = ttk.Checkbutton(screen5,text='english',variable='english_yes_or_no',onvalue='yes',offvalue='no')
    english.place(x=160,y=147)
    arabic = ttk.Checkbutton(screen5,text='arabic',variable='arabic_yes_or_no',onvalue='yes',offvalue='no')
    arabic.place(x=240,y=147)

  global math1
    global english1
    global arabic1
    math1 = StringVar()
    english1 = StringVar()
    arabic1 = StringVar()
    math1 = math.state()
    english1 = english.state()
    arabic1 = arabic.state()

    global math_value
    global english_value
    global arabic_value
    if 'selected' in math1:
        math_value = 'yes'
    else:
        math_value = 'no'

    if 'selected' in english1:
        english_value = 'yes'
    else:
        english_value = 'no'

    if 'selected' in arabic1:
        arabic_value = 'yes'
    else:
        arabic_value = 'no'

[carmine totem]

thank you

[torn sphinx]

Might be quicker to use 1 and 0 for yes and no

[carmine totem]

i know but then it will be hard to take it out

from the db

[brave bridge]

@carmine totem SQLite supports boolean values. You can just store True and False

nevermind, it doesn't

🤔

but if you want, you can use sqlite3's more advanced features to create your own datatypes

[fallen bone]

MySQL error: Can't connect to MySQL server on '127.0.0.1' (10061) Pls help

[inland stone]

@fallen bone sounds like your mysql server isn't running on the computer or server where you tried to run it

[fallen bone]

Thanks I will check out

[torn sphinx]

hello when every i open my python file it just closes rightaway

[mint dagger]

youve asked this on multiple channels now, ask in one. (its because your getting an error)

[sleek halo]

ccursor.execute("CREATE TABLE EMPLOYEES (idemp int NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(20), lname VARCHAR(20), datbirth date,timbirth TIME, childnum int(20), adremp text(258), numcard bigint(65535), salaryyemp numeric , contract boolean)")```

out of range why?

[fallen bone]

MySQL error: Can't connect to MySQL server on '127.0.0.1' (10061) Pls help

[frigid glen]

where is the databasee?

[fallen bone]

import mysql.connector

mydb = mysql.connector.connect(
  host="127.0.0.1",
  user="test111",
  password="test"
)

print(mydb)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE mydatabase")

This is what I have

[frigid glen]

and mysql is running on the same machine, or a container or where?

[fallen bone]

on my machine

[frigid glen]

try

mydb = mysql.connector.connect(
  host="127.0.0.1",
  port=3306,
  user="test111",
  password="test"
)

[torn sphinx]

Hey folks, I've made a warehouse management system and I was wondering if anyone could give me feedback on it?

[tired urchin]

Hi, I facing some issues regarding Django database. When I tried to run, it prompt "No Such Table on XXX". Solution that i have taken: 1. Python manage.py make migrations/ migrate . 2. Make sure the APP added into INSTALLED_APP. 3. Delete entire migrations done and re-done the step 1

[fallen bone]

sadly doesnt work

I get this

[sudden thistle]

Is this how json works. its my first time using it.

perms = {811935502059569152, 811935502059569152}
with open("sample.json", "w") as outfile:   
    outfile.write(json.dumps(perms, indent = 4))

[tacit sapphire]

I have a take home assignment for a job interview that requests a locally deployable database (it's setting up a super simple but self contained API). I've usually used mongo or aws, what is the simplest and lightest solution for this? My short googling has landed on Sqlite3.

[half gyro]

That is the correct answer 🙂

it's in the stdlib, so nothing to install

[tacit sapphire]

Thank you!

Sometimes I should trust my googling lol.

[thorn geode]

@burnt turret 👋 If I was to use aiosqlite, would it be best to commit once you've done whatever or use a task to commit periodically?

[burnt turret]

Ah I haven't had to think about this much personally, but I think committing immedietely would be better right? Otherwise anytime you access data before the task has committed the changes, you'd be accessing old data

[thorn geode]

yea

That's true actually

Thanks!

I was just wondering because I thought it might be expensive/time consuming lol

[burnt turret]

oh, does committing take too long?

I've been using asyncpg and it autocommits after every operation

[thorn geode]

I don't think so, I'm yet to use aiosqlite yet lol

I was just reading up on the documentation ahead of tomorrow

[carmine totem]

how do i check if username already exist
using mysql

[burnt turret]

Right, I don't think committing is a very expensive operation (but I may be wrong on this)

[carmine totem]

the username i wanna check stored as username

[thorn geode]

Alright, thank you again!

[burnt turret]

you have a table with a column containing usernames?

[carmine totem]

yes

data base name is database

table is users

and column is username

dont mind the other staff

[burnt turret]

so you could do SELECT username FROM users where username=<some username you want to check> and then if the query returns something, it means the username exists?

if you want all the columns, do SELECT * ...

[carmine totem]

if the username i want to check is stored as var called username

than code will be

query_vals = (username)
handler.execute('SELECT username FROM users where username=%s',query_vals)

am i right or mad wrong

[burnt turret]

really close actually

only problem is that query_vals needs to be a tuple - and to define single element tuples, you need to put a comma at the end

[carmine totem]

here

like this right?

[burnt turret]

(username,)

[carmine totem]

oh ok yeah

realy close indeed

thank you let me try

def error_on_insert_user_username_in_use():
    quary_vals = (username,)
    username_check = handler.execute('SELECT uesrname FROM users WHERE username = username',query_vals)
    if username_check != 0:
        text_label = Label(screen5, text=username + ' is taken \n please choose new one', bg="yellow", fg='black', width="22", height="3",
        font=("Helvetica", 13))
        text_label.place(x=102, y=170)
        error_in_insert()
        screen5.update()
        screen5.after(2500, text_label.destroy())
    else:
        error_on_insert_user_password_too_ez_to_guess

why error?

oh uesrname

found it

[burnt turret]

your if statement is a little off btw, username_check will never be equal to 0 really

[carmine totem]

yes my bad i went realy dumbbb

so whats the right code may i ask
?

[burnt turret]

wait a minute, let me refer the docs

[carmine totem]

👍🏻 👍🏻

[burnt turret]

huh, the site doesn't seem to load for me

So I may be wrong here but that will mostly be a Cursor object?

[carmine totem]

im realy noob i would be much happy if you can explain what do you mean

this?

[burnt turret]

Let me read the doc again then, it's been a while since I've used this and I don't wanna give you incorrect info😅

okay so in your case db is the connection object, and handler is a Cursor object.
after executing the query, the result set is stored in the Cursor object, i.e handler
Now to actually get the values from it, you have to use a fetch method

data = handler.fetchall()

This takes all the rows which had matched your query, and assigns it to the variable data

You can check if the len of this is 0 -> this would happen only if no rows matched your query

[burnt turret]

there are other fetch methods, like fetchone, fetchmany etc

[burnt turret]

Actually what would be better in your case is handler.rowcount - this directly gives you the number of rows that matched your query

if handler.rowcount != 0:
  do something

[carmine totem]

ok thank you so much

def error_on_insert_user_username_in_use():
    quary_vals = (username,)
    username_check = handler.execute('SELECT username FROM users WHERE username = username',query_vals)
    if handler.rowcount != 0:
        text_label = Label(screen5, text=username + ' is taken \n please choose new one', bg="yellow", fg='black', width="22", height="3",
        font=("Helvetica", 13))
        text_label.place(x=102, y=170)
        error_in_insert()
        screen5.update()
        screen5.after(2500, text_label.destroy())
    else:
        error_on_insert_user_password_too_ez_to_guess

thats weird