np
#databases
weary warren
torn sphinx
Hi, everybody. how was your day? ๐
I have issue with pymongo.
import pymongo
mongoclient = pymongo.MongoClient("mongodb://dbUser:dbpassword@104.238.234.183:27017/")```I got following error with running above code
pymongo.errors.OperationFailure: Authentication failed., full error: {'ok': 0.0, 'errmsg': 'Authentication failed.', 'code': 18, 'codeName': 'AuthenticationFailed'}
But when I try with Mongodb Compass, it successes with above db url - mongodb://dbUser:dbpassword@104.238.234.183:27017/
https://prnt.sc/vz5tmk
torn bane
no idea
torn sphinx
real snow
hi guys i have 4 variables, "age" , "sex" , "year" and "expected" & am trying to plot a graph using only the data from "male" which is in the "sex" column in my .csv file .. I have tried lifedf.plot.bar('year','expected') , but this is just plotting it all
doing this in jupyter notebook btw
cold ocean
hello everyone, just discovered this channel exists!
i am trying to update a user i have but it updates the wrong one, how do i fix this?
def update_user(conn, user):
sql = ''' UPDATE users
SET name = ?,
admin = ?
WHERE id = ?'''
cur = conn.cursor()
cur.execute(sql, user)
conn.commit()
create_user(conn, ('Galgalon', False))
update_user(conn, (2, 'Glgltz', True))
it does this instead
proven arrow
@cold ocean The parameters you pass to the query are positional, with their placeholders
regal moss
hey guys, anyone played around with TinyDB for python? got one question, why .purge() doesn't work anymore?
how can I purge now
cold ocean
<@329351646943641601> The parameters you pass to the query are positional, with ...
Uhh what does that mean exactly? Im kinda new to sqlite...
proven arrow
Your Query is being translated to this:
UPDATE users SET name = 2, admin = 'Giglit' WHERE id = True
Because that is the order you passed the variables in.
regal moss
def update_user(conn, user, admin, id):
sql = f'UPDATE users
SET name = {name},
admin = {admin}
WHERE id = {id}'
etc
regal moss
values in " "
silk vortex
how do I connect to a database on another machine?
regal moss
VM
use VM
make a network local and do that XAMPP for localhost
so db will be localhost/phpmyadmin
and then just ocnnect to: host = localhost
user = root
pass = " "
etc
regal moss
hey guys, anyone played around with TinyDB for python? got one question, why .pu...
Im answering my own question, table .purge() has been replaced with .truncate()
ty 8off it took me 4h to find it
np mate, take care
u2 ty bb
cold ocean
if i have a table of users, and a table of groups, can i put a user inside a group?
proven arrow
if i have a table of users, and a table of groups, can i put a user inside a gro...
Yes. This is known as a relationship. There are different types of relationships, and the one you need depends your logic. There are different kind of relationships, which you can read about here, and decide which one you need. https://condor.depaul.edu/gandrus/240IT/accesspages/relationships.htm
To implement the relationship in your database, your table will have what is known as a foreign key. Think of this as a pointer that points to some record in another table. For example, your users table can have a column called group_id which basically holds (or references in database terminology) a row in your groups table. It makes this reference by storing the primary key of the groups table in it.
cold ocean
what if i want a column in the group table that holds all the users inside each group, can i do that?
like put a list in it or something
proven arrow
That is not an efficient way to store such data, especially when it can be split up and put into seperate tables and columns.
And querying/joining becomes much more complex as well. So it's better to have seperate tables and make the relationship as mentioned in my previous message.
lethal goblet
Does anyone know how to create ERD diagrams?
silk vortex
Uh oh ๐ฆ no pg_hba.conf entry for host "71.234.227.103", user "root", database "exo", SSL off
This is what I get when I try to connect to my ubuntu postgresql database from popsql
does anyone know why?
torpid sky
How do I search for a tuple using sqlite. so like search for the tuple Hello [(Name, 123), (Hello, 156)] How would I be able to grab that Hello, 156
torn bane
do you even need the other tuple?
torpid sky
wdym
no So basically im using discord py making a currency bot I need to match their name to a tuple then getting the second variable in it
torn bane
if not then why don't you query just for the one with ("hello", 156)
what 's the column?
torpid sky
wdym
torpid sky
uh havent made yet
torn bane
def getHello(listx):
for x in listx:
if x == ("hello", 156):
return x
how do I grab the first va
torn bane
??
torpid sky
how to grab var from tuple
torpid sky
nm
slender rose
okay so i have 3 MySQL with my hosting, should i use those for my discord bot or should i get postgresSQL
radiant elbow
MySQL is fine. Bit weird, has some quirks, but a totally acceptable database.
slender rose
also my database would be mainly writing based, meaning i'll have to do writing on it a lot. can MySQL handle many writing queries at the same time?
proven arrow
Yes it can handle that
slender rose
also google says MySQL can store only 65,535 bytes? so that is like 0.06 GigaBytes (65 MegaBytes)
proven arrow
What size are you referring to?
If I remember that value is for the row size of MySQL internally
proven arrow
No that is not the whole size of the database.
slender rose
okay lmao nvm that, what i just want to ask is that more or less MySQL is okay for a single guilded discord bot?
shell ocean
okay lmao nvm that, what i just want to ask is that more or less MySQL is okay f...
yes.
way more than enough.
proven arrow
Perfectly fine.
slender rose
great well thats all i needed to know haha
proven arrow
People run entire e-commerce applications using MySQL. And probably most of the web uses it too.
slender rose
how do i connect to a MySQL?
import asyncio
import aiomysql
loop = asyncio.get_event_loop()
@asyncio.coroutine
def test_example():
conn = yield from aiomysql.connect(host='127.0.0.1', port=3306,
user='root', password='', db='mysql',
loop=loop)
cur = yield from conn.cursor()
yield from cur.execute("SELECT Host,User FROM user")
print(cur.description)
r = yield from cur.fetchall()
print(r)
yield from cur.close()
conn.close()
loop.run_until_complete(test_example())```is this correct?
trying to use aiomysql
proven arrow
Looks fine for that kind of example, but in your application I recommend using a connection pool.
proven arrow
Let me try find it? Didn't you get that example from the docs also? Connection pool should also be there too
slender rose
lemme check rq
proven arrow
Take a look at the basic example on pypi, shows how to do the connection pool https://pypi.org/project/aiomysql/
slender rose
do i need to close the pool?
what if i keep it running so i dont have to connect every time i need to use it
proven arrow
what if i keep it running so i dont have to connect every time i need to use it
That is what you should be doing and the purpose of the pool
Open it when your application starts and close on shutdown
slender rose
ohh
proven arrow
Is it for a discord bot you said?
proven arrow
Ok so a common thing people do is assign the database/pool connection to the bot instance as a variable. That way you can easily access the database from your other classes wherever you have access to the bot instance.
slender rose
ohhh
proven arrow
Database username is the database user you use to access the database. You can have different users each with different privileges. Database name is the name of the database, whatever you called it.
proven arrow
What printed that? The example?
slender rose
ye
async with pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute("SELECT 42;")
print(cur.description)
(r,) = await cur.fetchone()
assert r == 42```That's just to show you basic operations and also for you to test the connection works.
proven arrow
Either through the command line it installed for you, or if you want a visual gui tool to manage it you can use some software for it. Things like MySQL workbench, or dbeaver.
slender rose
oh sweet
how many digits can an INT have
like for user IDs should we use INT or i think there was something like BIGINT or smth?
proven arrow
Use bigint
You can see the exact values here, https://dev.mysql.com/doc/refman/8.0/en/integer-types.html
slender rose
import asyncio
import aiomysql
print("hello")
async def connection(loop):
pool=await aiomysql.create_pool(host = "aaaaaaaa",
port = 3306,
user = "aaaaaaa",
password = "aa",
db = "aaaaaaaa",
loop = loop)
async with pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute("SELECT 42;")
print(cur.description)
(r,) = await cur.fetchone()
assert r == 42
loop = asyncio.new_event_loop()
loop.run_until_complete(connection(loop))
async def test(loop):
async with pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute("""CREATE TABLE LEVEL_DATA
(id bigint,
exp int,
level int,
PRIMARY KEY (id));""")
await cur.commit()
loop = asyncio.new_event_loop()
loop.run_until_complete(test(loop))```how do i get the pool on the second function
proven arrow
I'm not an async expert, but those are two different event loops.
And your pool is local to a function
slender rose
so do i make the pool outside a function?
wait nvm i cant do that await needs to be inside an async function
proven arrow
You can't because await needs to be inside an async function,
You need to assign it somewhere and store it
Like I said to you before with the bot example, or in this case you can assign it to the loop.
slender rose
im confused lol how do you assign it to a loop
proven arrow
Just like how you would assign any other variable to something
You need a function that returns the pool connection, then do loop.db_conn = loop.run_until_complete(some_func())
slender rose
Ohhh
slender rose
Traceback (most recent call last):
File "C:\Users\User\Downloads\test.py", line 43, in <module>
caller.run_until_complete(my_function())
File "C:\Users\User\AppData\Local\Programs\Python\Python38-32\lib\asyncio\base_events.py", line 616, in run_until_complete
return future.result()
File "C:\Users\User\Downloads\test.py", line 38, in my_function
await cursor.execute("""INSERT INTO LEVEL_DATA (id, exp, level) VALUES ("{}", "{}", "{}")""".format(700026158904901740, 1, 1))
File "C:\Users\User\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiomysql\utils.py", line 103, in __aexit__
await self._pool.release(self._conn)
RuntimeError: Task <Task pending name='Task-2' coro=<my_function() running at C:\Users\User\Downloads\test.py:38> cb=[_run_until_complete_cb() at C:\Users\User\AppData\Local\Programs\Python\Python38-32\lib\asyncio\base_events.py:184]> got Future <Task pending name='Task-3' coro=<Pool._wakeup() running at C:\Users\User\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiomysql\pool.py:188>> attached to a different loop
Task was destroyed but it is pending!
task: <Task pending name='Task-3' coro=<Pool._wakeup() running at C:\Users\User\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiomysql\pool.py:188>>
C:\Users\User\AppData\Local\Programs\Python\Python38-32\lib\asyncio\base_events.py:641: RuntimeWarning: coroutine 'Pool._wakeup' was never awaited```why am i getting this
slender rose
also im using MySQL and i have a Primary key, how can i check if a key already exists or not, like if it doesnt exist then INSERT, if it exists then UPDATE
should i use try, except?
slender rose
i fixed that
old cradle
Can someone help me setup postgreSQL ?
slender rose
also im using MySQL and i have a Primary key, how can i check if a key already exists or not, like if it doesnt exist then INSERT, if it exists then UPDATE
should i use try, except?
this is the current problem
slender rose
so try and except?
someone said doing INSERT ... ON DUPLICATE KEY UPDATE but it didnt work
summer jetty
primary keys are supposed to be unique
so if u try to insert one that already exists
then it should error
in sqlite it raises sqlite.IntegrityError
slender rose
oh its integrity error in mysql as well
summer jetty
nice
slender rose
except pymysql.err.IntegrityError:
Traceback (most recent call last):
File "C:\Users\User\Downloads\test.py", line 47, in <module>
loop.run_until_complete(my_function())
File "C:\Users\User\AppData\Local\Programs\Python\Python38-32\lib\asyncio\base_events.py", line 616, in run_until_complete
return future.result()
File "C:\Users\User\Downloads\test.py", line 42, in my_function
except pymysql.err.IntegrityError:
NameError: name 'pymysql' is not defined```ye
slender rose
lmao what
slender rose
aiomysql
summer jetty
then use their IntegrityError
summer jetty
ok
summer jetty
nice
old cradle
Can someone help me setup `postgreSQL` ?
Anyone?
summer jetty
never used it sorry
soft coyote
Trying to understand MySQL theory. Lets say I want to store price per time on stocks, do I create 1 table? with stock name/ticker being the primary key? or tables for each tickers?
proven arrow
Trying to understand MySQL theory. Lets say I want to store price per time on st...
You dont want a table for each ticker. Thats generally bad design.
Instead store the price per time in a single table. Also you probably don't want the ticker as the primary key because you cant have duplicate primary keys, since they are unique. Also its a good idea to split data up (normalise) into different tables and join them using relationships. For example, price data in one table, and company info in another.
slender rose
do you need to use fetchall() to use ORDER BY and get position of something?
i.e. i have experience (int) as one of the column, need to get the position of it by descending, do i need to fetch all and use index to get the position/ranking or is there any other way
proven arrow
do you need to use `fetchall()` to use ORDER BY and get position of something?
i...
If you want the database to give you the position then you can use the ROW_NUMBER() window function, which would add a column to each row containing the position value. Although, to note mysql supports this from version 8. You will still need to use fetchall and orderby, however the position is returned by the database, and you dont need to manually calculate it on your application side.
It is still possible to do the same on mysql 5.7 or older, however window functions is more simpler.
proven arrow
Well if you want to get only a single row, and its position you can still do fetchone, after using LIMIT and OFFSET in your query, but you will have to know in advance the position you want to retreive.
slender rose
yeah thats not what i want
vapid jackal
Hello
Guys i am new on here
just want to discuss with someone about the Database question
I will write my questions and my answer here, if anyone has time can discuss together that will be great thanks
torpid sky
async def addbal(self, user, amount: int):
conn = await aiosqlite.connect("Currency.db")
c = await conn.execute("SELECT tuples FROM Currency.db WHERE guild_id={ctx.guild.id}")
tuples = await c.fetchone()
print(tuples)
if tuples is not None:
for x in tuples:
fulltuple = x
x = x[0]
if x == user:
tuples.remove(fulltuple)
num = int(fulltuple[1])+amount
tuples.append((x,num))
tuples = list(tuples)
sql = "UPDATE Currency.db SET tuples=? WHERE guild_id=?"
val = (tuples, ctx.guild.id)
y+=1
elif tuples is None:
sql = "INSERT INTO Currency.db(guild_id, tuples) VALUES(?,?)"
val = (ctx.guild.id, (user, amount))
await conn.execute(sql, val)
await conn.commit()
await c.close()
await conn.close()
@commands.command()
@commands.cooldown(1, 60*60, commands.BucketType.user)
async def work(self, ctx):
await ctx.send("You worked hehe")
await self.addbal(thewalkingsea, 100)```discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: unrecognized token: "{"
proven arrow
That your entering a value out of the allowed range for that column. The number is bigger or smaller than the allowed size.
torn sphinx
i have a database that is going down in peak times. not good.
how do i tell sqlalchemy to use memory and then, when database is up, commit all changes made to the database?
hoary vessel
slender rose
>>> Traceback (most recent call last):
>>> File "activity.py", line 35, in <module>
>>> import aiomysql
>>> File "/.local/lib/python3.7/site-packages/aiomysql/__init__.py", line 32, in <module>
>>> from .connection import Connection, connect
>>> File "/.local/lib/python3.7/site-packages/aiomysql/connection.py", line 45, in <module>
>>> DEFAULT_USER = getpass.getuser()
>>> File "/usr/lib/python3.7/getpass.py", line 169, in getuser
>>> return pwd.getpwuid(os.getuid())[0]
>>> KeyError: 'getpwuid(): uid not found: 1199```whats this
main gazelle
Hey everyone,
Just a note of warning: I am very new to SQL
Is there any way I can connect to a local database on my machine from heroku?
torn sphinx
no
do port
forwarding
to sql port (google)
then tell heroku to point @ your ip to local connections
main gazelle
Is there any guide that can help me do that?
torn sphinx
can you access the router
via
if not (google) your router's provider default gatewat
get the gateway, put it in your browser, put credentials, which normally are available on internet, (google it)
torn sphinx
you need to port forwarding or heroku wont connect
main gazelle
Alright
slender rose
mysql cant get your currnerwbjefuasjed
is this not an error with importing/installation?
also wym by current user
slender rose
i tried googling it
but i cant find anything
i found 'getpwuid(): uid not found: 999 but not 1199
also whats moaslltr?
old cradle
i installed PostgreSQL and set it up but when i try to open pgAdmin 4 it just keeps loading
Wont open
torpid sky
val = str(guildid, user, amount)
await conn.execute(sql, val)
await conn.commit()```How do I set multi values
delicate stream
Anyone can help with a simple pandas/numpy statistical question? I'm trying to find the sum of rows of data for the column (number of COVID cases) within the month of March (there's a date column) but the rows show cases not for each day, but the sum of the days prior up to and including the present day........if this makes sense
modest ibex
I am doing a command for my bot which consists of a select max in my database. I just finished it and it works for me perfectly. But there is a problem, the data is not updated in real time, that is, if the select max is updated (from 100 it goes to 500) it still shows 100.
This is only updated if I restart my bot. Any ideas?
grim pier
Hey can anyone help me? Im storing too much data in 1 field on a DB and its truncated
Cal_7_62x39mm_Ammobox
BP_Magazine_AK15
BP_WeaponSights_V3_Holographic
BP_WeaponSuppressor_AK15
Cal_7_62x39mm_Ammobox_TR
This is an example of how much data i have in the field.
the structure is set to longtext
is there a better way to store this data?
torn sphinx
@grim pier ALTER TABLE shop ALTER COLUMN Scum_Code VARCHAR(2048);
this might help
goto scumbandit > shop > sql and then copy paste this
grim pier
@torn sphinx hey i just tried that. im still getting Warning: Data truncated for column 'ScumCode' at row 1 await self._query(query)
grim pier
@torn sphinx Still the same error
async def deliverorder(orderid):
db = await connect_to_sql_db()
async with db.cursor() as cursor:
await cursor.execute(f"SELECT SteamID,ScumCode FROM orders WHERE OrderCode = '{orderid}'")
data = await cursor.fetchone()
time.sleep(2) # allow for lag
await key_press('T')
pyautogui.typewrite(f'#teleportto {data[0]}')
await key_press('Enter')
time.sleep(10) # allow for lag
for i in data[1].split():
pyautogui.typewrite(f'#spawnitem {i}')
await key_press('Enter')
time.sleep(2) # allow for lag
print(f"{data[0]} has been delivered: {i}.")
await cursor.execute(f"DELETE FROM orders WHERE OrderCode ='{orderid}'")```hmmm
reinsert the data you inserted
the one whos truncated
phpmyadmin, copy+paste the data that you want and paste it to the truncated field
grim pier
@torn sphinx how do you mean? i dont understand ๐
torn sphinx
the data you inserted
click on the truncated column
copy the data you want on it
paste
then click anywhere else
boom
grim pier
@torn sphinx Cop the "Scum code" and re - paste it back in?
torn sphinx
yes
grim pier
I just tried, still the same ๐ฆ
whole briar
3.5NF
round osprey
Is there a tutorial you would recommend for learning Sqlite?
whole briar
the website
worn acorn
Hello everyone, do you think I can do such a thing?
I have to synchronize a local database made with access (which is used for a management system) with a remote sql database (woocommerce) so that if a product is sold it is seen by the local Db and vice versa I hope I have explained myself well.
hoary vessel
torn sphinx
"""CREATE TABLE IF NOT EXISTS todo (
user_id bigint,
todo varchar,
"time" timestamp without time zone,
jump_url varchar
)"""
i have this table and i want to count how many rows are there for user_id xyz, how can i do it?
Hi Guys ,
My database looks like this :
CREATE TABLE IF NOT EXISTS 'test'(
id integer primary key autoincrement,
date TEXT
c_bless integer,
DI_bless integer) ```
For add a value to DI_bless (daily income) I use this command:
```def update_bless(Name, T_bless):
with conn:
c.execute("INSERT INTO {} (date, DI_bless) VALUES (DATETIME('now'), {});".format(Name, T_bless))```
But I want to add last entry DI_bless to the column c_bless (character bless), could someone please give me an advice ?
Thank you !
astral gorge
is it possible to remove the row if its value is less than 0 after UPDATE?
or do I need to do another query?
torn sphinx
show code
astral gorge
ok, for now I have this, if I'm correct this should remove the item if the count is less than 1:
count = await conn.fetchrow('''INSERT INTO inventories
(member_id, item_id, item_count)
VALUES ($1, $2, $3)
ON CONFLICT(item_id)
DO UPDATE SET item_count = item_count + $3
WHERE member_id = $1 AND item_id = $2 RETURNING item_count;'''
, member_id, id, counts[i])
if count < 1:
await conn.execute('''DELETE FROM inventories
where member_id = $1
and item_id = $2;'''
, member_id, id)```@astral gorge You probably want to use fetchval so it returns you a single int value instead of the result as a row object, which fetchrow is currently doing.
astral gorge
ah, yes thanks
astral gorge
wait how can I fetch multiple rows using asyncpg? fetchrow seems to always return only 1 row?
proven arrow
wait how can I fetch multiple rows using asyncpg? `fetchrow` seems to always ret...
just fetch
astral gorge
thanks
foggy quarry
Hi i have a question
I dont have sa user in my pc
How can i login to sql server by sa user
torn sphinx
Hi, I am using the bult-in sqlite3 module and I am trying to rename a column called 'fears' to 'fear(s)' in my table called 'Fearz', but I got this, instead:
Traceback (most recent call last):
File "/data/user/0/ru.iiec.pydroid3/files/accomp_files/iiec_run/iiec_run.py", line 31, in <module>
start(fakepyfile,mainpyfile)
File "/data/user/0/ru.iiec.pydroid3/files/accomp_files/iiec_run/iiec_run.py", line 30, in start
exec(open(mainpyfile).read(), __main__.__dict__)
File "<string>", line 4, in <module>
sqlite3.OperationalError: near "COLUMN": syntax error
[Program finished]
my code:
import sqlite3
connection = sqlite3.connect("Mark_El-Korashy.db")
cursor = connection.cursor()
cursor.execute(
"ALTER TABLE fearz RENAME COLUMN fears TO fear(s)"
)
connection.commit()
cursor.close()
connection.close()
Regarding your alter table statement there is no syntax error in the one you posted near the word column. Maybe you didnt save it? Also your new column name need to be escaped to string literal because you have brackets in it (you can do this by putting single quotes around the new name).
torn sphinx
Regarding your alter table statement there is no syntax error in the one you pos...
ok
Didn't work
proven arrow
Didn't work
Whats the error? Because works fine for me, https://www.db-fiddle.com/f/aQypR1LEKqgxuizyQxzR3U/0
torn sphinx
Whats the error? Because works fine for me, https://www.db-fiddle.com/f/aQypR1LE...
the same error as before
proven arrow
Which version of sqlite you have?
Only thing i can think of is you have an older version of sqlite, because alter table rename column was introduced in 3.2x something of sqlite. Update your python version to something more recent and it should work. You can check sqlite version by printing the value of sqlite3.sqlite_version within python.
eternal raptor
Traceback (most recent call last):
File "c:\Users\sebik\Desktop\discordbot\0512lptwbot\client.py", line 15, in <module>
json_dict = json.load(f)
File "C:\Users\sebik\AppData\Local\Programs\Python\Python39\lib\json\__init__.py", line 293, in load
return loads(fp.read(),
File "C:\Users\sebik\AppData\Local\Programs\Python\Python39\lib\json\__init__.py", line 346, in loads
return _default_decoder.decode(s)
File "C:\Users\sebik\AppData\Local\Programs\Python\Python39\lib\json\decoder.py", line 337, in decode
obj, end = self.raw_decode(s, idx=_w(s, 0).end())
File "C:\Users\sebik\AppData\Local\Programs\Python\Python39\lib\json\decoder.py", line 355, in raw_decode
raise JSONDecodeError("Expecting value", s, err.value) from None
json.decoder.JSONDecodeError: Expecting value: line 3 column 20 (char 22)
with open('C:\\Users\\sebik\\Desktop\\discordbot\\0512lptwbot\\exte.json') as f:
json_dict = json.load(f)
extensions = json_dict["extensions"]
outer yacht
I'm trying to work on a jupyter notebook that runs sql codes and edits them as strings with python. Would this be the right place to ask questions regarding the python code I'm writing?
torn sphinx
hello i need help
{"785172835952754700": {"owner": "na", "welcome": "na", "welcomeid": "na"}} so how can i change one thing in here? so if i wanted to change owner from na to lets say mrbeast, how would i do that?
hushed flicker
torn sphinx
@torn sphinx
data[userid]['owner'] = 'mrbeast'
unreal cloak
Hi guys! Maybe you can help? I got a problem with SQLite3, problem described here: https://www.reddit.com/r/learnpython/comments/k9ljg4/sqlite3_syntax_error/
Help really appreciated!
proven arrow
@unreal cloak all your column names are incorrect in your insert statement
Look at the column names you have when creating the table. Whenever you want to interact with that same column again with any SQL command you must use that same column name.
unreal cloak
@proven arrow Thought python knows when INSERT INTO customers VALUES (x,y,z,..) to fill in x for the first, y for second, z for third etc.?
proven arrow
But that is not what you are doing. Your input to the database is just a string atm. You haven't bind any parameters to it yet.
cursor.execute("INSERT INTO customers VALUES(?, ?, ?,...)", (value1, value2, value3, ...))
This is what you want. Basically the ? is a placeholder and the values in the tuple (the second argument) will be binded to that.
And ignore what I said before. I misread your question.
unreal cloak
Okay, thanks.. now I get sqlite3.OperationalError: no such column: customer_id_insert
I feel like a total idiot ๐
proven arrow
Can you show the updated code
unreal cloak
Can I PM you, to save the chat?^^
proven arrow
It's fine here. It's pretty quite usually in this channel anyways ๐
unreal cloak
Okay ๐
I got it now like this: cursor.execute("""INSERT INTO customers VALUES (customer_id, first_name, last_name, street, zip_code, city, phone, email, notes), (customer_id_insert, first_name_insert, last_name_insert, street_insert, zip_code_insert, city_insert, phone_insert, email_insert, note_insert)""")
unreal cloak
cursor.execute("INSERT INTO customers VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)", (customer_id_insert, first_name, last_name, street, zip_code, city, phone, email, note))
Like this?
proven arrow
I can barely read on my small phone ๐
But yeah that looks fine. Try that.
unreal cloak
Haha okay, I tried that before, now I get NameError: name 'customer_id_insert' is not defined^^
grim pier
https://discordapp.com/channels/267624335836053506/342318764227821568/786754335136284732 Anyone got any ideas how to fix this? its truncated but its set to a longtext
unreal cloak
That's why I tried it the way I had it earlier.. haha
proven arrow
Haha okay, I tried that before, now I get `NameError: name 'customer_id_insert' ...
Is that value in scope of your function?
From the code you posted online it wasn't
unreal cloak
Yes, thats this one:
command1 = """CREATE TABLE IF NOT EXISTS customers(customer_id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT, street TEXT, zip_code INTEGER, city TEXT, phone INTEGER, e_mail TEXT, notes TEXT)"""
proven arrow
No, try to understand your error first. It's no longer a database error you are getting but it's to do with python. NameError is trying to tell you that the name customer_id_insert is not defined or is not accessible whenever you are trying to access it.
def foo():
x = 5
def bar():
print(x)
This is kind of what you are doing.
unreal cloak
I gotta make the vars global ๐คฆโโ๏ธ
proven arrow
Not necessarily no.
Although you are calling your get_customer_form function, that function is not returning any values. You just need to have that function return some values, maybe in a tuple/list or something so you can retrieve them in your query function. If that makes sense?
You can still make them global, but as long as you understand why you get that error you should be able to figure it out, from here onwards.
unreal cloak
I guess I understand what I did wrong here. Basically I was trying to use information from one function, in another function. Is that kinda right?^^
proven arrow
Yes
unreal cloak
Okay, thanks for your time! ๐
proven arrow
@grim pier what if you double click on the field? Does it allow you to edit it? If so you should see all the data there
Because phpmyadmin usually truncates upto x characters, when displaying the data. You can change it from settings.
turbid escarp
I'm making a small program in which I input user ids and it will immediately add itself onto the database, but I want to query before adding to see if the user ID is identical to one in the database
pymongo
scarlet nymph
SELECT TOP (1) [start_time]
FROM [jive_call_reporting].[dbo].[call_records]
ORDER BY start_time DESC;
I think this is a dumb question, but... it's not behaving the way I would expect. The Internet says that ORDER BY sorts the result set, but when I run this query I get the latest record of hundreds of thousands, and when I run it with ASC I get the oldest record. Can I count on this finding the oldest and newest records (at least by start_time--a datetime field)
SQL Server btw
proven arrow
```
SELECT TOP (1) [start_time]
FROM [jive_call_reporting].[dbo].[call_records...
That is how order by works, for datetime.
When you do order by ASC, the row with the oldest date will be first, because that's what came first right. And vice versa.
grim pier
@proven arrow Hey, yes i can edit it if i double click the field
torn sphinx
I'm working on a website and I need to properly learn SQL + database design. Could anyone suggest some good learning resources + free courses to me? I found one that is decent but it doesn't doesn't cover database design.
proven arrow
<@!613362435860070414> Hey, yes i can edit it if i double click the field
So when you do this, is all the data you entered in that field? You might have to scroll down a little inside that field to see it/ or do select all.
grim pier
@proven arrow yes all the data is there
still igloo
complete newbie here with SQL
i prepared query tool on PostgreSQL, now how do i save it on my computer?
i can see the "save" option on the toolbar allows me to browse to a location but the "create" option is greyed out.
proven arrow
<@613362435860070414> yes all the data is there
Right so then your data isn't truncated by the database. It's just phpmyadmin outputting it like that to make it easier for you to read. There is a option to change the size of it, from their settings although I don't remember exactly where it's located.
proven arrow
complete newbie here with SQL
i prepared query tool on PostgreSQL, now how do i ...
Are you using pgadmin?
still igloo
yes @proven arrow
proven arrow
Ok so when you go to save, the file browser should open. At the top you will see the path of where to save. You need to enter a filename at the end of it, and then it should allow you to click save or create.
proven arrow
No
still igloo
okay
wicked nest
hi guys
i wan to ask
about i had enter the fastboot oem unlock
but that say my device is lock
got any idea of this
slender rose
okay so in my postgres i have a column containing amount of money users have, and i wanted to make a command that'll show how much money a specific user has and their ranking in the server richest user wise, is there any way to get that ranking without having to fetch all and then use index on that list?
wintry stream
you can use the WHERE clause
proven arrow
okay so in my postgres i have a column containing amount of money users have, an...
You can have a query that selects from a derived table/subquery, where the subquery uses a window function to get the row number
slender rose
well not the row number, no
wintry stream
so if you have a table with like User_ID (pk) | Money, you can do SELECT Money FROM tablename WHERE User_ID = ...... @slender rose
if that is what you mean
proven arrow
well not the row number, no
Row number is what you need, it's the position of a person rank.
slender rose
i.e. if you have 200 which is highest you're rank one, if you have 199 which is second highest then you're rank 2
wintry stream
slender rose
does sql sort the rows?
wintry stream
@slender rose oh postgres also supports ordering in the query
slender rose
like according to the values of a column? or just put them in order of where they were added to the db
wintry stream
SORT BY
slender rose
ORDER BY*
slender rose
and yes ik but that takes 2-3 seconds time to get everything since my sql has 50k users
i want to bring down that time
proven arrow
Order by is not what they need. Because they don't want all users.
slender rose
exactly
wintry stream
you can add a WHERE clause most likely to filter out users with no money for instance
proven arrow
Use what I said, earlier.
slender rose
i only one one specific user, and their ranking on richest user based
how do we use row number
proven arrow
You will want something like this:
SELECT position, user_id, name, level
FROM (
SELECT *, ROW_NUMBER() OVER(ORDER BY level DESC) AS position
FROM scores
) result
WHERE user_id = 100
wait lemme try that rq
proven arrow
You can further filter it by things like guild ID if you store such things inside the subquery, as I'm not sure how your table looks.
slender rose
wait i still didnt understand the whole of it
a) if i did SELECT * FROM that should be fine right?
proven arrow
Yes, consider everything inside the FROM another table which we have called result
It's a derived table so like a temporary table for that query
proven arrow
Just a way of giving that row number column a name/alias
slender rose
so it printed this (783551897978011679, 51671, 12, 5)
so 5 is my position, correct?
proven arrow
If that is how you positioned the column, in the select then yeah
slender rose
thats how my colums look like
id exp level
proven arrow
I meant in the Query
slender rose
oh well like this
await cur.execute("""SELECT * FROM (
SELECT *, ROW_NUMBER() OVER(ORDER BY level DESC) AS position
FROM activity
) result
WHERE id = 663786070722215947""")```wait i think im still a bit confused
the person who's supposed to be on 1 shows 5
and it shows 5 for my id as well
my id: (783551897978011679, 51671, 12, 5)
other person's id: (663786070722215947, 72751, 12, 5)
how's both person on rank 5
oh wait
nvm
thanks a lot
also in postres a string would be varchar?
string might contain all sorts of character such as ,.!^&$
proven arrow
Stored as a string yes
slender rose
does varchar have any character limit?
proven arrow
Yes it's varchar(n)
slender rose
so like we can set limit to whatever i want? for instance how about 1500 characters
like 1500 or 2000, since its supposed to store tags, same as !tag of python bot
proven arrow
Yeah or use TEXT then in that case
slender rose
i thought text couldnt have special characters in them?
cause in tag the message could contant all sorts of letters/numbers/signs
proven arrow
I think postgres TEXT type is varchar but just unlimited length. You may have to double check since I don't really use postgres much, so I might be incorrect.
proven arrow
i thought text couldnt have special characters in them?
It can
slender rose
ohhh
torn sphinx
hey guys,
so i am stuck at a place so what i am trying to do is like i want to remove a row from my db but im unable to do, so it works like
await self.bot.db.execute("INSERT INTO todo(user_id, todo, time, jump_url) VALUES ($1, $2, $3, $4)", ctx.author.id, todo, datetime.utcnow(), ctx.message.jump_url)
this is the saving thing ^
i want to make a command which can remove a todo how can i do that?
if a do like todo remove 5 and it should remove 5th index of the todo from ther user's todo
torn sphinx
i got it
i made it an dict
and access all data
now idk whats happening
query12 = """CREATE TABLE IF NOT EXISTS todo (
user_id bigint,
todo varchar,
"time" timestamp without time zone,
jump_url varchar
)"""
DETAIL: Key (user_id)=(706449706447798283) already exists.
its making it primary key
Hi, I am using asqlite which is by Danny,
how do I make functions for my connector and cursor so that I can execute them repeatedly without having to keep on creating new connections and cursors?
my code:
async with asqlite.connect("database.db") as connection:
async with connection.cursor() as cursor:
...
and i am not even telling him to make it primary
fickle cloak
grim pier
Hello, what is the best way to store multiple data in a single collum in MySQL
torn sphinx
Is there a way to visualize how my DB structure/design looks like in SQLite?
proven arrow
Is there a way to visualize how my DB structure/design looks like in SQLite?
Do you want to view just a single table structure or an ERD of the entire database and its relationships between different tables?
torn sphinx
huh? Sorry but can u please explain what u just said? Im a newbie
proven arrow
What kind of structure do you want to see? What info do you want to visualise?
proven arrow
@torn sphinx Let me just give you both the ways and then you can try them out:
- To get a table description, and see the table schema (think of schema as like the table plan), then you can use the command
PRAGMA table_info(table_name);. To use this command you need to execute it against the database. See this link for more details, and some more helpful functions. https://www.sqlite.org/pragma.html#pragma_table_info - If you want to see a visual fancy/colourful plan (known as Entity Relationship Diagram) of how all the different tables in your database look like, including their column names, and how these tables are related to one another (if any such relation exists), then you would need some software that allows you to view this. You would need to download this software first. A good one is DBeaver.
proven arrow
Hello, what is the best way to store multiple data in a single collum in MySQL
You can store it as a list/array, or a serialised version of it if your database doesn't support arrays.
mighty echo
could someone help out with pandas question: https://stackoverflow.com/questions/65076439/how-to-get-rid-of-rows-with-pandas-in-a-csv-where-the-value-of-cells-in-a-specif
torn sphinx
ah thank u a lot @proven arrow
mental quiver
What exactly is the purpose of changing the page_size pragma in sqlite. Under what circumstances and such may one want to do such a thing, or care at all about the page size? @ me when answering.
proven arrow
@mental quiver Page size is a concept of memory management of operating systems. Changing the default size in sqlite can affect things like I/O operations on the page level. However, you should not need to worry about this or need to change it unless you have some serious performance issues. Just leave it as it is unless you know what you are doing.
Changing it can have both a positive/negative effect. But it's important to remember that databases are smart enough to pick the correct page size for your hardware or system. So generally the default will give you the best possible I/O performance, and this is also what is recommended by sqlite.
eternal raptor
sql2 = 'SELECT cash FROM economy WHERE UserID = ?'
wynik2 = await cur.fetchone(sql2, member_id)
Traceback (most recent call last):
File "C:\Users\sebik\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "c:\Users\sebik\Desktop\discordbot\0512lptwbot\cogs\ekonomia.py", line 63, in utworzprof
wynik2 = await cur.fetchone(sql2, member_id)
TypeError: fetchone() takes 1 positional argument but 3 were given
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\Users\sebik\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 903, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\sebik\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 859, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\sebik\AppData\Local\Programs\Python\Python39\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: TypeError: fetchone() takes 1 positional argument but 3 were given
What database and library are you using?
eternal raptor
aiosqlite
full code
@commands.command()
async def utworzprof(self, ctx):
gildia = ctx.guild.id
# user = ctx.message.author.name
# role = discord.utils.find(lambda r: r.name == 'Zarabiacz', ctx.message.guild.roles)
# if role in ctx.author.roles:
# embed = discord.Embed(title="Bลฤ
d!", description=f"{user} masz juลผ utworzony profil na tym serwerze o ID: {gildia}.", color=discord.Color.red())
# await ctx.send(embed=embed)
#else:
member = ctx.message.author
member_id = ctx.message.author.id
async with aiosqlite.connect('C:\\Users\\sebik\\Desktop\\discordbot\\0512lptwbot\\lptwdb.db') as db:
cur = await db.cursor()
# sql1 = 'INSERT INTO economy (UserID, cash) VALUES (?, ?)'
# await db.execute(sql1, member_id, gildia, 0)
# await ctx.author.add_roles(role)
sql2 = 'SELECT cash FROM economy WHERE UserID = ?'
wynik2 = await cur.fetchone(sql2, member_id)
if not wynik2:
sql1 = 'INSERT INTO economy (UserID, cash) VALUES (?, ?)'
await cur.execute(sql1, member_id, gildia, 0)
embed = discord.Embed(title="Sukces!", description=f"Utworzono profil na serwerze o ID: {gildia}", color=discord.Color.blue())
await ctx.send(embed=embed)
await cur.commit()
else:
embed1 = discord.Embed(title="lptwIT", description="Sprawdลบ DM.", color = discord.Color.blue())
await ctx.send(embed=embed1)
embed2 = discord.Embed(title=f"Bลฤ
d!", description=f"{member.mention} masz juลผ utworzony profil.", color = discord.Color.blue())
await member.send(embed=embed2)
skip #
Now I'm writing smaller bot in sqlite
proven arrow
Ok I see
It's because you have to use execute to make your query
And then use fetchone to get that data
eternal raptor
so first execute, next fetchone?
proven arrow
Fetchone doesn't take any parameters in it
eternal raptor
example sql2 = '...'
query2 = await cur.execute(sql2, member_id)
query2_2 = await cur.fetchone(sql2)
?
proven arrow
Yeah, but your values need to be passed as an iterable like a list or tuple
So, await cur.execute(sql2, (member_id,))
eternal raptor
so without fetchone?
proven arrow
Read my messages again. You need fetchone to get the data, after executing, and as I already said fetchone doesn't take any parameters.
mental quiver
<@312049173895839746> Page size is a concept of memory management of operating s...
hmm. I basically have a wrapper for sqlite3 base module, that forms sqlite commands for me and all and gives results. I like OOP better. https://github.com/pmblue1/auxilus#sqliteobj-documentation
Essentially, I am working on taking it to the next level, and adding some pragmas and much more to it as well, and making better docs so that it is more usable by public. Do you think it is worth including page_size in the module at all?
Well, at least editting it that is?
eternal raptor
example:
sql2 = 'SELECT cash FROM economy WHERE UserID = ?'
wynik2_2 = await cur.execute(sql2, (member_id,))
wynik2 = await cur.fetchone(sql2)
this is ok, yeah?
proven arrow
hmm. I basically have a wrapper for sqlite3 base module, that forms sqlite comma...
I can probably take a look tomorrow, however some pragma related things like page or cache size should not be the concern of your module. It should be left for the user to configure themselves for their own environment.
mortal forum
Hi! I'm working on a small little project and am messing around with MongoDB
What would be a good way to store datetimes
I'm currently using this but was curious if there was a better solution
data = {'user': Member.id, 'reason': Reason, 'date': datetime.now().strftime('%Y-%m-%d %H:%M:%S')}
No, and for the third time, fetchone() doesn't take any parameters.
mental quiver
eternal raptor
but how bot will know what it must call/print ?
mental quiver
When you are fetching from the cur object you have there, it will be retrieving the first result of the query you executed
As said in the docs which I linked above.
eternal raptor
example:
sql2 = 'SELECT cash FROM economy WHERE UserID = ?'
await cur.execute(sql2, (member_id,))
wynik2 = await cur.fetchone()
this is correct?
mental quiver
one question is why do you have the indent
also wondering why you have that extra comma after member_id @eternal raptor
mental quiver
should be
uneven lava
If I use sqlalchemy, which is not asynchronous, need just I to put all db call in run_in_executor ?
mental quiver
How do async sqlite mods works on a basic level? Whats the difference in operation?
pale canopy
Why does sqlalchemy.create_engine("sqlite:///path/to/database.db") not create database.db in the specified path? it gives out no output and just doesn't create a db file
elder karma
in sqlite can you have a foreign key be a primary key to a table?
say i have a database with two tables. 1. defines a node type with some data. 2. defines edges between nodes in the format of (node a, node b) defines an edge a -> b.
in the second table i just wanted to make sure the node ids were ids from the first table
craggy girder
await conn.execute("UPDATE quirks SET messages=$3 WHERE userid=$1, guild=$2", message.author.id, message.guild.id, msg_count)``` why am i getting a postgres syntax error here?asyncpg.exceptions.PostgresSyntaxError: syntax error at or near ","```heres the full traceback pb await conn.execute("UPDATE quirks SET messages=$3 WHERE userid=$1, guild=$2", File "/home/spongy/.pyenv/versions/3.8.6/lib/python3.8/site-packages/asyncpg/connection.py", line 297, in execute _, status, _ = await self._execute(query, args, 0, timeout, True) File "/home/spongy/.pyenv/versions/3.8.6/lib/python3.8/site-packages/asyncpg/connection.py", line 1445, in _execute result, _ = await self.__execute( File "/home/spongy/.pyenv/versions/3.8.6/lib/python3.8/site-packages/asyncpg/connection.py", line 1454, in __execute return await self._do_execute(query, executor, timeout) File "/home/spongy/.pyenv/versions/3.8.6/lib/python3.8/site-packages/asyncpg/connection.py", line 1466, in _do_execute stmt = await self._get_statement(query, None) File "/home/spongy/.pyenv/versions/3.8.6/lib/python3.8/site-packages/asyncpg/connection.py", line 351, in _get_statement statement = await self._protocol.prepare(stmt_name, query, timeout) File "asyncpg/protocol/protocol.pyx", line 163, in prepare asyncpg.exceptions.PostgresSyntaxError: syntax error at or near ","
torn sphinx
Hello everybody I have a question concerning datastructurs in Python ! How do you know about data structure like XOR, List, etc... Thank you for reading me and respond to it ๐
torn sphinx
anyone online i wanna ask a query
you know that you can delete this msg and edit it ,right ?
proven arrow
in sqlite can you have a foreign key be a primary key to a table?
Yes it can. This would be a one to one relationship.
proven arrow
```py
await conn.execute("UPDATE quirks SET messages=$3 WHERE userid=$1, guild=$...
Your conditions in the WHERE clause need to be separated by logical operator like AND, and not a comma.
craggy girder
thanks a lot
torn sphinx
query12 = """CREATE TABLE IF NOT EXISTS todo (
user_id bigint,
todo varchar,
"time" timestamp without time zone,
jump_url varchar
)"""
oops sorry
i sent the wrong query
check now
idk its so glitchy like it happens if i do it first time it makes it primary key and if i delete and make it again with query it works fine
proven arrow
Most likely you are confusing yourself with some other query or error. Because the updated table has no primary keys.
You can see this dB fiddle where I enter the same insert twice, and it works fine, for that table. https://www.db-fiddle.com/f/ZGerw9x8eNETkywFnYuCF/0
crimson granite
lets say i have sales department, how would i go about keeping monthly sales record?
filling all the record into one cell would be bad i assume?
create "sales" table, relate it with department and add new row each month?
proven arrow
@crimson granite It depends what data you want to store. Is your sales data aggregated somewhere else externally and entered into the database once every month?
Because generally what you'd see is every time an individual sale is made its entered into the database. So each row in the sales/orders table will be a new sale which can be linked to a department table. Then when you want to view the monthly sales you would write a query that queries the tables to see monthly sales values.
But as for your question, yes you should have seperate tables for department and the sales, on whichever approach you choose to store the data (either monthly or per sale).
crimson granite
that makes sense thanks @proven arrow
uneven lava
How ? I didnโt understand how async-sqlalchemy work, the AsnycSession has no .query() method
sturdy kestrel
hey guys i m working with flask and have 2 classes in main.py which are tables. Also i have 2 SQLAlchemyes and i m trying to create them with python ...already have one sqlite3 database done lets say it's called producats where is "class product". so my problem is that i create another sqlite3 which is users but i have in database users table with products and another table with USER . how can i delete one of this tables with PYTHON or create just one table
torn sphinx
@bot.command()
async def mongodb(ctx, *, thing):
coll = db["moneys"]
post = {"_id" : 0, "test" : thing}
coll.insert_one(post)
await ctx.send("exited with positive results")
``` why is this code not working?pymongo btw
boreal niche
mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111) welp
proven arrow
`mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL serv...
Your error is saying the connection is being refused. Make sure your connecting to the address mysql is listening on, and make sure server is running. You can also try localhost as the address
rapid fractal
delicate fieldBOT
:incoming_envelope: :ok_hand: applied mute to @rapid fractal until 2020-12-13 16:00 (9 minutes and 58 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).
torn sphinx
god database is locked
pale citrus
i have a databse question in #help-candy
dark kernel
where is a good place to host large csv?
dapper plank
Does anyone know how I'd get a specific element from a document in pymongo? For example: I'd like to get the value from staff_role in this image. Please @ me
tawny fulcrum
Hey guys. I have data from solar installations which are stored on an FTP server. Data are sent every day at 3 a.m (csv files). I then have to analyze the data to see if the solar power plants are working properly. What kind of tools should I use to monitor the data (visualize, alerts and emails) ? Can I do it with only Grafana ?
warped frigate
are there any libraries that allow you to return data from postgres queries as a numpy array instead of python tuples/lists
mental quiver
I want to find the source code for the default Row factory for the sqlite3 module, and have failed to do so for over an hour now. Where can I find it, or how can I get it?
@ me when responding please.
south cradle
Sql is the best
vital edge
hello
I use mariaDB on an ovh server
And for the second time my databases have been hacked
After the first time I've set up a pass word and I don't have a clue how they manage to access it
Do someone have an idea please ?
I didn't have anything valuable and kept the code
But I'd like my db to be safe in the future, it's boring to fix it every time
proven arrow
@vital edge how do you mean you got hacked? What exactly happened? Can you show how some of your queries are constructed? Also do you connect over localhost or to a remote database, from your application?
vital edge
- hacked : all my tables are gone and there is another one called "Warning" in which a text explain that my tables are taken somewhere else and that I have to pay to retrieve them
- what do you mean by my queries construction ?
- it's a remote database hosted on an ovh server, I use mariadb.connect to access it
proven arrow
Can you show some example queries from your python code, so some execute functions?
In terms of connection I was asking if the application is running on the same server as the database. From your code do you connect to the database over localhost?
vital edge
my code is on localhost for my tests but is supposed to be on the same server when finished
I do connect to the server within the code
proven arrow
my code is on localhost for my tests but is supposed to be on the same server wh...
Right ok, well the query looks fine there, since it makes use of prepared statement (I'm hoping all your other queries have the same format, where you use placeholders and parameters for the values. If this is the case then you should be fine with that.
vital edge
yep they're all the same format
proven arrow
As for system security I would recommend you first secure your server. Maybe reset it from the admin panel, and choose secure passwords. You should also have some sort of firewall in place, for incoming connections (if your server communicates with the outside world). As for database security, don't use the root account. Instead make a new database user and only grant it the least privileges it requires to function for your application. And in your code you need to use this new user to connect to the database.
This will allow you to decide exactly what operations the user can perform. You can deny it things like DROP, CREATE etc. and only allow SELECT, INSERT, DELETE, or whatever the most basic privileges are required for your application to function.
And use environment variables to store you database credentials, which means don't directly put them into something like version control.
The issue could be somewhere else in your code as well, in case you have something like eval going on. Unless you know exactly how it happened the possible solutions can go on forever, so I'll just end it here, as it is something you need to maybe look into further, either through logs or some other method.
vital edge
okay thanks a lot
it gives me a lot of trails to follow
i'll do my research
I also had another problem that kept me bugging for a while and that have nothing to do with the hacking
I have 2 programs :
- a discord bot that create datas in a table from a database
- an html webpage that display the informations in the database
everything works fine
but I noticed that when I add a data with the bot and update my webpage it causes an error saying "access to socket denied"
Im not sure but I think it may come from the database side
if I close and re-open my 2 programs before updating the webpage everything works fine
I find it really strange
pale canopy
How do I check if a column already exists in sqlalchemy
proven arrow
@vital edge doesn't look like database error, however in the code you posted earlier your database library is not async, and your function had some await calls inside it. So if your application is also primarily based around sync then your database calls should be also.
Anyways I need to go sleep so maybe someone else can help you.
vital edge
@proven arrow what do you mean by the librairy not being async ? it may be the key
good night
torn sphinx
Any good dbs yall recommend? ||other than using sqlite3||
pale canopy
sqlalchemy mysql?
How do I get a thing from a row
like in my table
I a row called that's "email" and it has a password
how do I get that password in SQLAlchemy
I'm new
pale canopy
nevermind I did it
eager robin
Any good dbs yall recommend? ||other than using sqlite3||
postgres/asyncpg is the way to go
elder karma
postgres/asyncpg is the way to go
asyncpg is my favorite library by far
but aiosqlite isnt bad either if you dont want something so heavy
eager robin
true
mental quiver
At least from what I have seen, there seem to be few more basic wrappers for sqlite3 mod out there. Seems to be mostly just ORMs, rather than modules with far more simplistic functions that form and/or run the sqlite3 commands for you. Is this observation correct, or are there some I am missing?
pure cypress
Can you elaborate on what you have in mind? I can't picture what sort of functionality a wrapper would have that isn't something advanced like an ORM.
The Python DB API spec is already quite basic.
I don't know what else could be abstracted
mental quiver
Can you elaborate on what you have in mind? I can't picture what sort of functio...
I personally have a wrapper, where you provide a file name to init the main object. This object then has many functions, that create sqlite statements and run them. It largely works off of dictionaries. So to get all rows in a table where first_name column is Jeff, I would do db.select({"first_name": "Jeff"}), and it would return a list of either dicts or Row objects (depending on settings).
Wondering if there are other similar wrappers out there
pure cypress
So an abstraction that avoids using SQL?
mental quiver
Essentially lol
pure cypress
I lost connection, sorry. Have to retype my message.
The only similar thing I know of is SQLAlchemy. While it does have an ORM, I don't believe you're required to use it. It has an API to build SQL queries by chaining function calls in Python. It's reminiscent of LINQ in C# if you're familiar with that.
mental quiver
Huh, interesting
worthy pawn
hey. is there anything you can see wrong with this. when i execute it it gives an sql error in syntax ```py
self.cursor.execute(f"SELECT * FROM {table} WHERE {key} = '{val}' ORDER BY {order}")
ofc i input values that should work for the database
im using mysql
frigid glen
@worthy pawn assign the f string to a variable, log it before execution, then you know precisely how your strings are formatted before sent to the DB
worthy pawn
yep ok will try
carmine heart
scarlet nymph
I know this is dependent on a lot of factors, but can anyone give me some ballpark estimates for the time taken to add an index to a table? I am trying to index a datetime column, and the dialog box has been sitting there for like 10 minutes...
I assumed that the dialog would return immediately, and the actual index building would happen in the background
nm it timed out. I guess it wasn't even doing anything.
torn sphinx
speaking of indexes, i have question. Why not we just index everything like every column? Wont it be faster?
scarlet nymph
writes take longer
but yeah I think if you write once a day and read thousands of times a day, you probably want to index a lot
torn sphinx
oh thanks man, but it takes longer why?
scarlet nymph
I think because every time a new row is added, it must also be added to the index
and if there's 10 indexes, it has to add to each one
torn sphinx
yeah right ok i see
scarlet nymph
if adding a row take a millisecond, ok, but if adding to each index adds one milliseconds, suddenly your insert takes 10 ms
I pulled that number out of my butt, I have zero idea how long inserts take :p
torn sphinx
lament raven
Hey everyone, was looking for some help with pandas
so i want To update Status Text to "Turbine not in operation" and Service "Required" where Error > 3โ and โFaultMsgโ is TRUE. in my .csv file
my code look like this atm
f_reader = pd.read_csv('Turbine_Latest.csv', delimiter=';')
df = pd.DataFrame(f_reader)
df.loc[(df['Error'] >= 3) & (df['FaultMsg'] == 'TRUE'), 'Service'] = "Required"
df.loc[(df['Error'] >= 3) & (df['FaultMsg'] == 'TRUE'), 'Status Text'] = "Turbine not in operation"
df.to_csv("test1.csv")
my issue is that df.to_csv("test1.csv") dont save the changes made by df.loc
keen gorge
Does someone know a bit about aiomysql? Especially the autocommit, so does the autocommit always safes after a change on a database?
sonic whale
Hi. Let me ask you a question. If I were to create a DB that stores the schedule of each server, should I do this?
alpine dock
cursor.execute(f'INSERT INTO users (username, password) VALUES("{user}", "{passwdn}");') Why this is not inserting the values into the table?
It only works if i do it manually on my vps
torn sphinx
hi, I am using a function and using it as a loop to keep my database open so that I don't have to keep on creating connections and cursors in all of my commands and events, but the connection and cursor variable won't work, and blocks any command or event that has the variables from working
my code:
# function
async def db_database():
# connection
async with asqlite.connect("Discord bot database.db") as connection:
# cursor
async with connection.cursor() as cursor:
BOT.connection = connection
BOT.cursor = cursor
BOT.loop.run_until_complete(db_database())
@BOT.command()
async def register_me(ctx):
await BOT.cursor.execute("INSERT INTO register (user_id, guild_id) VALUES (?, ?)", (ctx.author.id, ctx.guild.id))
await BOT.connection.commit()
await ctx.send("{} has been registered".format(ctx.author))
@torn sphinx because your using the with block, which closes the connection when the block completes. So make the connection without it. Also your cursor should not be global. It should be created when you need it and disposed of after.
torn sphinx
I see, but asqlite must have the with block, otherwise
async asqlite.connect("database.db") = connection
isn't valid syntax
so what should I do instead?..
proven arrow
No it should work perfectly fine without it
connection = await asqlite.connect(...)
proven arrow
Hi. Let me ask you a question. If I were to create a DB that stores the schedule...
What do you mean by schedule of each server? What is a schedule? How many schedules can each server have?
torn sphinx
wait, should I use cursor = connection.cursor() or cursor = await connection.cursor()?
proven arrow
Await
tall olive
Is this a good forum to talk about dataclasses, type checkers and SQL?
vital edge
This worked few minutes ago but when I converted my code into async it started doing this error
But I didn't change the query or the formats,
The only thing I changed is adding await self.bot and there is still as much gap than argument given
I don't understand im confused
eager robin
what do you need help with
grim pier
When connecting to MySQL DB in python should it be awaited? and used with async?
sql = await connect_to_sql_db()
async with sql.cursor() as cursor:
``` it throws all my indentation out ๐@torn sphinx yes i am ๐
Any ideas why im getting this error?
Ignoring exception in command buypack:
Traceback (most recent call last):
File "C:\Users\Jorda\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "C:/Users/Jorda/PycharmProjects/BanditBot-2.5/banditbot.py", line 1350, in buypack
await cursor.execute(f"INSERT INTO orders (SteamID, PlayerName, OrderCode, ScumCode, Requested, Time)"
TypeError: 'NoneType' object is not subscriptable
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\Users\Jorda\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\Jorda\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 859, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\Jorda\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: TypeError: 'NoneType' object is not subscriptable
Process finished with exit code -1
await cursor.execute(f"INSERT INTO orders (SteamID, PlayerName, OrderCode, ScumCode, Requested, Time)"
f"VALUES ('{data[0]}','{data[1]}','{uniquecode}','{scumcode}','False','{time}')")```oh really?
sure
async with db.cursor() as cursor:
await cursor.execute(f"SELECT SteamID,PlayerName FROM players WHERE DiscordID ='{ctx.author.id}'")
data = await cursor.fetchone()
# Creating the order log.
await cursor.execute(f"INSERT INTO orders (SteamID, PlayerName, OrderCode, ScumCode, Requested, Time)"
f"VALUES ('{data[0]}','{data[1]}','{uniquecode}','{scumcode}','False','{time}')") # THIS IS THE LINE OF THE ERROR
await db.commit()
else:
await ctx.channel.send(f"{ctx.author} Pack does not exist.")```it was all working perfectly untill i added the "requested" field in
@torn sphinx Any ideas? ๐
brazen charm
Ig yeah
torn sphinx
https://github.com/ibmdb/python-ibmdb/wiki/APIs is this the right place to learn about the python DB2 API?
And before anyone asks, I am required to use DB2 by my university, but they allow us to do our assigments in either python or java, so I went for the obvious choice.
Cause let's be real, who wants to program a website in java these days?
Yeah we're supposed to make a website that does some fancy stuff as part of a practical experience thing
torn sphinx
hi, as SQlite 3 returns a fetched value (retrieved by fetchone()) in a tuple, I am using in instead of ==. But, the if-statement won't return true, and instead keeps moving straight to the else statement. Why is this?
my code:
import sqlite3
connection = sqlite3.connect (
"people.db"
)
cursor = connection.cursor()
cursor.execute(
"SELECT id FROM customers WHERE forename = 'Mehah'"
)
iD = cursor.fetchone()
cursor.execute(
"SELECT forename FROM customers WHERE id = 45678219"
)
forename = cursor.fetchone()
print(iD[0])
print(type(iD[0]))
print(forename[0])
print(type(forename[0]))
if 45678219 in iD and "Mehah" in forename:
print(True)
else:
print(False)
cursor.close()
connection.close()
misty crane
so I am repetitively new to using databases and I am using sqlite3 and I just want to insert 1 thing into a specified column. Its probably really simple, but I cant figure it out.
I have
c.execute("INSERT INTO main (guild_id) VALUES (?)", (gid,))
nvm, I just noticed what I had was correct and I just had a silly spelling error in a variable ๐
glass gorge
i was curious
i thought using flask migrate
would update the models in my db
INFO [alembic.autogenerate.compare] Detected added table 'notification_content'
INFO [alembic.autogenerate.compare] Detected added table 'roll_call'
INFO [alembic.autogenerate.compare] Detected NULL on column 'phone_survey.upload_timestamp'
INFO [alembic.autogenerate.compare] Detected added column 'users.active'```but then i check my mysql db
and the tables haven't changed at all, though I do see some alembic tables
keen gorge
can aiomysql return a dict?
proven arrow
can aiomysql return a dict?
Are you wanting it so you can access the values by name?
keen gorge
Are you wanting it so you can access the values by name?
yeah
async with self.bot.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute("SELECT * FROM profil WHERE uid=%s", (user.id))
desc = await cur.fetchone()```so this is my code here
proven arrow
Ok so to customise the output row, and have a custom way of returning results you can use row_factory of pythons sqlite module. aiosqlite also will have support for this as well. There is a good example here that shows how to do this: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory
However, a better option is to use the already optimised Row class, which is basically a row_factory as above but instead of writing your own its already been done for you. https://docs.python.org/3/library/sqlite3.html#sqlite3.Row
The sqlite.Row class also allows you to get values from case insensitive keys, so both ID and id would work
keen gorge
Oh, thats so nice, but I'm using aiomysql :l
proven arrow
Its the same, since aiosqlite is based of this anyways. You just need to add this to your connection object: connection.row_factory = aiosqlite.Row
proven arrow
@keen gorge Actually ignore everything i just said above. I thought you were using sqlite, and not mysql.
My bad ๐ฌ
proven arrow
Instead you can use this: https://aiomysql.readthedocs.io/en/latest/cursors.html#DictCursor
keen gorge
ahhh great too thank you for helping me out
proven arrow
That is what you need, see the examples provided. Its similar to what i said above, but now you can do
cursor = await conn.cursor(aiomysql.DictCursor)
torn sphinx
Whats wrong with this code?
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="Admin"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE social_system")
mycursor.execute("SHOW DATABASES")
for x in mycursor:
print(x)
narrow schooner
Whats wrong with this code?
import mysql.connector
mydb = mysql.connector.conn...
!code
delicate fieldBOT
torn sphinx
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="Admin"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE social_system")
mycursor.execute("SHOW DATABASES")
for x in mycursor:
print(x)
Whats worng with that^
compact tiger
hey
I have heard that there's mysql for py
cuz I have always been using
SQLite
vale void
what can i do ??
compact tiger
so I need to start a MYSQL server or with a single command I can enable it ?
just like sqlite?
torn sphinx
When I am tring to connect with my mongodb, this just does not work. 3 hours ago everything was fine, now when I run the same code, nothing happend, and after 30 seconds I get timeout error.
proven arrow
@torn sphinx The code looks fine, any errors?
proven arrow
so I need to start a MYSQL server or with a single command I can enable it ?
You need to have the MySQL server running
torn sphinx
<@456226577798135808> The code looks fine, any errors?
yes
im just using workbench tho
thanks anyways
sick nacelle
I was trying to setup a mock datestore using pgcli but when I try extracting the data from my csv I keep getting a can't open filename.csv. Can someone help me with this?
https://cdn.discordapp.com/attachments/668636896897007627/788546837005991936/unknown.png
flat isle
Does anyone have any recommendations for a database for a Discord Pokemon Bot (i mean technically it's gonna be yo-kai watch but uh i just need pokemon-based recs)
wooden reef
I was trying to setup a mock datestore using pgcli but when I try extracting the...
One thing that caught my eye here is there is no blankspace before DELIMITER:
...de_project/setup/raw_input_data'Delimiter
Instead of:
...de_project/setup/raw_input_data' <--------> Delimiter
Then again, this can be caused by the lack of permissions on the file too.
I would check the appropriate chmod for this.
torn sphinx
hey there i am trying to import sql.connector but it showing error that sql not found and i am using Ubuntu WSL on win 10, please help me...
wooden reef
hey there i am trying to import sql.connector but it showing error that sql not ...
What's the error message?
I think you need to install both an ubuntu sql-client with apt, and python sql-connector with pip (or whatever you are using)
for MySQL for example, it would be:
$ sudo apt install mysql-client
$ pip3 install mysql-connector
for MSSQL you would need a driver like odbc and a python package like pymssql or pyodbc
$ sudo apt install libmyodbc
$ pip3 install pyodbc
Guys, I need a little help with an excel file. Can someone help?
wooden reef
Guys, I need a little help with an excel file. Can someone help?
I may be able to if it's something quick
wooden reef
shoot bud
pale ermine
Idk where to ask but im trying to get a list from a self.db and im really new to python.
anyway its a fork of a modmail bot plugins and im trying to have a list of all the names of every tag i have in the plugins database
@tags.command(name='list')
async def list_(self, ctx: commands.Context):
"""
Show list of commands
"""
tag = await self.find_db(name=name)
if tag:
await ctx.send('Tags: ' + ', '.join(tag))
else:
await ctx.send('No tags saved')``` this is something what im trying to add but idk what im doing wrong @pale ermine what database does it use? And can you show the find_db method
pale ermine
class TagsPlugin(commands.Cog):
def __init__(self, bot):
self.bot: discord.Client = bot
self.db = bot.plugin_db.get_partition(self)```
```py
async def find_db(self, name: str):
return await self.db.find_one({"name": name})```
um I hope this is what you need idk unless you are asking what it imports from which is discord an json ig?
proven arrow
Looks like it uses a mongo dB database, so do you have that all setup?
pale ermine
yeah
like all the tags and such work im just having trouble making a new command to show all the commands in the database
boreal niche
mysql.connector.errors.DatabaseError: 2006 (HY000): MySQL server has gone away
compact tiger
You need to have the MySQL server running
okey thanks
If I have a web with a mysql server running ? should I connect to it with the url ? or ip?
cuz when connecting with php files in the same web, I connect it just putting localhost as host.
proven arrow
like all the tags and such work im just having trouble making a new command to s...
I'm not so sure about MongoDB because I never have used it, or the driver's it uses with python. Maybe you can ask again and wait for someone who has mongo, and it would also help if you can share how your collections/documents are structured.
proven arrow
If I have a web with a mysql server running ? should I connect to it with the ur...
You can connect through localhost
pale ermine
oke thanks for the information! c:
proven arrow
mysql.connector.errors.DatabaseError: 2006 (HY000): MySQL server has gone away
Looks like you did something which closed connection to the server, or the connection was lost some other way.
boreal niche
Looks like you did something which closed connection to the server, or the conne...
Nvm looks like mysql has a timeout for when it's not being used.In this case it was set to 120mins in seconds.
compact tiger
You can connect through localhost
hmm okey nice
sonic whale
Hi. I'm going to use Python to store each discord server data in mysql DB. But if there is an emoticon in the server name, an error appears. How can i do?
random tartan
:idk:
sonic whale
Data type is char.
random tartan
i said idk
sonic whale
I solved it. It works because i changed the format of utf8 to utf8mb4.
normal glade
def addBalance():
with sqlite3.connect("users.db") as db:
c = db.cursor()
name = input("Where did you spent your money?\n")
price = input("How much did you spend?\n")
dateTime = input("When did you spend it? dd/mm/yyyy\n")
insertData = """INSERT INTO income(name,price,date)
VALUES(?,?,?)"""
c.execute(insertData[name,price,dateTime])
print(name, price, dateTime)```Why is this a thing?
proven arrow
Why is this a thing?
your missing a comma to seperate those two values.
should be c.execute(insertData, [name,price,dateTime])
normal glade
Omg, thanks
def addBalance():
with sqlite3.connect("users.db") as db:
c = db.cursor()
name = input("Where did you spent your money?\n")
price = input("How much did you spend?\n")
dateTime = input("When did you spend it? dd/mm/yyyy\n")
insertData = """INSERT INTO income(name,price,date)
VALUES(?,?,?)"""
c.execute(insertData,[name,price,dateTime])
print(name, price, dateTime)```Then I get this for some reason
c.execute("""
CREATE TABLE IF NOT EXISTS income(
userID INTEGER PRIMARY KEY,
name NOT NULL,
price INTEGER,
date NOT NULL);
""")```Thats most likely because your schema has changed since you first made the table.
normal glade
selectData = """SELECT name,price,date FROM income"""
data = c.fetchall()
print(data)```Then this prints an empty list
hollow quail
I need to connect a database to python - it is a django project. I would like to use postgresql. Now i am a little stuck on how you do requests? anyone have any documentatiion on this?
this is my project
vale sphinx
I have a 2 million line JSON file, which I need to load, but it is way too large for my computer to handle. Is there any way to load it one section at a time, rather than loading it all, and then using the sections?
torn sphinx
for MySQL for example, it would be:
```bash
$ sudo apt install mysql-client
$ pi...
this is what error is like bro, i had already installed packages you asked me to install
i tried importing mysql.connector but same error is coming
please help ,e
me*
elder ferry
good morning, just wondering if anyone help me? I'm trying to create the WorksIn relation but I want to know how best to add the eid column from Employee relation and did column from Department into the WorksIn relation? I've got the Employee relation and Department relation created in MySQL
proven arrow
good morning, just wondering if anyone help me? I'm trying to create the WorksIn...
What kind of relation are you wanting? Many to many?
elder ferry
so eid and did will be the primary keys in the WorksIn relation and each employee will have a date for which they started working at that department
can you only add columns from one other table? or can you add multiple columns from multiple tables into one new table?
proven arrow
What you have shown in the image is fine.
elder ferry
sorry my bad I have only created the Employee and Department relation
I don't know how to create the WorksIn relation
the attributes in the brackets are the names of the columns
proven arrow
ok i see
elder ferry
I need to find the a way to add the eid attribute from Employee and did attribute from Department and add those attributes to a new table called WorksIn
does that make more sense? my bad
and eid and did will then act as a primary key for WorksIn relation
proven arrow
CREATE TABLE employee_department (
emp_id INTEGER NOT NULL,
dep_id INTEGER NOT NULL,
since VARCHAR(255) NOT NULL
PRIMARY KEY (emp_id, dep_id),
FOREIGN KEY (emp_id) REFERENCES employee (eid) ON DELETE CASCADE,
FOREIGN KEY (dep_id) REFERENCES department (did) ON DELETE CASCADE
)
@elder ferry Try that, you can change the table name but generally for this kind of relationship thats how you would name it
You can also add a surrogate key, if you want which can be useful sometimes
Oh and i forgot to add your since column
elder ferry
ahhh thank you! I'll give it a go now. Just another question if you don't mind? do you where you have called the attributes emp_id and dep_id? do those attribute names have to be different from eid and did? like you cannot have the same names from another table?
proven arrow
ahhh thank you! I'll give it a go now. Just another question if you don't mind? ...
You can have the same, but i generally keep them different to avoid ambiguities
elder ferry
ahh that makes sense thank you. I'll give that code a go and i'll let you know the outcome
proven arrow
I updated the code above to include the since column, you can change the type of it as you need
elder ferry
ah thank you!
magic violet
Good afternoon,
Been stuck on this exercise for hours here.
This is the code I've been trying to use.
SELECT racer_id, segment_id, MIN(time)
FROM racing
GROUP BY racer_id, segment_id, time
ORDER BY MIN(time) ASC;
Any help is very appreciated!
proven arrow
<@!613362435860070414> hi I got this error
Can you show how you made the other tables?
Also make sure the keys exist in the parent tables, and the col types are the same
proven arrow
That is hard to read from screenshots, but i see you have UNSIGNED for the column ids
So you also need to add this into the junction table
proven arrow
Good afternoon,
Been stuck on this exercise for hours here.
This is the code I'...
What is not working for you?
modern parcel
hi i have a question
i have two tables, PromotionItem(itemno, startdate, enddate, promotiondiscount)
and Item(itemno, itemdesc, catID, itemprice)
does that make promotionitem a weak entity?
magic violet
What is not working for you?
Hi, just solved it! Had to remove 'time' from the GROUP BY line.
torn sphinx
good evening, i am stuck on a small problem form 4 hr actually i am unable to run my CGI enabled python file with python3 and when i am trying to import mysql.connector it shows me a error missing module but when i am trying to run the same with direct method it works fine.. PLEASE HELP ME!
proven arrow
<@!613362435860070414> seems to still throw up an error
Right yeah because this is now a syntax error. For some reason you have added an extra openeing bracket before the table name.
Also unsigned should be before not null
elder ferry
ahhhhhh got ya my bad let me amend
ahhhh thank you it has accepted it now! so if I was to update an entry in the Employee relation, will it automatically update in the WorksIn relation?
@proven arrow
and then I just do the Alter table command and add in the "Since" column
proven arrow
ahhhh thank you it has accepted it now! so if I was to update an entry in the Em...
Do you mean if you update the primary key field in employee then you also want to it updated in worksin?
elder ferry
Do you mean if you update the primary key field in employee then you also want t...
yes that's correct and the same with the primark key in Department
will that code you provided do that?
proven arrow
No
If you want that then you have to also add ON UPDATE CASCADE to the referencing column
But i don't see a need for that because generally employee/department id should not change. And its probably a good idea to remove on delete cascade if you want the data to remain if any parent rows are deleted.
elder ferry
ahhhh okay that makes sense. So if a new entry was added to the employee and department relations will this also be added to the WorksIn relation?
or would you need ON UPDATE CASCADE to do that?
proven arrow
ahhhh okay that makes sense. So if a new entry was added to the employee and dep...
No. I think you are misunderstanding how it works. Your employee/department table have no knowledge that they are linked together. Your worksin table is a junction/pivot table that sits in the middle and links these two.
The worksin table is there to only link data between the two tables, so everytime you want to form a relationship you need to enter it into the WorksIn table
elder ferry
ah okay. Just to clarify, so say a new employee comes to work for the company, their information will be inputted into the employee relation right? so they will automatically be given an eID. Would that eID then show up in the WorksIn relation? or would you have to manually enter that eID into the worksIn table?
proven arrow
The record will need to be added to the worksin table as well.
Your application should normally do this at the same time (as seperate queries), if they are to be assigned a department when their account is created.
elder ferry
Ah I see, thank you for the clarification.
torn sphinx
Pls help with
Pymongo
import pymongo
from pymongo import MongoClient
intents = discord.Intents.all()
bot = commands.Bot(command_prefix="!", help_command=None, intents=intents)
cluster = MongoClient("mongodb+srv://stuff@morwetsts.a4kmo.mongodb.net/stuff?retryWrites=true&w=majority")
db = cluster["harckepys-slave"]
@bot.command()
async def mongodb(ctx, *, thing):
coll = db["moneys"]
post = {"_id" : 0, "test" : thing}
coll.insert_one(post)
await ctx.send("exited with positive results")
@tasks.loop(seconds=5)
async def status():
await bot.change_presence(activity=discord.Game(next(statuses)))
```how ould i change this
flint imp
from motor import motor_asyncio
intents = discord.Intents.all()
bot = commands.Bot(command_prefix="!", help_command=None, intents=intents)
cluster = motor_asyncio.AsyncIOMotorClient("mongodb+srv://stuff@morwetsts.a4kmo.mongodb.net/stuff?retryWrites=true&w=majority")
db = cluster["harckepys-slave"]
@bot.command()
async def mongodb(ctx, *, thing):
coll = db["moneys"]
post = {"_id" : 0, "test" : thing}
await coll.insert_one(post)
await ctx.send("exited with positive results")
@tasks.loop(seconds=5)
async def status():
await bot.change_presence(activity=discord.Game(next(statuses)))```
@torn sphinx
true kelp
hey guys im very new to this i need some help ```python
import sqlite3
conn = sqlite3.connect('Passwords.db')
c = conn.cursor()
ph = "ph"
c.execute("INSERT INTO password VALUES (ph ,ph ,ph ,ph )")
conn.commit()
conn.close()
@stark lagoon
i like just started learning db tonight at 3am now its 5am
@sweet wolf
?
rough hearth
@true kelp Please stop pinging random people.
true kelp
ok
rough hearth
Looks like you want to use f-strings
rough hearth
!e
ph = 'hi'
sql_statement = f'INSERT INTO password VALUES ({ph}, {ph}, {ph}, {ph})'
print(sql_statement)
@rough hearth :white_check_mark: Your eval job has completed with return code 0.
INSERT INTO password VALUES (hi, hi, hi, hi)
remember that you also need the f in front of the string
true kelp
wym
rough hearth
f'INSERT INTO password VALUES ({ph}, {ph}, {ph}, {ph})' there's a lower case f before the start of the string
that's what tells Python that it's an f-string.
rough hearth
Otherwise it will print INSERT INTO password VALUES ({ph}, {ph}, {ph}, {ph}) literally
No problem!
true kelp
cya later
rough hearth
Just to reiterate, all of our staff members are volunteers, so don't ping people randomly to draw attention to questions. Though you can (and should) ping the moderators if there's an urgent moderation issue.
true kelp
ok i just saw the (helper) thing so sorry about that wont do again
proven arrow
Also it's not a good idea to use f strings btw for your queries
burnt turret
yeah, check pinned messages for an explanation as to why
rough hearth
ok i just saw the (helper) thing so sorry about that wont do again
No problem. Thanks for understanding ๐
proven arrow
If your just trying something out I guess it's fine, but not good practice so try not to use in actual code.
true kelp
If your just trying something out I guess it's fine, but not good practice so tr...
when you mean actual code dose that mean things like password sheets or real databases
proven arrow
I mean its ok to use it if you want to see some proof of concept or to quickly test something. But don't use when the code will be used or is accessible by someone else
true kelp
k
import sqlite3
conn = sqlite3.connect('Passwords.db')
c = conn.cursor()
ph = str("placeholder")
c.execute(f'INSERT INTO password VALUES ({ph}, {ph}, {ph}, {ph})')
conn.commit()
conn.close()```c.execute(f'INSERT INTO password VALUES ({ph}, {ph}, {ph}, {ph})')
sqlite3.OperationalError: no such column: placeholder```Whatโs the pip?
proven arrow
Well that's another reason to not use F strings for queries ๐
true kelp
Well that's another reason to not use F strings for queries ๐
well then what do i use
proven arrow
Your error is because the values are not passed as the string type. So since your values are not in quotes it treats as a column name.
use this instead,
c.execute("INSERT INTO password VALUES (?,?,?,?)", (ph, ph, ph, ph))
That's the correct way to make queries, and it will handle the conversion for you.
true kelp
that makes alot of sence
torn sphinx
2020-12-16T18:59:50.762325+00:00 app[worker.1]: Traceback (most recent call last):
2020-12-16T18:59:50.762343+00:00 app[worker.1]: File "harckepys-slave.py", line 15, in <module>
2020-12-16T18:59:50.762548+00:00 app[worker.1]: cluster = motor_asyncio("mongodb+srv://anme:passsssk@kdjdjnhds.a4kmo.mongodb.net/harckepys-slave?
```bruh2020-12-16T18:59:50.762574+00:00 app[worker.1]: TypeError: 'module' object is not callable
```errorOHHHH, I RED CODE WRONG
And thatโs not my real pass btw
true kelp
is there away to add password id
import sqlite3
conn = sqlite3.connect('Passwords.db')
c = conn.cursor()
new_account = input(str("Website: \n"))
new_username = input(str("Username: \n"))
new_email = input(str("Email: \n"))
new_password = input(str("Password: \n"))
#account
#user
#email
#password
c.execute("INSERT INTO password VALUES (?,?,?,?)", (new_account, new_username, new_email, new_password))
print(c.fetchall())
conn.commit()
conn.close()```how do i stop it from dissconnecting ammediantly
spice mango
Keep doing work.
torn sphinx
thganks
@bot.command()
async def mongodb(ctx, *, thing):
coll = db["moneys"]
post = {"_id" : 0, "test" : thing}
coll.insert_one(post)
await ctx.send("exited with positive results")
``` this code still isnt workingcode ```py
import sqlite3
while True:
conn = sqlite3.connect('Passwords.db')
c = conn.cursor()
new_account = input(str("Website: \n"))
new_username = input(str("Username: \n"))
new_email = input(str("Email: \n"))
new_password = input(str("Password: \n"))
#account
#user
#email
#password
c.execute("INSERT INTO password VALUES (?,?,?,?)", (new_account,new_username,new_email,new_password))
print(c.fetchall())
conn.commit()
newp = input("Press enter to add a new password ")
conn.close() outputWebsite:
f
Username:
f
Email:
f
Password:
f
[]
Press enter to add a new password```
why did it not print
true kelp
i just relised
lol
well i just did an all nighter learning pythonmy 13yo brain gonna die today
python*
glass gorge
do you guys remove users from their table when they are to be deleted
or do you just mark their status as inactive
torn sphinx
```py
@bot.command()
async def mongodb(ctx, *, thing):
coll = db["moneys"]
pos...
h
help
brazen charm
do you guys remove users from their table when they are to be deleted
deleting them is basically the same as marking them as inactive
the DB will just mark the data as hidden usually
glass gorge
why?
brazen charm
GDPR and general privacy protects plus more hassle on the system
glass gorge
this is employment records
brazen charm
i would 100% delete after the user leaves then
sick nacelle
Can someone help me figure out why postgres can't open my csv?
brazen charm
last time i checked postgres doesnt natively support loading from a csv
sick nacelle
I was using pgcli and tried using the following script from a tutorial
CREATE SCHEMA retail;
CREATE TABLE retail.user_purchase (
invoice_number varchar(10),
stock_code varchar(20),
detail varchar(1000),
quantity int,
invoice_date timestamp,
unit_price Numeric(8,3),
customer_id int,
country varchar(20)
);
COPY retail.user_purchase(invoice_number,
stock_code,detail,quantity,
invoice_date,unit_price,customer_id,country)
FROM '/data/retail/OnlineRetail.csv'
DELIMITER ',' CSV HEADER;
And got that error
Thats the tutorial
mighty echo
x['db'] = pd.to_numeric(x[db]) NameError: name 'x' is not defined
mighty echo
`x['db'] = pd.to_numeric(x[db])
NameError: name 'x' is not defined`
I've read in a db and when I try to make them all numbers I keep getting a x is not defined error? would appreciate some helkp
civic rover
Should you leave out fields in a NoSQL database which are empty?
E.g. I have stored
Car1:
- color: blue
- id: 232
Car2
- color: null
- id: 143
wooden reef
this is what error is like bro, i had already installed packages you asked me to...
you would use
import pyodbc
or
import mysql.connector
depending on the DB type.
You can use:
$ pip3 freeze
to echo out installed python packages that are ready to import.
mysql-connector or pyodbc should be printed.
How do you execute uploadrqst.py?
python uploadrqst.py
or do you have a web server executing it?
Web server would be using a different python environment, and wouldn't have access to python packages that you installed for a user.
grim pier
await cursor.execute(f"SELECT SteamID,ScumCode,PlayerName,OrderCode,Requested FROM orders ORDER BY ID Desc")
data = await cursor.fetchone()
if not data[0]:
print("No orders requested.")
time.sleep(5)
return
anyone know what im doing wrong here?
Unhandled exception in internal background task 'main_loop'.
Traceback (most recent call last):
File "C:/Users/Jorda/PycharmProjects/BANDITBot delivery/main.py", line 140, in deliver_order
if not data[0]:
TypeError: 'NoneType' object is not subscriptable
c.execute("DELETE from password WHERE rowid = (?)", [delete])
it runs no syntaxbut it dose not delete
sinful condor
I dont know why I am getting syntax error I didnt get this before I started using repl.it hosting its on this line query = """ SELECT RANK() OVER(ORDER BY xp DESC) AS rank, userguild, xp, level FROM level;""" async with db.execute(query) as cursor2: I use aiosqlite
indenting is weird but its right
tepid cradle
@grim pier fetchone returns a tuple. When the query returns no rows, the tuple is empty. Therefore data[0] will give an error.
Just print out data after you run the fetch command and you'll be able to see.
meager vine
I have a task where I SELECT a subset of rows from a table and then do some process with data from each row and then update each row. Currently I do:
select_query
for row in selected:
do_the_thing(row)
update_query
I have been thinking about converting some of this code to a celery based task system and in this case I was thinking a new task would be triggered for each row of the select query. However, how would I best prevent two tasks running for the same row? Effectively, what is the best way to lock the row (and check it still meets the criteria of the SELECT query) at the start of each subtask?
This is is MSSQL
So can't use SELECT FOR UPDATE unfortunately
torn sphinx
you would use
```python
import pyodbc
```
or
```python
import mysql.connector
...
i am using a apache server to run the file sir
torn sphinx
you would use
```python
import pyodbc
```
or
```python
import mysql.connector
...
so how do i python install packages for apache server?
import mysql.connector
this package i am unable to import in python while using apache web server
i installed the package using pip
worthy pawn
is there something wrong with this syntax? ```py
SELECT * FROM 614714968755011597 WHERE game = 'minecraft' ORDER BY time
i get a mysql syntax error when runthe table name is correct and minecraft and time are a table and collumn respectively
eager robin
does that matter?
Why are you selecting * from that whole Integer?
worthy pawn
i need to select all entries from the table
yep i will
you mean py SELECT * FROM '614714968755011597' WHERE game = 'minecraft' ORDER BY time
@slow cove
it didnt work
i might have to check the database
worthy pawn
alternatively is there a way to store enough information about a discord guild so you can reference it? @slow cove
that is what i was thinking
i guess i can do that in the mean time before i figure something else out
yeh
elder ferry
Hello, could anyone tell me how to close this query? I thought the semi colon would end the query?
torn sphinx
Hello, could anyone tell me how to close this query? I thought the semi colon wo...
Which SGBD ?
Weird, it's a valid statement in mysql 8 https://dev.mysql.com/doc/refman/8.0/en/insert.html
misty crane
So Im using sqlite and I want to check if there is a value in a certain row
try:
c.execute("SELECT * FROM main WHERE guild_id=?", (gid,))
result = c.fetchone()
content = f"Guild id: {result[0]}\n"
print(f"Printing information into guild {gid}\n")
# create embed:
if records[1] is None:
content += "id of BeamMP server owner: Not Set\n"
else:
content += f"id of BeamMP server owner: {result[1]}\n"
if records[2] is None:
content += "Server is using default prefix: `!`"
else:
content += f"Prefix: `{result[2]}`"
save_embed.add_field(name='Information for this Guild:', value=content)
except sqlite3.Error as error:
...
Currently it crashs when it runs with no error, I dont know if its as simple as the way I am joining strings, or if its the way that I check if records[index] is None.
torn sphinx
So Im using sqlite and I want to check if there is a value in a certain row
```p...
what is your records object ??
torn sphinx
i am using a apache server to run the file sir
please helllppppp me
misty crane
Im so dumb
misty crane
what is your `records` object ??
I was supossed to be result
๐คฆโโ๏ธ
all that time
and that was the problem
torn sphinx
Happen to everyone one time
tribal hound
anyone know much about ayden payment encryption
wooden reef
so how do i python install packages for apache server?
You can use the same environment for apache - unless you installed your python packages with sudo (pip install ...). That's a bad practice.
When deploying, you need to point WSGIPythonHome variable to the python lib path - /usr/local/lib or ~/.local/lib
For now, make sure that www-data:www-data (apache user:group) have access and permissions to run python packages. Apache docs may help with that.
eager robin
anyone know what the query should be? await self.db.pool.execute('''TypeError: execute() missing 1 required positional argument: 'query'
await self.db.pool.execute('''
CREATE TABLE IF NOT EXISTS members(
id character varying PRIMARY KEY,
name TEXT
)
''')
Hey there is there anyone that could help me real quick, i have a piece of code for a SQLite database insert but its really in efficient as its one long line is there anyone that could show me a better way to do this so that it is efficient
con.commit()```Help
frigid glen
@torn sphinx with what?
torn sphinx
mother asynchronous for mongo db
it never works for me when decorated with @bot.command() (discord.py decorator)
motor*
viscid osprey
I have the following asyncpg based line of code:
await self.bot.db.execute("UPDATE member SET birthday_greeted_at = NULL WHERE date(birthday_greeted_at) < $1", date().today())
When this line of code is hit, I expected the Postgres date function to return the date portion of a timestamp column, so the update applies to all rows where the birthday_greeted_at date is less than today's date.
Instead, when I hit that line of code, I get the following error:
TypeError("function missing required argument 'year' (pos 1)")
@viscid osprey probably need to put 'date().today()' as string, otherwise python may treat as expected python function
viscid osprey
I want date().today() to be treated as a python function, one I expect to return today's date.
frigid glen
>>> from datetime import date
>>> date().today()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
TypeError: function missing required argument 'year' (pos 1)
Oh I see, it should be date.today() without parenthesis after date. That is working fine a few lines before
Thank you, @frigid glen
frigid glen
>>> print(date.today())
2020-12-17
What type of value will date(birthday_greeted_at) compare against?
viscid osprey
It is a date value, like that returned from date.today(). To be precise, it is a class 'datetime.date'
frigid glen
Ok as long as they are "comparable" ๐
torn sphinx
Can anyone send me the database commands for moba mysql?
Creation/table creation etc
frigid glen
@torn sphinx moba sql ?
proven arrow
Can anyone send me the database commands for moba mysql?
The SQL commands will always be the same, regardless of what system you are on.
torn sphinx
I still need em G haha, but thanks for pointing that out to me
proven arrow
Well there's many commands, and how you use each can depend slightly differently on what you are trying to do, so which one are you looking for specifically?
proven arrow
To create a database it's, CREATE DATABASE your_database_name;
To use that database it's, USE your_database_name;
To create table it depends on how you want to create that table, but for example it can be like CREATE TABLE table_name ( column_name INTEGER) ;
These links show more examples and might be easier to h understand. https://www.mysqltutorial.org/mysql-create-database/
https://www.mysqltutorial.org/mysql-create-table/
torn sphinx
@proven arrow thanks homie
torn sphinx
this comes from discord but its a data storage thing so ill post it here, [<Emoji id=720374376117043250 name='woah' animated=True managed=False>, <Emoji id=720375542934929498 name='kermitsipstea' animated=True managed=False>], how do I remake something like this without there being so many errors in my database
languid shale
sinful condor
what is wrong with this code?
query = """ SELECT RANK() OVER(ORDER BY xp DESC) AS rank,
userguild,
xp,
level
FROM level;"""
async with db.execute(query) as cursor2:```I am getting an error on ( on db.execute
async with db.execute(query) as cursor2:
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/context.py", line 41, in __aenter__
self._obj = await self._coro
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 175, in execute
cursor = await self._execute(self._conn.execute, sql, parameters)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 122, in _execute
return await future
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 98, in run
result = function()
sqlite3.OperationalError: near "(": syntax error```Your query itself is correct.
Maybe you have an older version of sqlite with python, which is why the window function doesnt work
I am unsure and have never looked into how python decides which sqlite version gets picked with each python release, but you can check your version by running sqlite3.sqlite_version
scenic bough
In mysql is there a way in table contraits to say
"This column(x) cannot be null if other column (y) is NOT null"
So basically
Can I put this logic inside the table? Or will I have to add this check in the code. ๐ค
proven arrow
You can yes. Mysql newer version supports check constraints. I think previous versions also supported but would just ignore it.
elder ferry
Hi could anyone help with a query? I need the correct query that will show me the names of the departments which sell blue products and do not have employees older than 40
my problem is that I can get it to show me the employees under 40 but the Jenny Store has an employee that is 45. So I need the right query that will omit jenny store from the table?