¯_(ツ)_/¯
#databases
torn sphinx
It works without tuples in other queries
If i don't update whereto the query works
could it be something in my <textarea> tag
cinder sierra
damn it's been too long since i've done actual sql i need to stop using ORMs lmao
i have no clue why i didn't think of that
wintry pasture
that snippet is full of race condition issues
cinder sierra
would you like to help him with it then?
wintry pasture
I don't know how to deal with it in postgres
cinder sierra
ahh
wintry pasture
good point tho. i'll look it up
cinder sierra
well i'm a bit unfamiliar with what makes it one if you don't mind letting me know? genuine question
wintry pasture
if 2 requests come in at the same time and both execute the SELECT statement, then one of them tries the insert before another one tries to insert then depending on the existence of constraints (PK?) you'll have one request going into an exception.
cinder sierra
ahhh now you refreshed my memory lol
explains the logical naming of race condition
in cases like those and assuming that's for a discord bot cause "lvlup_msg" couldn't you theoretically avoid that by putting all of it into a coroutine so there's never one executing before another?
wintry pasture
this is my first day on discord, I don't know what bots are.
cinder sierra
ah lol
essentially just API endpoints for discord to interact with it programmatically
!resources
delicate fieldBOT
cinder sierra
^ bot
torn sphinx
So I am adding a column to a table with ALTER TABLE(im using postgresql), the datatype of the column is bigint[] but I want to add the default as [] not None.
How would one do this?
await self.bot.db.execute("ALTER TABLE automod ADD COLUMN user_ids BIGINT[] NOT NULL DEFAULT '[]'")
This gives me errors
I'm new to postgresql so I don't understand how to make it default bigint list
Please ping me if you respond, thanks
wintry pasture
@torn sphinx ALTER TABLE automod ADD COLUMN user_ids BIGINT[] NOT NULL DEFAULT ARRAY[]::BIGINT[];
how do you ping somebody?
reef hawk
specific question for asyncpg; how do I insert a NULL value from python back into the db (postgres)?
hazy orchid
@reef hawk just insert None? asyncpg will handle it for you
hazy orchid
i think that works yeah (with the ," switched)
hazy orchid
@autumn coral i'm currently using postgres, which i switched to (from mysql? i think) because it has quite advanced datatypes, so i can store more of my data as-is rather than resorting to json-serialized fields etc in the database
autumn coral
Oh
I had a question
Can it be ran with heroku?
Without paying for the database alone?
@hazy orchid
hazy orchid
again, heroku is bad, i don't use it or know much about it
autumn coral
Where do you host your bot then?
hazy orchid
people have such huge aversion to paying any money whatsoever for stuff on the internet
the amount of time it takes to set up systems to abuse free services like heroku is not worth the few $ it would cost to rent a server
hazy orchid
you can run databases on the same computer you use to run the bot, yes
runic pilot
heroku is bad
woof. it's actually a great service that I've been running production software in for years
hazy orchid
on the free tier?
runic pilot
it's great for prototyping, even on the free tier
hazy orchid
can you not just test stuff locally?
in a docker or vm if you need a particular environment
runic pilot
you can and should, no remote environment (heroku included) is not a good alternative to testing locally
hazy orchid
what do you mean great for prototyping tho
runic pilot
for getting something into the world wide web and working so other people can use it
hazy orchid
oh right that makes sense
torn sphinx
I have some questions about noSQL and maybe hbase
what does it mean when we say row keys should not be a domain.. because it would result in grouping
I dont get it
runic pilot
can you give a little more context? and which noSQL db are you using?
torn sphinx
and this says, they can't be sequential either.. I dont understand that either
Big Table.. but it's open source version is HBase
runic pilot
unfortunately it looks like they don't fully explain it
torn sphinx
yeah.. I found a question that's remotely associated with it
but still lacking context..
runic pilot
but usually (from my experience with MongoDB and AWS DynamoDB) the key is used for deciding which disk partition the row should be written to, and in order to not have "hot" partitions (partitions where reads/writes are overly heavy, thus taxing the IO operations of that partition) they need some sort of guarantee that the data will be evenly spread over the disk
the best way to ensure this: hash the UUID key to decide where it should be written
torn sphinx
hotspotting I believe it's called.. yes, that makes sense..
I understand hashing is like, something unique.. so you're saying the uuid for the row key should be unique in the entire table?
is that all
runic pilot
not just the table, globally unique
(seems to be what the book is implying for that database specifically)
but yes, the key should always be unique except for times when the database is tuned to have grouped keys (such as dynamodb)
torn sphinx
got it.. thank you
torn sphinx
so unique, but not ordered
I don't understand how sequential row keys affect distribution of writes and write performance
runic pilot
correct, not ordered necessarily- though again, it depends on the database
let's imagine we're making our own noSQL db
let's take for granted that we want an even distribution of writes throughout the disk so we can have random access
(or some other hand wavey reason)
how should we guarantee that we can, given some list of records, write them as evenly as possible throughout the disk?
torn sphinx
hmm.. maybe do the first n writes on one node and move to the next?
runic pilot
what if the user gives less than n rows? then we've written them all to the same node
torn sphinx
hmm yeah.. maybe randomly keep writing to different nodes.. like 1-1-1
runic pilot
that's a good idea, how should we seed our random function?
sorry let me rephrase that
actually, I think it's fair to say "roll an n sided die" if n is the number of partitions we make on our disk
so now let's talk about the process or rolling that die and reading the value
if we have 100 writes come in, should 1 process be responsible for rolling that die and writing, rolling, writing, ...etc ?
how might that bottleneck our database?
torn sphinx
increased latency in writes because we need to wait for a decision?
runic pilot
I agree with "increased latency in writes"
can you elaborate on the "wait for a decision" part?
torn sphinx
like.. we need to wait for the dice to roll, then read.. so we're essentially waiting for this process to happen every time between writes
runic pilot
yeah, I'd agree with that, just for fun, let's pseudocode the process of writing one row
def write_row(row):
disk_partition = get_disk_partition_from_last_die_roll()
disk_write_address = find_best_empty_address(row.key, disk_partition)
write(row, disk_partition, disk_write_address)
reroll_and_set_global_die()
let's try to rank these line in order of latency (most to least)
keeping in mind that it's kind of arbitrary, since we're waving our hand around a lot here and don't know the details under the hood of the database
how would you rank them?
torn sphinx
what does find best empty address do
I mean, I thought we were only using a random die roll to decide the partition
I think in order of latency, they will all take almost the same time.. except the write which will take a bit longer
runic pilot
you kind of alluded to what finding an empty address does earlier, we have to make sure we don't overwrite an existing row with a new one, so we have to find enough space on the disk to fit the entire thing
again, hand wavey
I would argue that the first and last line are very fast, and the middle two are very slow comparatively, because they're both IO bound (they have to touch the disk in order to do their jobs)
but yes, generally, writing will most like take the majority of the time
so how can we speed our database up if we have to write one row at a time?
(hint: it's something we haven't discussed yet)
torn sphinx
maybe not write one row at a time.. and write parallely?
runic pilot
👏
ok so our database will spin up more threads or processes or whatever we like and write simultaneously
or maybe it does some sort of in-memory queue with a fixed amount of processes/threads
or we could think of 20 other ways to make it parallel
so now that we have multiple processes of our database writing to and reading from disk, what could go wrong?
torn sphinx
hmm.. hotspotting again, because some reads might be more on one node?
runic pilot
er, I should say: "what could go wrong" -> "where did we move the bottleneck"
yeah that could happen, but we're getting to the "why" of it
ok so we're no longer bottlenecked as much at our database software writing a bunch of rows when they all come in
but they're all still sharing the same disk
and disk is physical, we can't really control it much, and it has an upper bound of the number of IO operations it can do
so if we use a "serial" key to decide where to write different rows, we'll end up getting 1-1000 on partition 1, 1001-2000 on partition 2, etc.
making sure that no matter how many parallel processes we spin up, they're all blocked on the same hardware constraint
torn sphinx
ok.. so we need to write sequentially because there's just one disk?
runic pilot
sorry let me clarity: we need to avoid writing sequentially because each partition on the disk blocks one a single write operation, so we need to distribute that work as randomly as we can
one disk might have many partitions, and the partition has the IO constraint, the disk overall has the throughput of the sum of its partitions
and we achieved distribution by using the random die roll
torn sphinx
ok I got the part about the partitions.. im still not sure if we need the random die roll
runic pilot
do we agree that we need to distribute writes evenly across partitions?
torn sphinx
yes
because we dont want hotspotting happening during writes.. where we write more to one partition and not the others
runic pilot
"happening during writes.. where we write more to one partition and not the others" -> "happening.. where we access one partition more than the others"
(just to make sure we're including read operations too, those count toward our IO constraint)
dusty helm
hey guys, what is the best auth library for flask?
runic pilot
flask-login
dusty helm
alright, thank you!
runic pilot
👍
torn sphinx
yes, but reads are managed by the db itself, it also shuffles the data around so we dont need to worry about hotspotting during reads.. because the only thing we can do to avert it is during schema and row key design
runic pilot
well we're playing this scenario as if we're the ones who are building the DB, so that's actually our problem
torn sphinx
ok then
runic pilot
okay one more main point I wanted to get to, we made our write function:
def write_row(row):
disk_partition = get_disk_partition_from_last_die_roll()
disk_write_address = find_best_empty_address(row.key, disk_partition)
write(row, disk_partition, disk_write_address)
reroll_and_set_global_die()
now let's try a "read" function given a row's id (or key)
def read_row(row_id):
# ...uhhh
disk_partition = # ... how do we get this?
oh shoot
torn sphinx
I actually don't understand how reads work based on the row key.. does it do a scan of the whole table? but that's too costly
runic pilot
right
if we want to find the row address on the disk based on its identifier we need a consistent way to find the address of where we would write that row, if we had it
torn sphinx
so we would need to know which partition the row keys for a certain range of row keys would be
runic pilot
well let's back up just a step
if the functionality we're trying to provide is "give me the row, given an ID", we don't need anything about a "range" of keys
just a single one, but yes the concept is there
so if we need to be able to take a row key and find a memory address to read, we need to take that same row key and find the same memory address to write
torn sphinx
ok, so maybe we store that information somewhere.. like a map, during writes, so during reads it's easier to retrieve
runic pilot
ah okay
you've just invented indexes
been around in SQL world since the 70s
the idea of noSQL is that the database itself is the index- you hash the key for the row to find the memory address of where it belongs
torn sphinx
does that mean the hashed row keys are sequential in terms of the memory address
runic pilot
all memory addresses are sequential
so, in a way, yes
but we want to avoid sequential writes to the same partition (as much as we can, we can't always guarantee this very easily)
torn sphinx
I understand why we need to avoid sequential writes to the same partition.. I'm having trouble understanding much else, because I dont grasp the hashing, sharding and indexing concepts fully yet
runic pilot
forget shards and indexes for now
torn sphinx
ok then
hazy orchid
is it doing like some local binary searching and stuff to make sure individual pages don't get too full?
runic pilot
we kind of did the hashing and then called it hashing after the fact
most dbs have a "vacuum" process that does this periodically for them, but it depends on the DB itself
hazy orchid
rather than typical hashmap method of lists or something at each hash
yeah that makes sense
runic pilot
anyway, if we want to be able to guarantee a user that if they give us a row, we can get it for them later given the same key, we need to turn the key of the row into a memory address in a consistent way
(that's all hashing is)
again, it'll depend on the db implementation (some might do that)
torn sphinx
ok.. a hash is a value that's a representation of a row key and it's unique because the row key is unique.. and this value has an address in memory
is that right?
torn sphinx
ok then
runic pilot
but a hash it just a function really hash(f) -> f' so that no matter what input, it'll turn it into the same output
we need it to turn into a memory address
and since memory addresses are sequential, we'd rather the key we're hashing is not sequential, so we don't have a hot partition
torn sphinx
could you tell me this in steps..
if a row key is 12 for example.. it's hashed and we get a value that's a memory address.. so the memory address is the reference to that row key
the next row key cannot be 13, because if it's hashed it'll return the sequential memory address to the previous one
so row keys cannot be sequential.. and must be unique.. am I getting it right?
runic pilot
yes
torn sphinx
what do we do with the memory address.. how do we read it back, and how does the db know a certain row is where
runic pilot
imagine our db only has two functions we're exposing to the programmer:
def save_row(key, row_data) -> bool:
"""Given some key for the row and the row data, return True if we
could write it successfully and False if we encountered some error."""
pass
def get_row(key):
"""Given the key for the row, return the row data or None if no
such row exists with that key."""
pass
if the db only has a key, some data to write and a disk (with partitions) to write to, we need to uniquely turn a key into an address
so that when we go to read it later, we know exactly where it would be, if we had written it
the secret sauce is in the function that turns the key into a memory address
torn sphinx
does that mean the hashing function is what we use to find the memory address again?
but I thought hashing functions worked only one way
runic pilot
exactly right
our function takes a key and returns a memory address to our database code, and we use that address to read from disk and return the row's data
torn sphinx
ok.. so it still works one way, we don't use the memory address through the function to get the row key or anything..
I get it now
thanks for the explanation.. I wouldn't have the basics down if you hadn't walked me through it
runic pilot
glad to have helped!
flat bear
@tawny rivet Might be worth asking your question about Firestore in this channel.
I assumed you've consulted the docs for updating data?
https://cloud.google.com/firestore/docs/manage-data/add-data
alpine flower
Hi - I'm looking to build a SQL database that I can query from the cloud. I'd like to be able to create tables and insert data with Python. I do like sqlite3 but I do not believe it supports cloud functionality. Any recs? Rly appreciate the help
I also looked at Google SQL server but it seems rly advanced for my use case. Looking to build a database with like 7-10 columns (single table) that fetches data every ~10min
runic pilot
heroku offers free postgresql db for up to 10,000 rows
alpine flower
thanks @runic pilot - I need more storage than that but wouldnt mind paying up a bit
runic pilot
the next tier up for them is 10,000,000 rows at $9/mo
or AWS offers RDS instances in the free tier for t2.micro instance sizes
that's probably the best bet if you have more than 10,000 rows but not heavy traffic
alpine flower
ok, I just get rly lost in the documentation and just need to see like one sample python script that shows how someone hooks into the cloud instance to insert tables/update data.. guess I'll have to check these places out a bit moee
I'm very experienced in Python and SQL but have never built my own db before
runic pilot
if you can run the commands to get a db working locally then you can run them on a cloud server, most DBs offer a --host flag when using the CLI and any in-code solution (Flask-SQLAlchemy, Alembic, etc.) will be the same way
alpine flower
@runic pilot thanks very much. I hope I can help you out one day
runic pilot
👍 I'm sure you will
blissful minnow
Hi, i want to store and process data for a bidimensinal map with 10000 x 10000
Any advice? I want to save and prcess the position in a map to store in specify position
grim carbon
Hey, I need some help with a project I am working on. Someone please pm, I need help asap lol
wintry pasture
@blissful minnow too vague. what's the actual issue?
grim carbon
name 'playsound' is not defined . even though I installed playsound?
Please someone lol
blissful minnow
I have a 2d map and need process all availables position. 10000 x 10000
And this info should be used by other threads
Every thread read/save in random position
What is the best way? Because i need initialize with bidimencional array with empty values
?
runic pilot
you probably want a database
wintry pasture
@blissful minnow
N = 10000
M = dict((i,dict((i,0) for i in range(N))) for i in range(N))
for i in range(N):
for j in range(N):
M[i][j] = 1
This takes 14 seconds to create and 10 seconds to fully traverse. Will that do?
blissful minnow
Mmm maybe, how i know the memory used of the array?
Because this is loaded at the begin and during all process the threads going to read/write in soecify positions
runic pilot
take a look at the memory of a python process before allocating the array, then after and subtract the difference
blissful minnow
Thanks
cinder sierra
@blissful minnow you can also look into hdf5 if you'd like. python has a library that supports it called h5py and it supports SWMR (Single Writer Multiple Reader)
blissful minnow
Thanks @cinder sierra
grizzled rock
how do i install pymogo
on ubuntu
i literally followed their instructions and it doesn't work
i really need help
upbeat lily
Do you have a link to the instructions you followed and a description of how exactly it won't work? I've never touched it so probably can't help - but that info will make life easier for someone who sees this and might be able to help
grizzled rock
cinder sierra
Define doesn't work @grizzled rock?
grizzled rock
nvm
opal seal
hey im using flask and mongo and im trying to figure out how to add to an editing/appending to an existing set of data
i want to append a dict to "args"
i tried append and insert which didnt work
do i use .update instead?
cinder sierra
if that's a dictionary itself yes
cinder sierra
data['args']['new_key'] = new_data
or data['args'].update({'new_key': 'new_data'})
opal seal
ok thank you
dusty helm
Guys, i'm getting an strange exception, i'm studying relationships in databases and got this error :
sqlalchemy.exc.InterfaceError: <exception str() failed>
it was right after trying to add role_id to user
# The Models
# - User Login Auth
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(20))
password = db.Column(db.String(25))
email = db.Column(db.String(50))
posts = db.relationship('BlogPost', backref='posted_by')
role = db.Column(db.Integer, db.ForeignKey('role.id'))
def __repr__(self):
return 'User: ' + str(self.username) + f" [{self.id}]"
class Role(db.Model):
__tablename__ = 'role'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20))
users = db.relationship('User', backref='role_of_user')
def __repr__(self):
return str(self.name)
i think it's on db.relationships (Role) and role (User)
but idk
slender loom
hello there, i am using sqlite3 python to store character and user information for a discord rpg-bot.
while updating the character table for a specific character id i am getting a unique constraint error, although none of the values being updated are assigned as unique values.
the traceback:
Traceback (most recent call last):
File "C:\Users\knorke3\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
ret = await coro(*args, **kwargs)
File "c:\Users\knorke3\Desktop\Seminarfacharbeit_Python\Discord Bot\Avabot_Real.py", line 2987, in Battlereq
await sqlsave(ctx, slot1, character1, True, id1)
File "c:\Users\knorke3\Desktop\Seminarfacharbeit_Python\Discord Bot\Avabot_Real.py", line 680, in sqlsave
cursor.execute("INSERT INTO character(CharID) VALUES(?);", (str(UID0),))
sqlite3.IntegrityError: UNIQUE constraint failed: character.CharID
I would much prefer if someone could help based on a screenshare within a discord call as i dont really know what code is actually relevant.
the create statement:
cursor.execute("""
CREATE TABLE character(
CharID INTEGER,
Race BLOB,
Charname BLOB,
Age BLOB,
Affinity BLOB,
Stamina BLOB,
Spells BLOB,
Skillpoints BLOB,
Strength BLOB,
Defense BLOB,
Willpower BLOB,
Arcana BLOB,
Mana BLOB,
Hp BLOB,
ShieldHP BLOB,
Maxmana BLOB,
Maxhp BLOB,
Maxstamina BLOB,
Dead BLOB,
Trickstep BLOB,
Shield BLOB,
Battle BLOB,
Items BLOB,
Mindblock BLOB,
Guard BLOB,
RGuard BLOB,
Battleacc BLOB,
Battlereq BLOB,
Turncount BLOB,
Activeturn BLOB,
Won BLOB,
Spelldmg BLOB,
PRIMARY KEY(CharID));
""")
the update statement:
cursor.execute("UPDATE character SET Race = ?, Charname = ?, Age = ?, Affinity = ?, Stamina = ?, Spells = ?, Skillpoints = ?, Strength = ?, Defense = ?, Willpower = ?, Arcana = ?, Mana = ?, Hp = ?, ShieldHP = ?, Maxmana = ?, Maxhp = ?, Maxstamina = ?, Dead = ?, Trickstep = ?, Shield = ?, Battle = ?, Items = ?, Mindblock = ?, Guard = ?, RGuard = ?, Battleacc = ?, Battlereq = ?, Turncount = ?, Activeturn = ?, Won = ?, Spelldmg = ? WHERE CharID = ?;", (chartosave.race, chartosave.charname, chartosave.age, chartosave.affinity, chartosave.stamina, spells, chartosave.skillpoints, chartosave.strength, chartosave.defense, chartosave.willpower, chartosave.arcana, chartosave.mana, chartosave.hp, chartosave.ShieldHP, chartosave.maxmana, chartosave.maxhp, chartosave.maxstamina, chartosave.dead, chartosave.Trickstep, chartosave.Shield, chartosave.battle, chartosave.items, chartosave.mindblock, chartosave.Guard, chartosave.RGuard, chartosave.battleacc, chartosave.battlereq, chartosave.turncount, chartosave.activeturn, chartosave.won, chartosave.spelldmg, int(str(ctx.author.id) + str(slot))))
chartosave is a Character object, which is a custom dataclass
running python 3.8.1 in vscode for debugging
slender loom
will go offline for today and should be back around 11-12am gmt+1 tomorrow :)
true kiln
What would you guys recommend?
CREATE TABLE profiles (
`id` INT PRIMARY KEY AUTO_INCREMENT,
`user_id` BIGINT NOT NULL,
`level` INT DEFAULT 1,
`experience` BIGINT DEFAUlT 0,
`reputation` INT DEFAULT 0,
`coins` BIGINT DEFAULT 0
);
or
CREATE TABLE profiles (
`user_id` BIGINT PRIMARY KEY,
`level` INT DEFAULT 1,
`experience` BIGINT DEFAUlT 0,
`reputation` INT DEFAULT 0,
`coins` BIGINT DEFAULT 0
);
Always use an id? Or is it okay to skip it?
umbral ridge
I would keep the id, but that's just because I'm not sure if user_id and id will always be in sync
true kiln
What do you mean with in sync?
keen maple
I'm having a problem with connections from a pool not seeing rows inserted after the pool is created with aiomysql. I've tried to search around but cant seem to find any solution
The rows inserted btw is inserted from a completely different session
cinder sierra
@true kiln no need for id, use second one
@keen maple sounds like it's not being properly committed and synced
cinder sierra
nice
autumn coral
I installed psql on my pc and I wanted the username to be different
When I tried to alter its' username, it said "I can't alter the session user" (or something like that)
So I made a new user
But when I try to login, it doesn't let me to!
autumn coral
Okay, I solved this problem
But now that I've successfully renamed the database, when I do psql -U wizzie (new username), It requires me to add in the database, for some reason?
It didn't do that before 
cinder sierra
Mind posting your solution for the last one?
Not an issue I have but it's useful reference for others
autumn coral
Sure
1: psql -U postgres (Or the main/admin user which you'll be using to make a new user)
2: CREATE USER testuser WITH SUPERUSER PASSWORD 'password'
3: \q
4: psql -U testuser -d postgres
5: ALTER USER postgres (the user to rename) RENAME TO wizzie (new username)
NOTE: Don't worry if it shows *password reset* or something
6: ALTER USER wizzie (new username) WITH password 'password for the renamed user, for instance, wizzy'
7: \q
8: psql -U renamed_user/wizzy -d database
9: DROP USER testuser;
10: Enjoy!
Note: This was done inside of the windows command prompt
@cinder sierra ^
cinder sierra
👌
Note, pretty sure going into the postgres db is necessary for altering "root" user, not random
glacial cosmos
how do I join 4 tables in a single query?
autumn coral
Wait, good question, how do you measure a database/ where is it located when I create one?
cinder sierra
Should still be relevant
pg_size_pretty(pg_database_size('dbname'));
hazy orchid
SELECT pg_size_pretty(pg_database_size('dbname'));```postgres always needs a keyword
hazy orchid
it's probably just something about the default block size
so you can store a fair bit of data before it increases size past that 8MB
cinder sierra
^
though do note my reference of "a lot of space" is relative to what i work with which is usually in the hundreds of GBs lol
autumn coral
oh
cinder sierra
until your bot gets in maybe a hundred servers though each with a decent amount of people and interacting with the DB i wouldn't really worry much about space
slender loom
I am now back online in case anyone knows what might cause my problem:
https://discordapp.com/channels/267624335836053506/342318764227821568/702610673611309106
autumn coral
I accidently forgot to close my database on psycopg2 to run a test code, is that a big problem?
cinder sierra
@autumn coral I wouldn't worry too much about it because they should have built in a handler for cases like that. Just know that if it didn't get handled it's a suspended open connection
torn sphinx
I was curious with learning PostgreSQL that can you create database.db file for the database and handle that like postgres? Or if I want something to be hosted with the database, how do I make it happen?
(I was curious after I saw @autumn coral's question)
cinder sierra
i don't understand that really at all, what?
torn sphinx
@cinder sierra I was curious to know if I can make a postgres database from my python code with a different user, not altering the current one
cinder sierra
yes?
cinder sierra
yes you can make a database using the driver.
autumn coral
Trust me @torn sphinx, I've been asking the same question for over 3 days
cinder sierra
cur.execute('CREATE DATABASE new_db')
autumn coral
Soul is really a great Soul if it does this
cinder sierra
the driver has full control of the db as long as the user you're connecting as does
torn sphinx
I don't have a connection
cinder sierra
ensure the user you're connecting with is superuser or has create_db perms
so you don't have a database
autumn coral
I don't
torn sphinx
I'm afk for a min
cinder sierra
you can't create and connect to something that isn't set up
psycopg2 is only meant to interface with an active db
if that db is active and you log in, you can do anything with psycopg2 that you can in terminal as that user
autumn coral
But an SqLite db can be made with a database.db
autumn coral
Huh
cinder sierra
sqlite is a single file database. postgres is a system
autumn coral
My entire problem is that I'm hosting a bot to heroku
(I've heard enough of "Don't host a bot on heroku", lets not get to that please)
And I want a PostgreSQL database
Now I hope you understand my problem here
cinder sierra
well lol you already said the answer to that
autumn coral
I didn't notice
cinder sierra
i don't know if heroku even provides database solutions and given that you're using heroku, you can't host your own db
autumn coral
Can you tell me where exactly did I do it?
cinder sierra
don't host a bot on heroku
autumn coral
Oh.
autumn coral
HALLELUJAH
Thanks for comming!
Drops the boulder on rdbaker
You take the load to explain me now
upbeat lily
Why not just grab a DigitalOcean VPS? You get 100$ free credit which is ~20months of VPS hosting
autumn coral
Please
cinder sierra
he jumps in any time somebody mentions heroku to, for some reason, redeem it
runic pilot
I ❤️ heroku
autumn coral
lmfao
upbeat lily
that would be much better than forcing a bot onto Heroku
autumn coral
🤣
cinder sierra
with $300 for 12 months as well
cinder sierra
in all seriousness though if you're using heroku and hosting a discord bot that you want to be public and popular, it'll only end in disaster
autumn coral
Or is there a guide/documentation on that?
in all seriousness though if you're using heroku and hosting a discord bot that you want to be public and popular, it'll only end in disaster
@cinder sierra Its a small server
I made it for fun for a random kiddo
runic pilot
@cinder sierra why is that?
autumn coral
He was like "Can u mak meh a bot for me server pls"
cinder sierra
only 10k rows for free and the traffic?
runic pilot
cinder sierra
heroku's never exactly had quite a good reputation lol
runic pilot
you're welcome!
autumn coral
Be there if I need help with heroku please 😅
runic pilot
if the issue is price, most cloud providers are more expensive because their starting tier is more than you'd need
cinder sierra
$3/month is hardly expensive
cinder sierra
scaleway
autumn coral
3$ a month for a kiddo? I'm down!
cinder sierra
2vCPUs, 2GB RAM
i use one to host my jenkins, postgres, and 2 bots
and it's barely under load
runic pilot
yeah, to be fair my only frame of reference is AWS or Heroku lol
autumn coral
(Besides the fact that I'm hosting my bot for $10 a month, if we're talking about budget)
upbeat lily
50$ a month?
upbeat lily
thats absurd
cinder sierra
still even $10, who?
upbeat lily
I'm guessing you meant /year
autumn coral
I have 5 bots
cinder sierra
he corrected
autumn coral
That was the total
upbeat lily
just rent a dedi
autumn coral
Its the great linode
autumn coral
I'm not giving the money though
cinder sierra
also @runic pilot GCP has fairly reasonable prices honestly compared to AWS and as you can imagine, being google, has a great ecosystem
autumn coral
@upbeat lily I'm not paying
upbeat lily
then where did the 50$ budget come form
autumn coral
They are giving the money
@upbeat lily Some people are giving it away for their website
Made with flask
runic pilot
I'm only seeing 8eur/month starting price for managed psql servers on scaleway
(unless I'm missing something)
autumn coral
There's another guy making the site
He doesn't want to make the database
He's not familiar with the language
-He says
cinder sierra
@runic pilot those are full-scale managed psqls for production. i just put postgres on a $3/month VPS
runic pilot
oh I see
autumn coral
Meanwhile: Me: searches up on youtube of "how to learn psql" and gets a 4 hour video, and finishes it
Clever boy, aren't I?
🧠 👈
cinder sierra
@runic pilot
autumn coral
I love the way how it seems so complex for people having no knowledge of computer science
If I remove all the duplicates, its hardly 6 lines
Its 7, I counted it
@runic pilot wow
Btw
@runic pilot The ram on the free plan is 0 bytes..
-_-
runic pilot
it's shared tenancy, so you're not getting anything dedicated to you
autumn coral
And I though america was great once again..
@runic pilot What is the performance gonna be?
runic pilot
you'll want to upgrade it before you put it into production
autumn coral
huh.
runic pilot
any production product I make, I prefer to pay for the db & server because I get dedicated resources (plus things like automated backups and server patching)
I'm not, I've just used it for years
cinder sierra
how much are those dedicated resources @runic pilot?
autumn coral
ok
Nice question @cinder sierra
How much will I have to pay for them in separate?
runic pilot
I pay $7/mo for the app server and it handles ~1M queries a day
autumn coral
I'll need to get off my computer now
Time to make my eyes cool down
They're on fire
I'm having a strain
cinder sierra
and the hardware?
runic pilot
I've never actually paid for the $9/mo postgres server, any time I need a production server, I need way more than 10,000,000 rows
cinder sierra
do they tell you the hardware given?
autumn coral
Woooah..
runic pilot
probably somewhere in the fine print, but I haven't had any issues with it, so I never looked into it too much
cinder sierra
i'd definitely swap to someone else then if this is like a hobby thing
runic pilot
you can probably tell I'm more of a "fast & loose" than "build it right the first time" dev
cinder sierra
i tout scaleway these days but i used DO before that
autumn coral
@runic pilot Can you give me the link to the website where you'll need to pay for about 50,000 rows?
cinder sierra
with scaleway you'll definitely get more for less
autumn coral
I mean, if you're getting A MILLION queries with $7, there's probably a much cheaper plan for just 50,000 rows
runic pilot
again, my only frames of reference are heroku (starts at $9/mo for the paid DB) and AWS (starts at ~$16/mo for the paid DB)
runic pilot
well that's 1 million requests to my app server
cinder sierra
i know, i'm just letting you know of what i find better solutions
autumn coral
Is there a website that offers me database and bot hosting for below $10/month?
runic pilot
and we haven't even talked much about GCP or azure
autumn coral
It gives you database, aye?
runic pilot
damn maybe I should start a hosting service for these things lol
cinder sierra
or a vast swarm of others
you set it up yourself
which isn't hard
imo a VPS is millions times better than third party services unless those services offer advanced configurations
such as GCP and AWS
autumn coral
@cinder sierra Can you give me the link of scaleway
runic pilot
can you explain why?
cinder sierra
because being able to do whatever i need to my programs is vital to me
if i need to constantly update the server, just set a cron task, not difficult
runic pilot
👍 great reason
cinder sierra
GCP and AWS are the only two i'm familiar with that i'd be willing to pay that premium for
cinder sierra
because i know for a fact they'll handle my instances well
autumn coral
My eyes can't get it anymore
cinder sierra
mate it's a quick google
autumn coral
I can't get it
There are a LOT of categories
Which one is for me
I don't knowwww
cinder sierra
dev1-s
caps at 3 euros/month. you pay less if the instance isn't on all the time
autumn coral
oh ok
God me eyes
Bye bye
I ordered the eye drops
The whole pandemic is being an issue
For everyone
cinder sierra
@runic pilot i'm not sure if you use heroku for work or something but the clients my employer tends to work with all prefer full control of their communications, instances, and containers. heroku seems way too hands on for any of those companies to ever consider it especially with the lack of transparency on what resources are underlying their services and that as well is a reason i would never pay $9/month for an unknown amount of hardware
runic pilot
I've used it and seen it used by companies who want to make a proof of concept without much devops work
rarely seen it used by companies who are actually generating revenue
cinder sierra
if it's a proof of concept what's the enticement for devops period?
runic pilot
*without developers with devops knowledge
cinder sierra
i still mean if you're just trying to show a proof of concept to a board or a team you wouldn't really need devops just somebody opening it localhost real quick to show performance and how it works in a demo
runic pilot
when I say proof of concept I'm talking about a product website that people can sign up on and use, I should really have said MVP
cinder sierra
well, i do still highly recommend to you something like GCP, DigitalOcean, or Scaleway. they make it extremely simple to have groupings of instances to simulate something like that on a basic level and scale up from. saves a lot of money in the long run and the investment to learn is almost nil. if you decide to give any a shot, i'm familiar with all three and wouldn't mind helping.
runic pilot
thanks, appreciate that- I'm a one man team right now, but once we need to scale and cutback on costs by starting to look at other providers, I'll take you up on that!
delicate fieldBOT
Hey @marsh bear!
marsh bear
Oops
willow shard
Is there a smart to achieve this:
A User register on a web app and even if other tables are unaffected by its registration gets filled in an empty character so i can update instead of insert?
Because someone might want to fill out the others later.
slender loom
just want to quickly put a reminder to my previous question: https://discordapp.com/channels/267624335836053506/342318764227821568/702610673611309106
-if it is not formulated well or any additional information is required please tell me. i am also open to sharing my screen for a better view at the code :)
cinder sierra
@willow shard i believe you're looking for the NULL keyword on a field. you can insert data without including a value for that field and later update that entry with a value
willow shard
Lol
cinder sierra
?
willow shard
Ok so i haven't know that NULL is equal to that. So with update table i can write to field with NULL?
cinder sierra
yes
cinder sierra
NULL means the field can be left empty when inserting an entry. NOT NULL means the field is required and cannot have an entry inserted without that field being filled
willow shard
Allright this solves it
Something else, are html inputfields bad? Should i use Flask-WTF instead to prevent sql injection or id that no problem for security reasons?
cinder sierra
always use security precautions. in this case, yes, use WTF to help prevent injections. also make sure to use tuples or other methods when passing arguments to your queries
a few examples:
cur.execute('INSERT INTO users VALUES (?, ?)', (request.form['username'], request.form['password']))
cur.execute('INSERT INTO users VALUES (%s, %s)', (request.form['username'], request.form['password']))
cur.execute('INSERT INTO users VALUES (:username, :password)', {'username': request.form['username'], 'password': request.form['password']})
from this section of PEP 249 https://www.python.org/dev/peps/pep-0249/#paramstyle
willow shard
Need to change that tol. Will look at this tut later today. Also i give admin functions for manipulating the db to a registered account i verify through the html page by using a session key value pair check inside a code block. Is that bad too?
So is it better to do "superuser" db-manipulating things in different html page than a else/if codeblock like mentioned?
Man my webapp is for sure a playground for every experienced dev 😂
At least i have salted passwords
cinder sierra
if you have access to the DB definitely only do db editing through direct access rather than something like that
willow shard
But updating/deleting things is okay as long as i cant dumb tables or whole db?
cinder sierra
i would never use requests to delete any tables or databases, ever
updating sure, because user data can be changed and a request should be sent to reflect that
but major changes to the db should never be nonchalantly passed around like post requests. they should be purposeful
tame quartz
Question - whats the best approach to running a query through many databases on a server (SQL Server in this case). i could accomplish it with a loop but i'd like to see if theres something a little quicker
since thats sequential and not parallel
cinder sierra
@tame quartz same query across different databases, just to be clear?
tame quartz
YEP
cinder sierra
from concurrent.futures import ThreadPoolExecutor, as_completed
def insert_data(db, query, data):
db.execute(query, data)
# Adjust max_workers as needed. It is the maximum amount of threads allowed to be spawned by this executor.
with ThreadPoolExecutor(max_workers=4) as executor:
query = 'INSERT INTO . . .'
data = (1, 2, 3, . . .)
futures = {executor.submit(insert_data, db, query, data): db for db in dbs}
for future in as_completed(futures):
print(f'Completed query for database {futures[future]}')
very quick and likely poor example
just for example sake
but that's the idea
tame quartz
ok, so in this case it would be a read but that works
slender loom
thanks for all the help with my question, the response really was overwhelming - turns out i was updating with the exact same data as was already in the database - problem fixed :D
cinder sierra
@slender loom to be honest, seeing your massive create table really turned me off from whatever was going on. you definitely don't want to be shoving all of that into a single table. it can cause a lot of problems. look into foreign keys
slender loom
will do - thanks :)
torn sphinx
How do I make a python database?
runic pilot
first, change your frame of reference, you're making a database that you can access and use in python
python itself has no opinion about a database (aside from sqlite)
the next step is to figure out what database you want to use- do you have one in mind?
slate bluff
I'm using the mysql.connector library in my program, I'd like to be able to make connections using the with statement so that I do not need to rewrite the same try/finally syntax each time I want to make a connection.
Is anyone aware of an existing example that handles this and cursor objects along with error handling? I'm having difficulty finding anything related to this library online considering the broad terms that make up the name of it.
slate bluff
I've been able to create this
from utility.config_manager import db_config
import mysql.connector
class db_connection:
def __init__(self):
self.db = None
self.cursor = None
def __enter__(self):
self.db = mysql.connector.connect(**db_config())
self.cursor = self.db.cursor()
return self
def __exit__(self, exc_type, exc_val, exc_tb):
self.cursor.close()
self.db.close()
Which I've successfully returned the expected data using
with db.db_connection() as abc:
abc.cursor.execute('SELECT id FROM `projects` ORDER BY `projects`.`id` DESC LIMIT 1')
project_ID = abc.cursor.fetchone()
I suppose I'll experiment with how it currently handles exceptions, such as no network connection, blocked by a firewall and incorrect credentials.
I imagine I'll have to add something like a try/finally into the db_connection class.
But I'd still appreciate any feedback on this implementation and how I could improve it 🙂
cinder sierra
@slate bluff you should be doing the work inside __enter__ in __init__ instead in case you ever need to use the class without a context manager
also, i don't know how the mysql connector works, but in most db driver libraries i've used cursors aren't always required ex. the raw connection can execute create and insert statements or something of the like so i tend to allow a with_curs boolean to my __init__
class Database:
def __init__(self, *your_db_detail_args, with_curs: bool = False):
self.conn = your_lib.connect(*your_db_detail_args)
self.curs = self.conn.cursor() if with_curs else None
def __enter__(self):
return self.curs or self.conn
def __exit__(self, exc_type, exc_val, exc_tb):
if self.curs:
self.curs.close()
self.conn.close()
Okay, thank you! In this case I've found to always be using a cursor and within a context manager, which was my original intention of writing this, so it could be used in a context manager. But I can see the value in allowing for these different options!
quartz tusk
So in postgresql there are two servers PostgreSQL and PostgreSQL12 which get reflect changes in each other
Is there any difference between them? Should I use any one in particular?
torn sphinx
Hi. How to work with databases on heroku if with dynos restarting it dumps my tinydb base to commit state?
I mean there are only data in database when I did commit.
glass gorge
so, I'm trying to figure out the convention by which data is cataloged/organized in a data set
is there an easy way to do this? I'm trying to "decode" the way a data set is organized, so that I can build relationships with a different data set
cinder sierra
what do you mean?
glass gorge
so like
a site catalogs their items in a schema
im trying to figure out that schema
true kiln
So, I have this query
select `user_id`, `level`, `experience`, `rank` FROM
(
select t.*, @r := @r + 1 as `rank`
from profiles t,
(select @r := 0) r
order by `level` desc, `experience` desc
) as t
limit %s, %s
Which throws this warning: Warning: Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives
Any idea on how to solve this?
cinder sierra
@glass gorge if it's not ordered by name, category, price, or entry date there's just no order
glass gorge
there is a schema though
cinder sierra
Then what is it?
torn sphinx
Hey, i have problem with finding and updating a value in PyMongo and MongoDb, So basically, i want to find all documents that their "name" is "dead_light" and their dead_light's "count" is 0. How can i find it and how can i change that value (Count) to 1? (Please answer if you know Mongodb otherwise ik how to do it with other databases)
Here is sample database:
{
"_id": 123190512905,
"streamers": [{"name": "dead_light", "count": 0}, {"name": "someoneelse1", "count": 2}, {"name": "someoneelse4", "count": 0} ]
}
{
"_id": 12316262690512905,
"streamers": [{"name": "someoneelse2", "count": 13}, {"name": "dead_light", "count": 2}]
}
(Here i want to change all dead_light's "count" that are 0 not other numbers)
cinder sierra
@torn sphinx
update_many({'name': 'dead_light', 'count': 0}, {'$set': {'count': 1}})
should work?
torn sphinx
@cinder sierra
no unfortunately it doesn't, it actually updates all of the objects in that document which has dead_light to zero so if i had{"name": "someoneelse1", "count": 0} it would change this count to 1 also...
cinder sierra
it shouldn't be updating other fields, that's for sure.
torn sphinx
i tried that before, it does.
cinder sierra
and you're positive? the update doesn't fulfill the set unless the query is found true
torn sphinx
Still that's not the way to update deadlight's count because it is in array and you need to use streamers.name: dead_light to select which document. what i exactly want is like
thatobject = streamers.name: deadlight and streamers.name's count is equal to 0 ( i don't know how to tell mongodb this part)
and then
update_many(thatobject, {"$set": {"count": 1}})
i understand what you want. i was giving it a shot before confirming my other idea
what you're saying about it updating other documents should be completely incorrect though
after testing for myself i can confirm that it definitely does not change any values, much less other fields
torn sphinx
i didn't get your last sentence but i guess yes
can you send the shell command that you used
cinder sierra
i'm using pymongo
torn sphinx
im using it too
cinder sierra
and i used exactly what i sent you as a test. it doesn't work
torn sphinx
You mean it just exactly change what we want?
cinder sierra
it doesn't do anything, i already said that
torn sphinx
If you are using this for sure it doesn't do anything
update_many({'name': 'dead_light', 'count': 0}, {'$set': {'count': 1}})
because you defined that field wrongly
dawn pulsar
How do I do
SELECT A, B, C FROM x
But only select C if a condition
So if C > 0, add it to the query, if not, only select A and B
Just as an example
runic pilot
in SQL (and relational algebra), the select (projection) is the last step in the query and applies equally to all items in the selected set
dawn pulsar
So it’s not possible?
runic pilot
you might be able to do something funky with CASE statements in your select, but I'm not super familiar with that operator
dawn pulsar
Thanks anyway
runic pilot
SELECT CASE WHEN created_at > NOW() THEN created_at ELSE null END from contacts;
ok so I just tried that and it works
it's always seemed weird to me to do CASE statements, but yeah, totally possible
dawn pulsar
Sorry, I’m such a SQL noobie, could you help me slightly translate that into something slightly more similar to my case? :)
Actually, that should work, I think if can do that
runic pilot
whats your condition?
SELECT A, B, CASE WHEN C > 0 THEN C ELSE null END as C from mytable;
@torn sphinx
coll.update_many({'streamers.name': 'dead_light', 'streamers.count': 0}, {'$set': {'streamers.$.count': 1}})
pale oriole
sqlFormule = "INSERT INTO users (name, gmail, password) VALUES (%s %s %s)"
user = (name, gmail, password)
mycursor.execute(sqlFormule, user)
i get: mysql.connector.errors.DataError: 1136 (21S01): Column count doesn't match value count at row 1
what is wrong here?
celest blaze
not sure, but maybe there should be commas between the %s %s %s
stable parrot
hi everyone
i have created a database
and wrote some php code
when i do some user input, my input is not registered in the database
not sure why
i can share my code if needed
celest blaze
I'm afraid that if I looked at PHP code, my hair would fall out, and I wouldn't be able to have children
pulsar stag
do yall have any good articles on how to architect a db layer in python
i was going to use sqllite
torn sphinx
.
pale oriole
sqlFormule = "SELECT gmail AND password FROM users WHERE gmail=?"
mycursor.execute(sqlFormule, gmail)
i get:
ValueError: Could not process parameters
what am i doing wrong?
solid void
what did you put in the "gmail" variable?
pale oriole
@solid void a gmail like a normal gmail as a string
solid void
that's not correct, you need it to be a tuple/list of values
even if it's just one value
Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())
@pale oriole
torn sphinx
@cinder sierra Thank you it works, but i want to know if there is anyway to check if "count" of "dead_light" is equal to 0 not other numbers and then it change it to 1. Because this one also changes the one which is not 0. so if "dead_light"'s "count" was equal to 2 it would change it to 1
pale oriole
so @solid void it should be like:
mycursor.execute(sqlFormule, (gmail,))
?
solid void
yes, that would work
pale oriole
i get this error:
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement
solid void
hm, that's weird, you have one ? in your query, and one item in your tuple, that should match
i usually use the named way, rather than positional, but this should work as well
pale oriole
yes
solid void
just in case try mycursor.execute("SELECT gmail AND password FROM users WHERE gmail=:gmail", {'gmail': gmail})
uh, the AND here is wrong btw
it should be SELECT gmail, password FROM…
pale oriole
Yes, thank you very much it works! 🙂
solid void
great 🙂
obsidian leaf
hiya - i have a one to one relationship in my database for my snakes and ladders game - moves and adjusted moves table (so not every move has a adjusted move but every adjustedmove has a move). So basically i was wondering if my adjustedmove primary key could be the move primary key - or can primary keys not double up as foreign keys and would i need another identifier for it. hope this makes sense? thanks 🙂
clever topaz
@obsidian leaf PKs can represent whatever you want, so in this case they can act both as a PK and a foreign key - as long as there is 0% chance of duplicates.
rain wagon
@obsidian leaf A primary key always acts as a foreign key if used this way, because it is only a foreign key in a foreign table
obsidian leaf
alright thanks - so its fine if i declare it to be both in mysql?
rain wagon
@obsidian leaf You declare the relation in the foreign table so not sure what you are asking
If you mean if a FK can be a PK in the foreign table, then the answer to that is yes as well
obsidian leaf
that last one was exactly what i was after, thank you 🙂
spring star
Hey guys! So, I'm having a little bit of an issue with inserting data from my Tkinter entry fields to my MySQL database. It seems as though no matter what I do I consistently get this error:
pymysql.err.ProgrammingError: (1064, "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 'keys(id, licenseKey, activated, email, recoveryEmail) VALUES(1, 'JARVIS891', 1, ' at line 1")
Here is my whole code regarding inserting the data:
try:
with conn.cursor() as cursor:
myCursor.execute("INSERT INTO keys(id, licenseKey, activated, email, recoveryEmail) VALUES(1, "
"'JARVIS891', 1, 'rob@gmail.com', 'robrecovery@gmail.com');")
print("DATA INSERTED")
finally:
conn.commit()
conn.close()```I will be using variables for all the entries later of course but i put those placeholders in for testing
versed coral
Hi,
Is there any way to get slice of records between given two id from mongo db collections?
scenic zinc
For some reason I get a ModuleNotFound error when I am importing "from databases import Database". I am using pipenv to manage my package
and it does exist, does anyone have a clue what might be the issue?
The error happens in my docker container.
celest blaze
have you installed some library that provides a module calld databases?
python seems to think that you haven't
celest blaze
do you have two different versions of python installed?
scenic zinc
The default 2.7 that comes on the macos but pipenv is specified to run 3.8.2
celest blaze
are you certain that you're running python 3.8.2 when it gives that ModuleNotFound error?
torn sphinx
if i create a class in models.py and then migrate it and it creates database tables in postgres, then can i reverse this process simply be deleting the the class and also deleting the table?
runic pilot
Your migration tool should have a “downgrade” command that you should run before deleting code
indigo dawn
i've used datagrip and DB Browser for SQLite, anyone here using something that's in between these two feature and price wise?
torn sphinx
ok thanks @runic pilot
torn sphinx
Table names are automatically generated by combining the name of the app and the lowercase name of the model. (You can override this behavior.) who thought this was a good idea, heh
runic pilot
What orm are you using?
torn sphinx
well, the override ability was a good idea
it's just django, whatever the stock one is
database is postgresql, but that doesn't make a difference i am pretty sure
runic pilot
torn sphinx
ty ty. i was actually about to google myself hah but i just wanted to point it out. i'll check this out
just in case anyone is interested, here is how you prevent django from adding the app name and underscore to the table name https://stackoverflow.com/questions/32657766/how-to-control-table-names-created-by-django-migrate
plucky pelican
I'm new to databases and I want to know how to create a table 😁
cosmic ivy
can someone explain to me why my dice game does not display my result?
plucky pelican
Error @cosmic ivy
jolly shore
Does anyone know how to get commands like .schema or .show on an SQLite database through python? statements work through the execute method in the sqlite3 module but I cant find the python way of getting the same functionality I get from the CLI for SQLite.
can someone explain to me why my dice game does not display my result?
@cosmic ivy
@cosmic ivy you should make the yes and no dictionaries you have into lists instead of dictionaries i think. use [] instead of {}
oh you made them into tuples and it worked, good
cosmic ivy
😄
jolly shore
sorry to ping you after you already got it working haha.
cosmic ivy
it is my first code
jolly shore
congrats
cosmic ivy
ty
solid void
@cosmic ivy "lancer"* please fix your french :P, but also, #databases doesn't seem like the appropriate place for this question, please ask in a more relevant channel in the future
cosmic ivy
im sorry i can go on "PYTHON HELP DORMANT" ?
solid void
no, available help channels are in the python help: available" section
pick one of these, ask your question, wait for an answer 🙂 (and take note of where you asked your question, in case you move outside and come back to look for the answer)
willow shard
guys, when i have a PrmaryKey with auto_increment, do i set the foreign key also to auto_increment or not?
solid void
the foreign key won't be set automatically, it'll be set by you when you create a record, so no
(also, it can be totally normal that multiple rows have the same value for the foreign key)
willow shard
so when i add a new record on the table with the foreign key, it has always the same value as the Primary who is on auto_increment?
even when adding new record to the table with the foreign later? because now i have the table with the Foreign empty and gets filled later
obsidian iron
await self.client.database.execute(f"INSERT INTO blacklist (id,reason) VALUES ({victim.id},{reason})")
what am i doing wrong here
ohhhhhhh
nevermind im stupid
shouldn't be using the same variables
nevermind i still get the same error
solid void
@willow shard unless you use an orm that cares about relationship, setting the value of the foreign key is your responsability, to indicate which row on the other table is related to this one, by putting the primary key of that row, the db engine can't guess this value.
severe flame
I've got a really strange issue. I'm writing a database query that's intentionally vulnerable to SQLi, as you do. When hit with the query ```sql
SELECT * FROM users WHERE username='user' and password='pass' OR 5117=LIKE('ABCDEFG',UPPER(HEX(RANDOMBLOB(500000000/2))))-- zWnG
What on earth is going on (and how can I made it not chew through system memory)?
I've tried ```sql
PRAGMA soft_heap_limit=1024;
PRAGMA hard_heap_limit=1024;
silver pier
i use a postgresql database and want to change the id(the primary key) back to one after i did some testing how can i do that
runic pilot
you'll need to drop the primary key constraint on the table the add that constraint to the new column
torn sphinx
in normal postgresql and mysql db's, how do reads and write scale
torn sphinx
I forgot how normal databases work
I've been preparing for my cloud certification exam, and I'm so immersed in distributed databases, columnar olap data warehouses and nosql based cluster-type databases... that I forgot about how reads and write scaling work in normal databases
runic pilot
can you be more specific in "how they scale"? what are you specifically asking?
how they handle growth of connections? growth of IOPS? growth of data?
torn sphinx
like, if you want to plan for higher demand in read and writes.. what do you do in which case, when does horizontal and vertical scaling come in
runic pilot
I can only speak to that from a practical sense, and not a theoretical or academic sense, but I'll scale the DB server vertically when I want more IOPS throughput and horizontally when I need read/write replicas
though proper use of the database is much more important than scaling the hardware
torn sphinx
when you write to one node in a horizontal set up, does that data get replicated across the other nodes, and when are you able to read the same data from the other nodes, like when does it become consistent
runic pilot
totally depends on the setup of the database and the database implementation itself
though, yes, most setups will have written data either replicated to all other shards, or only shards that claim to own that data
torn sphinx
how do backups work.. like, what happens when you set auto-backup based on time..
how do they compare with failover replicas
do backups relate to intime recovery at all
runic pilot
backups are almost always managed outside of the database itself, using some command to dump the contents of the database to a file
so if you're backing up a db based on time, it's almost always a script that you manage or some service from your cloud host
failover replicas are just what they sound like: a database to fail over to in the case that your primary goes down
torn sphinx
so if it's a 3 node set up, does it mean all three have 3 nodes to fail over to?
runic pilot
it's usually smaller and best suited for short bursts of traffic while you restore the primary
torn sphinx
how is it a replica, if it doesn't have all the data from the primary
runic pilot
again, depends on the db setup
are these replicated or partitioned nodes?
(or a mix?)
torn sphinx
I thought data was always partitioned..
runic pilot
depends what definition you're using
torn sphinx
and I dont understand replication I think.. it's a replica only if it's an exact copy of the node right?
hmm.. please walk me through it
runic pilot
I mostly use postgres so when I say partition I mean what they talk about here https://www.postgresql.org/docs/12/ddl-partitioning.html
again, it depends
something like postgresql would probably replicate all data across any node in the cluster, but a database like elasticsearch is well known for its ability to distribute data across different "data nodes" in the cluster and route queries to the right nodes based on the data in each node
torn sphinx
I dont understand the difference..
postgres maintains partitions of data across nodes.. a replica of a node is a copy
that's what I understand
so how it it different from elasticsearch.. which also spreads data across nodes
runic pilot
when postgres says partition it's usually talking about splitting a big table into different physical locations on the disk
you can expand that definition to work across multiple disks instead of just one
torn sphinx
ok, so in case of reads and writes they get split between these locations..
is the index used to make the reads faster?
runic pilot
yes, the index ultimately points to an address on disk
torn sphinx
so writes are not random.. do they happen like a round robin or something
torn sphinx
yes
runic pilot
woof that's a hell of a question
one I can't answer super confidently
I don't know if there are guarantees about how the db decides where to write on a particular partition, but there's usually a function that splits partitions and that is used to decide where to write data
e.g. at my old company, we had a customer that overloaded lots of parts of our system, so we had a partition that wrote their data to a dedicated node
torn sphinx
ok then.. thanks a lot.. this cleared up quite a bit
but I see I have some reading to do to get the concepts down
slim frost
Heyo, I am working on a script that scrapes the info off a page of surveys, and puts them into a .csv doc. This is my first time ever attempting to web-scrape. Anyway- I ran my program, and it made the .csv doc, but it didn't put any info in it. Would someone mind reviewing my code to see where it went wrong?
r = requests.get('https://www.ysense.com/surveys')
from bs4 import BeautifulSoup
soup = BeautifulSoup(r.text, 'html.parser')
results = soup.find_all('div', attrs={'class':'insurvey'})
records = []
for result in results:
amount = result.find('div', attrs={'class':'survey-button blue'})
time = result.find('div', attrs={'class':'details'})
import pandas as pd
df = pd.DataFrame(records, columns=['amount', 'time'])
df.to_csv('surveys.csv', index=False, encoding='utf-8')
hmmm idk if this can work since the survey info doesn't show up in the page source, only in inspect
minor ruin
@torn sphinx Depends on database software, for example with MSSQL, it's Primary/Secondary Model where writes must go to primary but secondaries can be configured to deliver reads, alot of this craziness is why Cloud DBs became a thing
gloomy bone
Hello, May someone please tell me which way is the right way ?
- Writing the
AND s.valid = truewithin the JOIN clause - Adding it to a WHERE clause at the end
SELECT
t.id,
t.name,
ifnull(sum(c.score), 0) as toal_score
FROM teams as t
LEFT JOIN users as u
ON u.team_id = t.id
LEFT JOIN submissions as s
ON s.user_id = u.id
AND s.valid = true
LEFT JOIN challenges as c
ON c.id = s.challenge_id
GROUP BY
t.name
ORDER BY
toal_score DESC,
s.creation ASC;
I'm trying to create a list of all the teams along with their total score calculated based on members submissions
carmine heart
You'll probably notice that survey websites don't like you scraping their website, @slim frost. Data's usually their own product. I'm pretty sure this website has similar provisions in their ToS that state that you can't use robots or other automated tools on their website.
slim frost
I don't see why they wouldn't mind me taking the amount of compensation and the time though
Idk
mystic glade
im working in python with sqlite3, is it possible to get the values between two rows? data = [('2020-04-24T11:16:33', 364, 133, 342, 10, 1015, 10, 31), ('2020-04-25T11:17:44', 2, 2113, 14, 10, 1015, 10, 31), ('2020-04-27T11:18:55', 36, 155, 56, 10, 1015, 10, 31), ('2020-04-28T11:19:33', 14, 144, 2, 10, 1015, 10, 31)]
say id like to get out the values between the times of 16:33-18:55
gloomy bone
SELECT
*
FROM tableName
WHERE
dateData BETWEEN date1 AND date2;
@mystic glade
torn sphinx
def create_table():
c.execute('CREATE TABLE IF NOT EXISTS nordvpn(email TEXT, password TEXT)')
def data_entry():
c.execute("INSERT INTO nordvpn VALUES(emailsss, passsworddd)")
conn.commit()
c.close
conn.close()
no such columns as emailsss
runic pilot
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3...) is the syntax
torn sphinx
hm ok
def create_table():
c.execute('CREATE TABLE IF NOT EXISTS nordvpn(email TEXT, password TEXT)')
def data_entry():
c.execute("INSERT INTO nordvpn VALUES(email, password) VALUES(emaillls, passwordsss)")
conn.commit()
c.close
conn.close()
getting a syntax error oof @runic pilot
runic pilot
what's your statement now?
torn sphinx
def create_table():
c.execute('CREATE TABLE IF NOT EXISTS nordvpn(email TEXT, password TEXT)')
def data_entry():
c.execute("INSERT INTO nordvpn (email, password) VALUES(emaillls, passwordsss)")
conn.commit()
c.close
conn.close()
you might need to wrap the values in single quotes to let SQL know that those are strings and not identifiers
VALUES ('emaillls', 'passwordsss')
runic pilot
are you using sqlite?
torn sphinx
i did import sqlite3 at the top
runic pilot
unless you configured a data file I think it's entirely in memory
do you have a line like this?
conn = sqlite3.connect('example.db')
torn sphinx
conn = sqlite3.connect('nordvpn_accounts.db') is what i have
but the file already exists
could that be why?
actually it probably is
runic pilot
no, that's the database
torn sphinx
oh
runic pilot
if you want to use it from a SQL repl you can use the sqlite3 command
torn sphinx
it doesnt allow me to open db files with the db browser
mystic glade
@gloomy bone how it the syntax in python?
c.execute('SELECT * FROM data WHERE dateData BETWEEN 2020-04-24T11:16:33 AND 2020-04-27T11:18:55 ')
like this?
runic pilot
rdbaker $ ipython
import Python 3.8.1 (default, Jan 11 2020, 14:27:26)
Type 'copyright', 'credits' or 'license' for more information
IPython 7.11.1 -- An enhanced Interactive Python. Type '?' for help.
In [1]: import sqlite3
In [2]: conn = sqlite3.connect('base.db')
In [3]: conn.execute('CREATE TABLE IF NOT EXISTS nordvpn(email TEXT, password TEXT)')
Out[3]: <sqlite3.Cursor at 0x1071add50>
In [4]: conn.execute("INSERT INTO nordvpn (email, password) VALUES ('emaillls', 'passwordsss')")
Out[4]: <sqlite3.Cursor at 0x109594f80>
In [5]: conn.commit()
In [6]:
Do you really want to exit ([y]/n)?
rdbaker $ sqlite3 base.db
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
sqlite> select * from nordvpn ;
emaillls|passwordsss
sqlite>
all your code worked for me locally
torn sphinx
@torn sphinx I refuse to support alting, it's against the TOS
tacit lark
hi im using django rn and im not sure how to do this login auth thing
i already styled and set up my own log in thing
how do i integrate just the core parts of the auth package
winged mica
import csv
cursor = Connection.get_cursor()
conn = Connection.get_connection()
def csv_to_db():
with open('C:/StudentTracker/Code/database_entries/student.csv', newline='') as csvfile:
count = 0
headers = []
rows = []
csvreader = csv.reader(csvfile)
for row in csvreader:
if count == 0:
headers.append(row)
else:
rows.append(row)
for item in rows:
cursor.execute('INSERT INTO student VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', item)
if name == 'main':
csv_to_db()
^ this is running fine, but it's not actually putting anything into my database/table.
Any idea's why?
Using sqlite3, in case that helps.
Wait
Figured it out. haha
Well, I figured part of it out, I suppose. Still nothing showing up in the db.
torn sphinx
Database = puzzle that's hard
winged mica
Yea. So far I hate working with a database, but kind of need to learn it.
dim bridge
hey is it better to query an entire user from a mysql table, and return the requested field to the client or just query a field needed?
and have a ton of query functions for every field
torn sphinx
https://docs.datastax.com/en/archived/cassandra/3.0/cassandra/architecture/archDataDistributeHashing.html
I understand how Writes happen across virtual nodes on nosql DBs, but I don't understand Reads..
how do we know where a certain row is when we want to do a read.. can you give me an example with a query
remote plinth
i am going through mysql injection
so does iron' or 1=1# means
sudden ocean
In SQL injection, the database is taking user input and concatenating it into a SQL string without checking the user input at all. This means the user may be able to send valid SQL as input and it will actually run as SQL which can lead to chaos
Frameworks like Django will automatically protect you from this
So for example if you have a SQL query like
select * from posts where title like '%a';
That will return any posts which have titles starting with 'a'
Now what if we want to implement search, and so whatever the user types in the search bar we will add to our query to send to the db
Lets say the user searches for 'toast'
Then we might do something like
query_str = 'toast'
sql("select * from posts where title like '%" + query_str + "';")
Okay that's all fine
Now what if the user is a hacker and they enter some SQL in the search bar instead
query_str = "a' or 1=1"
sql("select * from posts where title like '%" + query_str + "';")
Now are query checks two conditions
- Whether title starts with a
- If 1=1 (which is always true)
So for every post in the posts table, this condition will be true. And that means the user will see every post in the whole db...
Now consider a more harmful situation. What if instead of posts on a blog there were a SQL statement to return users and their credit card info?
The hacker can use this 1=1 condition, in an injection to make the query true for every item in a table and read all the rows of some table of your db
@remote plinth
sudden ocean
No problem, glad to help
graceful hatch
Hello everyone.
Is it possible to access any record based on attribute value? or do you need to specify a table? (Mysql)
sudden ocean
You always have to specify a table in SQL I think
oak bane
easily
torn sphinx
how do I fix this
I installed xamp on a new pc, then deleted it and copy pasted my old xampp folder from my previous PC
I want to use apache server and phpliteadmin to view my sqlite database
mystic glade
choices2 = c.execute('SELECT time date FROM data')
cb = ttk.Combobox(self)
cb.grid(row = 3, column = 1)
cb['values'] = choices2```
Does someone know how to populate combobox with database values?this give me https://i.imgur.com/1Q2ynsE.png
remote plinth
i am going through the bwapp sql injection
when i do iron' union select 1,2,3,4,5,6,7 the code doesnt work
but when i do iron' union select 1,2,3,4,5,6,7# the code does work
why # works
thorn nymph
Anyone knows how to create databse using flask?
thorn nymph
I have an error where i cant creat db, like db.creat_all() doesnt work, and code editor doesnt recognize Column or integer, any ide ahow to fix it? @woeful tusk
woeful tusk
have you properly imported flask_sqlalchemy
woeful tusk
have you tried the flask quick start?
is your evironment set up properly?
what database are you using?
slqite?
thorn nymph
Oh wait i didnt saw it properly, i used sql alchemy
woeful tusk
yes you need flask_sqlapchemy
thorn nymph
woeful tusk
@thorn nymph
Perhaps try this to make sure your db is being found?
basedir = os.path.abspath(os.path.dirname(__file__))
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///'+os.path.join(basedir,'data.sqlite')```incase youre running it from a different directory
Oh you also have a sytax error
creat_all should be create_all
and you should add this line to remove the nag message:
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
pearl tree
when it comes to database design for automatically generating new tables and such, is it recommended to have logic within the get function to create a new table with default values if said table isnt found.
thorn nymph
lol i can make it now, i didnt notice it, tanks xD
woeful tusk
# creates db table from each model
db.create_all()
i forgot to mention this is for ORM based libraries such as SQLalchemy i know if it was just a standard SQL i canj ust do that and then do all the data filling later
woeful tusk
yes
thorn nymph
how to do that?
woeful tusk
start console session
$ sqlite3
show list of . commands
sqlite> .help
open the db
sqlite> .open data.sqlite
replace data.sqlite with the name of your database
list dbs
sqlite> .databases
list tables
sqlite> .tables
run SQL to manage the db
sqlite> SELECT * FROM yourtablename;
theres also a .schema command to show your schema
fluid tusk
can i print the row values without for loop with sqlite
thorn nymph
wait i should copy and sqlite> . ....
past current
Hello guys, could someone please help me with a backend for my application?, I was thinking about building a django restframework api and with that do stuffs but I have no clue about how it even works. Could someone please maybe introduce me to a backend/database for my android and ios app please!. My development is falling back and I really need this please?
sudden ocean
If you have never used drf and time is an issue then don't use it now
It takes time to learn though it's a great tool and most love using it
You should use Firebase or some other nosql esp if you have never worked with a backend before
past current
Do you have any tips @sudden ocean ?
sudden ocean
Not because they are better solutions (I personally dislike them) but because you seem panicked about time and they will be quicker to use for you
past current
@sudden ocean what do you use?
sudden ocean
I use drf with postgresql database
past current
Ok, could we talk if you don't mind please?
@sudden ocean I just need to know, how it all works and connects? and I will do some research on my own!
@sudden ocean I have learned a bit but it did not felt like it so that's why I said I have no clue, lol
sudden ocean
DRF has some of the best documentation out there
The tutorial is in backwards order in my opinion.
Modelviewsets are the most powerful part of DRF but they come last in the tutorial
past current
I know, I have looked at it but it just feels like I have come no where
sudden ocean
Try writing a models.py to reflect your db schema, then write a serializers.py then a views.py which uses ModelViewsets to expose the rest API, then write urls.py for the routing and then you should be able to run the server and use it fine
All these are described on DRF website
Take time, read the docs and do the tutorial
past current
I have done all that stuffs, but how do I connect it with a database
@sudden ocean sorry if I'm taking your time. But could you show me a db schema of yours if you have any. So that I can see how, i should structure mine?
sudden ocean
Set up the db on your system and connect it in your Django settings
Here's a mock db schema
fluid tusk
how to get the values of row without using for loop for sqlite3
past current
@sudden ocean That's very useful, thank you very much for you time man. You really helped me! :). And before I go just one last question, how many users can drf with postgresql database handle before you need to pay money? and in this case do you know how much you have to pay?
sudden ocean
You need to pay for a server to host it on. That's where money goes
past current
what do you mean?
sudden ocean
Firebase and others are self hosted and have pricing tiers. Here you would put your server on digital ocean or aws
past current
Aha okay, I get it. Thanks man!
sudden ocean
@past current 👍
torn sphinx
@bot.command()
async def redeem(ctx, key):
c.execute("SELECT * FROM key WHERE keys=?", (key,))
print("key found")
how would i make it so if the key is not found in the database it prints "not found"?
woeful tusk
id imagine youd need to check the query result
torn sphinx
how'd i do that tho
woeful tusk
depends on the db youre using
torn sphinx
sqlite3
woeful tusk
did you google: sqlite check query results?
torn sphinx
nah ill see if that helps tho
woeful tusk
im sure there will be plenty of answers
I have not used sqlite w/o flask-alchemy so I would need to google that as well
torn sphinx
this seems confusing oof
woeful tusk
do you have a good tutorial?
torn sphinx
not rlly
torn sphinx
nah sqlite3
woeful tusk
sqlalchemy is an ORM that supoorts sqlite
is this in a flask app or straight python script?
learning a relational db is non-trivial so you should look for a good tutorial
see if this helps: @torn sphinx
https://www.sqlitetutorial.net/sqlite-python/
torn sphinx
alright ty
reef hawk
How do I export databases as a csv file arranged and sorted by a certain column?
mystic glade
@reef hawk you can look at how i do it ```
def export(*args):
#print(tkvar.get()[2:-3], tkvar2.get()[2:-3])
q = (tkvar.get()[2:-3], tkvar2.get()[2:-3])
# for row in c.execute("SELECT * FROM data WHERE time BETWEEN ? AND ?", q):
#print(row)
if( tkvar.get()[2:-3]>=tkvar2.get()[2:-3]):
tk.messagebox.showerror("Error", "Start time can not be less or equal to end time")
else:
export_file_path = filedialog.asksaveasfilename(defaultextension='.csv')
print ("Exporting data into CSV............")
cursor = conn.cursor()
cursor.execute("SELECT * FROM data WHERE time BETWEEN ? AND ?", q)
with open(export_file_path, "w") as csv_file:
csv_writer = csv.writer(csv_file)
csv_writer.writerow([i[0] for i in cursor.description])
csv_writer.writerows(cursor)```
i use pandas lib and tkinter with this one
from pandas import DataFrame
import tkinter as tk```this will give you the option to choose where you want to store the csv, if not you can just copy the 7 last lines and change the csv.writer('enterfilename.csv') and it stores in your local directory
wild nest
hello has anyone used flask to connect to a pstgres db?
lavish ferry
hi, I'm using pgadmin 4, how can I change the position of the columns when they are already created ?
pale oriole
Python, SQL:
mycursor.execute("SELECT name FROM users WHERE gmail=gmail", {'gmail': gmail})
myresult = mycursor.fetchone()
I get the column of all the names and not only the name which match the gmail why is that?
celest blaze
is that the right syntax? I'm used to seeing e.g. ("SELECT name FROM users WHERE gmail=?", (gmail, ))
pale oriole
no idea this is a code that someone from here told me to use, i don't have a lot of knowledge in sql
but i will try what u just said
is that the right syntax? I'm used to seeing e.g.
("SELECT name FROM users WHERE gmail=?", (gmail, ))
@celest blaze mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement i get this error
pale oriole
Python, SQL:
mycursor.execute("SELECT name FROM users WHERE gmail=gmail", {'gmail': gmail})
myresult = mycursor.fetchone()
I get the column of all the names and not only the name which match the gmail why is that?
@pale oriole someone?
reef hawk
So I have a pretty big table (10000 values-ish, but will keep expanding over time), that I would have to periodically update (add 1 to a value if requirements are met) every few minutes or so. Is it optimal to just use UPDATE all WHERE requirements are met?
I've been reading something else on inner joining tables or something for large table, to create a temp table and then join them via Primary Key but I'm still a little confused on how that works
elfin geyser
It takes me 4.5 minutes to get 100 documents ids from collection of total 1107 documents (228mb overall)
is that normal?
(mongodb cloud -> pymongo driver)
elfin geyser
BIG_LIST = mycol.find({"_id": {"$gte": 100, "$lte": 199}})
start_time = time.time()
for api in BIG_LIST:
print(int(time.time() - start_time))
break
>>> 65```@bot.command()
async def redeem(ctx, key):
val = c.execute("SELECT * FROM key WHERE keys=?", (key,)).fetchone()
if not val:
print("key not found")
I know this was hours ago but I typed this out without realizing, either way would this not work for your case? @torn sphinx
elfin geyser
