got it..
#databases
torn sphinx
urban cradle
nice
dreamy onyx
Anyone know of some free-plan quick to setup database for a personal private use (want to move some data from Gdrive to that one) ...Python friendly, maybe.. 
obsidian leaf
when creating a table im using "box" BOOLEAN,, however it still allows me to insert strings as the type and it goes in to the database. do any of you know why that works?
upbeat lily
Are you using SQLite?
golden comet
I have a database with multiple many to many and many to one (and one to many) relationships - two of the tables (Category, Tags) I had set up as a one to many so that I could get the list of tags for each category, but now I'm thinking that may not be the best option, but I'm unsure what would be?
I need something that allows me to allow for tags being set on more than one category, but be unique for that category? Should I do a many to many relationship or should I use a unique constraint? The two tables in question are currently set up as:
class Category(Base):
__tablename__ = 'categories'
id = Column(Integer, Sequence('category_id_seq'), primary_key=True)
name = Column(String(50), nullable=False, unique=True)
description = Column(String(255), nullable=True, default='Placeholder to be written later')
def __repr__(self):
return "<Category(id='%s', name='%s')>" % (
self.id, self.name)
class Tag(Base):
__tablename__ = 'tags'
id = Column(Integer, Sequence('tag_id_seq'), primary_key=True)
tagname = Column(String(120), nullable=False, unique=True)
category_id = Column(Integer, ForeignKey('categories.id'))
category = relationship('Category', back_populates='tags')
def __repr__(self):
return "<Tag(tag_name='%s')>" % self.tagname
Category.tags = relationship("Tag", order_by=Tag.id, back_populates="category")
example categories: Companies, Women, Men, Kids, Historical, Fabric
example tags: Designer Stitch, misses, shorts, blouses, pockets
I need to allow for shorts to be in Women, Men & Kids but only show for each (I hope that makes sense?)
quartz rivet
Alright
nova hawk
The injection might be happening at a different point.
nova hawk
I do believe so.
quartz rivet
so what can be happening there?
how is the database getting injected?
I dont have any idea actually
quartz rivet
Everywhere we need an sql execution
nova hawk
And there are no operations performed on those strings anywhere?
quartz rivet
except this one? i believe no
maybe before this function is called, but does it matter?
nova hawk
Yes
quartz rivet
So if the string is changed, it is exploited to injections?
what can i do about it :(
nova hawk
If the string is modified based on user input there is a risk for injections.
What you can do about it is never doing that.
quartz rivet
Ok, im trying to find it rn, i will contact you if i got something, thanks!
@nova hawk is this what you are talking about?
sql = "SELECT ? FROM ?"
# execute sql
sql = "ANOTHER DIFF QUERY"
# execute sql again
Or changing it like that is ok?
nova hawk
I don't think you can use the table name as a variable. At least you can't in some libraries I've used
quartz rivet
No thats just an example
nova hawk
Oh, no I don't mean that.
That's just different SQL strings, they aren't getting modified
quartz rivet
can you give an example?
nova hawk
More like
f"SELECT ? FROM {table_name}"
sorry for not understading, im just a little bit tired
oh alright
And if the params are modified? @nova hawk does it also exploits?
nova hawk
What do you mean with that?
You shouldn't ever do any operations on a query involving user input.
quartz rivet
if the params argument is modified, does it also exploit me?
nova hawk
no
quartz rivet
alright thanks
nova hawk
params is where the user input should go.
fringe tiger
just don't use format or f strings on your querries
quartz rivet
not doing that, ill check again
fringe tiger
or any string conceation or anything similar
nova hawk
If you have a problem with injections you should check what people are sending to do it.
quartz rivet
%%%
For getting all the info from a search
That is the main problem, my other injections are taken by the formatting and cloudflare
hazy crystal
Guys is it possible to delete an ID column and regenerate it
for some reason my ID col has numbers over the total rows (can't tell the reason why)
ionic pecan
postgresql? that's normal
see "Notes" here https://www.postgresql.org/docs/12/sql-createsequence.html
Because nextval and setval calls are never rolled back, sequence objects cannot be used if “gapless” assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.
you can regenerate the id column but i wouldn't recommend doing that
and it also doesn't work if you have foreign key constraints to that table
hazy crystal
well i mostly needed it for optimizing a sitemap generation query (using where for speeding up offset)
quartz rivet
sql = ("SELECT ID, CurrentName, CurrentDiscriminator, CreatedAt FROM Users "
"WHERE %s LIKE %s %s "
"ORDER BY CreatedAt DESC "
"LIMIT %s, %s")
name_after_query = "LOWER(CurrentName)" if not case_sensitive else "CurrentName COLLATE Latin1_General_BIN"
disc_request = "" if discriminator is None else f" AND CurrentDiscriminator = {params[1]}"
page_offset = (page - 1) * RESULTS_PER_PAGE
results_per_page = RESULTS_PER_PAGE
if discriminator:
params = ("%{}%".format(term), str(discriminator))
else:
params = ("%{}%".format(term), "")
await cur.execute(sql, (name_after_query, params[0]) + (disc_request, page_offset, results_per_page))
TypeError: not all arguments converted during string formatting
copper echo
SELECT * FROM users WHERE (JSON_VALUE(tinder::json, '$.age') - 3) <= $1 AND $1 <= (JSON_VALUE(tinder::json, '$.age') + 5)
``` im getting ```
asyncpg.exceptions.UndefinedFunctionError: function json_value(json, unknown) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.``` if you can help me please ping meYo guys, i am trying to format an sql query but keep it safe, because i need to change my query because of some conditions, is there any way to format the string and keep it safe from injections? @nova hawk @fringe tiger
I can send the full code if you guys need it
if anybody else knows, please ping me or dm me, thanks! :D
copper echo
@quartz rivet you are looking like you have done more in sql than me, maybe you know what is wrong with my code?
quartz rivet
Nope, still looking for an answer for my problem
copper echo
i already figured my problem json_value doesnt exist in postgres and i had to use https://www.postgresql.org/docs/9.3/functions-json.html
rich trout
@quartz rivet either enumerate your options in a way that makes it clear that your query will not be set to user assigned data (by user assigned data is ok, but to user assigned data is not), or I suppose very strict restrictions and some careful handling may work ( EG: alpha ONLY, quoted by you, no spaces, forced prefix ). But we'd need to know exactly what you're trying to do
You're skirting the line between user control and security, basically, and more user access generally means less security
Letting the user control arbitrary columns, for example, is a bad idea even outside of special symbol injection. What happens if there are too many columns? Are there special column names you're not allowed to use? Can they DOS your system by creating too many indexes?
For what it's worth, you can use format strings in conjunction with parameterization, but format strings should be application controlled data not user controlled data. Whitelist vs Blacklist mentality
Something like
query_parts = {
"name": "NAME = ?",
"value": "VALUE = ?",
}
if not user_option in query_parts:
return BAD_REQUEST
query = query_parts[user_option]
res = cursor.execute(f"SELECT * FROM table_1 WHERE {query}", (user_data,)).fetchall()
return process_res(res)
Is "safe"--no user data is passed to the sql except through parameters, you are simply varying the query among known, code-controlled forms as guided by the user
It's not as safe as writing out each query by hand, as unexpected input could cause buggy behavior, but it's not injectable and it's not immediately the wrong thing to do
quartz rivet
@rich trout alright thanks, ill take a look at it
errant sable
are there any difference between python 2 DB API and Python 3 DB API?
besides the syntax
steel plover
Sry for asking on top of ur question, are there good postgres db tutorial?
I wanted to make a simple todo command for my bot which stores todo list of a person
cinder sierra
you should be able to do that pretty straightforward if you understand SQL
i've heard the python module that works with pg is pretty nice
shell ocean
psycopg2?
steel plover
Well i had to use the async lib one
wispy seal
One question if I insert a list into one column of a postgresql then it will be considered as 1 item right?
patent hearth
Hi, I need some assistance. I am writing an aws lambda function that queries a mysql database based on user parameter/check box selection on a php site and then downloads the query to csv. Does anyone know of any good resources/links for this?
I am able to establish a db connection and make a query which saves to csv but unsure of how to link various queries to all the possible user selected parameters. Thank you kindly
mossy lotus
I made a sign up and log in with html and php that show on phpmyadmin, now i wanna make a user profile for the logged in, can someone simplify to me what i need to?
devout sand
@mossy lotus I can't directly help, but are you locked in with using PHP?
I only ask because if you're familiar with Python, Django is a really good framework to use that handles user auth internally. And you can reference things in a Pythonic way to build dynamic pages like you're trying to do with your user profile page.
mossy lotus
@devout sand i'd prefer using python! Yes I’m a bit familiar with python so i can use django framework instead of php to connect to the db?
devout sand
It would take some learning, but https://www.youtube.com/watch?v=UmljXZIypDc&list=PL-osiE80TeTtoQCKZ03TU5fNfx2UY6U4p is probably the best video series I've seen on django. He also deals with your specific problem of creating a user profile page with Django.
Setup is kind of involved so you'd have to pay attention closely to that, other than that, it's all written in Python.
mossy lotus
Great I’ll watch it right now thanks!
devout sand
No problem
reef hawk
Is it possible to fill out data manually in a database? I'm using postgresql and I was wondering if I'm able to replace the values in a table manually within the pgadmin4 site
pure cypress
Queries can be executed using psql https://www.postgresql.org/docs/current/app-psql.html
I don't know about pgadmin however
reef hawk
@pure cypress hm it says I can edit it but I need a primary key in order to edit the info; I'm really new to databases but why would that be required to edit the data, and what's the function of the primary key?
pure cypress
The primary key uniquely identifies a record
reef hawk
so what I'm assuming is that there has to be a unique column?
like, the column cannot have the same value
in different rows
pure cypress
I don't know if technically a PK is required but in most cases your table would have one, otherwise it's probably designed poorly 🤔
reef hawk
ah alright
so let's say I wanted to make a table for discord users, I could put user_id as the primary key?
pure cypress
Yes
reef hawk
okay, thanks!
reef hawk
Does anyone know what the fetch function database returns? Like what type of variable is it
[<Record card_id=1 card_img='C:\Users\legoe\Downloads\PythonWorks\DCBot2\anime_images\Eto.jpg' user_id='271315596405112833'>, <Record card_id=2 card_img='C:\Users\legoe\Downloads\PythonWorks\DCBot2\anime_images\Zeni.jpg' user_id='271315596405112833'>]
Heres an example of what I get from it when I assign it and print it
Asyncpg
btw
clear adder
reef hawk
I want to basically make a list
from the results I recieve
lets say I collect all the data from fetch (asyncpg), I want to make a variable that contains a list of those elements
thorny musk
i take this error when i try to create new table with sqlalchemy can some bros help?
reef hawk
@urban cradle hey sorry to bother but do u know to select row number as one of the data u want to extract, like a column?
urban cradle
you mean the id of the row?
reef hawk
yeah, like the first data input would be 1, 2nd would be 2, etc.. how would I select it?
urban cradle
assuming the table is set up as you'd expect a table to be set up
urban cradle
select id from table;
reef hawk
oh it's just called id?
urban cradle
usually
reef hawk
ah alright, thank you so much!
urban cradle
np
reef hawk
and u can call on it just like any other column?
urban cradle
yeah
reef hawk
hmm doesn't seem to work ):
urban cradle
hm
reef hawk
urban cradle
DESCRIBE table_name;```that should give you a list of columns and names
providing this is mysql / mariadb / oracle sql
reef hawk
postgresql
reef hawk
hmm I'm seeing an example of describe
and it doesn't seem to show how to reference the #
just shows the columns that u set up
urban cradle
perhaps you should redefine the table to have an auto-incrementing id?
reef hawk
yeah that's what I was considering, to have an incrementing id column that functions the same way
but if I delete something it doesn't change it
like if I delete a data row the id would still stay the same
reef hawk
that was what I needed thank you!
urban cradle
no problem
urban cradle
i am not sure
broken cloud
Quick question if someone can help
I had set a field as NOT NULL
How do I alter it so that i can accept null values?
originally:py ("""ALTER TABLE `BANLIST` `discord_join_date` DATETIME NOT NULL""")
Will:
("""ALTER TABLE `BANLIST` `discord_join_date` DATETIME""")``` work?try it and see?
urban cradle
ALTER TABLE `banlist` MODIFY COLUMN `discord_join_date` DATETIME```assuming you're talking mysql rather than mssql
or postgres, idk shit about postgres
cyan lark
does Pandas fall under databases topic?
torn sphinx
I want to simplify a database query, I want to insert into the database, and if I insert a second time with the same values, it should not create a duplicate, how can I do that ?
golden comet
I am getting a traceback issue while trying to add my first item on a sqlalchemy (mysql) db table with a one to many relationship and a many to many relationship can anybody please take a peek at the gist below and point me in the right direction I would be so greatful.
https://gist.github.com/binarybrat/058b7038071f259f9e84911ccfb6aadd
golden comet
I know that at least the connection between the Pattern table and ImageSet table is working at least enough that I was able to load 50 patterns each with around 4 to 7 images each pattern into the previous test I did, but once I added the PendingPosts table and tried to include the relationship between Pattern and PendingPosts it threw trackeback (detailed in above gist - or I can also just copy/paste here)
torn sphinx
Am I using the sqlite module properly ?
async with asqlite.connect(db_path) as con:
cur = await con.cursor()
await cur.execute(f"INSERT INTO block_commands_general (user_id, guild_id) VALUES({message.author.id}, {message.guild.id})")```that‘s not the sqlite module
supple fox
I'm working on a discord bot and planning on a command to back up the entire servers messages and then build stats off that when it comes to channel and user activity.
However i'm unsure what's the best design for the postgres tables are.
Should i have 1 big table per guild, or serveral smaller tables per channel and then a meta table that links the channels together to a specific guild?
The guild currently got ~1.5m posts with around 1m~ in 2 channels.
ionic pecan
generally you shouldn't dynamically create tables
you could partition the table by guild ID when it gets too big
also see https://github.com/strinking/statbot
supple fox
ah perfect, both postgres and python with the same purpose.
thank you, ill take a look at it.
scarlet canopy
Anyone know why the 1st line works while the 2nd fails? using iomysql. DM_message is text while Server_ID is int(20) if that makes a difference.
await cur.execute(f"insert into welcome (Server_ID) values ({ctx.guild.id})")
await cur.execute(f"insert into welcome (DM_message) values ('test') where Server_ID = ({ctx.guild.id})")
error is 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 'where Server_ID = (604118094977302531)' at line 1")
just started using DB's so not sure whats up
seen that insert into doesn't support a where condition so i tried
await cur.execute(f"update welcome set DM_message = 'test', where Server_ID = {ctx.guild.id}")
and get 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 'where Server_ID = 604118094977302531' at line 1")
patent glen
@scarlet canopy for one thing, you shouldn't be using f-strings to insert values into sql. It's "fine" for simple ints, but it's a very bad habit to get into. That isn't your problem, though. as you said, insert doesn't support where [what do you do if the row doesn't exist, though?], and the update clause should not have a comma before where
i.e. you want
await cur.execute("update welcome set DM_message = 'test' where Server_ID = %s", [ctx.guild.id])
but you need to consider both what do you do if the row doesn't already exist [you have that insert that inserts only the guild id and leaves dm_message blank], and what do you do if it does?
sorry this is slightly confusing to me because i don't know what you're using the table for, and DMs are generally associated with users not servers
reef hawk
If I wanted to run a process indefinitely (e.g. check from a database of a million users whether or not a = 1 and b = 2), would that be super data consuming / laggy?
or maybe that process every second
scarlet canopy
@patent glen just to explain what I'm doing, a welcome/goodbye part to my bot, so i want every server to be able to change what message people get DM'ed on join/leave. also why are f-strings a bad habit?
also i that was a 2nd part to an if statement, so if it already exists it gets ignored, i done that just to make sure that the server is in the table and as a default value since text cant have default values
patent glen
because while integers are fine, you can't safely pass strings to sql this way
@reef hawk it'd probably be useful to create an index so it doesn't have to scan the whole table every time you run the query
reef hawk
an index?
what do u mean
@patent glen so basically index
is like
a mini table
within a table
right?
patent glen
it's something in the database that puts all the values in one column in order, so that the database can find which rows have a = 1 without looking at every row and looking at a
even though the 1's could be anywhere, either at the very beginning (there could be 0, or negative values) or the end (there might be only 0 and 1, and no values greater than 1) or anywhere in between, it can find them by starting from the middle, going halfway forward or halfway back depending on if the value it found was greater or less, and when it finds one they're all together
reef hawk
hmm ok
patent glen
that's called a binary search
reef hawk
binary search tree?
patent glen
it's not a tree [that is a thing but it's not what i'm talking about], it's just a way to search through a sorted list
though the index might be a tree
you don't have to care about that, the database engine deals with it
[it's actually a b-tree, in sqlite]
reef hawk
oh alright, what I'm trying to do is to create an exp system
and checks when exp is higher than level, u level up
patent glen
ok uh
normally you would do that each time they earn xp
rather than once per second or whatever
patent glen
and then you'd have the table indexed (or probably primary key) by user id so the queries are fast
reef hawk
ah alright i guess I'll look into indexing tables then, super new to dbs and dont rly have any clue about it
patent glen
so whenever the user talks or does whatever earns xp
you get the row for that user, get their current xp
calculate the new xp
if it's over the level amount, you do the level too
and then update where user id = whatever
patent glen
they should all call one function to do this
reef hawk
u can earn exp in multiple ways
patent glen
like add_xp(user, number_xp)
reef hawk
yeah I have that system rn actually
patent glen
the user id should be a primary key of the table, so it's indexed automatically without having to do anything special
reef hawk
but its kinda weird
I think execute cant be both executed at the same time?
in python
patent glen
wdym
patent glen
those should not be commands or listeners
reef hawk
so what happens is that when exp > exp needed
patent glen
they should just be regular async def functions that you call from your on message or whatever
reef hawk
nono gain exp is a function rn
like I'm just using it rn to
gain exp
manual way to earn exp
to test things
patent glen
my point is it should not be @commands.command
reef hawk
I want to call on it for now
patent glen
ok but you should have a core function that just takes a user and number
you can make your command call that function
reef hawk
okay
patent glen
and idk what is going on with your @listener() in the other one
can you post your code as text instead of an image? if it's too long you can use https://paste.pythondiscord.com
reef hawk
basically what happens is that the gainexp command is the one that checks it, calls on the lvl_up with the user in the id
okay
patent glen
ok but that shouldn't have cog.listener, i don't even know what that does but i know you don't want it here
patent glen
i'd probably split stamina and xp into two functions, hang on
reef hawk
the main problem I'm experiencing is that:
Whenever I call gainexp, and the exp goes over the required exp, it doesn't level up; it only levels up after I call it the next time I use gainexp again, making me to think that it doesn't check it
it will be separated later, I'm just using a gainexp function as a tester function
patent glen
nevermind, it makes more sense as one function
hmm
yeah but your problem is the architecture is making it hard to tell when stuff is happening
ok i see your actual problem
patent glen
you're passing the user value you fetched at the beginning into lvl_up, so it's the previous xp
reef hawk
ohhh
I see
yeah that makes total sense
I'm thinking of turning lvl_up into a util to import, do u think that would be a good idea?
patent glen
no it should still be a function
wait, no
you can just take care of all the logic in the exp function
reef hawk
gainexp function?
patent glen
[it might make sense for that to be a util function, but then you'll need to figure out how the util functions can access pg_con and ctx]
reef hawk
ah right
I was thinking of turning it into a function that can be called from any command since I'll probably have multiple sources to gain exp from
patent glen
i'm finishing up my idea to post here
patent glen
yeah, that's what i'm doing, but it's still a function inside the bot, not a separate place to import... not sure how that works if it's in a cog and you want to call from another cog though
check this out ```py
@commands.command()
async def gainexp(self, ctx):
if ctx.author == self.bot.user:
return
if (await self.cost_stamina(ctx.author, 2)):
await self.add_exp(ctx.author, 2)
async def cost_stamina(self, ctx, user, cost):
"Deducts a user's stamina and returns whether they had enough stamina"
user_id = str(user.id)
userdata = await self.bot.pg_con.fetchrow("SELECT * FROM gameusers WHERE user_id = $1", user_id)
if not userdata:
await ctx.send(f"{user.mention} has not registered with the bot! Please type start to continue!")
return False
if userdata['stamina'] >= cost:
await self.bot.pg_con.execute("UPDATE gameusers SET stamina = stamina - $1 WHERE user_id = $2", cost, user_id)
return True
else:
return False
async def add_exp(self, ctx, user, amount):
"Takes care of the actual logic of adding XP and leveling up."
# NOTE assumes that the user can only level up once each time
user_id = str(user.id)
userdata = await self.bot.pg_con.fetchrow("SELECT * FROM gameusers WHERE user_id = $1", user_id)
exp = userdata['exp']
lvl = userdata['lvl']
await ctx.send("Gained EXP!")
exp = exp + 2 # adding it to the variable so level check has correct value
threshold = int(round(4 * lvl ** 2))
do_lvl = (exp >= threshold)
if do_lvl:
lvl += 1
exp -= threshold
await ctx.send(f"Congratulations fellow summoner {user.mention}, you are now level **{lvl}**!")
await self.bot.pg_con.execute("UPDATE gameusers SET lvl = $1, exp = $2 WHERE user_id = $3", lvl, exp, user_id)
[i changed the stamina to use stamina - $1 to show you can do the math inside the sql, i thought about doing the same for exp/level but it ended up being too complicated
edited a couple times for renamed variables i didn't catch
reef hawk
ahh okay
patent glen
so cost_stamina checks if the user has at least 2 stamina and takes it away if so, and returns true
reef hawk
thank u so much for ur time!
patent glen
then add_exp gives the actual xp and takes care of level up
reef hawk
yeah I'll check it out, really appreciate it
patent glen
incidentally
you are probably going to need to rewrite these functions to automatically register users or just silently return if they're not registered
instead of having the start command and noisy error telling you to type start
reef hawk
gotcha
patent glen
i'd probably just make a function that returns self.bot.pg_con.fetchrow("SELECT * FROM gameusers WHERE user_id = $1", user_id) if the user exists, or registers the user if not
then both of the other functions can call it to get the user info
reef hawk
yeah that's my idea too
1 question about guild ids though, since they have different lengths of character in them
is it fine to use character varying as the data type for them?
patent glen
uh
they should be 64-bit integers
if you saw one that was shorter it was because the guild was older
patent glen
sure... it'd probably be better to use bigint though
that way you don't have to use str(user.id) too
you can just use user.id directly
reef hawk
hmm so I should use bigint for user id as well instead of char varying?
patent glen
i would yeah
reef hawk
okay, I'll make those changes, tyvm!!
patent glen
that or numeric(20) [technically bigint has half the range of discord ids because it's signed, but that won't matter until the year 2084]
so are you really expecting to have a million users?
like i'm not saying i'm doubting you if you are, just wanted to point out that making a bot for a large server or one that's going to sit in a lot of different servers means anything that's inefficient has its cost magnified a lot so it's very important to look at efficiency
reef hawk
nah not at all, I just want to design something that can be efficient, thats why I say it
patent glen
ah
patent glen
one thing that might be interesting to add is a caching mechanism so you don't have to re-get the stamina xp and levels of the users from the database every time
[especially since the separation of functions i added means you're getting it twice, once for the stamina and once for the xp/level]
reef hawk
oh okay I'll look into that, thanks c:
I think this happened last time too with a problem that I had
@commands.command()
async def gainexp(self, ctx):
if ctx.author == self.bot.user:
return
if (await self.cost_stamina(ctx.author, 2)):
await self.add_exp(ctx.author, 2)
async def cost_stamina(self, ctx, user, cost):
"Deducts a user's stamina and returns whether they had enough stamina"
user_id = str(user.id)
userdata = await self.bot.pg_con.fetchrow("SELECT * FROM gameusers WHERE user_id = $1", user_id)
if not userdata:
await ctx.send(f"{user.mention} has not registered with the bot! Please type start to continue!")
return False
if userdata['stamina'] >= cost:
await self.bot.pg_con.execute("UPDATE gameusers SET stamina = stamina - $1 WHERE user_id = $2", cost, user_id)
return True
else:
return False```theres an error on the self.cost_stamina
patent glen
sorry, forgot to pass ctx in
if (await self.cost_stamina(ctx, ctx.author, 2)):
await self.add_exp(ctx, ctx.author, 2)
ahh right
last time I had it with self, but u do that by calling self.function
got it thanks!
torn sphinx
Whats an efficient way to get all of the profiles in a database and export it to a csv without it taking too long?
karmic cliff
#afkcommand
def load_json(path="afk.json"):
""" Load the json from JSON. """
if os.path.exists(path):
with open(path) as config_file:
haha = json.load(config_file)
else:
print(
f"No json file exists at {path}.")
exit(1)
return haha
def save_json(json_dict: dict, path="afk.json"):
""" Save json in a passed config file. """
with open(path, "w") as config_file:
json.dump(json_dict, config_file, indent=4)
return True
AFK_STUFF = load_json("afk.json")
@client.command()
async def afk(ctx, *, reason="afk"):
AFK_STUFF[str(ctx.guild.id)][str(ctx.author.id)] = reason
save_json(AFK_STUFF, "afk.json")
await ctx.send(f"**``{ctx.author}``, I set your ``AFK`` to : ``{reason}`` :white_check_mark:!**")``` ok so, this is my script that puts it into a database, is there a way i can use method ``pop`` and remove the data from the database on some event?How to create a remote postgresql database?
I can only find a way to make a local db but not remote
steel plover
Sry i dont exactly understand that yet, maybe there are a tutorial or web to read abt this?
ocean prairie
cy, u mean to say that u already have a program to create a database in ur local
all u need is to do the same to a database which is hosted somewhere else ?
@steel plover
steel plover
Yes, say hosted on postgresql server
ocean prairie
all you need to replace is ur connection string with the server details
and the same set privileges that requires to crate db
@steel plover
steel plover
Ohh i see, i'll try it after im done with a work. thank you
reef hawk
I'm having troubles understanding how to transfer database info and tables itself into a new database on a VPS. I (think) I've downloaded postgres already along with python + the imports I need, but I have no clue how to transfer the database
My PC is using Windows and the VPS I'm using is Debian if that matters at all, if it complicates things a lot I can switch the VPS to Windows
ocean prairie
@reef hawk ur source db is postgres ?
reef hawk
yep
reef hawk
@ocean prairie using Vultr
ocean prairie
so u mean u are cloning postgres from some place to vultr ?
ur target and source is going to be postgres ?
reef hawk
Yes
ocean prairie
@reef hawk
reef hawk
lol sorry im new and beginner to this stuff
very low
not sure but
not heavy at all
Its a bot I havent opened yet, ive only tested it with myself
so almost 0 data in it
ocean prairie
i think we shall use psycopg2 package to do this
let me try to find any available packages or code is there online
reef hawk
Okay, thank u so much!
reef hawk
@ocean prairie yep, saw them, ty!
ocean prairie
those looks extensive, if you are planning to go for huge project then its perfectly alright
if you want play around with small amount of data then use psycopg2 with pandas
to achieve the same 👍
@reef hawk
reef hawk
I'm trying to back it up but it said password authentication failed, I'm pretty sure I have the right password
I searched around and it might be because the username is off? apparently your computer username has to match the DB username? not sure, need some clarification
ah nevermind it's probably asking for admin permissions from PC, in which case I'm still confused because my DB pw and PC pw is the same
ocean prairie
@reef hawk still the same prob ?
reef hawk
@ocean prairie well I havent been trying to run it on admin, it might help my problem but yeah
ill try to run on admin in a bit and see if I still get that
ocean prairie
@reef hawk 👍
reef hawk
@ocean prairie yea still doesnt work, no clue
running as administrator still prompts the password, and when I enter in the pw it doesnt work
flint bobcat
Throwong it here in case some people are interrsted.
This is how we manage postgresql databases at one of the biggest french website.
iron axle
Looking for help with SQLAlchemy and creating an efficient DB query since the one I have now is ludicrously complex for what it should be 
torn sphinx
did you remember all the rules for writing a good query
- Filter early and often use Where.. 2. Order on the outermost query, 3. Don't select * when you don't need to 4. High cardinality (more unique groups) will result in slower queries when using Group By; result in more shuffling and key skew can lead to increased tail latency, 4. Excess number of rows weigh the query performance down as much as number of columns .. 5. Consider your JOIN order, filter the sets pre-JOIN.. 6. Exact Count Distinct is very costly, try to use an approx count distinct if you have it
reef hawk
Serial doesn't keep track of what u delete?
I deleted 20 columns, and I guess it doesn't keep track of it
is there a way to make it keep track?
reef hawk
@torn sphinx I'm trying to use serial so that the row # matches the card ID
I used to have 20 ID's before, but I deleted it and added 7 more
however, serial still starts from where I deleted
civic rover
I have 500 KB audio files and want to store them in my MongoDB. Should I go for GridFS or store it as binary data directly in the document?
paper grail
Hey guys, have an app architecture question with databases. Using Flask with SQLalchemy. Have every model in a separate file. One of the things I’m having trouble with is circular dependencies.
For example, I have a class User and class Student. User imports Student because when User.create() is called, it also creates a Student.
Now at this point, the Student model file can not import User due to any circular dependencies. Is there any way around this or should I not be importing models within models? I also face this issue if I have a query within the model. For example, if the Student model imports User to join on User to run a query.
ocean prairie
@reef hawk serial counter work in that way oly
@iron axle resolved your complex query ?
reef hawk
yeah figured ):
rich trout
@paper grail The easiest away around this issue is to not use from x import y style imports
For example:
# file 1
from file2 import A
class B:
def a(self):
return A()
# file2
from file1 import B
class A:
def b(self):
return B()
Will fail, because when file1 imports file2, file1 then tries to import the name B from file2, but it's not defined yet--and can never be defined yet, since the other import must resolve first. However, if you do not import a specific name, that is, your code looks like this:
# file 1
import file2
class B:
def a(self):
return file2.A()
# file2
import file1
class A:
def b(self):
return file1.B()
It will work--perhaps counterintuitively, but entirely reasonably.
When your code does an import, it first checks to see if a module has been imported already. If it has, it simply uses the already imported file, even if that file has not been completely executed yet. This can lead to some very interesting errors if you access the module while it's still loading, but as long as you only define functions that use them and don't actually try to construct a circular programmatic flow it should work just fine.
that is, circular imports used within functions that are not called until the modules are done loading are ok, but circular imports used immediately are not--because how is the code after the import supposed to run if the import is not done? The functions can, because they don't rely on the module being loaded before they are defined but on the module being available when they are executed.
iron axle
@ocean prairie nah, I'm not gonna be able to do it very well. Query is running as a computed field on a model thats being dumped and is adding nearly 2s of time to each endpoint it touches
Our structure is essentially this:
We have a Parent model thats inherited by three different types of Child models.
We also have a Foo model that has four many-to-many relationships that can accept any of the Parent models or the Child models that inherit Parent, we can assume that we will have no pure Parent models tho and they're all Child types
Now.... we also have a many field of Foo on a model
I wanted to collect a UNIQUE list all of the inherited models (ie Child models) across all four many-to-many fields across all instances of Foo
so we have many duplicate listings because many of the Foo models will have duplicate relationships between all four fields, just shuffled around
the real kicker is that the model that has the many field of Foo also is an object we have many of
so yeah, really complex query that has a lot of moving parts in it and I'm not sure how to write something thats not just a direct SQL statement
plain radish
@ocean prairie can you please fix your nickname up to comply with our policy: https://pythondiscord.com/pages/rules#nickname-policy
Noisy unicode characters aren't allowed.
ocean prairie
@plain radish sure
plain radish
thanks
ocean prairie
@plain radish I didn't read it properly in the first place 😶
lusty igloo
What would be best type of data to be used for storing stuff like bot prefixes? Right now I use json in a hacky way but doubt it is anyhow good. Could it be possible to put sets into postgres perhaps or maybe tuples?
rich trout
Generally you'd make a table with a prefix column, and a server id column, which is essentially a dictionary if you look at it from a python perspective. You could do it in sqlite, postgres, or another DB type
Usually when you find yourself asking "How do I store a list in a db column" what you really should be doing is making a new table and a relationship. Instead of
Table Thing
name items
"test" [1, 2, 3, 4]
This makes more sense from a relational perspective and is essentially the same data format:
Table Thing
name id
"test" 1
Table Items
thing value
1 1
1 2
1 3
1 4
Sounds bit complicated
rich trout
You'd then query across them with something like
SELECT `Thing.name`, `items.value` FROM `Thing` JOIN `Items` ON `items.thing` == `Thing.name` WHERE `Thing.name` == "test"
It is a little complicated, but it's really just a different perspective on the data, looking at it from a "things only expand in number of rows" kind of way. It's one of the details that grants databases their efficiency and speed
lusty igloo
Well that sounds pretty complicated and out of my knowledge range
rich trout
If you don't want to configure the SQL yourself, you can always use an ORM, but if you don't want to have a burden of knowledge you'll want to pick a simple one. One I've spent time with is Pony, which doesn't really do some of the very complicated stuff, but that sounds perfect for your use case.
lusty igloo
All I really know about databases is inserting creating dropping and thats about it
rich trout
class Thing(db.Entity):
name = orm.Required(str)
items = orm.Set(lambda: Item)
class Item(db.Entity):
value = orm.Required(int)
Is what it would look like under pony
and to request, something like ```py
Thing.get(name=="test").items
ORM?
rich trout
Object-relational-mapper
Takes your python objects, turns them into relational database structures for you
lusty igloo
I'd assume learning all of that would take me more time than it would profit me sadly
rich trout
Maybe, maybe not, it's quite valuable information. If not you can always go back to JSON in a file or pickling your prefix dictionary/lists
lusty igloo
As of now I just try to use dicts as some sort of sets and dump them into json strings that I upload to database
rich trout
Mongodb is also an option, but I've got little or no experience with them
sly zinc
and is easy to use
lusty igloo
I don't think my way is efficient as well
Those are dicts
I need to set the values for them and I only use keys
Nothing is easy to use when you use ubuntu that tries to go against you
rich trout
I would honestly suggest learning Pony for your use case. It will be both more valuable than you expect and do everything you'd want from it
sly zinc
the nightmare was the cloud9 loging out every hour
lusty igloo
I'm just set pretty well with Postgres and I'd rather not to convert all of the data I've got there to MongoDB
Also from what I've seen and heard MongoDB is really niche for d.py bots
sly zinc
I'm using postgres now
lusty igloo
@rich trout Thanks btw, will try to learn Pony then
blissful harness
Anyone have any idea on how can i store the data offline and then whenever the connection establishes(i.e. whenever i will get connected to the internet) the data will get transfered to the database
odd heron
@blissful harness yes you theres no reason you can't do that, ofcourse depending on what you are using as your data store and code/application architecture might depend on how you execute that task.
If you know what time you will be connecting to the internet you can always set up some type of cron job to run a data extraction type job
toxic rune
So I have the following schemas (simplified to shorten): https://mystb.in/pirirujuci.sql
Now, taking into account that every item can have one or multiple categories, I thought of storing it on a SMALLINT[] array, the issue here is that this would bypass the lookup table (Game.ItemCategory) and inexistent categories could be added (shouldn't happen though) is it better to have a separate table where I store every item category per row? For example:
item_id 1
category_id 1
item_id 1
category_id 5
Or is there some other alternative?
rich trout
If you're only going to have very few categories (1-5) it might be efficient to have five columns for each item. Otherwise yes, the additional table is the usual solution
From my perspective, the difference between a smallint array and a lookup table is really just the fact that you're attempting to do the optimization instead of the database
Realistically, you cannot store the array within the row and keep the easy linearization pattern the other rows support. So it would make sense to keep it in a separate table, so item 50 is always at position rowlen*50. But then you'd have to split the items up to keep that property in the second table, and now you're exactly where you started: with an intermediate table of item<->category associations
Of course, you might think it's simpler to only support a small number of elements, and if larger go to another table, but that's the "a few columns" approach as well, except that also provides you with the relationship validation
toxic rune
@rich trout I see. Now that I'm on my own PC (I should've probably waited) I can provide a better example. I'd have ~20 categories, but in this example would be type of equipment. With the better example: Each character has 3 equipment slots in which every slot can equip one, two (maybe three? But the amount can vary.) items from those equipment categories. Would it still be appropriate to use the additional table? For example a character:
Name: char_1
equip_slot_1: category_equip_1, category_equip_6
equip_slot_2: category_equip_2, category_equip_1
equip_slot_3: category_equip_5
rugged sluice
Question: I have Acces, Acces ODBC ( i checked at ODBC) but jupitar can find only this one:
; SQL Server
SQL Server Native Client 11.0
SQL Server Native Client RDA 11.0
Oracle in OraDB18Home1
ODBC Driver 17 for SQL Server
how can i fix this? 🙂
simple check is: import pyodbc
for driver in pyodbc.drivers():
print(driver)
reef hawk
how does the data in a table move around?
my data keeps jumping around randomly lmfao when I update it? I think, idk
minor ruin
Jumping around?
reef hawk
yeah
like
it moves from the 7th row
to the first row
super weird
idk just random jumps
minor ruin
Tables are not ordered unless select them that way and there is a key to sort on
reef hawk
ohhh
how do u order them? I have a key
but
doesnt seem to order it
incremental key
sequence
reef hawk
oh alright so what I'm doing rn is calling the db from python
and every time I call it I want it to be in a certain order
but when I call it it sometimes randomizes
minor ruin
Even with sort by/order by?
minor ruin
Every time you select and want it ordered
reef hawk
ah alright
so is it normal that everytime I update or select the db, the rows change in pgadmin?
im viewing the data and it jumps around and just wondering if thats normal
minor ruin
Don’t use it but I assume it doesn’t show tables by sort command
reef hawk
it shows up in the same way every time if I don't do anything with the table
minor ruin
And Postgres is order by
reef hawk
only when I select it and update it it randomly does it
minor ruin
If you do not issue order by command, the database software can and will issue in whatever order it feels like
It’s why that command exists
reef hawk
ah alright, so there isn't a default order?
I was thinking the default order would be inserting at the last row, and keeping it that way
by time essentially
paper grail
Hello question if anyone knows this. Using sqlalchemy with flask. I'm trying to get sqlalchemy to return tuples with a list inside of them on one to many tables. For example, I have user and comments and I can do something like this,
User.query.first().comments == [comment1, comment2, comment3]
What i'm trying to do is the following;
db.session.query(User, Posts).first() == [user1, [comment1, comment2, comment3]] but instead it's returning
[user1, comment1 (first instance of comment from user)]
Anyone have an familiarity with this or done anything similar?
dusky vessel
Hey guys. Concept question. Any recommendations on writing to a MySQL database (using Flask-SQLAlchemy or I guess just SQLAlchemy) from a Celery task? If I use Flask's db.session object in the Celery task, there is no guarantee it will work because the task might take a very long time, and by then the MySQL connection may have "gone away" (the connection will have timed out). I could do something like create an engine every time I need to write to it in the Celery task, but that seems extremely inefficient.
I don't necessarily need code, just some guidance. For some reason I can't find info about this simple use case online.
torn sphinx
Using MYSQL & its official Connector, how can one get both the previous and next row of a specific row with fetchmany?
iron axle
@dusky vessel flask-sqlalchemy requries app context which I'm not sure you'd be able to pass along with celery. We had a similar issue and solved it by using dramatiq with a redis queue message broker
this allowed us to spawn workers that were exact clones of our main flask app and run the "actors" there
all things considered, dramatiq was more feature rich with what we wanted and I'd suggest looking at it as a celery replacement
dusky vessel
@iron axle You actually can pass along the app context. Found online some code to do it a while back. It works as intended, but the issue is that I just don’t think I should. Why? The timeouts. If the session starts at the request and exists all throughout the task, the MySQL connection is going to “go away” by the time it actually does a write to the database. It seems like I need to just open a connection with every instance variable I update.
mossy lotus
My teacher recommended me mongoDB is it good?
carmine heart
For which application?
mossy lotus
Same purpose as phpmyadmin
Databases
Like sql, I don’t know sorry but i told him i need something better than phpmyadmin
For the purpose of displaying data from a db to an html and from an html form to a db
carmine heart
You can't really say "this database system is always the best", but phpmyadmin isn't a database. It's a web application used to manage MySQL databases.
I'm not a database expert, but, from my understanding, mongoDB is a document-oriented, non-relational, NoSQL database
mossy lotus
Ah yeah its has a good user interface
carmine heart
As opposed to another popular database system, Postgresql
phpmyadmin is a user interface that interacts with a database system; it's not the database itself
It's like an app/webpage you can use to do things with your actual database system; to manage it
mossy lotus
Oh i see
Ah so phpmyadmin is like for managing the db while mongoDB is more like the db itself
Its a bit confusing since you can also do a lot of things with sql in phpmyadmin
I’m confused about all this now
carmine heart
Well, phpmyadmin is a user interface that's explicitly designed to manage a mysql database
mossy lotus
I’ll read about it in google that’ll probably clear it out for me thanks 🙏
carmine heart
That's why it allows you to use SQL queries directly, manage the database by interacting with it, and so on
mossy lotus
Right
carmine heart
It will then communicate with the actual database system for you
dusky vessel
I remember when I used MyAdmin in college. That thing is old-looking.
carmine heart
It depends entirely on how you're using it. I don't have experience with mongodb myself, but I do use Postgresql in combination with Django, where Django does most of the heavy lifting for me in its ORM (object-relational mapping)
That means I rarely have to write SQL queries by hand, as I can use the utilities that Django provides for me
mossy lotus
Oh cool
Django feels like a whole another programming language it gets so much things done
I should probably stick with Myadmin in this case
I’m more like a front end
Thanks a lot
gentle sparrow
Does anyone know the powershell command to get into the django environment you created
carmine heart
Assuming python points to the python version you've installed django with, python manage.py shell in the directory that contains manage.py
wanton aurora
Does anyone know how to get a row from oracle that is raw datatype using sqlalchemy?
native vapor
I have a dumb question about SQL query. I want to join a table based on 2 values at once
If you have a table with A B C and another with A B D i'd like to join Where A =A and B =B)
gentle sparrow
@carmine heart Thanks
native vapor
What would be the best way to do this ?
Ah nvm i think i'm dumb or proc SQL is being weird
dawn hill
@native vapor You can have two joins in a query at once
void otter
evening guys
i have a question regarding designing tables, using flask_sqlalchemy
i have this code:
from app import db
from datetime import datetime
def datetime_now_iso_format():
return datetime.now().isoformat()
class User(db.Model):
uuid = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(30), unique=True, nullable=False)
fname = db.Column(db.String(20), nullable=False)
surname = db.Column(db.String(35), nullable=False)
is_verified = db.Column(db.Boolean(), default=False)
profile_pic = db.Column(db.Text(), default="default.jpg")
email = db.Column(db.String(120), unique=True, nullable=False)
password = db.Column(db.String(50), nullable=False)
events = db.relationship("Event", backref="organizer", lazy=True)
def __repr__(self):
return f"User({self.uuid}, {self.fname}, {self.surname}, {self.email})"
class Event(db.Model):
uuid = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(120), nullable=False)
date_posted = db.Column(db.String(50), nullable=False, default=datetime_now_iso_format)
description = db.Column(db.Text)
#joined_users = db.relationship("Joined_Users", backref="joined_users", uselist=False, lazy=True)
user_uuid = db.Column(db.Integer, db.ForeignKey("user.uuid"), nullable=False)
def __repr__(self):
return f"Event({self.uuid}, {self.title}, {self.description}, oranizer id={self.user_uuid})"
i can define users and events, set users as event organizators without any problems
but now i would need to have joined_users column that would have all the users joined
like
joined_usrs = Event.query.first().joined_users # returns [user(1, saki1), user(2, saki2)]
i am being told that another table is needed for this to work, but i can't wrap my head around it
thanks in advance
i can do this with postgres.ARRAY but it would be waste of resources and very messy so i am trying to avoid it
rich trout
@void otter you would need an association table, see here: https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#many-to-many
void otter
so many to many relationship between the 2?
and how would i add users to event
is it handled like an ordinary array?
rich trout
yes
myparent.children.remove(somechild)
myparent.children.add(somechild)
I also could have sworn you did not need to describe the association table if you were not using it, but I will need a moment to look for an example
void otter
i found one on youtube
he didn't use db.Model to create table
he used db.Table
if that's what you mean
rich trout
👍
tulip heart
Anyone have experience with redis queue and pooling database connection between workers?
ember heath
Hey guys, I've never used or worked with databases before but do you think it would be a good idea to if I wanted to build a rank system into my discord bot>
void otter
I would say first learn some basic concepts
Tables, columns, sql syntax etc
Before tackling
Because rank system can be quite a task
ember heath
Sure, I was just wondering if using a database would be a good approach and if so I'll start going over them
void otter
Yes, it is a good approach
ember heath
Okay, thank you. Do you recommend any resources ?
void otter
i don't know any good resources since i learned databases basics at school
ember heath
I'm getting this error:
asyncpg.exceptions.InvalidCatalogNameError: database "servermanagerdb" does not exist
despite the database being running...
ember heath
I used the pgAdmin4 tool that comes pre-installed
nvm made the table in the wrong datavase :))))
void otter
🙃
heavy topaz
Hi guys, I've got a question regarding a database design for a simple inventory management system. I'm after guidance on how-to manage / avoid a polymorphic relationship.Would be grateful if you could take a look. Question with schema picture is here: https://dba.stackexchange.com/questions/257634/simple-inventory-management-db-design-polymorphic-association
tame anchor
I was reading some SQLite documentation and don't really understand the difference between using [brackets] and using (parentheses).
c.execute('INSERT INTO list VALUES(?)', [name])
c.execute('INSERT INTO list VALUES(?)', (name))
c.execute('INSERT INTO list VALUES(?, ?)', ('name', 'age')
Could something like this work?
c.execute('INSERT INTO list VALUES(?, ?)', [name, age])
ionic pecan
c.execute('INSERT INTO list VALUES(?)', (name)) isn't valid, as that will just pass name directly
(name) == name
But yes, brackets and parantheses are equivalent here
tame anchor
@ionic pecan ah ok so (name) in this case would be like it getting changed from print(name) to print('name').
c.execute('INSERT INTO list VALUES(?, ?)', [name, age]) this would be the correct way if i was getting 2 different values right? or is there another preferred method?
ionic pecan
@tame anchor no, print(name) and print('name') is different, that's passing a variable named name or passing a string with the content name
if you want to pass a single value you can either use [name] or (name,) in c.execute, as (name) == name
acoustic leaf
Is there a way to wend sending a message for a dynamic website to receive back additional information such as the table/database
Or to look at it through some means?
jade dune
wrong channel
bleak pecan
@torn sphinx when you pass 1 arg its usually .execute(query, (arg,)) maybe try that with/without arg in a list, also I see such mentioned a few messages up
torn sphinx
query = 'UPDATE guilds SET members = array_cat(members, $1);'
await self.pool.execute(query, (member.id,))
tried that, but doesn't work
@bleak pecan
i think i am appending in the wrong way
actually the values was been appended
so prob the selecting is wrong
idk
torn sphinx
hello ! i have a simple question, I'm new to python, and I was wondering if it was possible to integrate mysql queries in a function ?
cinder sierra
if you mean create a function that uses the mysql connector and executes queries...yeah?
light pike
So I'm trying to have a default value on sqlalchemy
I tried using this
class Account(Base):
__tablename__ = 'account'
id = Column(Integer, primary_key=True)
cookie_count = Column(SmallInteger, default=select([0]))
def __repr__(self):
return f"<Account id=\"{self.id}\" cookie_count=\"{self.cookie_count}\">"```
however when I instantiate an object those default values will be None, which is not what I want because stuff requires to add numbers from the original valueI think you've confused the purpose of select here? select is intended to determine the default dynamically from the database. If you want columns to default to 0, then you should just put default=0
light pike
tried changing it, keeps being None
rich trout
Try specifying nullable=False
It's possible to insert None into a nullable column with a default
You may also want server_default, which sets the default value on the server side rather than on insertion
light pike
keeps being None
rich trout
Are you recreating your schema, and what does your insertion code look like?
If you've set nullable to false I find it hard to believe you've gotten none
light pike
I'm deleting the database every time, and my code rebuilds it if it is missing
CREATE TABLE account (
id INTEGER NOT NULL,
cookie_count SMALLINT NOT NULL,
PRIMARY KEY (id)
)```That's the correct schema--so if you're getting none out of that, I'm not sure exactly what's gone wrong
Maybe if you haven't done session.add() yet?
But that really doesn't seem right
light pike
my code runs through a function that creates the object and adds it right away
def get_user(self, user_id):
result = self.session.query(self.DBDeclare.Account).filter_by(id=user_id).first()
if result is None:
new_user = self.DBDeclare.Account(id=user_id)
self.session.add(new_user)
return new_user
else:
return result```And your current account code that's giving us the above schema?
light pike
...?
class Account(Base):
__tablename__ = 'account'
id = Column(Integer, primary_key=True)
cookie_count = Column(SmallInteger, default=0, nullable=False, server_default="0")
def __repr__(self):
return f"<Account id=\"{self.id}\" cookie_count=\"{self.cookie_count}\">"```
this?Yes
light pike
I added the server default after
rich trout
Ah. I have determined the issue
Turns out sqlalchemy's defaults are not avialable until after the object has been not only added but committed
from sqlalchemy_utils import force_instant_defaults
force_instant_defaults()
Can be used to alter that behavior
light pike
after creating the object?
rich trout
during your import step
light pike
hmmm there is no sqlalchemy_utils
rich trout
It's an additional package that provides some sqlalchemy helpers
You could also set the default in your __init__ for your model
light pike
it's working now
rich trout
silent mantle
Is there a company that provides database servers free as long as it's open source work being done?
pure scroll
AND u.champion_id = c.id; is redundant
you are doing a join on this condition, so it does noot make any sense to put the same statement into the where clause
native vapor
i have to parse a value in proc sql 😒 is that even possible
rough onyx
@native vapor prxparse
native vapor
managed with prxmatch in the end
blazing gust
Anyone here who deals with SQL for some big company?
cinder sierra
i feel you'll be surprised what people here know, big company or not. just ask your question please
blazing gust
@cinder sierra well I just ran into a query that is like 8000 characters (300 lines) looks completely fucked up and I just wonder if that is normal
I work at a pretty big company and this is the first time I had to deal with their queries
Normally I just get the data, do my calculations and automation and shove it out the other side but we have some explainable issues
the results dont match up, so I had to get the query and figure out wtf is going on
cinder sierra
doesn't sound preferable but also doesn't sound unreasonable. they likely just don't have a pipeline set up
blazing gust
ic
@cinder sierra also the reason I wondered was not related to the amount of knowledge just if steaming piles of shit is common or not kinda
We get thousands of query results delivered daily by email
cinder sierra
mate i'm still here, please don't keep @ing me, just type
blazing gust
sorry man, just a habit
then we have scripts that fetch them from those emails and so on
I am just hoping this is not how the rest of the industry functions
if it is the case then that is really depressing
cinder sierra
in any business anything can become a massive fuck up, it's just a given if the business doesn't focus structure in a given department, tech or not
blazing gust
mm
cinder sierra
in this case no, that would likely not be an industry standard. there have been systems developed for the exact purpose of avoiding this or at least making it a one and done code
blazing gust
Alright
native vapor
at my job i have massive requests that are 300 lines of SQL nested queries with tables names such as aaa and bb @blazing gust
automating emails would be "too much work"
so we have 3 people doing it manually for some stuff
(of course i'm the lucky winner to rework that)
blazing gust
@native vapor similar situation, nothing named in a way that makes sense. We automate everything but in the worst possible way.
its not pretty
native vapor
i feel you
blazing gust
Well I quit, I will be out in a few weeks.
native vapor
ah lucky you 😮
need to find a new job
ah lucky you 😮
need to find a new job
ah lucky you 😮
i have 3/4 months to find a new job to move in with my partner
i want out as well, because i feel like if i stay too long i will start to think like them
blazing gust
I know what you mean. I am not looking for a new job, starting my own company with blackjack and hookers!
Maybe it will crash and burn, we'll see but at least I will try to escape my chains. :)
native vapor
maybe not good for your wallet but good for your spirits!
foggy aspen
SQL at a large company is just like you describe above... you can see everything from elegant logic to terrible messes
It really depends on who is doing the querying... human or machine (lots of tools auto generate SQL which, while semantically correct, are a total pain to read by humans),
For human-written queries: you have everything from low-skill technical support folks to programmers from a non-SQL paradigm to business users just learning to crazy data analysts... it’s all based on user’s job/function, their knowledge/training, and their queries’ purpose
I’ve worked at both large companies and small startups... the businesses that invest in good technical middle-managers that guide their tech folks to code well (whether in sql, python, or anything else) seem to have the best success at creating and maintain efficient code bases (including, in this case, the daily deluge of sql queries one might see)
minor ruin
I work at big company and we have some doozy of SQL queries. I found this is more common with bigger companies for two reasons, A) "It works, leave it alone" tends to be much bigger at enterprises and B) They tend to have Enterprise budgets so server is slow? Here is nice SSD/NVMe SAN array to fix that.
or with cloud, even better
I've got 20k/mthly USD Azure SQL Database
native vapor
At my company it's more "it used to work but then the guy left"
"We don't have time to redoit but reuse his job, but we need to change x/y/z"
burn your worker
guy leaves
repeat
torn sphinx
what's better asyncpg or aiopg and does SA support this drivers?
tame anchor
torn sphinx
@harsh pulsar hey can you help me with asyncpgsa?
how to insert or create table with asyncpgsa?
median remnant
I get a foreign key mismatch error while performing python manage.py migrate, can anybody please help?
class BaseUser(AbstractUser):
fullName = models.CharField(verbose_name='Full Name', max_length=500, null=True)
contactNumber = models.BigIntegerField(verbose_name='Contact Number')
address = models.CharField(max_length=255, verbose_name='Address')
identificationNumber = models.BigIntegerField(verbose_name='Identification Number', unique=True, primary_key=True)
def __str__(self):
return self.fullName
class Customer(BaseUser):
licenseNumber = models.BigIntegerField(verbose_name='License Number', unique=True)
profilePicture = models.ImageField(
upload_to='customers/',
verbose_name='Profile Picture',
default='defaultUser.jpg'
)
noOfVehicles = models.IntegerField(verbose_name='Number of Vehicles')
isLandlord = False
isCustomer = True
class Meta:
verbose_name = 'Customer'
verbose_name_plural = 'Customers'
def save(self, *args, **kwargs):
super().save(*args, **kwargs)
self.fullName = self.first_name + " " + self.last_name
img = Image.open(self.profilePicture.path)
if img.height > 300 or img.width > 300:
outputSize = (300, 300)
img.thumbnail(outputSize)
img.save(self.profilePicture.path)
class VehicleDetails(models.Model):
owner = models.ForeignKey(Customer, on_delete=models.CASCADE, to_field='identificationNumber',
related_name='vehicleDetails', null=True)
vehiclePlateNumber = models.CharField(verbose_name='Vehicle Plate Number', max_length=100)
vehicleTypes = [
('CAR', 'Car'),
('BIKE', 'Motorcycle'),
]
vehicleType = models.CharField(verbose_name='Vehicle Type', max_length=10, choices=vehicleTypes, default='CAR')
def __str__(self):
return self.vehiclePlateNumber
This fires an error django.db.utils.OperationalError: foreign key mismatch - "accounts_vehicledetails" referencing "accounts_customer"
median remnant
Nevermind, solved after I removed to_field and related_name from owner
errant sable
whats the easiest way to print your table in sqlite database?
foggy aspen
this looks pretty easy: https://stackoverflow.com/questions/10522830/how-to-export-sqlite-to-csv-in-python-without-being-formatted-as-a-list
errant sable
can I print it in my terminal?
foggy aspen
not with that Stack Overflow code, unfortunately...the suggestion there prints to a CSV file
look at the pattern the person asking the question uses... this person uses a method that might print to terminal (although you'd likely need to play around with it)
foggy aspen
is there any time/date-based stuff that identifies whether the records from the API are "new", or is it all by the unique name?
with only like 2,200 records, it's probably not a big deal to take your approach... depends on what kind of equipment you're running the code on (or if you pay by processing time, like a Lambda function)
lilac spruce
Hi, im trying to integrate a database into my discord bot using discord.py but im running into an issue with custom prefixes. Im getting these 2 errors:
TypeError: 'function' object is not iterable
TypeError: command_prefix must be plain string, iterable of strings, or callable returning either of these, not function
the line causing the issue is return commands.when_mentioned_or(guild)
but when i define guild ( guild = await bot.pg_con.fetchval("SELECT prefix FROM guilds WHERE guild_id = $1",str(message.guild.id))) and using good old print functions, the console prints that guild is not a function, like how the error states if im reading it correctly, but is actually a string. And when printing guild outright, it prints the correct prefix i expect to be in the database
Why would i be getting typeerrors for a function when using type() prints out as a string?
toxic rune
If I have a grid like this: https://mystb.in/muzigijego.bash What would be a proper approach to store it in a database? I thought of having a list like this: [[1, 0], [0, 1], [-1, 0]] each list inside the list is a different row, the first value would be the row number and the second the amount of columns. Or alternatively just a simple list like: [0, 1, 0] each number is a different row and the numbers are the amount of columns per row.
blazing arch
Most databases now allow you to store json directly
toxic rune
Wouldn't it be overkill since I just need to know the amount of columns? Second method already tells me amount of rows and columns.
foggy aspen
@royal mason : one approach I'd suggest is looking into MongoDB's Unique Indexes feature: https://docs.mongodb.com/manual/core/index-unique/ ... depending on your data, it might allow you to just pull everything from the API each time, try inserting all of it, and then handle cases where the unique index rejects stuff that already exists
Also, not sure if you have a specific need for MongoDB as a datastore for the stuff you're getting... if it's relatively simple data (relational, row-based easycheese), then you might be able to lose the overhead of MongoDB by using something like SQLite (it has a UNIQUE constraint that can do the same thing as Unique Index)
mossy lotus
I wanna make an MySql column that combines the data of two other columns
Basically i have a table with firstname and lastname columns
I added a fullname column and i want it to be firstname+lastname
foggy aspen
you can google around about the SQL command "INSERT", then play around with inserting data into your fullname column
foggy aspen
lol... yup, that makes a lot more sense
agreed that it's definitely not the place for any unique/distinct database constraints 😉
torn sphinx
hey
rugged sluice
Oke; I have a question
import pyodbc
for driver in pyodbc.drivers():
print(driver)
Result:
SQL Server
SQL Server Native Client 11.0
SQL Server Native Client RDA 11.0
Oracle in OraDB18Home1
ODBC Driver 17 for SQL Server
ODBC Driver 13 for SQL Server
But there is no ODBC of MS acces
if i check @ my own windows
if i check used DSN: i see this
how can i use the acces ODBC ?
or install, but i dont know..used all version of micrisoft and installed
Solution: Oooh i see i can not 64 bits python with 32 bits
brazen charm
[SQL: INSERT INTO users ("charId", "userId") VALUES (?, ?)]
[parameters: (-2767700970244215861, 290923752475066368)]
(Background on this error at: http://sqlalche.me/e/gkpj)```can someone explain what the hell this error means
im fairly new to using alchemy
and this is confusing the crap out of me because i have no idea what is causing it
brazen charm
Fixed
formal escarp
?av
wooden prairie
lmao
glad bobcat
Ok sooooo I need some help, not really Python-related though, and I’m not finding much online
I’d like to read an existing MySQL (InnoDB) database that I created on Windows from a Linux instance having access to the InnoDB files.
Anybody has any clues on how to do that, and is it even doable? I just want to use the same config/data for another MySQL instance pretty much.
terse stump
not sure what you are asking here..
could you explain what you are trying to do @glad bobcat ?
glad bobcat
I have a computer with multiple OSs and I would like to be able to use the same MySQL database on both.
Feels like it should be pretty painless actually 🤔
terse stump
so just connect to the server from multiple machines
oh.. i see
you need a mysql server to host the data
i do not know of any way to read the database file on the fly
glad bobcat
Well MySQL by itself is only the software that then interacts with the files, right?
The server part is software, but I would like to plug this piece of software to data from another system
I’m just wondering how much of it is OS/config dependant
glacial sparrow
I everyone ! I have a very hard time, actually trying to get the data i need from my database, and i really don't know if the problem is my data structure, or if the query is just difficult to make, but i really struggle and have no idea at wich direction i should go to try to solve my problem.
TLDR : I have data in 4 tables and i need everytable to join another and get data from all the tables :/
If anyone is willing to help, would be so nice :p
copper echo
pls mention me if you know the answer
torn sphinx
mycursor.execute("CREATE DATABASE '363023ecgd4e6h42d5i9bf7j2adf72c4a7da'")```
`mysql.connector.errors.ProgrammingError: 1064 (42000): 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 ''363023ecgd4e6h42d5i9bf7j2adf72c4a7da'' at line 1`
What is wrong with that SQL?
crude stratus
Im trying to make a database in debian
and giving my own user all acces
but for some reason it doesnt work
its very anoying to read like this, but im doing this on VB as an assignment
NVM
STUPID MISTAKE
scenic pecan
@rain wagon wdym?
rain wagon
First of all, PDO can connect to 11 database backends. Second, it is safer
as well as object oriented
scenic pecan
to be honest i'm not familiar with php i did all this after watching youtube tutorial and some w3schools tutorials but i'm learning it fast
so you're saying PDO is a lot better than mysqli?
tall spoke
what's the difference between mysql and mariadb? and can i still use the same library to access mariadb
restive jasper
when i just input something like psql mydatabasename in ubuntu, what psql user do i log in as?
minor ruin
mariadb is fork of mysql, they attempt to maintain compatibility with MySQL so you can drop in MariaDB for mysql without any work on your end
slim zenith
Hi guys, i am thinking of databases for the past few days. i know about mysql but there are plenty of options out there, like azure and firebase. provided that i want to have a database on a mobile app which one would be better? i want to create a user profile, where most of the fields will be read-only to the users
sand stream
@slim zenith managed setup like firebase will be easier to setup/maintain, but more likely more costly
By firebase I assume you mean 'realtime database'
Can someone help me with a simple many-to-many relation?
Someone here directed me towards this setup: https://dbdiagram.io/d/5da934dd02e6e93440f25ae6
However, I'm not quite sure I understand it
Here's my scenario: I'm keeping track of 1000 video's views
I want to be able to track the views on those videos in arbitrary time intervals, and be able to tell when I made the measurement. Please ping me if you have any ideas.
slim zenith
By firebase I assume you mean 'realtime database'
@sand stream yes realtime. i want the user to be able to push data to the database and the server to push data/requests to the user
does it make any sense?
push like high score
from user to the server
and the server can push quests and stuff to the user
sand stream
I'm not experienced w/ firebase, but that should definately be possible with firebase
sand stream
MySQL is doable too, though, you wouldn't specifically need a MySQL database, almost any SQL database would do. But setting up/maintaining a MySQL server can be daunting/annoying.
torn sphinx
Mongo db
slim zenith
Mongo db
@torn sphinx thanks! i will give it a try tonight
sand stream
Can someone check my proposed database structure? I'm a noob when it comes to SQL
I'm keeping track of views of YT videos over time
cinder sierra
@sand stream i don't see why you have 3 tables repeating the same data essentially. just have one table?
if you're just keeping track of video views over time, you might as well just use json or mongodb instead
or even sqlite
sand stream
Because it's a little better than 1000 column table? That was my impression anyway. Somewhere here recommended it. This allows for views to be updated on a id-by-id basis. With a 1000 column table, to update on a id-by-id basis, you'd need to NULL 998 fields, which is inefficient I think. I'm still a SQL noob though.
cinder sierra
how many videos are you tracking
also the fact you say there's a chance of a 1000 column table means one of those tables is going to be 1000 columns anyways each referencing some video ID
which at that point, it's pointless for mysql. just use sqlite or even mongodb since you don't need the relationship
@sand stream
cinder sierra
and are you tracking their latest views or are you adding multiple rows of their views at different times
sand stream
latter
cinder sierra
just use mongodb
insert a document with the video ID and its views at insertion time
done
cinder sierra
yeah for sure. always keep it simple if your problem is simple
wild nest
good evening, I was wondering if anyone can help me install psycopg2 into terminal for mac. I need help installing it for Postgresql. I've tried pip install, sudo , and manually by directory but i can not successfully install it. If anyone could help I'd greatly appreciate it. Thank you.
sand stream
What's the error you are getting
wild nest
i get this error
ld: library not found for -lssl
clang: error: linker command failed with exit code 1 (use -v to see invocation)
error: command 'gcc' failed with exit status 1
----------------------------------------
ERROR: Failed building wheel for psycopg2
@wild nest Try the prebuilt binaries: pip install psycopg2-binary. Doesn't require compiling.
cinder sierra
if that doesn't work, just go to the app store and install "Xcode"
wild nest
when i install with pip i get this huge list of warnings then eventually comes to a runtime error
i have xcode downloaded
cinder sierra
try what he said then
wild nest
when i pip install psycopg2-binary it works
sand stream
Alright, your problem is fixed
wild nest
is gcc5 the latest version?
sand stream
It should be noted on the psycopg2-binary readme:
The binary package is a practical choice for development and testing but in production it is advised to use the package built from sources.
GCC 9 is the most recent by the looks of it.
wild nest
well im using it for classroom project, i have a class assginement where im using pycharm to call posgresql
okay thanks ill type that in
sand stream
Uhh, no offence, but I wouldn't classify that as 'production'
cinder sierra
yeah definitely not production
if it's just for class or a personal project that nobody ends up seeing, it's not gonna be production
wild nest
true, my bad
sand stream
Some of the reasons you'd want to compile it for production:
- You can install the latest version and can always stay updated, whether it be a security patch or a new feature.
- Allows you to trim down the features while installing so as to suit your needs.
- Similarly you can add some features which may not be provided in the binary.
All of these bring likely bring 0 benefit to the table for you
A class project likely isn't going to use a feature that's not built in by default. You get the point.
wild nest
yea totally understand
slim zenith
just a quick one. lets say i have a vps where i work on my project there and i need to edit files like index.html. can i somehow connect my vscode to that vps so i can have access to those files on the fly?
glacial sparrow
Hi, I repost my issue with actual data to get the context now.
Basically i struggle to create the query that wil get all the infos i want. And i dn't know if it's because i'm too bad to do the query, or if my Data structure is too complex and not optimized.
Basically for each line in the RELIC table, i get the USER.PSEUDO, and with a join at ERA and NAME is get all the values from RELICDATA table.
So i can get the c_1, c_2, c_3, etc. Each of these values contain a name of an item.
I can link this table to PRIMEPARTDATA with PRIMEPARTDATA.Name, to get the PRICEPLAT and PRICEDUCATS values.
I want to get these two values for c_1, c_2, c_3, u_1, u_2, r_1.
So the result of the final query would be :
Relic.Name, Relic.Era, User.Pseudo, Relic.Quantity, RelicData.c_1, PrimePartData.PricePlat as c_1_plat, PrimePartData.Ducats as c_2_plat,
ETCETCETC for each values in RelicData
And it's really complicated, should i try to write the query ? Should i re-orgnaise my DB for easier querying ? Should i just do multiple queries and regorganise everything in python in the backend since i'm using flask ?
toxic rune
I have the following simple schema: https://mystb.in/elupiditug.sql where Item has the non-translatable stuff whereas ItemTranslation has the translation for every locale. I was looking onto methods on how to assign "tags" to these items and stumbled upon this stackoverflow post: https://stackoverflow.com/questions/20219503/is-it-bad-design-to-use-arrays-within-a-database how can I apply the same method but with taking into account that an Item can have multiple tags and these tags can also be translated? I want to avoid arrays since I want to be able to search easily through Items with X tags.
I thought of having a table that has the item_id and a tag_id as a column with pk item_id, tag_id. But not sure how I'd do the rest of the tables / querying.
copper echo
how do i move one column data to one in other table? Postgresql
no one answered me before so im asking one more time
foggy aspen
@copper echo try using the INSERT sql command... have you found any google resources on INSERT that you have questions on?
copper echo
not really
dusty helm
guys, what is the difference between serial and bigserial?
toxic rune
JOIN (SELECT it_tl.item_id, it_tl.name FROM Game.ItemTranslation it_tl WHERE it_tl.locale_id = i_locale_id) ON it.item_id = it_tl.item_id
What's the difference if I move the WHERE outside the inside select to something like this?
JOIN (SELECT it_tl.item_id, it_tl.locale_id, it_tl.name FROM Game.ItemTranslation it_tl) it_tl2 ON it.item_id = it_tl2.item_id AND it_tl2.locale_id = i_locale_id
How to save files in a database?
torn sphinx
Anyone know MySQL here?
torn sphinx
.
coarse coral
you will have more joy posting your question, instead of waiting for someone to respond yes
buoyant breach
!ask
delicate fieldBOT
torn sphinx
I need to know why the difference in the output?
clear turret
Don’t you need to have the like statement formatted as ‘%A%’ to get the correct response?
trail rune
@torn sphinx This is simple what do you think '_' does??
The answer lies in the docs here: https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html
reef hawk
let's say I wanted to access a postgres database from a VPS, and use a discord bot in the VPS to run on it
I should make the postgres database in the VPS to be local and remotely call it from my personal pc right?
as the bot can call on the database faster if it is local to the VPS, and for my own testing purposes I can use it remotely to call on it
reef hawk
or should I use 2 separate databases?
daring cairn
Any suggestions?
cinder sierra
suggestions for?
daring cairn
Ah sorry I guess I forgot to delete that as well, I asked a question but then decided to take another route entirely, my bad 😅
mystic drift
Hey guys! The data in my mysql server looks fine, here's an example I’ve had a dog in past (last 5 years)
but when I query it from my python application, i get "Iâ€:tm:ve had a dog in past (last 5 years)\r"
How can I not have the carriage return and encoded characters?? any help greatly appreciated
icy shore
I have a question about the right DB for our python Responsive design application. we will have multiple agencies logging in so data compartmentalization is key as is security. we are looking at Azure with Python/Appache and Flask? and Mongo vs. MySQL. we will have 1000s of users but concurrency won't be that high.
any thoughts on the right DB? Azure seems to have all of them .
tender dagger
can anyone tell me why this does not return any rows?
SELECT * FROM 'guilds' WHERE 'id' = '516704678004785167' LIMIT 1;
to me it looks like it should?
id and board are text not null
signal sapphire
try trim()
foggy aspen
is field id is text, can you try searching with a LIKE statement, that catches text like your pattern? The specific implementation would depend on what you're using for your database backend
tender dagger
huh
removing the ' from SELECT * FROM 'guilds' WHERE id = '516704678004785167' LIMIT 1; fixed it
foggy aspen
d'oh! good catch, hehee
tender dagger
ill take it i guess even though i dont understand it at all
why does guilds work with ' but not id? who knows, i never will
signal sapphire
the datbase understand the 'id' like a string
not a field
so it was comparing the string 'id' with '516704678004785167' which is always false
signal sapphire
@mystic drift the character set of your database is different from the application, need to equalize both
hazy peak
Hey guys, has anyone here used elasticsearch?
copper echo
INSERT INTO rose_dev.guild_settings(guild_id, prefix) SELECT guild_id, prefix FROM rose.guild_settings; so im trying to copy data from database "rose" and from table "guild_settings" to another table called the same but in other database
but this is not working
maybe you know any way how can i do this?
craggy jackal
If it is in a separate database you can write a python script to make a connection to each and transfer data that way @copper echo
Unsure if SQL has that kind of cross-database support?
mint wolf
i'm looking for some help on the best process for inserting data into a postgres database that i'm interacting with via sqlalchemy. I can successfully insert data into a table that has no relationships, but when I try to break up the table into the desired structure, I'm having problems inserting a batch of data in a single step (maybe not possible?). If it's relevant, this is part of a scrapy project. Here's a portion of my models.py file:
class Upc(Base):
tablename = "upc"
id = Column(Integer, primary_key=True)
upc = Column('upc', Text(), unique=True, nullable=False)
asin = relationship("Asin", back_populates='upc')
class Asin(Base):
tablename = 'asin'
id = Column(Integer, primary_key=True)
asin = Column('asin', Text(), unique=True, nullable=False)
upc_id = Column(Integer, ForeignKey('upc.id'))
prod_title = Column('prod_title', Text())
product_url = Column('product_url', Text())
listing_price = Column('listing_price', Text())
datetime_scraped = Column('dt_scraped', DateTime)
upc = relationship("Upc", back_populates='asin')
and where I try to insert data:
def process_item(self, item, spider):
session = self.Session()
asin = Asin()
# upc = Upc()
asin.datetime_scraped = item["datetime_scraped"]
asin.upc = item["upc"]
# upc.upc = item["upc"]
asin.asin = item["asin"]
asin.prod_title = item["prod_title"]
asin.listing_price = item["listing_price"]
asin.avg_stars = item["avg_stars"]
asin.product_url = item["product_url"]
errant sable
whats psycopg2?
mint wolf
a tool for talking to postgres databases from python
terse olive
hello i am looking for some help with sqlite
i have a huge amount of json with a lot of repeated data in multiple files. I got it all into a sqlite database but I want to get rid of the duplicate data.
cars:[{
vin#:
type: {
name
weight class }
color:
manufacturer: {
name
country
logo }```
basically, imagine I had 1000 car documents and 50% are toyota and 50% are mazda, then i will have 500 repeats of the toyota information and 500 repeats of the mazda information
what I am trying to do is have relations so i only need to have 1 document for all that informationdelete
terse olive
😦
flat slate
I think this is the right place to ask this. ..... So, I have a situation where I need to: identify types of files being used (csv, json, xml ... ect) then, I have to place those in a sub category based on terms used within the file (probably just use regex and setup criteria for that no problem but time consuming). I think I can use python-magic to do a reliable job IDing file types, I'm not sure how to approach the second part with out manually coding out tests with lists of regex and such.
Also.... some of the files, like json and such use a reference sheet associated with ID's
so i suppose i could just convert and substitue values based on the reference sheet, though I'm not sure of a "pythonic" way to do that
this all stems from trying to pull data from a large number of very different sources and put them into one large data set in another database
cinder sierra
that seems very undescriptive and i can't tell what issue your having, only that you're trying to attempt something. do you mind explaining it in a shorter amount of words?
flat slate
looking for tools to identify(from a wide range of file types), parse, output to specific file type
large sets of data are coming from a number of different locations and they need to fit a specific mold to work with a specific program
void otter
usually people don't write whole parsers as a module or package, they usually just do the i/o bound
since every file type has a lot of stuff to parse in and out
flat slate
@void otter what do you mean "usually do the i/o bound" ?
flat slate
mhmm
void otter
that's a module that is input/output bound
it allows read and write and not much else
flat slate
gotcha
ok so what I would like to do is: figure out what the file types are (using python-magic probably) then just simply orginize them into folders, then I would automate using what ever tool is needed to read in each file type, probably store the data from each file in either one or multiple dictionaries, then write them into one specific file type like json or something
idk if someone has tackled something like this before
pull data from multiple databases with different formats, and load them into a single format in another database
hmm
trail rune
Hi I am researching what I need to build a web application.
Django can be used with a few different Databases.
Is there any one that stands out more than the others with Django or does it not matter which one is used?
I just need what ever is simple, efficient, can handle applications talking to it to use it for automation and easy for someone to come behind me and pick up.
flat slate
SQLite
with out having much more information about your intentions, SQLite is simple to use, quick to implement and easier for a beginner IMO
trail rune
We have about 1000 linux/unix servers
Tracked on an Excel Spreadsheet.
I started writing a Python script to process it to give me an Ansible Inentory file to make patching simpler.
I realised I could turn this into a web app and then have a UI.
The UI can then display to users when server was last patched, when its due next to be patched.
I can then query data to write invnetory files, run triggers, cron jobs to send emails to departments to say patching has completed/errored.
Patching happens about 1x a month on upto around 100 servers per run
uses can then log on make a request to change the time of patching (approved by my line manager also )
flat slate
ok well in that case I personally would not use Django
maybe flask
I still think sqlite would work fine
trail rune
Why flask? It's bare bones right?
Was thinking Django to deal with user authentication.
I haven't really written much but this is my opportunity so it doesn't bother me I am happy as long as I know it's gonna be useable going forwards. And I learn good skills
flat slate
bare bones, fast, easy to work with
you didn't say anything about user auth lol
Django is sort of like ... so much of it is premade you have to fit that specific mold when you try to implement anything with it
you can do user auth with flask though depends on how your current system is setup but it can work
this is reaching far past databases 😋
trail rune
I have no current system.
I am currently a trainee. This a wierd set up the company. Yes sorry my apologies. I guess it helps I have a tendency to drone on and bore people.
Tired to keep the initial query short and concise
flat slate
no worries I just don't want us to get in trouble lol
trail rune
Thank you for taking the time to answer. I have more than enough to make a start and read up on .
toxic rune
I made a SO post since it's too long to write here, It'd be helpful if someone can lend me a hand on how to approach this: https://stackoverflow.com/questions/59968866/how-to-represent-data-when-dealing-with-multiple-nested-joins-and-1m-m1-relati
pure cypress
@toxic rune what kind of format do you want
do you need it in JSON?
I came up with something really basic but not sure if it is adequate for your needs
SELECT
Character.name,
CharacterSkill.name,
SkillUpgradeItem.item_id,
SkillUpgradeItem.amount
FROM Character
JOIN CharacterSkill ON CharacterSkill.character = Character.id
JOIN SkillUpgradeItem ON SkillUpgradeItem.skill_id = CharacterSkill.id
ORDER BY Character.name, CharacterSkill.name, SkillUpgradeItem.item_id;
I think if you need JSON then the query will be relatively complex cause you'd need a subquery for each aggregate function.
toxic rune
@pure cypress Any format is fine, the issue is that without JSON I end up getting multiple rows (of course, the point of JOINs) the thing here is that lot of data from the first table will be duplicated (name, description), then the same will happen with the second table (CharacterSkill), etc, etc.
pure cypress
That's just a limitation of the table format