it's weird or i just dont grasp this sqlalchemy relationship pattern 
#databases
visual viper
btw thanks
lmao, when i changed the model's name to Treshold it worked
pseudo ruin
I learn the basics of mySQL, and have a grasp of OOP. Whats the steps to learning data science using python. Recommending courses?
twilit marlin
@pseudo ruin the pins in #data-science-and-ml have some selection of resources. You should also ask this question there, you'll get more informed answers.
novel moat
how can i highlight my values in a specific column in a dataframe? lets say i want to apply the condition: if df['price'] > 500 make it green and if df['price'] > 1000 orange
torn sphinx
what do you mean highlight your values.. where? @novel moat and I think this is more suited for #data-science-and-ml
novel moat
change the color in datafragme @torn sphinx
like if price is above 500, make the color red
novel moat
right now yes, but in jupyter notebook it should be able to display the color
or in an excel?
torn sphinx
df['price_x'].style.apply(lambda x: ["background: red" if val > 500 else "" for v in x], axis = 1)
I'm not sure of this, try it out.. but it should be something along the lines of this
novel moat
@torn sphinx AttributeError: 'Series' object has no attribute 'style'
why is it thinking its an series object?
torn sphinx
hmm yeah I think style is for the whole dataframe
series object because I selected only the price column
novel moat
ok i understand, is there something to apply it to just a column?
torn sphinx
data.style.applymap(lambda x: highlight_cols(x) if x>500 else x, subset=pd.IndexSlice[:, ['price_x'])
something like this
but, I haven't tested this yet, so try it out and fix it if need be
highlight_cols should be your function
novel moat
no error but also no color
mergedDf.style.applymap(lambda x: highlight_cols(x) if x > 500 else x, subset=pd.IndexSlice[:, ['price_x']])
i apply it like this right?
maybe anything wrong with my data?
torn sphinx
@novel moat did you make the function highlight_cols
@torn sphinx how are you normalizing
novel moat
yes
torn sphinx
show me
novel moat
def highlight_cols(s):
color = 'red'
return 'background-color: {}'.format(color)
was it this one, i have 5 highlighting function here atm xD
torn sphinx
well, nothing is happening in there, that's why you need to make your own function
try this @torn sphinx https://pypi.org/project/text-unidecode/
novel moat
well, nothing is happening in there, that's why you need to make your own function
@torn sphinx what do you mean? isnt it setting the color to red?
torn sphinx
no it's not, I can't write the function for you.. figure it out
in the apply I gave you, you're basically passing in values, so return values that are highlighted
I gave you two examples.. use them to understand and write your own
novel moat
mergedDf.style.applymap(lambda x: highlight_cols(x) if x > 500 else x, subset=pd.IndexSlice[:, ['price_x']])
so this is correct? and i need to adapt the highlight_cols function?
rich trout
@hasty juniper try removing the parens around the ?
rich trout
_ _
E Guild_ID = (?)
^ ^
hasty juniper
Thx
torn sphinx
I need some help
how do I put an ip in here
for asyncpg
I did ip=
but that didn’t work
proven arrow
@torn sphinx It's host= not ip https://magicstack.github.io/asyncpg/current/api/index.html#connection
proven arrow
Yes
dense steeple
Hi, anyone know how to check if the data/value is there in database and then return 'the value is already there' in pymongo?
rancid pine
Hi, anyone know how to check if the data/value is there in database and then return 'the value is already there' in pymongo?
@dense steeple when making database u can make said table have no duplicate entries so in python it would raise an error
if u tried to put an entry that was in table
already
kinda like how sets work
fast tulip
soo how the bot can create a .db file
fast tulip
a lib?(I'm a beginner sorry xD)
fast tulip
Thanks
torn sphinx
i am pretty new to databases and i was curious about what would be the best approach to store an order that contains a list of purchased items. I came across a few solutions including the usage of pickle but, the problem with that is it takes away the searching functionality (using sqlite btw) thanks in advance
minor venture
Tell me please. Should I 1. connect 2. insert data 3. disconnect or 1. connect 2. insert data when ever and not disconnect. So in short is connecting something i do once per time i restart my program or a lot whenever i have new data?
torn sphinx
should be once unless you close the connection and start it multiple times in the same session
minor venture
yes that is what i am asking do i ever need to close the connection ever?
torn sphinx
if you don't have to connect/disconnect multiple times then u should close the connection once when the app closes
minor venture
ok so a connection that is running 24/7 isnt a problem
torn sphinx
yes as long as you're not opening a connection to the same db from another file at same time and writing to it
quaint tiger
ok so a connection that is running 24/7 isnt a problem
@minor venture depends on a ton of factors. Sometimes your DB is configured to close idle connections after X seconds (oftentimes around 5 minutes).
Usually, you either connect every time, or use a connection pool.
But it really really depends on your stack.
earnest parcel
Do I need worry about converting strings like '1', '7'... to integers when using MAX?
round osprey
hey, I'm pretty new to python, and I'm creating a project, any simple databases you would recommend?
lime echo
Sqlite3 @round osprey
round osprey
thanks!
lime echo
np
brazen charm
comes pre installed aswell
lime echo
How do I create a table in Heroku?
white moth
I have a MySQL db in AWS RDS and I am using the MySQL client in terminal on MacOS. I already have a table created and I'm trying to upload a csv with 5gb of data into the table. Getting an error: ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
Curious if anyone has tips
lime echo
@brazen charm can you help me?
brazen charm
im just about to sleep im afraid
but do have a look at https://www.tutorialspoint.com/sqlite/sqlite_python.htm
lime echo
oh oki, have a good sleep!
not with Sqlite
How do I create a table in Heroku using by Postgre?
with Postgre
lime echo
yes
brazen charm
if you want a far more detailed set of options: https://www.postgresql.org/docs/9.1/sql-createtable.html
lime echo
I need to do it with Heroku because I can already do it in my Windows.
brazen charm
just either make a python script to execute the query or connect directly
heroku should have it exposed i think? idk
lime echo
@brazen charm I did it!
lime echo
I can't delete data from Postgre Unable to find a primary key for table "topic"
jovial notch
why?
earnest parcel
Execute doesn't return the value
You can cast warnings to a dict when you use fetch
jovial notch
i changed it.
still same output
so how would u do it like fetch and execute works the same for me
earnest parcel
I barely know how to use asyncpg but fetch returns a list of records
jovial notch
i changed it nothing changed maybe i need to put fetch and something else?
earnest parcel
So actually you could do [dict(warning) for warning in warnings] probably not the right way to do it though
But you don't need the values do you
Use await pool.fetchval('SELECT COUNT(stream) FROM revengewarnings WHERE stream = $1', stream)
Hoping $1 isn't just a Postgres thing... shouldn't be
It will return the number
earnest parcel
$1 is just a placeholder for the 1st arg... you should use f strings
jovial notch
ok 1 more question how can I delete everything from all the colums?
maybe have a command to delete everything
and have a command to delete like 1
2
3
earnest parcel
Just DELETE FROM table
I literally just learned this stuff 2 days ago so some of it might be off lol
jovial notch
if u mean this it didnt delete everything
earnest parcel
await pool.execute('DELETE FROM revengewarnings;')
shell ocean
DELETE FROM revengewarnings
earnest parcel
I usually like to do something like this
class Database:
def __init__(self):
self.ready = asyncio.Event()
async def create_pool(self):
self.pool = await asyncpg.create_pool(...)
self.ready.set()
class Bot(commands.Bot):
def __init__(self):
self.database = Database()
super().__init__(command_prefix='!')
self.loop.create_task(self.database.create_pool())
bot = Bot()
async def something():
await bot.database.ready.wait()
await bot.database.pool.execute...
bot.run(...)
Im working with a pretty large data set, 500 rows maybe like 10 columns. I am running forloops to create even more permutations, and all these permutations get saved as a new csv. The problem is it makes my computer crash after saving 2-3 CSV's. What Can I do to stop this from happening
twilit marlin
@torn sphinx what libraries are you using?
torn sphinx
pandas
and numpy
Basically each row is an item with different features (cars) and I am running forloops to show the different ways you can finance them
so each Vehicle produces a unique CSV with different financing options (5% down, 10% down etc) at different interest rates
But my computer just crashes once I get to a few completed items. I can run a few on their own
but once I load the large CSV it doesn't work
near arrow
I assume this is a channel for general questions? I can't figure out how to import module from another folder.
unreal slate
how can i automatically initialize database in flask for the first time of app run?
slender sentinel
tepid cradle
I assume this is a channel for general questions? I can't figure out how to import module from another folder.
@near arrow this is a channel for questions related to databases, as the name suggests.
@torn sphinx can you show some code and sample data? Preferably in a help channel, since the question isn't really about databases.
Ping me if you open a help channel
quaint tiger
can someone help me with this
@slender sentinel Could it be that your variables are the wrong type?roll.get()should beint(roll.get()), no? Same for phone.
Usually GUI libs get text entries as... text. Which works for varchar / string operations, but probably not for ints.
(Of course, you should handle Exceptions too, because users are often idiots)
torn sphinx
@tepid cradle are you around?
tepid cradle
yeah
torn sphinx
Whats the best way to send you csvs and a chunk of the code?
tepid cradle
!paste
delicate fieldBOT
torn sphinx
alright, Im in a meeting, but when Im done ill send it
tepid cradle
OK, sure
torn sphinx
Im running it on a seperate computer right now and it seems to be running just fine, Im trying to see how many full loops it can go through before crashing
28/300 so far, and yesterday it crashed at around 10 or so
tepid cradle
Pretty sure it can be optimized to run on any PC. 500 rows/loops doesn't seem like a lot.
keen plume
Hi , I have a issue regarding selecting previous weekly data in sql
twilit marlin
@keen plume https://dontasktoask.com/
torn sphinx
@tepid cradle Work is getting in the way
Ill see if I can get on tonight with some stuff
tepid cradle
We're probably in different time zones and I'll most likely be asleep in 2 hours. Post your question and ping me, I'll look at it tomorrow.
torn sphinx
sounds good, thank you for your assistance
soft sundial
Hi guys, i have the bellow query that's executed after clicling qtpy button, after the first correct query even with the same query and values, the cursor.fetchone() returns None
barren bronze
https://discordapp.com/channels/267624335836053506/342318764227821568/755266744892915712
@earnest parcel The Asyncio event object is deprecated since 3.8. Also can you explain shortly why its used in this context? Im interested in learning how to do asynchronous database connections.
tepid cradle
@soft sundial you don't need commit for select queries. It's needed only for queries which write to the database
earnest parcel
i think the loop parameter is deprecated, it simply lets you wait for something until the set method is called
barren bronze
I just checked. Yeah you are right.
Deprecated since version 3.8, will be removed in version 3.10: The loop parameter.
Is a really bad way to word that tho.
My bad.
earnest parcel
i agree
soft sundial
I removed the commit, but the result still the same @tepid cradle , also i've tryed to close the cursor
tepid cradle
Get a new cursor right before the query, so that every time you are executing a statement, you have a new cursor
torn sphinx
SELECT FROM profiles WHERE uid = '{member.id}';"
is there anything wrong with this statement? it says there is an error but idont see an issue
itis a mysql statement for clarification
harsh pulsar
@torn sphinx don't use F strings for sql
don't try to quote things manually
show your actual python code
also you need to SELECT * FROM or specify columns, not just SELECT FROM
torn sphinx
kk thanks for the help 🙇♂️
soft sundial
Yeah it's still happening, declaring cursor inside the function and before the query still result's on the same behavior.
harsh pulsar
@torn sphinx the correct way to inject data into a sql query is with "query parameters". consult your database library for the correct syntax and usage
@soft sundial are you using the same cursor for multiple queries?
soft sundial
I think my problem is happening because the binded function only happens once
harsh pulsar
you should only use the cursor 1 time
soft sundial
But how would i do, if this happens on a click event?
I tried removing the md4 filter, and the query worked with more than one click.
harsh pulsar
oh, that cursor usage is correct
maybe the md4(?) doesnt work properly. i'm not sure. can you try to run that query in a repl?
harsh pulsar
repl is the python console
but i didnt realize it was a custom function
i think you need to do h = MD4.new() inside md4Hash()
also why do you have except: raise Exception? this will just suppress all python exceptions and replace them with a non-specific Exception
def md4Hash(text: Union[bytes, bytearray, memoryview]) -> str:
h = MD4.new()
h.update(bytes(text, encoding='utf-8'))
return h.hexdigest()
try this instead
harsh pulsar
also your type annotation is incorrect
it should be text: str , no?
!e ```python
text = b"hello"
print(bytes(text, encoding="utf-8"))
@harsh pulsar :x: Your eval job has completed with return code 1.
001 | Traceback (most recent call last):
002 | File "<string>", line 2, in <module>
003 | TypeError: encoding without a string argument
right now i have 4 tables being order, order_details, category and product. product is linked to category and order_details is linked to order. here's what i have rn
CREATE TABLE order (
id INTEGER PRIMARY KEY autoincrement);
CREATE TABLE order_details (
id INTEGER PRIMARY KEY autoincrement,
order_id INTEGER NOT NULL,
order_date TIMESTAMP NOT NULL,
FOREIGN KEY(order_id) REFERENCES order(id));
CREATE TABLE category (
id INTEGER PRIMARY KEY autoincrement,
name TEXT NOT NULL);
CREATE TABLE product (
id INTEGER PRIMARY KEY autoincrement,
category_id INTEGER NOT NULL,
name TEXT NOT NULL,
price REAL NOT NULL,
FOREIGN KEY(category_id) REFERENCES category(id));
what i am trying to figure out is how to make a new order and add products to its order_details, assuming that I already have categories and products added to their corresponding tables (if there is anything wrong with my setup please do mention it) thanks in advance.
barren bronze
Curious why the md4 function is put in the sql? why not just have it on the variable itself?
soft sundial
the md4 Crypto update needs to be a byte array
harsh pulsar
@soft sundial but the function accepts text. bytes(text, encoding="utf-8") only works for strings. therefore your md4Hash function accepts only strings, even though the internal method accepts only bytes
@torn sphinx what about something like this?
CREATE TABLE order (
id INTEGER PRIMARY KEY autoincrement,
order_date TIMESTAMP NOT NULL);
CREATE TABLE order_details (
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
PRIMARY KEY(order_id, product_id),
FOREIGN KEY(order_id) REFERENCES order(id),
FOREIGN KEY(product_id) REFERENCES product(id));
you should also be careful that when you "update" a product you need to give the updated version with a new id, and keep the old version intact
or some other method for "soft deletion" - that makes sure that you can look up past orders with the products that existed at the time, not whatever they happen to currently be
soft sundial
@soft sundial but the function accepts text.
bytes(text, encoding="utf-8")only works for strings. therefore yourmd4Hashfunction accepts only strings, even though the internal method accepts only bytes
@harsh pulsar Make's sense.
harsh pulsar
and i am curious too, why do you need this in sql itself
barren bronze
@harsh pulsar That order_details would only be able to take one product per order. What if i wanted to buy 2 hamburgers?
soft sundial
Since the insertion of sensite data and retriving it again, need's to be encrypted, i chosed to only work that way, actually is what the university purposed
harsh pulsar
@barren bronze good point, either add a quantity field or add a separate primary key to enable duplicate order_id,product_id pairs
not sure if there are better patterns here
barren bronze
quantity seems easiest but it would be a mess to expand in the future. like an extra fields to have no tomato but thats more on what the actual database holds
harsh pulsar
thats a good point
CREATE TABLE order (
id INTEGER PRIMARY KEY autoincrement,
order_date TIMESTAMP NOT NULL);
CREATE TABLE order_details (
id INTEGER PRIMARY KEY autoincrement,
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
FOREIGN KEY(order_id) REFERENCES order(id),
FOREIGN KEY(product_id) REFERENCES product(id));
this makes sense
it might make it easier to update the quantity too
barren bronze
Yeh
harsh pulsar
im not sure how you'd reduce the quantity though
it makes concurrent additions ok but deletes are still a problem
barren bronze
DROP TABLE order_details obviously.
harsh pulsar
barren bronze
Couldnt you just delete the entry by the order_details primary key?
Or is there some SQL nonos around doing that?
harsh pulsar
yeah you could i guess
torn sphinx
what do you guys suggest i do? since adding multiple products to the same order is trivial to what i am doing
harsh pulsar
i'd go with the option sofi and i are discussing (see my most recent code snippet)
it seems like a good balance of extensibility and normalization
barren bronze
Also, you are asking us to implement your code for you.
And honestly i am not sure how to implement it in just sql. As my practical sql skills are a bit lacking.
harsh pulsar
i didnt get the impression they were asking for an implementation
barren bronze
what i am trying to figure out is how to make a new order and add products to its order_details, assuming that I already have categories and products added to their corresponding tables
This part.
Shouldnt it just be a
INSERT INTO order_details (
order_id,
product_id
)
VALUES
(
15,
21
),
(
15,
24
);
mine are lacking as well, i was just wondering how i could go on about inserting multiple products to the order. is it possible to insert into order_details using the same order_id but with a different product_id?
yup exactly what i had in mind
i will test it rn, thank you
barren bronze
I am interested now in how i can add creating the order_id into the same SQL code. And if that is a good idea to do... 🤔
young seal
Question on using pyodbc - do I need to roll back my connection if I do not commit similar to how i would with cx_oracle except im using a tsql server
torn sphinx
Hello people
I have a small logic problem that is related to but not completely dependent on an understanding of python
I am trying to understand branching and looping data
What I mean by this is not branching and looping in programming
For example, let's say we have a variable a that is basically a) a value and b) a pointer to another array with that value
so a has the values (and pointers to the values) 1, Z, and A
1, Z, and A may also have the values for a
therefor, to not have an infinite loop and computer overload you should stop the branching process there and tether the two pointers so they are the same
That's what I mean by looping
I have absolutely no clue how I would go about managing this data
my goal is to work with large databases like dictionaries, for which the definitions of words are words with definitions
fringe crater
("UPDATE PROFILE SET HP = (SELECT WEAPONS.WEAPON_HP FROM WEAPONS WHERE WEAPONS.WEAPON_NAME = PROFILE.WEAPON) WHERE EXISTS (SELECT * FROM WEAPONS WHERE WEAPONS.WEAPON_NAME = PROFILE.WEAPON)")
how do i run this in python?
it doesnt work if i use profile_db.execute() because it cant find the weapons_db
barren bronze
Sounds like an issue about the sql client setup rather than the SQL code.
torn sphinx
hey, so i'm using aiosqlite with python 3.8.2 and i'm trying to query my sqlite db to match all of the criteria, most of my code is working.
However, when i try to query the db it doesn't work, i've tried looking across the web to find an answer an watched a few videos yet to no avail, here is the code:
if chat:
print("success18")
time = chat.group(1)
psn = chat.group(2)
ign = chat.group(3)
msg = chat.group(4)
print("success19")
cursor = session.cursor()
print("success20")
await cursor.execute("""SELECT time FROM chat WHERE time=?""", (time,))
print("success21")
result = cursor.fetchall()
print("success22")
if result is None:
print("success23")
await cursor.execute("""INSERT INTO chat VALUES (?, ?, ?, ?)""", (time, psn, ign, msg))
print("success24")
session.commit()
print("success25")
session.close()
print("success22")
more specifically, this is the line of code that is not working how i expected it to: await cursor.execute("""SELECT time FROM chat WHERE time=?""", (time,))
what i was expecting was for it to match the time found in the chat database with the time in my regex pattern, can anyone see the problem?
pure cypress
fetchall() never returns None afaik. It would return an empty list.
torn sphinx
it's not returning none, (yet afaik) it's the cursor execution that's off, i'm trying to get it to match time = chat.group(1) in my db using await cursor.execute("""SELECT time FROM chat WHERE time=?""", (time,)) but it doesn't get past this so this must be wrong
torn sphinx
nope, it just stops my code there
i would know if it was carrying on through the code to fetchall() from my console saying success22 but it doesn't so it must be held up by that line of code
pure cypress
Which library you using? It's not sqlite3 obviously because it's async
torn sphinx
aiosqlite
torn sphinx
i'll try now
dusky siren
Idk if this is the right channel for this but it is database related so:
I am trying to implement a trueskill system for a game using:
https://trueskill.org/
My question is, with SQLite3, how would I store a user's data in a database table, and how would I use it in my code to rank players when two players fight?
If there is a better place to ask, let me know.
I haven't made an attempt yet, I usually figure out basic concepts, then implement them myself
twilit marlin
woeful tinsel
what would be the reason for me getting this error when i'm trying to create a connection pool with asyncpg?
OSError: Multiple exceptions: [Errno 61] Connect call failed ('::1', 5432, 0, 0), [Errno 61] Connect call failed ('127.0.0.1', 5432)
quaint tiger
what would be the reason for me getting this error when i'm trying to create a connection pool with asyncpg?
OSError: Multiple exceptions: [Errno 61] Connect call failed ('::1', 5432, 0, 0), [Errno 61] Connect call failed ('127.0.0.1', 5432)
@woeful tinselOSError61 means connection refused... so postgres not listening on that port?
woeful tinsel
oh i already gave up sry lol
rugged root
bruh
rugged root
With PostgreSQL and the python3 module psycopg2, I am getting OperationalError: FATAL: password authentication failed for user
I am 100% sure it is the correct user and password.
Relevant code:
conn = psycopg2.connect(
host='localhost',
database='postgres',
user=os.getenv('DB_USER'),
password=os.getenv('DB_PASS')
)
I have also tried replacing the environment variables as strings but no luck there.
I don't get this error trying to connect to the database through ssh which is how I know it's the correct user/pass
Yes, I have printed DB_USER and DB_PASS and they are what I expect
Ping me please
languid sigil
tepid cradle
Before this, try doing print(os.getenv('DB_USER'), os.getenv('DB_PASS')) just to make sure it is getting the correct values @rugged root
If you haven't tried that already, that is
jovial notch
AttributeError: 'int' object has no attribute 'fetchval'
How do I fix that? I can't type numbers only letters....
quaint tiger
bot.table = await bot.table.fetchval(query, steam)
Sounds like you are assigning an int to bot.table so try doing.... not that 🙂
Use another variable or something.
I don't know discord.py much though
jovial notch
@quaint tiger so how else would I define my argument
quaint tiger
maybe warnings = await bot.table.fetchval(query, steam)
quaint tiger
And then use warnings instead of bot.table in the following line
Sounds like it.
bot.table.fetchval ... 'int' object has no attribute 'fetchval'
If await bot.table.fetchval(query, steam) returns an int....
and you assign it to bot.table, you are probably overriding whatever bot.table is supposed to be?
Maybe try asking in #discord-bots though.... as I said, i am not really familiar with it
jovial notch
ok
torn sphinx
hi, i need to insert 1000 records into database every hour. Mysql database. What is best way to do this? for loop?
Query is same, but just have different values
rugged root
I figured it out
hidden nest
Hey everybody. I hope that I am in the right channel on this discord 🙂 I got a question concerning my DB.
I am working on a project where I got a joined table that has two columns (product_id and category_id). This table is there to link my products to their respective categories.
I have serval products that are of course in more than one category.
So I am trying do a query that gives back the categories containing most of the products with a limit of 30 rows.
I stared to do some testing and ended up with this, but it’s not complete.
‘’’
SELECT product_id, count(product_id) as num_products FROM product_category GROUP BY product_id ORDER BY num_products DESC LIMIT
‘’’
I know that I am not putting my second column in this query. But everytime I do the count goes to one.
Any tips? 🙂
Sorry maybe it’s a very basic question, just trying to learn by doing research etc.
tepid cradle
@rugged root what was the issue?
@hidden nest so you basically have 3 tables, one for products, one for categories, and one to just map products to categories.
Am I right?
hidden nest
@tepid cradle Yep that's it. And right now I am working on the table that maps my product_id to my category_id 🙂 basically my joined table looks like this (tell me if it's forbidden to post a screen shot 🙂 ) :
tepid cradle
No harm in posting screenshots, although, text is always appreciated
So you want categories with most number of products?
hidden nest
that's exactly it ! I will limit it to 30 rows because the are thousands of products. Basically I am creating a script for a menu and the query will just get the 30 most popular categories.
tepid cradle
I think this should work
select * from (
Select category, count(*) from prodcut_category_map
) t1 order by count desc limit 30
@hidden nest Let me know if that works for you
hidden nest
@tepid cradle nope not really replaced it with the right names but telling me that "unable to resolve column count'. As text is more efficient here is the full table nicely done 🙂 :
CREATE TABLE product_category
(
product_id INTEGER NOT NULL
CONSTRAINT product_category_product_id_fk
REFERENCES product,
category_id INTEGER NOT NULL
CONSTRAINT product_category_category_id_fk
REFERENCES category,
CONSTRAINT product_category_pk
PRIMARY KEY (product_id, category_id)
);
What I did to you query was :
select * from (Select category_id, count(*) from product_category) t1 order by count desc limit 30
amount = database_db.execute("SELECT GUN FROM PROFILE WHERE USER_ID = ?",(user_id,)).fetchone()[0]
how do i replace GUN with a value
tried ```py
amount = database_db.execute("SELECT ? FROM PROFILE WHERE USER_ID = ?",(item,user_id,)).fetchone()[0]
I think this should work
select * from (
Select category, count(*) as count from prodcut_category_map
) t1 order by count desc limit 30
@hidden nest
hidden nest
@tepid cradle almost ^^'.
[42803] ERROR: column "product_category.category_id" must appear in the GROUP BY clause or be used in an aggregate function Position : 23
Oh btw I haven't mentioned I am using Postgresql 🙂
tepid cradle
Oh, I forgot to add group by. Just add group by category inside the brackets
hidden nest
@tepid cradle thx very much for your help 🙂
warped valley
hello, im pretty new at using databases, what would be the ideal solution for storing huge strings, or documents like epub? pls be gentle
hidden nest
@warped valley ideal solution do you mean how to do it or what to use?
warped valley
@hidden nest what to use. where to start.
tepid cradle
tried ```py
amount = database_db.execute("SELECT ? FROM PROFILE WHERE USER_ID = ?",(item,user_id,)).fetchone()[0]
@Deluzi 🇻🇳#7416 if you absolutely have to do it, you'll have to use f-strings.
Best is to just define multiple queries and use the relevant one.
tepid cradle
@warped valley You can use a text field in a relational database
lilac axle
yea i found it thanks to @lilac axle
@fringe crater 😊
tepid cradle
If it's epub with images, I'd recommend simply storing the file in a storage solution (like Amazon S3) and storing a pointer to it in the database
warped valley
I'll try that out, thank you
noble dagger
hey guys id appreciate some help
I have two tables, called fixtures and bets. bets has a foreign key fixtureid from fixtures, and i want it to update the bets table when data is updated in fixtures table
as in the table fixtures has a column called result, when that is updated, i want to update the bets table by comparing that result with data on bets and if its the same, i want to update successful in bets to 1
noble dagger
CREATE TRIGGER update_bets AFTER UPDATE ON fixtures
FOR EACH ROW
UPDATE bets
SET successful = CASE
WHEN bets.bet_on = fixtures.result THEN 1
ELSE 0
END
WHERE bets.fixture_id = fixtures.fixture_id;
but this doesnt work
craggy girder
hey guys can someone brief me about what databases are and whats sqlite
tepid cradle
Google?
craggy girder
i didnt quite get it from google
like is database just an organized collection of data?
how does that relate to programming
and i couldnt find the clear answer for sqlite at all
south cobalt
part of my python script is creating a database however whenever i run the script on my work computer I am told I do not have the SA permissions to create a database. makes sense i am not an admin. but neither are all the people who will be running this script. is there anyway to write an admin login into the script so that the program has the permissions it needs to run
harsh thistle
hi i need a help
i am connecting python and SQL
and it shows this error
sorry if its too small
but can anyone help me fix this?
i tried on google, but couldn't find any relevant thing
ping me pls if anyone answers
harsh pulsar
@harsh thistle it's better if you post your error messages as text, not as an image
proven arrow
Also make sure your database is running on your computer @harsh thistle
torn sphinx
File "C:\Users\suhas\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\bot.py", line 903, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\suhas\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 855, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\suhas\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: UnboundLocalError: local variable 'channel1' referenced before assignment
this is the link please help me
proven arrow
This is not a #databases problem @torn sphinx . But your issue is that your assigning the value of channel1 inside the if statement.
However, your if statement is only executed if result is None:. So if this statement is not True then channel1 never exists in your code, since the code will never reach inside that statement.
torn sphinx
@proven arrow can you help me with syntax
proven arrow
Its not a syntax issue. Its a logic issue.
if result is None: means if the value of the variable result is None, then the statement is True
torn sphinx
i got a kind of issue how do i fix it
delicate fieldBOT
Hey @torn sphinx!
torn sphinx
https://paste.pythondiscord.com/tuseyiriyo.sql getting this issue
quaint tiger
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: t_user.contacts
Try querying a column that exists 🙂
torn sphinx
the point is i don't know how to do that @quaint tiger
i downloaded a source code from gitlab
quaint tiger
I don't know either from a traceback lol. You'd have to share the code.
nocturne basin
What is the best way to transfer NULL values to a PostgreSQL server?
harsh pulsar
@nocturne basin None in python will be converted to NULL automatically
nocturne basin
@harsh pulsar , the issue is when trying to parse the data into SQL columns. For example, Null doesn't work for my datetype column
I could just edit the data and "hack" it, such as 00-00-00 for datetime = Null
but wondering if there is a proper way to carry over the Null type data, because even if the data is null that still signifies something big in my dataset
harsh pulsar
@nocturne basin does your schema say NOT NULL?
because normally you can put NULL into any column, in most databases
and you definitely can in postgres
nocturne basin
nextDividendDate = Column('next_dividend_date', Date, nullable=True)
I have nullable = True
if thats what you meant
This is the error im getting when trying to transfer data from pandas to postgres
Error: invalid input syntax for type date: "" CONTEXT: COPY security_stats, line 1, column next_dividend_date: ""
my copy_from method reads nulls as an empty cell.
nocturne basin
Figured out how to fix: https://www.psycopg.org/docs/cursor.html
just change null parameter when using copy_from()
harsh pulsar
@nocturne basin no, "" is not the same as None in python
"" in python translates to '' in SQL (empty string)
None in python translates to NULL in SQL (null value)
they aren't the same
the error message states that the empty string is not a valid timestamp
ah, i see what you are doing
in that case yes, you did the right thing
null=''
rugged root
PSQL with psycopg2 module:
Getting a syntax error where I set async to 1 for some reason. The whole code block:
import select
import psycopg2
import psycopg2.extensions
import os
import asyncio
from dotenv import load_dotenv
#Connect to Database
def wait(conn):
while 1:
state = conn.poll()
if state == psycopg2.extensions.POLL_OK:
break
elif state == psycopg2.extensions.POLL_WRITE:
select.select([], [conn.fileno()], [])
elif state == psycopg2.extensions.POLL_READ:
select.select([conn.fileno()], [], [])
else:
raise psycopg2.OperationalError("poll() returned %s" % state)
aconn = psycopg2.connect(
host=os.getenv('DB_HOST'),
database='bot',
user=os.getenv('DB_USER'),
password=os.getenv('DB_PASS'),
async=1)
wait(aconn)
cur = aconn.cursor()
@rugged root async is a reserved word in python, so the parameter name must be something different. i didnt know psycopg2 even supported async
rugged root
odd, I followed their docs exactly
abstract berry
@rugged root
use async_ instead
rugged root
👍 thank you
abstract berry
works? if not try using python version < 3.7
rugged root
Yes I am no longer getting syntax errors from it
harsh pulsar
sounds like their docs are out of date then
keen gorge
mydb.execute("DELETE FROM voice WHERE gid=%s", (ctx.guild.id,))```
why doesnt it delete from voice?the gid does exists
harsh pulsar
do you need to commit first?
keen gorge
wait
@voice.command()
async def setup(self, ctx):
if ctx.message.author.guild_permissions.manage_channels:
temp=sql.c()
mydb=temp[1]
myuser=temp[0]
try:
category = await ctx.guild.create_category("Custom Voice System")
channel = await category.create_voice_channel("Erstelle einen Kanal")
except:
embed = discord.embed(color=info.red, description="Ich konnte keinen Kanal erstellen. Bitte überprüfe meine Berechtigungen und schalte `Kanäle verwalten` ein.", timestamp=datetime.utcnow())
embed.set_author(name=ctx.author, icon_url=ctx.author.avatar_url)
return await ctx.send(embed=embed)
mydb.execute("DELETE FROM voice WHERE gid=%s", (ctx.guild.id,))
myuser.commit()
mydb.execute("INSERT INTO voice VALUE (%s, %s, %s)", (ctx.guild.id, 0, channel.id,))
embed = discord.Embed(color=info.blue, description="Das Custom Voice System wurde erfolgreich erstellt.", timestamp=datetime.utcnow())
embed.set_author(name=ctx.author, icon_url=ctx.author.avatar_url)
await ctx.send(embed=embed)
myuser.commit()
myuser.close()
else:
embed = discord.Embed(color=info.blue, description="Du hast dazu nicht die Berechtigung.", timestamp=datetime.utcnow())
embed.set_author(name=ctx.author, icon_url=ctx.author.avatar_url)
await ctx.send(embed=embed)```no I dont think so
oh Im so lost
I found the error
torn sphinx
I really need help
i am creating a MSN server (windows live messenger) what doesnt work is the registration on the website here is the source code of it https://gitlab.com/escargot-chat/site
this is the error code i get when i try to start the server https://hastebin.com/gijitacema.sql
limber trail
Hey Folks, how do you apply the code on a jupyter notebook to many (100+) csv files.?
The output of the notebook is a single line df.
I was wanting to capture the output for all files in a master file.
Do you use the I/O tools for this or what is the appropriate package?
Thanks
scenic nova
can someone help me convert python script to exe file
rancid pine
gosh stick to 1 channel not all
south cobalt
@scenic nova i use pyinstaller
scenic nova
yes
south cobalt
it "should" catch it then but it can be a pain
do you have the command you are using
or do you use a .sepc file
scenic nova
also i need the console at the beginning
for an input
which then leads to the main program
south cobalt
if you need a terminal you shouldnt be using --onefile
scenic nova
wdym
south cobalt
so i have a file called like somename.spec
then i simply run the command pyinstaller somename.spec
scenic nova
oh
south cobalt
and i has all the settings baked into that file
south cobalt
if you want i can post my .spec file for you to base your stuff off of
!paste
in my example my python script is called main.py
if your script is quite simple a .spec file may be overkill
south cobalt
ah sorry
well with that solved may i ask a database question lol
i have the same issue as this person but my user will not be logging in as the administrator
i need to find a way with SQL Server to grant anyone the create database permission
rancid pine
ah sorry
@south cobalt not ur fault they had the patience to post in 30 channels
south cobalt
lol ah well i usually ask for help more then give it but pyinstaller was actually something i use lol
either way if anyone has some experience with SQL Server from MS would be appreciated
minor ruin
Give them sysadmin role?
I mean since you are chucking proper security out the window
south cobalt
yeah i know but its a pretty controlled enviorment and its just students
minor ruin
Oh yea, just students
south cobalt
they will just be opening a .exe for class
minor ruin
Let them blow up a table or two?
south cobalt
there should only be one database in there nothing on that virtual machine uses sql
minor ruin
Sysadmin role will grant create Database right along with a ton others
I’m sure roles are documented somewhere
south cobalt
can i grant only create database?
yeah i think its here but i dont really get what the page is saying
south cobalt
ah that seems good but i dont really get how the script can grant someone that role if the person running the script isnt a sysadmin
minor ruin
Mrpolymath whay
Create the users or use AD authentication
Create the student logins, grant them dbcreator role, let them create a database
Or just share a login and enjoy chaos
You need a login to grant rights
south cobalt
unfortunately I am just a student myself and not a comp sci student so i have a small understanding of how the school grants "roles" to students in their network
minor ruin
SQL server maintains its own authentication
south cobalt
when they loginto windows with their student idea thats a "login" am i able to use that
minor ruin
Either tied into AD or separate internal database
south cobalt
that "sounds" ideal
minor ruin
So grant students dbcreator
south cobalt
so there should be a way to tell if a log on is a student?
i myself don't have and admin perms so i am kinda of testing this blind on my local machine
minor ruin
It supports AD groups
south cobalt
ok i need to google ad groups then
minor ruin
Active directory groups
south cobalt
looking at a stack exchange on it now 😛
rugged root
PSQL psycopg2:
I have a list of values. I'm trying to check all the rows in a single column to see if each of the values are in that list. if they are not in the list, the row is to be removed from the database. can anyone point me in the right direction?
tepid cradle
PSQL psycopg2:
I have a list of values. I'm trying to check all the rows in a single column to see if each of the values are in that list. if they are not in the list, the row is to be removed from the database. can anyone point me in the right direction?
@rugged root I'm very confused
Where is the list? Which rows are you checking and which row needs to be removed?
rugged root
Just a list I have created full of ID's. I am checking every primary key in the database. If the primary key is not in the list I created, I want to delete the row
harsh pulsar
@jovial notch instead of COUNT(steam) you'll need to actually select and format the data
now you'll have several reasons returned from the query
and you'll have to figure out how to display that to the user
tepid cradle
Just a list I have created full of ID's. I am checking every primary key in the database. If the primary key is not in the list I created, I want to delete the row
@rugged root
delete from table where id not in (1, 2, 3, 4)
rugged root
awesome thank you.
had about 6 lines of code that was me trying to convert all the rows into a list so I could compare the two lists (and some more complicated nonsense), which wasn't working and was very messy.
so yeah this helped a lot, i should do more research on database stuff
jovial notch
@harsh pulsar u get what im trying to do yeah?
for every result i want the reason and staff too.
not only steam
south cobalt
@minor ruin TY i found a AD group for all undergraduate chemical engineering students
i dont have the permissions to give them dbcreator but i can get someone to do that
tepid cradle
for every result i want the reason and staff too.
@jovial notch you're using a count query, which means one count will have multiple reasons. So how do you want them? Do you want them as an array?
jovial notch
@tepid cradle i just want the count of warnings where steam = $1 and then i want embed.fields for the reasons and staff
u get me?
tepid cradle
No. Count(*) returns a single number for that ID. I understand that you want the reason, but which one? Because if you use aggregation on one column, then you can't return all values from another column
For Killer, there are two reasons, RDM, and VDM. Count will return one number, i.e., 2. So which reason should it return?
tepid cradle
You can't do that in the database. Get the data in python and summarise there
As Salt Rock Lamp said, use a select query to get all the rows.
jovial notch
query2 = f"""SELECT * FROM revengewarnings WHERE steam = $1"""
so my second query would be this right?
@tepid cradle
tepid cradle
You can just do
cur.execute("select * from warnings where id=$1")
result = cur.fetchall()
count = len(result)
yes but i dont want the count of anything or length
i just want the character output
tepid cradle
That's what I meant when I said do the summarization in Python
You'll get the same thing which you'll get with the count query
It's just the number of rows you're counting, whether in SQL or in Python
jovial notch
query2 = """SELECT * FROM revengewarnings WHERE steam = $1""" reason = await bot.db.fetchval(query2, steam) im really confused so i have this right now yeah? what i do next @tepid cradle
tepid cradle
Execute this query and look at the result first
jovial notch
@tepid cradle the result is just Killer idk why
tepid cradle
You're looking at steam, look at reason
jovial notch
@tepid cradle yes but i want to get the reason from the steam i define not any reason
tepid cradle
After running the query, look at the variable named reason. That's what you're assigning the result of the database query to
high geyser
c.execute("""SELECT keyword
FROM tags
WHERE guildid = ?""", (guildid,))```this is one select statement/query
```py
c.execute("""SELECT alias_name
FROM alias_storage
WHERE guildid = ?""", (ctx.guild.id,))``` this is another one. How can I select both the results in one query?pls ping me when help
pseudo cove
union select then
high geyser
thanks
c.execute("""SELECT keyword
FROM tags
WHERE guildid = ?
UNION
SELECT alias_name
FROM alias_storage
WHERE guildid = ?""", (guildid,guildid))```I did this
but in the tuple
both the elements are same
am I writing the elments in the right way?
or is there other way to write if the elements are same
???????????
delicate fieldBOT
pseudo cove
@high geyser
high geyser
thanks
@pseudo cove
but I have a doubt
c.execute("""SELECT keyword
FROM tags
WHERE guildid = theguildid
UNION
SELECT alias_name
FROM alias_storage
WHERE guildid = theguildid""", {"theguildid": ctx.guild.id})
keywords_found = c.fetchall()```colons needed
high geyser
?
pseudo cove
before the placeholders
high geyser
oh thanks
tepid cradle
@high geyser since both the tables have guildid, you can do an inner join as well. Union requires both results to have same structure, join doesn't.
query = """SELECT keyword, alias_name
FROM tags tg
Inner join alias_storage as on as.guildid = tg.guildid
WHERE tg.guildid = ?
This would work better.
tg and as are table aliases I have created, you an use anything in their places
high geyser
thanks
rugged root
PSQL psycopg2:
is psycopg2 recommended to use if I want asynchronous code? or should I use a different module?
Also, I have tried various different combinations to try to stop getting the TypeError: not all arguments converted during string formatting error with psycopg2 and I've had no luck. I tried:
sql = "DELETE FROM guilds WHERE id not in %s;"
val = (ids)
cur.execute(sql, val)
similar to this, I also tried making the value a tuple which also didnt work (although in the psycopg2 docs, it says I can use a list for what i'm trying to do.) When trying a tuple it gave me psycopg2.errors.SyntaxError: syntax error at or near "ARRAY" LINE 1: DELETE FROM guilds WHERE id not in ARRAY[577001038373191705,...
I also tried:
cur.execute("DELETE FROM guilds WHERE id not in %s;", (ids))
wait(cur.connection)
and different variations of this, which also didnt work. I printed ids and it IS a list. so yeah, been pretty confused
harsh pulsar
@rugged root the problem is that (ids) is not a length 1 tuple, use (ids,) instead
rugged root
okie dokie
harsh pulsar
Try = ANY(%s) instead of IN %s
rugged root
yeah I tried that as well, I'll try it again just to double check I wasn't messing something else up.
gimme a sec
harsh pulsar
Hmm maybe %s || id
harsh pulsar
What quotes? It's part of the query
harsh pulsar
what error
rugged root
TypeError: not all arguments converted during string formatting
harsh pulsar
you arent using the tuple
cur.execute("DELETE FROM guilds WHERE id != ALL(%s)", (ids,))
good, what's the errror now?
rugged root
wait
I haven't tried that
what you just semt
sent*
yep that was it
awesome possum
so I tried a bunch of different combinations with tuples, string. I never tried ALL though
harsh pulsar
yeah. != ALL is the opposite of = ANY
the reason you get the "not all parameters" error is that ids itself is a list
so it thinks ids is the parameter list
rugged root
not equal to ALL of the values
yeah
so checks each value
thank you
@harsh pulsar I'm curious, if you don't mind me asking. Why sometimes the correct code is:
cur.execute("DELETE FROM guilds WHERE id != ALL(%s)", (ids,))
and sometimes it's
sql = "DELETE FROM guilds WHERE id != ALL(%s)"
val = (ids,)
cur.execute(sql, val)
runic badger
those are both correct
its just variables instead of passing the values directly
i do see a problem though
why are you using %s. use ? instead. it should work the same
im not sure but it looks like a potential sql injection vulnerability especcialy if the string formatting is different
harsh pulsar
@runic badger this isnt string interpolation. psycopg2 uses %s as its placeholder
harsh pulsar
there are 4 valid parameter placeholder styles according to the db-api spec: ?, %s, $1, and :name
!pep 249
delicate fieldBOT
runic badger
if the library supports it natevly then nvm
harsh pulsar
yep
that said, it can be quite messy because you can accidentally interpolate things that you didnt mean to interpolate
runic badger
i would still probably use ? cause its 1 less keystroke, more readable at a glance, and for sqlite3 compatibility
harsh pulsar
so there are good arguments to avoid using it for future libraries... whereas on the other hand using %s for actual string interpolation is rare nowadays, and it lets you specify the actual format of the data in the query which most other libraries dont support
psycopg2 doesnt support ? at all, last i checked @runic badger
you have to use whatever style they support
runic badger
yea never heard of it.
harsh pulsar
if psycopg2 is fully compliant, psycopg2.paramstyle should contain "format" or "pyformat"
pure cypress
Sounds like you want a way to get the newest 15 logs per result type
Is that a correct assessment
fringe crater
database.execute(f"SELECT COINS FROM PROFILE WHERE USER_ID IN ({players_joined})")
how come this return a column instead of a field value?
pure cypress
Or is it a limit of 15 overall, rather than per group?
What do you mean by returning a column?
fringe crater
players_joined is a list of user id
when i print this code out it says cant find such column
where user id is already a column
harsh pulsar
don't construct sql queries by interpolating strings
also is coins a column in the profile table?
fringe crater
yea
harsh pulsar
what database is this
fringe crater
sqlite
harsh pulsar
database.execute("SELECT COINS FROM PROFILE WHERE USER_ID IN (?)", [tuple(players_joined)])
does this work?
i cant remember if you can bind a sequence like that
database.execute("SELECT COINS FROM PROFILE WHERE USER_ID IN ?", [tuple(players_joined)])
maybe this
fringe crater
okay imma try and get back
@harsh pulsar doesnt seem to be working
unsupported type
harsh pulsar
hm, i figured it might fail
!e ```python
import sqlite3
db = sqlite3.connect(':memory:')
with db:
db.execute('create table profile (coins int, user_id int)')
db.execute('insert into profile values (5, 10392)')
db.execute('insert into profile values (2, 4328)')
db.execute('insert into profile values (7, 9905)')
db.execute('insert into profile values (4, 531)')
ids = (10392, 9905)
curs = db.execute('select * from profile where user_id in ?', [ids])
print( curs.fetchall() )
@harsh pulsar :x: Your eval job has completed with return code 1.
001 | Traceback (most recent call last):
002 | File "<string>", line 12, in <module>
003 | sqlite3.OperationalError: near "?": syntax error
!e ```python
import sqlite3
db = sqlite3.connect(':memory:')
with db:
db.execute('create table profile (coins int, user_id int)')
db.execute('insert into profile values (5, 10392)')
db.execute('insert into profile values (2, 4328)')
db.execute('insert into profile values (7, 9905)')
db.execute('insert into profile values (4, 531)')
ids = (10392, 9905)
curs = db.execute('select * from profile where user_id in (?)', [ids])
print( curs.fetchall() )
@harsh pulsar :x: Your eval job has completed with return code 1.
001 | Traceback (most recent call last):
002 | File "<string>", line 12, in <module>
003 | sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.
you can always brute force it by manually creating the list of placeholders
!e ```python
import sqlite3
db = sqlite3.connect(':memory:')
with db:
db.execute('create table profile (coins int, user_id int)')
db.execute('insert into profile values (5, 10392)')
db.execute('insert into profile values (2, 4328)')
db.execute('insert into profile values (7, 9905)')
db.execute('insert into profile values (4, 531)')
ids = (10392, 9905)
placeholders = ', '.join('?' * len(ids))
curs = db.execute(f'select * from profile where user_id in ({placeholders})', ids)
print( curs.fetchall() )
@harsh pulsar :white_check_mark: Your eval job has completed with return code 0.
[(5, 10392), (7, 9905)]
@fringe crater so we use string interpolation to create a query with a list of placeholders in it, but we don't actually use string interpolation to pass data to the queyr
fringe crater
okay let me try
@harsh pulsar
so that works
but having a problem updating the coins using py placeholders = ','.join('?' * len(players_joined)) players_coins_raw = database.execute(f"SELECT COINS FROM PROFILE WHERE USER_ID IN ({placeholders})",players_joined).fetchall() players_coins = str(players_coins_raw)[1:-1].strip('()') print(players_coins) database.execute(f"UPDATE PROFILE SET COINS = ? WHERE USER_ID IN ({placeholders})",(players_coins + 1),players_joined,).fetchall()
high geyser
c.execute("""DELETE FROM tags
WHERE keyword = ? AND guildid = ?""", (key_word, ctx.guild.id))
c.execute("""DELETE FROM alias_storage
WHERE guildid = ? AND original_name=?""", (ctx.guild.id, key_word))```how can I merge these two queries
union?
pliant pendant
u could use $n instead of ?
i think*
but if u wanna merge both in a single query, you just put ; in the end of a query
and insert the other query
but use $n instead, so u dont need to repeat the variables in the format section of the function
(where n is a number scaling from 1)
u dont really merge anything, u just put them in the same request which is more efficient ofc (but barely if its a local database)
high geyser
oh thanks
torn sphinx
how do you create a column on sqlalchemy?
errant herald
so i just started using databases, namely the sqlite3 module, and im not quite sure how to retrieve data, anyone able to help?
torn sphinx
@errant herald having same issue
errant herald
ah, ok
haha hopefully that increases the chances that we can get an answer quicker
torn sphinx
Why
[tuple(players_joined)]
Instead of
(players_joined,)
@harsh pulsar ?
modern olive
anyone here who uses asyncpg to connect to a PostgreSQL database? I cannot figure out how to use it on an ubuntu ec2 instance
torn sphinx
Don't they both achieve the same goal?
high geyser
@pliant pendant i get an error
c.execute("""DELETE FROM tags
WHERE keyword = :thekeyword AND guildid = :theguildid;
DELETE FROM alias_storage
WHERE guildid = :theguildid AND original_name=:thekeyword""", {"theguildid":ctx.guild.id, "thekeyword":key_word})``` this is codeerror:
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: Warning: You can only execute one statement at a time.```execute 2 different times
pliant pendant
eugh its a limitation
pliant pendant
yeah its probs best way to do both separately, ur using sqlite
it can only handle 1 write connection anyways
high geyser
can I use UNION
errant herald
how can one get data from a table? ik this is a crazy simple question but i literally only discovered this module 15 mins ago
high geyser
bruh
high geyser
ok whats good for lots of servers
high geyser
and i dont b=need to change anything in db right
pliant pendant
cus u use sqlite
pliant pendant
my pleasure 
high geyser
so all i have to do is
instead of sqlite3.connect
i should write postgresql.connect?
pliant pendant
im not too good with sqlalchemy
high geyser
lol jk
pliant pendant
i see
the docs has all u need tho
and probs guides for that
i use postgres trough asyncpg or django ORM
high geyser
:) thanks
errant herald
soo, anyone know how to get not an entire row of data?
proven arrow
You can select by columns?
quaint tiger
select * from FOO where bar=BAZ;
You might want to be more precise in your question @errant herald
errant herald
my question was how to get a specific data point of a database, not an entire row or column
although u pinged me right as i figured it out : \
@quaint tiger, so dont worry about it anymore
pliant notch
help
errant herald
wait it was that easy?
thanks patryk
mk, so i have another problem now, how can i make it add variables?
because the required quotes make it a string in and of itself, not the variable
tepid cradle
That varies by database, which flavour are you using? @errant herald
errant herald
flavour?
tepid cradle
Which Db
tepid cradle
Sqlite is the db you're using. File extensions are just for your convenience (and some times the convenience of the OS).
I don't know where the "import" came from
quaint tiger
probably import sqlite3 in python....
tepid cradle
You need to do
select cast(a as decimal) from table where foo = bar
To convert the number stored as next to actual number
errant herald
yes
probably
import sqlite3in python....
tepid cradle
Though, I'm interested in knowing what you mean by "required quotes"
errant herald
as in, when you specify what you add to the table, you need quotes within double quotes of the function saying that its a string
and it errors if there arent these quotes
ill send an example one second...
tepid cradle
and it errors if there arent these quotes
@errant herald no it doesn't
Show an example of your code. The error is somewhere else
errant herald
cursor.execute("INSERT INTO table VALUES 'addcontent','message.author'")
wait
hold on
there
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
thats the example
the former was edited and doesnt match what i explained
ok, i think i found a solution...
one final question: is there a simple way to get the row number of something?
in a table
as for what i am doing, a method of getting the "id" of a data point (row) would be very nice
tepid cradle
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
@errant herald there's no problem in this query. The number will be stored as a number of the column has been defined as numeric. You shouldn't need to convert it
errant herald
yes, that was the example of the quotes though, as when i replaced that with a variablee in my code it did an error
tepid cradle
There is no row number by default. If you want it, you need to add an id column with type serial at the time of creating the table
tepid cradle
If you want to use variables in your query, you do it like this :
cur.execute("insert into stocks values (?,?,?,?), (var1, var2, var3, var4))
where var1, var2 represent whatever variable you have
tepid cradle
Go through a tutorial, all these will be clarified in the first couple of pages
Basics of SQL hardly take 2-3 hours to learn
errant herald
blackcursor.execute("insert into black values(?,?)",(addblackcontent, message.author))
this is the code, the word black is being used as i am making a black card white card cards against humanity kind of thing
ok cool
tepid cradle
Syntax of the query is correct. I'm a bit concerned about the structure of your data though
errant herald
what are these concerns?
torn sphinx
is there some online database i can use which i dont have to host on my pc?
quaint tiger
is there some online database i can use which i dont have to host on my pc?
@torn sphinx all the big cloud providers offer some. GCP, Azure,AWS
quaint tiger
You can also get a VPS from DigitalOcean, Linode, Vultr, etc. and host your own. Probably cheaper.
torn sphinx
why do databases have to be hosted adsgsdyau why cant there just be a website where it hosts databases and u can login into them on the website and crap bruh
light storm
@torn sphinx all the big cloud providers offer some. GCP, Azure,
AWS
@quaint tiger Why is AWS crossed through? 😂 Genuine question
quaint tiger
Cloud providers sometimes have a free tier...
Not sure which ones give you a free DB though.
@quaint tiger Why is AWS crossed through? 😂 Genuine question
@light storm accidentalfuck jeff bezos
light storm
xD
Fair enough
quaint tiger
All I am saying in this chan 🙂
torn sphinx
but if they require ccs or shit like that then its kinda useless
quaint tiger
Infrastructure costs money...
torn sphinx
how do you create a column
cosmic wave
torn sphinx
can someone help me create database on sqlite?
harsh pulsar
no its not async @cosmic wave
@torn sphinx in python, you create a database simply by specifying the filename. if the database file does not already exist, it is created automatically
harsh pulsar
import sqlite3
db = sqlite3.connect('database.db')
if the file database.db already exists, this will load the database. if the file does not exist, it will create the file and create a new database in it
how do you know you need a specific column? what are you trying to do?
torn sphinx
@harsh pulsar yes let me tell you
@harsh pulsar SELECT t_user.id AS t_user_id, t_user.date_created AS t_user_date_created, t_user.date_login AS t_user_date_login, t_user.uuid AS t_user_uuid, t_user.email AS t_user_email, t_user.verified AS t_user_verified, t_user.name AS t_user_name, t_user.message AS t_user_message, t_user.password AS t_user_password, t_user.settings AS t_user_settings, t_user.groups AS t_user_groups, t_user.contacts AS t_user_contacts, t_user.type AS t_user_type, t_user.password_md5 AS t_user_password_md5
FROM t_user
harsh pulsar
@torn sphinx where did you get this query?
torn sphinx
i got a error so
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: t_user.contacts
[SQL: SELECT t_user.id AS t_user_id, t_user.date_created AS t_user_date_created, t_user.date_login AS t_user_date_login, t_user.uuid AS t_user_uuid, t_user.email AS t_user_email, t_user.verified AS t_user_verified, t_user.name AS t_user_name, t_user.message AS t_user_message, t_user.password AS t_user_password, t_user.settings AS t_user_settings, t_user.groups AS t_user_groups, t_user.contacts AS t_user_contacts, t_user.type AS t_user_type, t_user.password_md5 AS t_user_password_md5
FROM t_user
harsh pulsar
@jovial notch anyone reading this channel can help
and no i have no idea why it outputs 2, it sounds like that is what is in your database?
@torn sphinx but why are you creating a new database when you already have an existing query? where did you get this query?
torn sphinx
@harsh pulsar i got it from a source code
jovial notch
On my query editor it's perfect on my python code it says SELECT 2.
harsh pulsar
what do you mean "SELECT 2"
oh
.execute does not return query results
what database library is this?
jovial notch
oh mb
harsh pulsar
@torn sphinx what source code?
jovial notch
i meant to do fetchall
torn sphinx
let me send private
harsh pulsar
i'd rather not
torn sphinx
https://gitlab.com/escargot-chat/site here then
harsh pulsar
i see. and what are you trying to do in general?
torn sphinx
i'm trying to get the register to talk
harsh pulsar
its very unusual that you want to run someone else's query against your own databases
torn sphinx
its very unusual that you want to run someone else's query against your own databases
@harsh pulsar it's free to use
and your allowed to use it so
harsh pulsar
i see that
so you just need to create the database that is used by the Escargot server
is that right?
torn sphinx
yes
torn sphinx
yeah but nothing about creating database
harsh pulsar
and do you see the files escargot.sqlite and stats.sqlite?
harsh pulsar
where does it tell you to create that
torn sphinx
at the error
harsh pulsar
what error?
torn sphinx
[SQL: SELECT t_user.id AS t_user_id, t_user.date_created AS t_user_date_created, t_user.date_login AS t_user_date_login, t_user.uuid AS t_user_uuid, t_user.email AS t_user_email, t_user.verified AS t_user_verified, t_user.name AS t_user_name, t_user.message AS t_user_message, t_user.password AS t_user_password, t_user.settings AS t_user_settings, t_user.groups AS t_user_groups, t_user.contacts AS t_user_contacts, t_user.type AS t_user_type, t_user.password_md5 AS t_user_password_md5
FROM t_user
WHERE lower(t_user.email) = ?]
[parameters: ('test1@gmail.com',)]
(Background on this error at: http://sqlalche.me/e/13/e3q8)
this
harsh pulsar
did you specify the database filenames in escargot/site/settings_local.py?
harsh pulsar
can you share those two lines? they should have DB = and STATS_DB =
torn sphinx
DB = 'sqlite:///../server/escargot.sqlite'
STATS_DB = 'sqlite:///../server/stats.sqlite'
harsh pulsar
and the database files are located in escargot/server/, right?
torn sphinx
yes
harsh pulsar
hm. i dont see where in the error it says you need to create a column
harsh pulsar
in fact i dont see what the error actually is, based on what you posted
torn sphinx
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: t_user.contacts
[SQL: SELECT t_user.id AS t_user_id, t_user.date_created AS t_user_date_created, t_user.date_login AS t_user_date_login, t_user.uuid AS t_user_uuid, t_user.email AS t_user_email, t_user.verified AS t_user_verified, t_user.name AS t_user_name, t_user.message AS t_user_message, t_user.password AS t_user_password, t_user.settings AS t_user_settings, t_user.groups AS t_user_groups, t_user.contacts AS t_user_contacts, t_user.type AS t_user_type, t_user.password_md5 AS t_user_password_md5
FROM t_user
harsh pulsar
consider also that there might be a bug in their code 😉
torn sphinx
this
harsh pulsar
ah, got it
torn sphinx
it actually saids no such column
harsh pulsar
i see
this might be a bug or mistake in their database creation code
i dont recommend just adding a column here
that column should exist https://gitlab.com/escargot-chat/server/-/blob/master/migrations/002.py
are you using windows? linux?
instead of just trying to make a new column, i think you should maybe delete the old database files and try to run the creation script again. something is off, and it probably isn't your fault
minor ruin
I love their site, join Discord which is modern chat software to run old chat software
harsh pulsar
hah good catch i didnt even notice
torn sphinx
are you using windows? linux?
@harsh pulsar windows
minor ruin
data probably isn't none
jovial notch
It's none though.
I put a random ID such ass 1000
There is no 1000 id databased
highest is 50
harsh pulsar
@jovial notch what database library is this
harsh pulsar
oh, asyncpg does things differently
torn sphinx
instead of just trying to make a new column, i think you should maybe delete the old database files and try to run the creation script again. something is off, and it probably isn't your fault
@harsh pulsar alright i will try
minor ruin
some DELETE queries return number of rows
harsh pulsar
@torn sphinx i also recommend downloading https://sqlitebrowser.org/ to visually inspect the databases
torn sphinx
alright will try that thank you @harsh pulsar for your help
harsh pulsar
good luck
dont be afraid to contact the maintainers directly for a problem like this
torn sphinx
i won't thank you! 😄
harsh pulsar
it sounds like they have their own discord server
minor ruin
Do they support running seperate servers?
harsh pulsar
so maybe ask there
minor ruin
it may be one of those, we do public development for OUR server but don't support others setting up their own
harsh pulsar
@jovial notch are you sure it returns None when it succeeds? it probably doesnt
@minor ruin it seems like it's meant to be self hosted
minor ruin
salt rock lamp, that's where I was going
harsh pulsar
you take this one i gotta head out anyway 😄
minor ruin
the downloads they offer only support THEIR servers
salt rock lamp, it's not uncommonf or that
young seal
I have a very basic question I need help with regarding pyodbc
young seal
Do I need to setup a commit and rollback try code block when I am connecting to a mssql/ tsql database? Similar to how I would when using cc oracle to connect?
I am not sure because I read online that transact use a database engine that doesn't require that, but whe. I was reading through other documents, it stated I might need; not sure where I can see all active connections; anyone know?
Cooler, just google it, there's plenty info on how to add a column in sql
nocturne bay
ok
young seal
You don't need python to do it btw
But if you wanted to , there's also info on how to do that, or how to add a column to a pandas df
nocturne bay
okk
minor ruin
dicem0ney, I believe you can setup commit rollback if you want with pyobdc
MSSQL supports rollback
wet pagoda
Hi, what would people recommend using for sqlite data at rest encryption. I'm currently using SQLCipher on linux, but the problem I have is that it's harder to use on windows, and a lot of the developers in our project are using windows. I also know of SQLeet, which is fully self contained, but I'm not too sure of the python bindings available for it. Does anyone know of a good way to do data at rest encryption which is easy to install on both linux and windows?
harsh pulsar
@jovial notch it might return something other than None to indicate that there are no rows in the output. you need to check the asyncpg documentation for what to do
young seal
Rabbit, it seems like best practice to do so then, in order to avoid multiple connections not closing... thanks for help
mystic shore
this is more of a cache question but i figured this is the closest thing
im using the redis library for python
redis will clear itself after the redis server restarts right?
its all in memory only?
runic badger
i believe so yes
azure turtle
uneven smelt
What do I need to do to allow connection without a password in postgres?
I added to my pg_hba.conf:
host dbname theusername 192.168.1.0/16 trust
But does not work unfortunately
quaint tiger
redis will clear itself after the redis server restarts right?
@mystic shore you can enable persistence, as f'pls' linked.
Writing to disk might temporarily slow redis down a bit, so it should be timed. Then if anything, you miss the last 5 minutes worth of data, but keep most of it.
mystic shore
i dont need it to be presistant, thats why i was wondering if its presistant by default. thanks!
uneven smelt
What do I need to do to allow connection without a password in postgres?
@uneven smelt Found the problem.
void ruin
how can i work with data bases to make a register/login system out of my python code?
is there a website to learn it?
torn sphinx
what type of database do you want to use
spark ravine
if anyone's got elasticsearch-py experience, please help in #help-pineapple thanks!
jovial notch
In my database asyncpg I store time as datetime.datetime.now() with timestamp without time zone. How could I make so if a data is longer than 25 days it get's hidden in the output?
tepid cradle
@jovial notch "data is longer than 25 days" needs more clarity. Data size is generally stated in number of records, number of rows, or KB, MB. Not sure what it means for data to be longer than 25 days
jovial notch
so what i want is on !warnings Killer
i only want a warning to stay for 25 days
then its hidden on !warnings command
but not deleted
just hidden
tepid cradle
There's no concept of hidden in a database. If you want, you can create a Boolean column named active and mark it as false after 25 days
tepid cradle
Or you can just integrate it into your query such that you fetch only the last 25 days' records
select * from revengepolice where name = $1 and date > current_date - '25 days'::interval
This query is valid only for Postgres, won't work on other databases
how could I do that?
@jovial notch You can create a column using alter table command. You'll have to run it independent of your Python code
alter table revengewarnings add column active boolean
To mark older warnings as inactive, you'll have to run a batch job which checks the Db every day/hour and changes the status of old warnings.
update revengewarnings set active = false where date <= current_date - '25 days'::inteval
How you'll run a batch job is a completely different question
tepid cradle
yes, this should work. It will give you warnings where the value of the time columns is within the last 20 days
high geyser
whats the difference between primary key and index in sqlite pls explain i couldnt understand
ping me when help pls
tepid cradle
Primary key is a unique identifier for each row
Index stores information about the data on a sorted manner to speed up sorting and searching
Databases create an index on the primary key by default
You can create additional indices on your own on columns based on which you feel you'll need to sort and search often. Column on which index is created need not be unique. But primary key needs to be unique
tepid cradle
do indexes need to be unique?????
@high geyser already answered above
high geyser
CREATE TABLE "tags" (
"keyword" TEXT,
"key" TEXT,
"guildid" INTEGER,
"authorid" INTEGER,
"tag_use_count" INTEGER,
"date_created" BLOB,
"time_created" BLOB,
"id" INTEGER,
PRIMARY KEY("id")
);``` this is what i havebut i dont use the id column anywhere when accessing the database
how it benefits me?
tepid cradle
Take this example.
Suppose you have student records. You know student names can't be unique. Let's say there are two John Smiths in your school. So you can't record that John Smith has paid their fee.
So what you have to do is assign each student a unique id. Let's call it student_id. This will be your primary key, since you know that one id shall be assigned to only one student.
But you know that people will often search for students by their name, since it's not possible for people to remember everyone's id. So you create an index on the name column. This index will significantly speed up searching of students by name.
high geyser
right
so how to create an index on a column?
thanks for explaining @tepid cradle
:D
tepid cradle
How to create index will vary by database. I don't remember the syntax since it's such a rarely used command. But you can easily look it up in the docs of the database you are using.
It is somewhat like create index student_name_index on students(name)
But please look up the exact syntax in the doc