#databases | Python | Page 122

torn sphinx Dec 3, 2020, 8:51 PM

#

File not found [mongodbclient.0]
Neither environment variable nor client file exist
Abort```

#

I have no idea why it isn't connecting

vale sphinx Dec 3, 2020, 9:07 PM

#

I have no previous knowledge of bigquery, and I am trying to follow the instructions on this article https://github.com/PolyAI-LDN/conversational-datasets/tree/master/reddit to get a big dataset to train a chatbot. I have followed the instruction up until ```
Ensure you have a BigQuery dataset to write the table to:

DATASET="data"
bq mk --dataset ${DATASET?}``` but I do not understand how to enter the DATASET="data" command. Can anyone help?

GitHub

PolyAI-LDN/conversational-datasets

Large datasets for conversational AI. Contribute to PolyAI-LDN/conversational-datasets development by creating an account on GitHub.

balmy flame Dec 4, 2020, 5:17 AM

#

Can anyone help me with this error The INSERT statement conflicted with the FOREIGN KEY constraint "FK__DimOrders__DateK__3D5E1FD2". The conflict occurred in database "TeaNMe", table "dbo.DimDate", column 'DateID'.

torn sphinx Dec 4, 2020, 7:08 AM

#


    query9 = """CREATE TABLE IF NOT EXISTS autorole (
                guild_id bigint,
                all bigint[],
                human bigint[],
                bot bigint[],
                PRIMARY KEY (guild_id)
                )"""
    await bot.db.execute(query9)
    print(Fore.CYAN + f"[TABLE] prefix created")

#

  File "C:\Users\91782\Desktop\PostgreSQL\bot.py", line 97, in <module>
    loop.run_until_complete(start_db(bot))
  File "C:\Users\91782\AppData\Local\Programs\Python\Python38-32\lib\asyncio\base_events.py", line 616, in run_until_complete
    return future.result()
  File "C:\Users\91782\Desktop\PostgreSQL\bot.py", line 66, in start_db
    await create_tables(bot)
  File "C:\Users\91782\Desktop\PostgreSQL\cogs\utils\cache.py", line 167, in create_tables
    await bot.db.execute(query9)
  File "C:\Users\91782\AppData\Local\Programs\Python\Python38-32\lib\site-packages\asyncpg\pool.py", line 518, in execute
    return await con.execute(query, *args, timeout=timeout)
  File "C:\Users\91782\AppData\Local\Programs\Python\Python38-32\lib\site-packages\asyncpg\connection.py", line 272, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg\protocol\protocol.pyx", line 316, in query
asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "all"```

proven arrow Dec 4, 2020, 7:17 AM

#

@torn sphinx all is a postgres operator so it thinks your trying to use that

#

You can change column name to something else or escape it by putting double quotes around it

torn sphinx Dec 4, 2020, 7:19 AM

#

oh

proven arrow Dec 4, 2020, 7:19 AM

#

It's the same. Directly using execute will get aquire the connection for you.

torn sphinx Dec 4, 2020, 7:19 AM

#

so

    query9 = """CREATE TABLE IF NOT EXISTS autorole (
                guild_id bigint,
                "all" bigint[],
                human bigint[],
                bot bigint[],
                PRIMARY KEY (guild_id)
                )"""
    await bot.db.execute(query9)
    print(Fore.CYAN + f"[TABLE] prefix created")

proven arrow Dec 4, 2020, 7:20 AM

#

I didn't say all the columns, I just said one

torn sphinx Dec 4, 2020, 7:21 AM

#

alr

torn sphinx Dec 4, 2020, 7:49 AM

#

Here is a mini project I worked on today to learn data mining. Feel free to add/change something. https://github.com/murathany7/scraper

GitHub

murathany7/scraper

This script mines data from the biggest forum in Turkey. It gets around 150,000 questions and answers in Turkish. - murathany7/scraper

torn sphinx Dec 4, 2020, 9:01 AM

#

import pymongo
from pymongo import MongoClient
import os

mongodbclient_token = os.getenv("My mongo connection would be here")

if mongodbclient_token is None:
    try:
        with open('./mongodbclient.0', 'r', encoding='utf-8') as client_url:
            print("Using MongoDB cluster url provided in file")
            cluster = MongoClient(client_url.read())
    except FileNotFoundError:
        print("File not found [mongodbclient.0]")
        print("Neither environment variable nor client file exist")
        print("Abort")
        exit()
else:
    print("Using MongoDB cluster url provided in environment variable..")
    cluster = MongoClient(mongodbclient_token)```

#

File not found [mongodbclient.0]
Neither environment variable nor client file exist
Abort```

cloud pebble Dec 4, 2020, 9:31 AM

#

Hey guys, What do you think is the best way to connect the a flask app with mysql?
should I use Flask-SqlAlcehmy? or Should I go for Flask-MySqlDB?
In my case the database is already created in Mysql and tables are already connected with each other using foreign key.
Also I got this suggestion that I should use phpmyadmin? I have no idea what's that and leads on this?

cloud pebble Dec 4, 2020, 10:08 AM

#

I tried connecting MySql to the flask app.
my port number is 3307, I checked it.
here's the Python code:-

from flask import Flask, request, render_template, url_for, redirect
from flask_mysqldb import MySQL
import yaml


app = Flask(__name__)
db=yaml.load(open('db.yaml'))
app.config['MYSQL_HOST']=db['mysql_host']
app.config['MYSQL_USER']=db['mysql_user']
app.config['MYSQL_PASSWORD']=db['mysql_password']
app.config['MYSQL_DB']=db['mysql_db']
app.config['MYSQL_PORT']=int(db['port'])



mysql=MySQL(app)



@app.route('/',methods=["POST","GET"])
def login():
    if request.method =="POST":
        coachDetails=request.form
        name=coachDetails['username1']
        passw=coachDetails['password1']
        conn=mysql.connection()
        cur=conn.cursor()
        cur.execute("INSERT INTO CoachData(cname,cpass) values (%s, %s)",(name,passw))
        mysql.connection.commit()
        conn.close()
        return 'success'
    else:
        return render_template('reg.html')

@app.route('/show')
def user():
    return render_template('show.html')




if __name__=='__main__':
    app.run(debug=True)

here's the Db.yaml code:-

mysql_host: 'localhost'
mysql_user: 'root'
mysql_password: 'yash'
mysql_db: 'loginEx'
port: '3307'

Here's the stack trace:- https://dpaste.org/uNfD

feral thorn Dec 4, 2020, 11:22 AM

#

how can i dump my whole database on postgres? OS - Ubantu

torn sphinx Dec 4, 2020, 1:06 PM

#

feral thorn how can i dump my whole database on postgres? OS - Ubantu

pg_dump name_of_database > name_of_backup_file

feral thorn Dec 4, 2020, 1:14 PM

#

did that but i don't know where it dumped file

#

i tried searching but couldn't find

#

found that..thanks

lost spear Dec 4, 2020, 2:29 PM

#

Anyone with experience with SQLAlchemy/postgres that canand wants to help me understand some things? DM me. Cheers.

torn sphinx Dec 4, 2020, 3:02 PM

#

Hi, does any awesome person know how to replace the ****** in \r\n "geography": ******, with the value 'US'
(from the country variable)????

country = 'US'

payload = "{\r\n    \"groupBy\": \"BgtOcc\",\r\n    \"timePeriod\": {\r\n        " \
        "\"from\": \"2010-01-01T00:00:00\",\r\n        \"to\": " \
        "\"2020-01-01T00:00:00\"\r\n    },\r\n    \"queryString\": \"([nationwide]: " \
        "\\\" nationwide  \\\") AND ([BgtOccFamily]: \\\"Hospitality, Food, " \
        "and Tourism\\\")\",\r\n    \"geography\": ******,\r\n    " \
        "\"includeTotalClassifiedPostings\": true,\r\n    " \
        "\"includeTotalUnclassifiedPostings\": true,\r\n    \"offset\": 0,\r\n    " \
        "\"limit\": 1000\r\n}"

pearl laurel Dec 4, 2020, 3:38 PM

#

I don't get why mysql does not update the bank amount in the withdraw function, but it works in the deposit function.

code:

    def deposit(self, id, amount):
        cash = int(self.get_cash(id))
        bank = int(self.get_bank(id))

        newCash = cash - amount
        newBank = bank + amount

        updateCash = f"UPDATE user_economy SET user_cash={newCash} WHERE user_id={id}"
        updateBank = f"UPDATE user_economy SET user_bank={newBank} WHERE user_id={id}"

        self.mycursor.execute(updateCash, updateBank)
        self.mydb.commit()

    def withdraw(self, id, amount):
        cash = int(self.get_cash(id))
        bank = int(self.get_bank(id))

        newCash = cash + amount
        newBank = bank - amount
        print(newBank)

        updateCash = f"UPDATE user_economy SET user_cash={newCash} WHERE user_id={id}"
        updateBank = f"UPDATE user_economy SET user_bank={newBank} WHERE user_id={id}"

        self.mycursor.execute(updateCash, updateBank)
        self.mydb.commit()

proven arrow Dec 4, 2020, 3:53 PM

#

@pearl laurel execute can only execute one query, your passing 2. And you shouldn't be using the cursor like that, instead create it when you need, and dispose of it when done.

pearl laurel Dec 4, 2020, 3:56 PM

#

how do you dispose a cursor? Define it for each function?

rare belfry Dec 4, 2020, 3:57 PM

#

can anyone here help me in making a query using django orm ?

#

I have a model with a boolean field and I want to generate a query set with True value at top and False value at bottom

proven arrow Dec 4, 2020, 4:00 PM

#

pearl laurel how do you dispose a cursor? Define it for each function?

call close() on it

cursor = mydb.cursor()
cursor.close()```

pearl laurel Dec 4, 2020, 4:06 PM

#

and lastly how to i execute more than 1 query?

proven arrow Dec 4, 2020, 4:08 PM

#

just use two seperate execute calls since your only making 2 queries.

#

@rare belfry You would need to order by that boolean column. You can have it order by DESC, to give true at top

pearl laurel Dec 4, 2020, 4:09 PM

#

proven arrow just use two seperate execute calls since your only making 2 queries.

is it possible to make it into a small script and run it?

proven arrow Dec 4, 2020, 4:10 PM

#

Your only making 2 queries, why a script?

pearl laurel Dec 4, 2020, 4:10 PM

#

looks cleaner idk

proven arrow Dec 4, 2020, 4:14 PM

#

No it does not, and you then have more issues such as passing those values to the script, with a different connection. So leave it as it is, and as last note, dont use f strings for your queries.

rare belfry Dec 4, 2020, 4:16 PM

#

proven arrow <@!482874869038579724> You would need to order by that boolean column. You can h...

Hi thanks, it worked

alpine dock Dec 4, 2020, 5:20 PM

#

📎 unknown.png

#

cursor.execute(f"SELECT user_id FROM levels WHERE guild_id = '{message.guild.id}' and user_id = '{message.author.id}'")
sqlite3.OperationalError: no such table: levels

#

Why is not recognizing the table?

#

nevermynd, fixed

torn sphinx Dec 4, 2020, 6:03 PM

#

Hey

torn sphinx Dec 4, 2020, 6:21 PM

#

can any1 help me with a basic sort from database

#

at #help-chocolate

plush sierra Dec 4, 2020, 9:57 PM

#

@cloud pebble the problem is still in "conn=mysql.connection()" i don't know how to solve this, you have try maybe on interactive shell, how to connect to the db

vestal kiln Dec 4, 2020, 10:07 PM

#

anyone know a good api for us stock exchange data feed?

polar badge Dec 4, 2020, 10:50 PM

#

@vestal kiln so long as your not doing a ton of calls https://polygon.io/ works great on their free plan. But gets expensive after that

Polygon.io

Polygon.io - Stock Market Data APIs

Free stock data APIs. Real time and historical data, unlimited usage, tick level and aggregate granularity, in standardized JSON and CSV formats. Plus currencies data, including forex, crypto, and more.

#

@alpine dock You shouldn't allow people to insert directly into your queries. you should do this first.

cursor.execute(query, (message.guild.id, message.author.id))```
Just note if you only are passing 1 value, you need it to me like `(query, (myValue,))` note the extra comma after myValue.

#

Otherwise someone could do an sql injection attack on you. Not likely, but best practice.

alpine dock Dec 4, 2020, 10:55 PM

#

i fixed it

#

but thanks anything

polar badge Dec 4, 2020, 10:59 PM

#

I am trying to use WHERE xyz LIKE but I need to check only the last character. such as xyz[:-1] and that it is equal to 1, the database i have has a bunch of values like something.1, something.2 something.3 and only the .1 do i care about. What's the best way to go about it? I am not figuring it out. figured it out didn't know i could use right(xyz,1)...

vestal kiln Dec 4, 2020, 11:23 PM

#

Ty

polar badge Dec 4, 2020, 11:24 PM

#

np

grim pier Dec 5, 2020, 1:15 AM

#

Anyone any ideas? 😄

grim pier Dec 5, 2020, 1:40 AM

#

should i just str replace to remove them?
Or is there an actual way to select it properly without them?

#

Monitor is going out the window soon... LOL

mellow scroll Dec 5, 2020, 4:18 AM

#

Hey so....

I'm planning on making some software for people to use, and I'm going to need a database, how can I insert data into this database safely, without allowing my users to be able to manipulate, import, edit and read the contents of the database?

#

this program will be publicly available.

#

Please ping me if you respond so i see the message 🙂

radiant elbow Dec 5, 2020, 4:23 AM

#

no, neither of those are right. You want:

        if row is None:
            query2 = """INSERT INTO antispamnewgen (choice) values ('on')"""
            await bot.db.execute(query2)
        else:
            query3 = """UPDATE antispamnewgen SET choice = 'on'"""
            await bot.db.execute(query3)

#

missing quotes in the "update", and the "insert" wasn't valid at all.

jovial notch Dec 5, 2020, 4:30 AM

#

appreciated

sly frigate Dec 5, 2020, 6:06 AM

#

File "C:/Users/Rico/Desktop/Genshin TCG/bot/mongotcg.py", line 4, in <module>
cluster = MongoClient("mongodb+srv://Mash:xxxxxxx@bots-genshin.l2ufp.mongodb.net/genshin?retryWrites=true&w=majority")

#

can iask what error is this?

#

i already updated my mongoDB

#

installed pip

#

when i run it

#

still same error

#

helpppppp

#

anyone here?

pale jay Dec 5, 2020, 7:28 AM

#

I am trying to make a login system and I want to store the register into a data base can anyone tell me how I can do that?

#

and which database would be good

proven arrow Dec 5, 2020, 7:33 AM

#

You need to be more specific. What exactly do you need help with? Structuring the schema? Picking if you need SQL Vs NoSQL? Writing the queries? Also more detail on how this system will be used, where and by whom/how many people.

pale jay Dec 5, 2020, 7:35 AM

#

I am making login system using tkinter and I am making a register function so when they enter the username field and the password field I want it to store to a database

#

should I use mysql connector and get the value entered and then create a table?

proven arrow Dec 5, 2020, 7:38 AM

#

So you already picked the database you want or you still need to pick one?

pale jay Dec 5, 2020, 7:38 AM

#

I need to pick one

#

I need SQL

proven arrow Dec 5, 2020, 7:40 AM

#

So why are you saying MySQL connector already?
Anyways, In terms of picking database you have two options, something like sqlite which is a file based database and the data can be accessed from wherever the file is kept. Or you can have a server based database like MySQL, or Postgres which will allow you to access data remotely over a server/network as well.

pale jay Dec 5, 2020, 7:41 AM

#

hmm okay

#

I said mysql connector bcuz I only know how to connect mysql with python

proven arrow Dec 5, 2020, 7:41 AM

#

Sqlite only allows a single writer at a time, as well which you might want to keep in mind.

pale jay Dec 5, 2020, 7:41 AM

#

oh okay

#

thx

proven arrow Dec 5, 2020, 7:44 AM

#

And for your schema you would want to have a users table where you store at least the (id, name, email, password, timestamps), and depending on your application functionality you may also want to store extra values like (email_verified, remember_token)

torn sphinx Dec 5, 2020, 9:21 AM

#

Hello, i was trying to code a "Username Taken" Code

#

NameList = []
name = input("Enter a name : ")
NameList.append(name)
print(NameList)

#

I know this will work

#

but how do i create a database, where the NameList will remember forever that theres a name called amcrq and it can't be used again

#

brb

proven arrow Dec 5, 2020, 9:29 AM

#

@torn sphinx You can have a UNIQUE constraint on the username column, which would never allow the same value to be inserted into that column more than once.

torn sphinx Dec 5, 2020, 9:38 AM

#

Back

#

How do i create those constriants

#

@proven arrow In Repl.It, theres a button called databases

#

do i click on that

proven arrow Dec 5, 2020, 9:40 AM

#

🤷‍♂️ I never use repl

torn sphinx Dec 5, 2020, 9:40 AM

#

Import
the database
from replit import db

Insert →
Set
a key to a value
db["
key
"] = "
value
"

Insert →
Get
a key's value
value = db["
key
"]

Insert →
Delete
a key
del db["
key
"]

Insert →
List
all keys
keys = db.keys()

Insert →
List
keys with a prefix
matches = db.prefix("
prefix
")

Insert →

#

Whats this

#

i get this words for database section

#

oh ok

proven arrow Dec 5, 2020, 9:41 AM

#

Looks like a key value store, maybe that is what they have?

torn sphinx Dec 5, 2020, 9:41 AM

#

key value

#

my beginners course never included this

#

but i just want to work on something

proven arrow Dec 5, 2020, 9:43 AM

#

Well what are you trying to store?

torn sphinx Dec 5, 2020, 9:43 AM

#

So

#

As i mentioned way above

#

I am working on a Username Taken project

#

If i enter the name

#

amcrq

#

amcrq cannot be a name entered

#

This is my Output
Enter a name : amcrq
['amcrq'

#

Forgot the missing bracket

#

so since i ahve entered amcrq

#

it can no longer be used as a name

#

so if i press enter again after typnig amcrq

#

it will print

#

username taken

proven arrow Dec 5, 2020, 9:52 AM

#

So basically there are two types of databases. SQL and NoSQL. The one you showed from repl is NoSQL, its just like a python dictionary, or a Map in other languages.
Although you can achieve what you want with both, it might be easier for you to implement it in SQL, as it is more simpler I feel for what you are trying to do.
There are different SQL databases. The easiest/simplest is SQLite which comes built into python, and you can have a UNIQUE constraint on the username so it doesn't allow multiple same entries.
But if you want to use the repl database, then you can make the key the username. Although you will have to check their documentation on how it works exactly, because its their own implementation of it.

torn sphinx Dec 5, 2020, 10:24 AM

#

Ok

#

Sorry i didn't reply

#

I was focused on developing something else in this project

tawny ingot Dec 5, 2020, 10:40 AM

#

I am trying to select something in my SQlite3 DB

deck = "test"
db = sqlite3.connect("DECKS.db")
cursor = db.cursor()
cursor.execute(f'SELECT NAME FROM DECKS WHERE USERID = {ctx.author.id} AND NAME = {deck}')
result = cursor.fetchone()
print(result[0])```
https://cdn.discordapp.com/attachments/587375768556797982/784724509063249930/unknown.png
and this is the error
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such column: test
though the column exists

#

can someone help me

#

why is this happening

molten sleet Dec 5, 2020, 10:52 AM

#

:+1:

tawny ingot Dec 5, 2020, 10:55 AM

#

molten sleet :+1:

?

shy condor Dec 5, 2020, 11:19 AM

#

Hey, need some help wiht json

so i created a json file with a dict in it and i have a function that dumps some more dict keys and stuff and this all works. but if i call the function another time and add stuff it overwrites the old stuff

#

how can i make it so it more likeley appends the new stuff?

proven arrow Dec 5, 2020, 11:44 AM

#

tawny ingot I am trying to select something in my SQlite3 DB ```py deck = "test" db = sqlite...

You need to escape it to make it a string literal, otherwise it treats it as a column name

#

Use this instead, which is also a safer way of making the query.

cursor.execute('SELECT NAME FROM DECKS WHERE USERID = ? AND NAME = ?', (ctx.author.id, deck))

tawny ingot Dec 5, 2020, 11:49 AM

#

yeah thanks for your help

pale jay Dec 5, 2020, 1:30 PM

#

CREATE TABLE info(
userid INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR INDEX UNIQUE NOT NULL,
password VARCHAR NOT NULL
);
```What is wrong in this?

#

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INDEX UNIQUE,
password VARCHAR
)' at line 3

proven arrow Dec 5, 2020, 1:46 PM

#

Put the index at the end of the statement, and also give varchar a value. For ex,

CREATE TABLE info(
  userid INT AUTO_INCREMENT,
  username VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  PRIMARY KEY (userid),
  INDEX (username) 
);
``` @pale jay

pale jay Dec 5, 2020, 1:47 PM

#

I will try that

proven arrow Dec 5, 2020, 1:47 PM

#

Actually wait no

#

I missed something

pale jay Dec 5, 2020, 1:47 PM

#

but no diff

proven arrow Dec 5, 2020, 1:47 PM

#

See updated

pale jay Dec 5, 2020, 1:48 PM

#

the size hmm

#

ok

pale jay Dec 5, 2020, 1:52 PM

#

proven arrow See updated

thx

#

worked

mellow scroll Dec 5, 2020, 2:42 PM

#

Hey so....

I'm planning on making some software for people to use, and I'm going to need a database, how can I insert data into this database safely, without allowing my users to be able to manipulate, import, edit and read the contents of the database?
If someone replies, please @ me so i can see the message 🙂

proven arrow Dec 5, 2020, 5:21 PM

#

@mellow scroll Well you should not let the users interact directly with the database. That's your applications job to do this. Your app should take the users input, process it and then pass it on to the database.
As some general rules:

Never trust the user (validate all input)
Never directly insert user input into the database
Don't use dynamic queries. Use prepared statements/parameterised queries.
Enforce database user privileges - only grant what they need to function, and not more. This is basically what is known as, principle of least privilege.

torn sphinx Dec 5, 2020, 5:33 PM

#

Hey people, i have a question about design. I have a users table, roles table, and users_role table. This is for my site, where user can have many role so i have many to many relation connection by the users_role table.

#

So my question is, is this bad because if user can have many roles then the entries will get many inside the users_role table. For example, 5000 users with 3 roles each is 15000 entries. Is this bad?

proven arrow Dec 5, 2020, 5:34 PM

#

@torn sphinx Why would it be bad?

#

Databases are made to store many rows. They were designed for this, and its their job to do this. There is nothing wrong with such an approach as long as you have the proper indexes.

torn sphinx Dec 5, 2020, 5:36 PM

#

ohh

#

yeah you right, make sense

#

thanks

proven arrow Dec 5, 2020, 5:42 PM

#

Your application credentials are generally kept in some sort of configuration or environment file. Your app just loads the values from it. This way your credentials can also be kept out of version control like git. But regarding your concerns of the server, obviously you should first protect your server with firewall or the other necessary precautions. And generally if your app is running on the same machine as the database then the database should only communicate over localhost so outside parties cant connect to it remotely. If they sit on separate servers, then you can also whitelist which IPs can access it, or have a service sitting in the middle to handle incoming/outgoing requests.

proven arrow Dec 5, 2020, 5:58 PM

#

@scarlet hollow Do you want to filter where value is one of the values of a given array?

#

SQL has an IN operator

#

For ex: SELECT * FROM users WHERE id IN (1, 2, 3);

torn sphinx Dec 5, 2020, 6:37 PM

#

    async def perform_migrations(self):
        version = (await (await self.db.execute("pragma user_version;")).fetchone())[0]
        self.bot.logger.info(f"database:Version {version} found")
        for i in sorted(MIGRATIONS.keys()):
            if i > version:
                self.bot.logger.info(f"database:Upgrading to version {version}")
                [await self.db.execute(_) for _ in MIGRATIONS[i].splitlines() if _]
                await self.db.execute(f"pragma user_version={version}")
                await self.db.commit()

#

this doesnt seem to be updating the user_version

#

INFO aoi » database » Version 0 found
returning exception duplicate column name: currency_img
Traceback (most recent call last):
  File "/home/crazygmr101/repo/Aoi/venv/lib/python3.8/site-packages/aiosqlite/core.py", line 95, in run
    result = function()
sqlite3.OperationalError: duplicate column name: currency_img

the migration was made, so it's raising the duplicate column name error

torn sphinx Dec 5, 2020, 7:01 PM

#

nvm. i am just. incredibly stupid

autumn turret Dec 5, 2020, 7:21 PM

#

I need help

v.execute("INSERT INTO config VALUES ('?', 'token', 'AsiTex')")
sqlite3.OperationalError: table config has 1 columns but 3 values were supplied```

#

I get that error

#

This is my code when I made the database

#

Connect = sqlite3.connect('config.db')
    v = Connect.cursor()
    v.execute("""CREATE TABLE config (
        Prefix text
        Token text
        Name text
    )
    """)

    Connect.commit()
    Connect.close()```

#

And my code when I inserted

#

    v.execute("INSERT INTO config VALUES ('?', 'my token', 'AsiTex')")

proven arrow Dec 5, 2020, 7:25 PM

#

@autumn turret When you make the table new columns should be separated with a comma

autumn turret Dec 5, 2020, 7:25 PM

#

Oh thank you

#

I tried this

#

Connect = sqlite3.connect('config.db')
v = Connect.cursor()
v.execute("SELECT * FROM config")
DataAll = v.fetchone()
for Prefix in DataAll:
  Prefix = Prefix[0]```

#

The prefix should be "?"

#

But instead it returns "A"

#

@proven arrow

proven arrow Dec 5, 2020, 8:09 PM

#

@autumn turret Because fetchone gives you a tuple, with the values of a row in your database. Each time you loop through it you get one value of that tuple and your indexing the first character of it. If that makes sense.

#

Also you need to rethink your variable names. They are unreadable and a total mess

autumn turret Dec 5, 2020, 8:15 PM

#

I understand

silk vortex Dec 5, 2020, 8:15 PM

#

Does anyone know why this is happening?

📎 alter_table_not_working.JPG

autumn turret Dec 5, 2020, 8:15 PM

#

But how do I grad a certain var

proven arrow Dec 5, 2020, 8:19 PM

#

@silk vortex User is a reserved keyword in postgres

#

You can escape it by wrapping it in double quotes

silk vortex Dec 5, 2020, 8:20 PM

#

oh ok

proven arrow Dec 5, 2020, 8:20 PM

#

autumn turret But how do I grad a certain var

what do you mean?

autumn turret Dec 5, 2020, 8:27 PM

#

So like how do I print the prefix from the db

#

@proven arrow

proven arrow Dec 5, 2020, 8:28 PM

#

Do you only want the data from a single column of your table or all?

autumn turret Dec 5, 2020, 8:29 PM

#

A single one

#

I want the one that has my prefix

proven arrow Dec 5, 2020, 8:30 PM

#

The * in SELECT * FROM config gets you all the columns. If you want just some columns then replace the * with the column name you want. That will give you a single value.

#

@autumn turret See the example here: https://discordapp.com/channels/267624335836053506/267659945086812160/784879878255935518

autumn turret Dec 5, 2020, 8:33 PM

#

Hm still a bit confused can you implement it into my code?

proven arrow Dec 5, 2020, 8:34 PM

#

Confused on what part? Say so i can explain. Im not here to write code for you.

autumn turret Dec 5, 2020, 8:35 PM

#

db.execute("SELECT name FROM subjects")

#

So would it be

db.execute("SELECT Prefix FROM config")```

proven arrow Dec 5, 2020, 8:37 PM

#

Yes.
As i mentioned above, * gets you all the columns. If you want just some specific columns replace it with the column name.

autumn turret Dec 5, 2020, 8:37 PM

#

Oh okay ty

shadow patrol Dec 5, 2020, 8:40 PM

#

guys can anyone help me with Mysql
installation
i downloaded it and whenever i try to open the shell it opens and closes immediat

#

and even the installation possess was different than the installation possess i saw in the tutorials , because they didn't give me the choice to specify the password and a lot of other things, help please I'm just starting with databases.

autumn turret Dec 5, 2020, 8:42 PM

#

It returns this "('?',)"

#

How do I make it return "?"

proven arrow Dec 5, 2020, 8:43 PM

#

You need to understand the value being returned. Its a tuple. Understanding this would help you also in accessing its values.
Look at the example i showed you again, and its output. I have shown how to access it.

silk vortex Dec 5, 2020, 8:56 PM

#

does anyone know why I get this error?

📎 another_error_in_on_message_del.JPG

proven arrow Dec 5, 2020, 9:00 PM

#

Your value is to big for the column size

#

Its more than 255 chars

silk vortex Dec 5, 2020, 9:01 PM

#

@client.event
async def on_message_delete(message):
    async with client.pool.acquire() as connection:
        channel = message.channel.id
        message_content = message.content
        author = message.author.name
        check = await connection.fetchrow('select message from "public"."snipe" where channel_id = $1', channel)
        if check == None:
            insert = await connection.execute('insert into "public"."snipe" ("channel_id", "message", "user") values ($1, $2, $3)', channel, message_content, author)
        else:
            update = await connection.execute('update "public"."snipe" set message = $1, user = $2 where channel_id = $3', message_content, author, channel)
``` This is my code

#

@proven arrow

proven arrow Dec 5, 2020, 9:05 PM

#

If your storing messages then use text as the column type, i dont know how big discord messages can be but that will solve you problem.

silk vortex Dec 5, 2020, 9:05 PM

#

oh ok

#

@proven arrow I think it works now but the update isn't working. I'm not getting an error either

proven arrow Dec 5, 2020, 9:15 PM

#

Well like i mentioned to you before USER is a reserved keyword in postgres, so you need to escape t

potent stirrup Dec 5, 2020, 10:46 PM

#

anyone have an idea of how to create an excel timetable using SQLite databases?

autumn turret Dec 6, 2020, 12:34 AM

#

@proven arrow How do I change the data value

silk vortex Dec 6, 2020, 2:26 AM

#

is there a way I can delete all rows in a postgresql database that have the same guild_id?

torn sphinx Dec 6, 2020, 2:33 AM

#

in json python, is it json.dump or json.dumps?

radiant elbow Dec 6, 2020, 3:41 AM

#

torn sphinx in json python, is it ``json.dump`` or ``json.dumps``?

Not the right channel, but they do different things. dump writes to a file, dumps creates a string

autumn turret Dec 6, 2020, 6:16 AM

#

@radiant elbow How do I change the data value

proven arrow Dec 6, 2020, 9:57 AM

#

autumn turret <@!613362435860070414> How do I change the data value

Can you be more specific? I can think of a hundred different ways to do this.

torn sphinx Dec 6, 2020, 10:01 AM

#

CREATE TABLE antiscam.users (
    id INT auto_increment NOT NULL,
    username varchar(32) NOT NULL,
    email varchar(100) NOT NULL,
    password varchar(60) NOT NULL,
    discord_id INT NOT NULL,
    created_at DATETIME NOT NULL,
    referral_code VARCHAR NOT NULL,
    CONSTRAINT users_pk PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;

#

what is wrong here?

#

I am gettin syntax error

proven arrow Dec 6, 2020, 10:06 AM

#

@torn sphinx what's the error?

#

Oh your missing the varchar value

pale jay Dec 6, 2020, 10:12 AM

#

CREATE TABLE IF NOT EXISTS info( userid INTEGER AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, password VARCHAR(25) NOT NULL);
```This is my table
```sql
CREATE TABLE notes( noteid INT AUTO_INCREMENT, filename VARCHAR(15) UNIQUE NOT NULL, note VARCHAR(200) NOT NULL, PRIMARY KEY (noteid), FOREIGN KEY (noteid) REFERENCES info(userid) );
```and this is my relation table

I get this error when I try to create more than one notes with same username and password

1452 (23000): Cannot add or update a child row: a foreign key constraint fails (user_info.notes, CONSTRAINT notes_ibfk_1 FOREIGN KEY (noteid) REFERENCES info (userid))

proven arrow Dec 6, 2020, 10:21 AM

#

Probably because there is no matching value of that foreign key in the parent table.

#

Also why is noteid a foreign key?

#

Your notes table should have a user_id column which is the foreign key. And noteid should be the Primary Key of the notes table.

pale jay Dec 6, 2020, 10:33 AM

#

oh okay

#

@proven arrow like this?

📎 unknown.png

#

should the userid in notes table should have auto_increment?

proven arrow Dec 6, 2020, 10:39 AM

#

Yes looks fine now, you can now have multiple notes belonging to each user.

pale jay Dec 6, 2020, 10:39 AM

#

ok I will try now

#

hmm this doesn't seem correct

📎 unknown.png

#

userid should have 1 right

proven arrow Dec 6, 2020, 10:43 AM

#

If you inserted it with the query then yeah.

pale jay Dec 6, 2020, 10:43 AM

#

I am using mysql connector

proven arrow Dec 6, 2020, 10:43 AM

#

You can avoid such issues by having the NOT NULL constraint on the userid column.

#

And it has nothing to do with MySQL connector.

#

It's how your entering the data

pale jay Dec 6, 2020, 10:44 AM

#

oh okay

#

if filename != '' and notes != '':
        sql = 'INSERT INTO notes (filename, note) VALUES (%s, %s)'
        val = (filename, notes)

        mycur.execute(sql, val)

        mydb.commit()

proven arrow Dec 6, 2020, 10:45 AM

#

Well it's not going to know the user id itself now is it. You need to also insert the user id value as well.

pale jay Dec 6, 2020, 10:45 AM

#

how would I do that

proven arrow Dec 6, 2020, 10:46 AM

#

Just add the extra column

#

In your insert query

pale jay Dec 6, 2020, 10:46 AM

#

and that would be userid?

proven arrow Dec 6, 2020, 10:46 AM

#

Yes

torn sphinx Dec 6, 2020, 10:46 AM

#

proven arrow Oh your missing the varchar value

i fixed it already thanks anyways

pale jay Dec 6, 2020, 10:48 AM

#

if filename != '' and notes != '':
        sql = 'INSERT INTO notes (filename, note, userid) VALUES (%s, %s)'
        val = (filename, notes)

        mycur.execute(sql, val)

        mydb.commit()
```this would give error right

#

cuz unmatched column and values

proven arrow Dec 6, 2020, 10:48 AM

#

Well why don't you think about it instead of guessing?

#

You give three column names, which means you also need to give three placeholders, and three values

pale jay Dec 6, 2020, 10:49 AM

#

yea but what would be the value how will I know which user has logged in

proven arrow Dec 6, 2020, 10:50 AM

#

Knowing which user is logged in is not a database issue. That's something your application needs to figure out

pale jay Dec 6, 2020, 10:50 AM

#

I mean how will I get the userid associated with the user

proven arrow Dec 6, 2020, 10:52 AM

#

You would get it however you authenticate the user. I have no idea how your application code works, and also the database doesn't care either. It just requires you to pass it the values and it will store it.

pale jay Dec 6, 2020, 10:52 AM

#

should I use WHERE clause to get the userid?

proven arrow Dec 6, 2020, 10:55 AM

#

If you have some details that uniquely identifies the user then yeah you could do that. The user details is something you must have, because you already authenticated them, when they logged in.

#

If you don't know who's logging into your system then that's bad..

pale jay Dec 6, 2020, 10:56 AM

#

username is unique

proven arrow Dec 6, 2020, 10:57 AM

#

If you have the username then you can get the id through it as well.

pale jay Dec 6, 2020, 11:10 AM

#

This is the error I get

Unknown column 'abc' in 'where clause'

and my code is

sql = f'SELECT userid FROM info WHERE username = {username1}'

    mycur.execute(sql)

    userid = mycur.fetchall()
    userid = userid[0]

    if filename != '' and notes != '':
        sql = 'INSERT INTO notes (filename, note, userid) VALUES (%s, %s, %s)'
        val = (filename, notes, userid)

        mycur.execute(sql, val)

        mydb.commit()

#

username1 stores the name of person who logs in

proven arrow Dec 6, 2020, 11:12 AM

#

Do not use f strings for dynamic queries. And error is because you need to escape value. Just use prepared statement/parameterised query for it

pale jay Dec 6, 2020, 11:12 AM

#

if I do (%s) format I get new error

#

ValueError: Could not process parameters

proven arrow Dec 6, 2020, 11:13 AM

#

Don't use format either

pale jay Dec 6, 2020, 11:13 AM

#

then

proven arrow Dec 6, 2020, 11:14 AM

#

#databases message

pale jay Dec 6, 2020, 11:14 AM

#

what is parameterised query

proven arrow Dec 6, 2020, 11:14 AM

#

The same style you used for your insert statement

pale jay Dec 6, 2020, 11:15 AM

#

you need to escape value wdym by that

proven arrow Dec 6, 2020, 11:17 AM

#

Put quotes around it

pale jay Dec 6, 2020, 11:20 AM

#

sql = 'SELECT userid FROM info WHERE username = %s'
val = (username1)
```Do you mean like this?

proven arrow Dec 6, 2020, 11:21 AM

#

Yes except Val has to be a tuple

pale jay Dec 6, 2020, 11:21 AM

#

oh

proven arrow Dec 6, 2020, 11:22 AM

#

val = (username1,)

pale jay Dec 6, 2020, 11:24 AM

#

I get this error
Not all parameters were used in the SQL statement
Code:

sql = 'SELECT userid FROM info WHERE username = %s'
val = (tuple(username1))

proven arrow Dec 6, 2020, 11:27 AM

#

Don't use functions without knowing what they do.

proven arrow Dec 6, 2020, 11:27 AM

#

proven arrow val = (username1,)

Here is how you can make it a tuple

pale jay Dec 6, 2020, 11:27 AM

#

(username1,) gives error too

finite lodge Dec 6, 2020, 11:27 AM

#

anyone know a good database host? just need it for a very small project but need multiple people to be able to access it so can't store locally

pale jay Dec 6, 2020, 11:28 AM

#

Python type tuple cannot be converted

proven arrow Dec 6, 2020, 11:28 AM

#

pale jay (username1,) gives error too

Then show that error because I am directing you down one path but you are going down another.

pale jay Dec 6, 2020, 11:28 AM

#

pale jay Python type tuple cannot be converted

this is the error

proven arrow Dec 6, 2020, 11:29 AM

#

And the code for it

pale jay Dec 6, 2020, 11:29 AM

#

sql = 'SELECT userid FROM info WHERE username = %s'
val = (username1,)

proven arrow Dec 6, 2020, 11:31 AM

#

What is the type of username1 ?

pale jay Dec 6, 2020, 11:31 AM

#

string

proven arrow Dec 6, 2020, 11:31 AM

#

It's probably a tuple, so then you have a tuple inside a tuple

#

Double check, do print(type(username1))

#

@finite lodge elephant SQL has some free options. Never used it myself but I heard some others use it in the past. You may want to check it out.

pale jay Dec 6, 2020, 11:34 AM

#

@proven arrow it's a str

proven arrow Dec 6, 2020, 11:35 AM

#

If it's a string then your error does not match your code.

pale jay Dec 6, 2020, 11:35 AM

#

hmmm

#

so what I do

proven arrow Dec 6, 2020, 11:39 AM

#

Replace username1 with an actual string value and see if you get the error

torn sphinx Dec 6, 2020, 11:39 AM

#

Hi, I am making a whitelist system for someone's Discord bot, and I am having a problem with the whitelisted command, which is there to display all users who are whitelisted in the context guild. but, I am having a problem with converting the user iDs into usernames and discriminators and then displaying all of the them.
this is my code:

# getting all users
			await cursor.execute (
			"SELECT user_id FROM whitelisted_users WHERE guild_id = {}".format ( ctx.guild.id )
			)
			users = await cursor.fetchall ( )
			for user in users:
				var = list ( user )
				for variable in var:
					global single_user
					single_user = client.get_user ( id = int ( variable ) )
			list_var = [ ]
			list_var.append (
			"{}#{}".format (
			single_user.name , single_user.discriminator
			)
			)
			embed.add_field (
			name = "Users",
			value = "\n".join ( list_var )

(and I know, this is to do with discord.py, but this is more about SQlite 3, and someone with no discord.py knowledge and only SQLite 3 knowledge could help me with this)

pale jay Dec 6, 2020, 11:41 AM

#

sql = 'SELECT userid FROM info WHERE username = %s'
val = (str(username1),)
```Still the same error

proven arrow Dec 6, 2020, 11:41 AM

#

I didn't say that

pale jay Dec 6, 2020, 11:42 AM

#

do you mean val = ('',)

proven arrow Dec 6, 2020, 11:42 AM

#

val = ("username",)

pale jay Dec 6, 2020, 11:42 AM

#

same error I did that too

proven arrow Dec 6, 2020, 11:42 AM

#

Show full stack trace

pale jay Dec 6, 2020, 11:42 AM

#

val = ('abc',)

#

Exception in Tkinter callback
Traceback (most recent call last):
  File "/usr/lib/python3.8/tkinter/__init__.py", line 1883, in __call__
    return self.func(*args)
  File "/home/arnav/Desktop/github-projects/login_system/login_system.py", line 35, in save
    mycur.execute(sql, val)
  File "/home/arnav/.local/lib/python3.8/site-packages/mysql/connector/cursor_cext.py", line 248, in execute
    prepared = self._cnx.prepare_for_mysql(params)
  File "/home/arnav/.local/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 643, in prepare_for_mysql
    result = self._cmysql.convert_to_mysql(*params)
_mysql_connector.MySQLInterfaceError: Python type tuple cannot be converted

proven arrow Dec 6, 2020, 11:46 AM

#

Did you save the code?

torn sphinx Dec 6, 2020, 11:47 AM

#

lol

pale jay Dec 6, 2020, 11:47 AM

#

yes

proven arrow Dec 6, 2020, 11:50 AM

#

Well it's impossible for me to reproduce a similar error with that piece of code. So there is definitely something your not showing here or some misunderstanding from what I'm saying and how you are implementing it.

pale jay Dec 6, 2020, 11:51 AM

#

should I show you my functions

#

all are set to StringVar()

#

and I am using .get()

#

on those to get the entered value

proven arrow Dec 6, 2020, 11:56 AM

#

Replace the above with this, and show the output:

mycur.execute("SELECT userid FROM info WHERE username = %s", ("1",))

pale jay Dec 6, 2020, 12:00 PM

#

    mycur.execute("SELECT userid FROM info WHERE username = %s", ('1',))

    userid = mycur.fetchall()

    if filename != '' and notes != '':
        sql = 'INSERT INTO notes (filename, note, userid) VALUES (%s, %s, %s)'
        val = (filename, notes, userid)

        mycur.execute(sql, val)

        mydb.commit()

Error: Python type list cannot be converted

proven arrow Dec 6, 2020, 12:02 PM

#

Well the error is not in that query then. Because no list was provided.

pale jay Dec 6, 2020, 12:03 PM

#

I don't even have a list in my whole program xD

proven arrow Dec 6, 2020, 12:05 PM

#

Well argue that with python, because its saying something is a list.
Issue is in one of your other queries, and not the one we have been trying to fix for the past half hour. Or it may be in another query you may have elsewhere in your code. You see the issue when you don't show all the code? Just wastes a lot of time.

pale jay Dec 6, 2020, 12:06 PM

#

my code is like 195 lines so that would be hard to debug for you

#

@proven arrow these are the parts where I use sql https://paste.pythondiscord.com/comofubiqu.rb

proven arrow Dec 6, 2020, 12:19 PM

#

@pale jay Do you know what userid = mycur.fetchall() returns?

pale jay Dec 6, 2020, 12:20 PM

#

a tuple

proven arrow Dec 6, 2020, 12:21 PM

#

It returns a list of tuples, and without extracting the value, your just passing it like that to val = (filename, notes, userid).

#

So val looks like this (filename, notes, [(1),])

pale jay Dec 6, 2020, 12:21 PM

#

oof

#

so I use indexing

proven arrow Dec 6, 2020, 12:22 PM

#

yes

pale jay Dec 6, 2020, 12:22 PM

#

that would be userid[0] and it will give (1)

#

list index out of range

proven arrow Dec 6, 2020, 12:24 PM

#

Yeah because you used id 1. There is no matching value returned

#

And you can do fetchrow instead to get a single row

pale jay Dec 6, 2020, 12:25 PM

#

wait what

#

I print userid and it empty list

#

oh wait

#

lol

proven arrow Dec 6, 2020, 12:25 PM

#

because you have no username = 1

#

Anyways you can figure this out now. Its basic python from on here.
And also your general program structure is pretty idk (can be improved lets say)
Like you are looping through all the rows to see a matching value when your database can literally do this for you. Maybe rethink how you do some things.

pale jay Dec 6, 2020, 12:28 PM

#

well tkinter is always messy

pale jay Dec 6, 2020, 12:28 PM

#

proven arrow Anyways you can figure this out now. Its basic python from on here. And also you...

oh ik what part you talking about for (username, password) in mycur right

#

yea I will change that

#

thx for helping

pale jay Dec 6, 2020, 2:14 PM

#

mycur.execute('DELETE FROM notes WHERE filename = %s', (filename2,))

This gives no error but also doesn't delete the file

proven arrow Dec 6, 2020, 2:18 PM

#

Did you commit after executing?

pale jay Dec 6, 2020, 2:18 PM

#

wait I think I didn't commit

proven arrow Dec 6, 2020, 2:19 PM

#

Also what if multiple users have the same file name? Have you considered what would happen in that case?

pale jay Dec 6, 2020, 2:19 PM

#

that's why I have userid

proven arrow Dec 6, 2020, 2:19 PM

#

where?

#

You dont have it in that query

pale jay Dec 6, 2020, 2:20 PM

#

I am doing it now

#

just made userid global and put it there

#

mycur.execute('DELETE FROM notes WHERE filename = %s AND userid = %s', (filename2,userid))```

#

wait why does global userid not work

#

global userid
userid = mycur.fetchall()[0][0]

#

anyways I will just copy paste that same query in the other functions

torn sphinx Dec 6, 2020, 3:17 PM

#

why is everyone uses online database connections? There's literally a built-in database module

autumn turret Dec 6, 2020, 3:23 PM

#

proven arrow Can you be more specific? I can think of a hundred different ways to do this.

I mean like I wanna make a prefix command so ?prefix !

#

And it would change the prefix value to args

proven arrow Dec 6, 2020, 3:28 PM

#

torn sphinx why is everyone uses online database connections? **There's literally a built-in...

Different people have different requirements. The built in sqlite module is file based database, and it has its limitations in some areas compared to a server based database.

#

@autumn turret SQL has an UPDATE statement. See the examples here https://www.w3schools.com/sql/sql_update.asp

pale jay Dec 6, 2020, 3:39 PM

#

sql = 'SELECT username, password FROM info'
mycur.execute(sql)
for (username, password) in mycur:
        if username == username1 and password == password1:
            login_success()
        elif username == username1 and password != password1:
            password_not_rec()
        else:
            user_not_found()
```Why does this always call user_not_found()?

pale jay Dec 6, 2020, 4:03 PM

#

I also print username and password it is correct

tepid cradle Dec 6, 2020, 4:29 PM

#

I think you need a fetch() somewhere

#

You're just executing the query, not fetching the value

proven arrow Dec 6, 2020, 4:31 PM

#

They can loop through mycur and it would achieve the same

#

But yes its better if you filter in the database by the username, and then fetchone, rather than looping through all records.

pale jay Dec 6, 2020, 4:32 PM

#

hm okay I will try

proven arrow Dec 6, 2020, 4:35 PM

#

mycur.execute("SELECT username, password FROM info WHERE username = %s", (username,))
data = mycur.fetchone()

if not data:
    # user with given username not found
else:
    db_username, db_password = data
    if your_hash_function(input_password) == db_password:
        # username and password matches
    else:
        # password does not match

Something like this.

pale jay Dec 6, 2020, 4:40 PM

#

    sql = 'SELECT username, password FROM info'

    mycur.execute(sql)

    data = mycur.fetchone()

    if not data:
        user_not_found()
    else:
        username, password = data
        if username == username1 and password == password1:
            login_success()
        else:
            unknown_cred()

#

gives error Unread result found

proven arrow Dec 6, 2020, 4:46 PM

#

Because you only fetched one, and you asked for all the rows

#

If you followed what I sent you will see the difference, there was a reason why I filtered by the username in the database.

pale jay Dec 6, 2020, 4:48 PM

#

oh I get it now

#

thx

autumn turret Dec 6, 2020, 4:59 PM

#

@proven arrow I got this error

#

Ignoring exception in command prefix:
Traceback (most recent call last):
  File "C:\Users\mahfu\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "c:/Users/mahfu/Downloads/BytexBot/main.py", line 321, in prefix
    CChange("Prefix", args)
  File "c:/Users/mahfu/Downloads/BytexBot/main.py", line 139, in CChange
    ItemChange = v.execute(f"UPDATE config SET {Item} = {Args}")
sqlite3.OperationalError: unrecognized token: "!"

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\mahfu\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\ext\commands\bot.py", line 903, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\mahfu\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\ext\commands\core.py", line 859, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\mahfu\AppData\Local\Programs\Python\Python37\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: OperationalError: unrecognized token: "!"```

#

def CChange(Item, Args):
    Connect = sqlite3.connect('config.db')
    v = Connect.cursor()

    ItemChange = v.execute(f"UPDATE config SET {Item} = {Args}")

    Connect.commit()
    Connect.close()```

proven arrow Dec 6, 2020, 5:08 PM

#

@autumn turret Don't use f-strings to make database queries. They are bad. Why they are bad? Have a read of the answer here, and see how they should be written. You can then apply that in your case: https://discordapp.com/channels/267624335836053506/342318764227821568/783623172968284170

autumn turret Dec 6, 2020, 6:04 PM

#

Okay

proven arrow Dec 6, 2020, 6:37 PM

#

A book can only have one author and publisher though right?

#

Author and publisher names should be stored in their own respective tables. You would then have a relationship from a book to these tables.

#

If a book can have more than one author, then in that case have a many-to-many relationship between the book, and author.

#

Your Name table should not have any relation with the Publisher/Author tables. Only have it with the books.

#

People only have one name?

#

Oh is that how books work. I had no idea. If i ever read a book i would know 😛

narrow schooner Dec 6, 2020, 6:44 PM

#

I'm making a discord bot for a month, and I've used a json file for all this time, but is there a better way to store data?
before my bot becomes too complicated to change a data file
~~it'll still be a pain to change a file~~

proven arrow Dec 6, 2020, 6:45 PM

#

Ok well in that case you can have the One-to-Many relationship between the Book/Author/Publisher and the Names table.

#

The foreign key for each of the Book,Author,Publisher tables would be inside the names table.

proven arrow Dec 6, 2020, 6:48 PM

#

narrow schooner I'm making a discord bot for a month, and I've used a json file for all this tim...

The only way is a database, because json is not for storing data.

narrow schooner Dec 6, 2020, 6:48 PM

#

proven arrow The only way is a database, because json is not for storing data.

wait, so what is json for?

proven arrow Dec 6, 2020, 6:49 PM

#

Yeah so as daiyo says, its for immutable data storage. More for representing data

narrow schooner Dec 6, 2020, 6:50 PM

#

oh ok, can you send me a good video about how to use db files? ~~or explain me it yourself, but I think it'll be quite long~~

#

thx so much!

proven arrow Dec 6, 2020, 6:52 PM

#

Also there are two main kinds of databases. SQL and NoSQL. In your case you will want a SQL one, so keep that in mind when searching it up.

narrow schooner Dec 6, 2020, 6:52 PM

#

what's the difference?

#

hmm, are you here..?

#

oh ok, thx

proven arrow Dec 6, 2020, 6:55 PM

#

Main difference is: SQL is structured with a schema and the data can be relational with tables and rows, whereas NoSQL has no structure.

narrow schooner Dec 6, 2020, 6:56 PM

#

If so, SQL is more suitable for the purpose

proven arrow Dec 6, 2020, 6:57 PM

#

Yes

narrow schooner Dec 6, 2020, 6:57 PM

#

Just from curiosity, on which language is db files written on?

#

~~I know json are on java~~

#

Are you talking about the db files?

proven arrow Dec 6, 2020, 6:59 PM

#

Both have their usecases. And yes you are right, however this is one of the reasons some people use nosql ones. The way i see it is: Use SQL relational databases in all cases, and only use NoSQL when a relational database can't solve your problem. But in majority of the cases SQL databases can solve your problems.

autumn turret Dec 6, 2020, 7:01 PM

#

@proven arrow This still did not work


def CChange(Item, Args):
    Connect = sqlite3.connect('config.db')
    v = Connect.cursor()

    ItemChange = v.execute(f"UPDATE config SET {Item} = ?", (Args,))

    Connect.commit()
    Connect.close()```

proven arrow Dec 6, 2020, 7:02 PM

#

What is item?

#

That should be replaced with your column name.

autumn turret Dec 6, 2020, 7:06 PM

#

CChange("Prefix", args)

proven arrow Dec 6, 2020, 7:07 PM

#

What is the point of that? Just manually right the column name inside the query

#

ItemChange = v.execute(f"UPDATE config SET Prefix = ?", (Args,))

autumn turret Dec 6, 2020, 7:07 PM

#

Bc im storing alot of data that will be needed to be changed time to time

#

So its easier for me

proven arrow Dec 6, 2020, 7:12 PM

#

There is no "best" way.

#

It depends on what you are trying to do, and how you will access/retreive that information.

inner onyx Dec 6, 2020, 7:58 PM

#

Can we ask SQLite3 Here?

viral fern Dec 6, 2020, 8:22 PM

#

hey can anyone recommend me anything(books, websites, videos) to learn databases

inner onyx Dec 6, 2020, 8:22 PM

#

https://www.youtube.com/watch?v=o-vsdfCBpsU&t=1s @viral fern

#

This is what i used an hour back.

#

And are you Indian? @viral fern

viral fern Dec 6, 2020, 8:25 PM

#

inner onyx And are you Indian? <@!758075164532670605>

y

inner onyx Dec 6, 2020, 8:25 PM

#

Because I am.

#

and you also look like.

deft glade Dec 6, 2020, 8:36 PM

#

I'm trying to create an easy automated DB backup using PyDrive.
Does anyone know how can i automatically login on my own account without requiring the browser oauth confirmation? Is it even possible?
My "app" is also going to restart from time to time, so i need the automated login feature, unfortunately

#

nvm, restarted the IDE and for some reason the save_config_file is being loaded automatically and it is working now

keen sundial Dec 6, 2020, 8:50 PM

#

got a mongo question... how do i make a multi document transaction in pymongo? I am using a seesion and start_transaction but i have two processes working on the same documents and I get write conflicts. they should simply work 1 after the other.

#

I get the following: WriteConflict error: this operation conflicted with another operation. Please retry your operation or multi-document transaction. but im using a transaction and see no options to make it multi-document 😕

keen sundial Dec 6, 2020, 9:33 PM

#

plant uml plugin for pycharm/intellij. @torn sphinx

#

otherwise, draw.io

slim mist Dec 6, 2020, 10:05 PM

#

Hey, I'm currently learning databases. Can someone tell me what I'm doing wrong? It's not giving me any errors, and it's not working.

import discord
from discord.ext import commands
import sqlite3


client = commands.Bot(command_prefix='/')

con = sqlite3.connect("database.db")

cursorObj = con.cursor()

@client.event
async def on_ready():
    print('bot ready')

@client.command()
async def fruits(ctx, apple, pear, orange, strawberry):
    sql = cursorObj.execute("INSERT INTO fruits VALUES (?,?,?,?)")
    val = (apple, pear, orange, strawberry)
    cursorObj.execute(sql, val)
    await ctx.send("Finished!")

client.run('mytoken')

fallow moon Dec 6, 2020, 11:10 PM

#

hey, all. I have a big text file consisting of lines of text. each line has a word/regular expression followed by various sets of digits that tag each word to a category. I'm trying to detect words whose lines have specific tags (e.g. the digits 50) and save those to a list, but I have no idea on how to tackle this. any tips?

#

nevermind I got something working 🙂

keen sundial Dec 7, 2020, 12:09 AM

#

@slim mist sql = should be a string not the result of execute. also executing without passing values for ? wont work

#

im surprised it's not giving errors through

meager vine Dec 7, 2020, 12:40 AM

#

Is there a good and active MSSQL or general SQL server?

keen sundial Dec 7, 2020, 12:57 AM

#

you mean python bindings? or as you asking if mssql is active? (it is). but you can also use postgres or mysql. these are all active.

meager vine Dec 7, 2020, 12:58 AM

#

I meant a discord server for it

#

Like this one

#

But for MSSQL

keen sundial Dec 7, 2020, 12:58 AM

#

ah derp. sorry1

#

no idea

uneven rivet Dec 7, 2020, 1:00 AM

#

hello

solemn sparrow Dec 7, 2020, 1:55 AM

#

Hellop

#

I need

#

a lot of help

#

I have a project due

#

and I don't know what to do

#

a friend made the program but didn't comment it

#

and I was in charge of making the stored procedures and the view

pale jay Dec 7, 2020, 6:52 AM

#

def saved():
    mycur.execute("SELECT userid FROM info WHERE username = %s", (username1,))

    userid = mycur.fetchall()[0][0]

    mycur.execute("SELECT filename FROM notes WHERE userid = %s", (userid,))

    raw_files_exist = mycur.fetchall()
    files_exist = []

    for i in raw_files_exist:
        for j in i:
            files_exist.append(j)
    print(files_exist)

    global root7
    root7 = Toplevel(root)
    root7.title('Notes v1.0 ~ Saved')
    root7.geometry('100x100')

    if filename in files_exist:
        print(filename)
```This is what I have. I am trying to do something like if filename already exist in database I need to give error file already exists!. So `print(files_exist)` gives `['n1', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i']` and when I create a note with file name `j` then the if statement at the end print `j` shouldn't it give all the previous files too?

#

filename is only storing what I enter

grim pier Dec 7, 2020, 6:57 AM

#

Hello is it possible to multiple information in a field?

#

📎 unknown.png

#

Like this for example?

#

At the minute its just set as a string. is the correct way to set it to a enum?

proven arrow Dec 7, 2020, 7:12 AM

#

What do you mean by "correct" way? What are you trying to optimise for?

grim pier Dec 7, 2020, 7:53 AM

#

@proven arrow so i can store multiple information

torn sphinx Dec 7, 2020, 8:02 AM

#

how do i a value from a specific entry

#

using it's _id

#

mongo db

#

and using pymongo

#

along with 3.8.2

proven arrow Dec 7, 2020, 8:22 AM

#

grim pier <@!613362435860070414> so i can store multiple information

Are those values attributes of that item? I'm also assuming a multiple item can share some of those values too? There are many ways to store multiple information. You can do it like you are doing now, or you can normalise it and put the values in a seperate table, you can store it as an array/json or a serialized version of it depending on your database. But the approach you choose depends on how you would like to retrieve this information later and manipulate it.

pale jay Dec 7, 2020, 8:31 AM

#

pale jay ```py def saved(): mycur.execute("SELECT userid FROM info WHERE username = %...

nvm figured it out

limber marsh Dec 7, 2020, 9:05 AM

#

what is wrong with this sql query, "INSERT INTO card(balance) VALUES (1200,) WHERE number = '12345';"

pale jay Dec 7, 2020, 9:06 AM

#

@limber marsh are you writing this in python?

#

if yes remove ;

limber marsh Dec 7, 2020, 9:07 AM

#

yes.

pale jay Dec 7, 2020, 9:07 AM

#

then remove ;

limber marsh Dec 7, 2020, 9:07 AM

#

I am getting problem in this query

pale jay Dec 7, 2020, 9:07 AM

#

@limber marsh #databases message

limber marsh Dec 7, 2020, 9:08 AM

#

I am using sqliteonline.com to find the error but keep getting "Help: near ")": syntax error"

pale jay Dec 7, 2020, 9:09 AM

#

can you show your code

limber marsh Dec 7, 2020, 9:09 AM

#

the link redirects to the same page

pale jay Dec 7, 2020, 9:09 AM

#

did you remove ; or no

eternal raptor Dec 7, 2020, 9:10 AM

#

sorry guys if not python related, but I have a question. In aiosqlite, what is a difference between fetchone and fetchrow?
because
I was writting a bigger bots with asyncpg (postgresql) and now I want to write a small bot and I want to use sqlite. In postgresql, I used fetchval
Anyone know? Thanks for any help, guys!
🙂

limber marsh Dec 7, 2020, 9:10 AM

#

i have some problem in ADD_BALANCE

📎 unknown.png

pale jay Dec 7, 2020, 9:10 AM

#

I told you to remove ;

#

can you pls do that

limber marsh Dec 7, 2020, 9:11 AM

#

pale jay did you remove ; or no

it works with the semi-colon on other sql query

pale jay Dec 7, 2020, 9:11 AM

#

can you show that query

limber marsh Dec 7, 2020, 9:12 AM

#

I have posted the screenshot in the chat

eternal raptor Dec 7, 2020, 9:12 AM

#

sorry guys if not python related, but I have a question. In aiosqlite, what is a difference between fetchone and fetchrow?
because
I was writting a bigger bots with asyncpg (postgresql) and now I want to write a small bot and I want to use sqlite. In postgresql, I used fetchval
Anyone know? Thanks for any help, guys!
🙂

limber marsh Dec 7, 2020, 9:13 AM

#

can anyone try those queries on sqliteonline.com and help me

#

📎 unknown.png

proven arrow Dec 7, 2020, 9:13 AM

#

@limber marsh why do you have a comma after the value

pale jay Dec 7, 2020, 9:14 AM

#

sql = INSERT INTO card(number, pin) VALUES (%s, %s) WHERE number = %s
val = (<val1>, <val2>, <val3>)
cur.execute(sql,val)
```I do it like this

limber marsh Dec 7, 2020, 9:15 AM

#

i thought if it is only one tuple you need comma afterwards

#

thanks arnav, i will try this.

#

well, the query that i use works perfectly for all, except the add balance one

#

can you try it for that one

pale jay Dec 7, 2020, 9:16 AM

#

the ADD_BALANCE?

limber marsh Dec 7, 2020, 9:16 AM

#

yes

#

I am using sqliteonline for debugging but keep getting errors

proven arrow Dec 7, 2020, 9:17 AM

#

I told you your issue above

limber marsh Dec 7, 2020, 9:18 AM

#

how can i type code here

proven arrow Dec 7, 2020, 9:18 AM

#

Remove the comma and your fine

pale jay Dec 7, 2020, 9:18 AM

#

add_balance = INSERT INTO car(balance) VALUES (%s) WHERE number = %s
val = (<val1>, <val2>)
cur.execute(add_balance, val)
```you don't need to do (%s,)

#

or ?,

limber marsh Dec 7, 2020, 9:18 AM

#

thank you so much arnav.

#

I followed this tutorial https://youtu.be/iXYeb2artTE

YouTube

teclado

Python SQLite Tutorial: Build a Python project with a SQLite database

Learn how SQL works from the ground up and how to use SQLite from your Python apps in this complete Python SQLite Tutorial!

In this video, we build a Python project that uses a SQLite database to store data. We also look at how SQL simplifies inserting, retrieving, and searching for data.

0:00 - Video Introduction / What to Expect
0:30 - Overv...

▶ Play video

pale jay Dec 7, 2020, 9:19 AM

#

How would I see if the username already exist in a database or not at the time of registration?

#

because I don't have a good way of fetching the usernames that already exist

proven arrow Dec 7, 2020, 9:22 AM

#

@eternal raptor fetchrow gets you a single row. Aiosqlite implements db api unlike asyncpg so you can read what each of the fetch operations do here https://www.python.org/dev/peps/pep-0249/#fetchone

Python.org

PEP 249 -- Python Database API Specification v2.0

The official home of the Python Programming Language

pale jay Dec 7, 2020, 9:23 AM

#

nvm I will just select the username from the table

eternal raptor Dec 7, 2020, 9:26 AM

#

@proven arrow in postgresql fetchval -> in sqlite fetchone ?

proven arrow Dec 7, 2020, 9:27 AM

#

@pale jay You can do select count(*) from users where username = ?
If the count is 0 then username doesn't exist.

pale jay Dec 7, 2020, 9:27 AM

#

I just did SELECT username FROM info is that a bad idea?

proven arrow Dec 7, 2020, 9:28 AM

#

yes

pale jay Dec 7, 2020, 9:28 AM

#

cuz it will make alot of comparisions if there are alot of users?

proven arrow Dec 7, 2020, 9:28 AM

#

eternal raptor <@!613362435860070414> in postgresql fetchval -> in sqlite fetchone ?

It doesnt have fetchval. fetchval is a asyncpg thing only.

#

fetchrow = fetchone
fetch = fetchall

eternal raptor Dec 7, 2020, 9:28 AM

#

ok, thank you so much, bro 😄

proven arrow Dec 7, 2020, 9:31 AM

#

pale jay cuz it will make alot of comparisions if there are alot of users?

No, because you are asking for more data than you need. Why do you need all the usernames?

pale jay Dec 7, 2020, 9:31 AM

#

I don't want all user names I only want to check if that user already exist

proven arrow Dec 7, 2020, 9:32 AM

#

Well that is what you are doing.

limber marsh Dec 7, 2020, 9:32 AM

#

guys, the correct query that worked for me was "UPDATE card
SET balance = 12.33
WHERE id=12345;

pale jay Dec 7, 2020, 9:32 AM

#

I will just use count(*) and check if it is equal to 1 or not

limber marsh Dec 7, 2020, 9:32 AM

#

thank you for your time

pale jay Dec 7, 2020, 9:37 AM

#

pale jay I will just use count(*) and check if it is equal to 1 or not

worked thx Lufthansa-Pilot

viral furnace Dec 7, 2020, 9:58 AM

#

I am looking for a good e-com relational database model. is this a good one to follow ?

#

📎 storediagram.gif

#

or can you point me something better

eternal raptor Dec 7, 2020, 10:04 AM

#

how you created this?

viral furnace Dec 7, 2020, 10:04 AM

#

i got it online

eternal raptor Dec 7, 2020, 10:04 AM

#

tihs model

#

ooo ok

viral furnace Dec 7, 2020, 10:05 AM

#

what do you think

proven arrow Dec 7, 2020, 10:07 AM

#

The one you posted can be further normalised. But first you should consider what your application does/requires. Then try to model around that.

viral furnace Dec 7, 2020, 10:14 AM

#

I don't know about database normalisation. should i look into that ?

#

if yes, what would be a good resource to start ?

proven arrow Dec 7, 2020, 10:24 AM

#

viral furnace if yes, what would be a good resource to start ?

Not so sure on good resources, or if the python discord site has any. I only learnt from from a book/lectures/experience. However the first few links on google search seem to be good, or you can try youtube videos if you prefer that style.

#

Maybe don't worry too much if your new to it. You may not get a perfect system but as long as you understand the benefits then you can work on that later I guess

torn sphinx Dec 7, 2020, 10:54 AM

#

syntax error at end of input line 53, I am seeing no issue here idk what is causing the error

db = self.bot.database
    # Prefix
    if str(reaction[0]) == reactions[0]:
        prefixembed = Embed(
            title="Type in the prefix that you want"
        )
        # Wait for websocket event
        prefix = await self.bot.wait_for("message")

        # Update the prefix in the database with the prefix given
        await db.execute( # line 53
            """                        
            UPDATE guilds SET prefix = $2 WHERE prefix = !
            """,
            str(prefix.content)
        )
        await ctx.send(embed=prefixembed)

here's the error https://hasteb.in/rupomivi.sql

#

Also another issue I am having, when I react to the embed, the embed is not sending

runic mirage Dec 7, 2020, 10:57 AM

#

Hey how to get specific entry only specific place for output in mysql

proven arrow Dec 7, 2020, 11:02 AM

#

torn sphinx syntax error at end of input line 53, I am seeing no issue here idk what is caus...

Because you didnt put your prefix value as a string

#

Also $2 should be $1, as you only have one parameter

torn sphinx Dec 7, 2020, 11:03 AM

#

I thought $1 represented where the row was

#

here is my rows

📎 unknown.png

proven arrow Dec 7, 2020, 11:05 AM

#

$x is just a placeholder for the value you want to input into the database

#

and they start at 1

torn sphinx Dec 7, 2020, 11:05 AM

#

It is really weird though because this works

channel_id = await self.bot.wait_for("message")

await db.execute(
    """
    INSERT INTO logs (guildID, logchannel)
    VALUES ($1, $2)
    """,
    ctx.message.guild.id,
    int(channel_id.content)
)

that's for setting my log channel

#

thanks though, also it has been a while since I have done anything with my bot that's why i kinda suck rn lol

proven arrow Dec 7, 2020, 11:05 AM

#

Yeah it works because you are inputting two values into the database, so you have $1, and $2

torn sphinx Dec 7, 2020, 11:06 AM

#

yeah I see

proven arrow Dec 7, 2020, 11:06 AM

#

What im saying is it starts at 1

torn sphinx Dec 7, 2020, 11:06 AM

#

I'll try my bot again, hopefully prefix.content is working as well

proven arrow Dec 7, 2020, 11:06 AM

#

You are basically binding the parameters so you say the first value goes here, second here and so on

torn sphinx Dec 7, 2020, 11:08 AM

#

I actually forgot the type I set for prefix, i'll have to check that because still getting syntax error. it should be a string but maybe it is something else

proven arrow Dec 7, 2020, 11:09 AM

#

Ok, and this is what you need:

await conn.execute("UPDATE guilds SET prefix = $1 WHERE prefix = '!'", str(prefix.content))

torn sphinx Dec 7, 2020, 11:10 AM

#

Thanks, it worked 😄, I tried with double quotes before and it didn't work, so I assumed that was not going to work either

#

i should not have assumed

runic mirage Dec 7, 2020, 11:15 AM

#

    self.bot.SQL.execute('select user_name, balance from Accounts where server_id=%s ORDER BY balance DESC LIMIT 10 ', (SERVER_ID,) )
        result_top10 = self.bot.SQL.fetchmany(2)
        

        embed = discord.Embed(
                colour=discord.Colour.orange()
        )

        embed.set_author(name="Top bank accounts")
        embed.add_field(name="#1", value=f"User: {result_top10[0][0]} Bal: {result_top10[0][1]}", inline=False)
        embed.add_field(name="#2", value=f"User: {result_top10[1][0]} Bal: {result_top10[1][1]}", inline=False)

        await ctx.send(embed=embed)

#

its take 2 entries but i want 10 entries if available then how to do that??

proven arrow Dec 7, 2020, 11:40 AM

#

@runic mirage You can fetchall() which will give you a list of rows. You can then loop through that list and add the fields.

runic mirage Dec 7, 2020, 11:43 AM

#

then how to set index for this?

#

@proven arrow

#

mean how to select col from fetchall()

proven arrow Dec 7, 2020, 11:46 AM

#

for index, row in enumerate(result_top10):
    embed.add_field(name=f"#{index}", value=f"User: {row[0]} Bal: {row[1]}", inline=False)

#

@runic mirage Something like that.

runic mirage Dec 7, 2020, 11:47 AM

#

okay got it

#

thanks

rugged dome Dec 7, 2020, 12:03 PM

#

what should i do with my aws credit it expires in like 3 weeks

pale jay Dec 7, 2020, 12:32 PM

#

I have it coded so that all the available files in the database are shown when you want to delete a file so if you delete a file the Label doesn't change until I close that delete notes window how can I make it so as soon as a file is deleted it gets removed from the label?

raw whale Dec 7, 2020, 12:44 PM

#

rugged dome what should i do with my aws credit it expires in like 3 weeks

try the new ARM servers 😄

cloud pebble Dec 7, 2020, 1:01 PM

#

Hey guys I have a doubt!

A normal Sql Select query with WHERE clause will be like this right:

SELECT * FROM student where studentName='SHAWN'

now in my case I have except the Sname from user and I am using parameterised query in pyhton

sname=request.form["susername"]
cur.execute("SELECT * FROM student WHERE  studentName=%s",(sname))

is this correct? how would I give those ' as an input?

raw whale Dec 7, 2020, 1:02 PM

#

major SQL Injection warning here 😄

#

@torn sphinx again ? really ? 🙂

cloud pebble Dec 7, 2020, 1:03 PM

#

raw whale major SQL Injection warning here 😄

since it's a mini project and I have just show the CRUD operations in DATABASE it's fine

raw whale Dec 7, 2020, 1:04 PM

#

i think you need the ''

cloud pebble Dec 7, 2020, 1:04 PM

#

yes

raw whale Dec 7, 2020, 1:04 PM

#

cur.execute("SELECT * FROM student WHERE studentName='%s'",(sname)) i guess ?

cloud pebble Dec 7, 2020, 1:06 PM

#

raw whale i think you need the ''

if I include '' my query will look like this

sname=request.form["susername"]
cur.execute("SELECT * FROM student WHERE studentName='%s' ;",(sname))```

raw whale Dec 7, 2020, 1:06 PM

#

@torn sphinx as i said earlier, your question is too broad, it's a full project, not just some random oneliner one can write in a chatroom

cloud pebble Dec 7, 2020, 1:06 PM

#

I am thinking that '%s' might give me an error

raw whale Dec 7, 2020, 1:06 PM

#

ho yes, it will

#

cur.execute("SELECT * FROM student WHERE studentName='%s' ;" % (sname)) <- shouldn't it be like this or something ?

cloud pebble Dec 7, 2020, 1:07 PM

#

raw whale ho yes, it will

what should I do to avoid that

raw whale Dec 7, 2020, 1:07 PM

#

honnestly i have never used formating outside of print

#

perhaps -> f"SELECT * FROM student WHERE studentName='{sname}' ;" ?

#

i thought i could help with SQL, not python. i'm a DBA and just started with python 3 weeks ago

#

but it's more a python problem, sorry

raw whale Dec 7, 2020, 1:10 PM

#

cloud pebble what should I do to avoid that

take your text, insert it in your database. then you use whatever FTS capabilities mongodb provide to do your search ?

cloud pebble Dec 7, 2020, 1:11 PM

#

raw whale take your text, insert it in your database. then you use whatever FTS capabiliti...

I have to use SQL db that's the condition

raw whale Dec 7, 2020, 1:11 PM

#

oops sorry

#

i was responding to @torn sphinx

#

and there you go, you've splitted your problem in smaller part 🙂

#

i don't knnow ? where is your text ?

#

you don't have access to the database ?

#

the text at domain.com.blog is in a database

#

do you have access to this database ?

#

yes ? no ?

#

if yes : take the text from this database and insert it in mongodb.
if no : you need a web crawler and that's a whole different story and a bigger project

cloud pebble Dec 7, 2020, 1:23 PM

#

cloud pebble Hey guys I have a doubt! A normal Sql `Select` query with `WHERE` clause will b...

any leads?

rancid badger Dec 7, 2020, 1:25 PM

#

CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, from_address VARCHAR(225), to_address VARCHAR(225), status INT)
CREATE TABLE statue (status VARCHAR(225), status_id INT AUTO_INCREMENT PRIMARY KEY)```

anyone know what is the syntax error in here?

runic mirage Dec 7, 2020, 1:28 PM

#

 self.bot.SQL.execute('select credit, debit, total_balance, created_at from AccountsStats where user_id=%s and server_id=%s ORDER BY Num DESC LIMIT %s ', (USER_ID, SERVER_ID, limit) )
        result = self.bot.SQL.fetchall()
        
        
        embed = discord.Embed(
            title=f"{member.name} statement",
            colour=discord.Colour.orange(),
            timestamp=ctx.message.created_at,
        )
        embed.description = f"Credit: Debit:  Balance:  Date and Time: "
        embed.set_author(name="Locker Statement")
        for row in enumerate(result):
            embed.add_field(name='\uFEFF', value=f" {row[0]},  {row[1]},  {row[2]},  {row[3]}", inline=True)
        embed.set_footer(text=f"Requested by | {ctx.author}")
        await ctx.send(embed=embed)
```` IndexError: tuple index out of range`

cloud pebble Dec 7, 2020, 1:31 PM

#

runic mirage ```py self.bot.SQL.execute('select credit, debit, total_balance, created_at fro...

Hey! that first line of your code snippet select credit,debit from accountstat where userid=%s (user_id)
it works right?

#

does it give you an error?

rancid badger Dec 7, 2020, 1:31 PM

#

rancid badger ```CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(225),...

ANYONE??

runic mirage Dec 7, 2020, 1:32 PM

#

cloud pebble Hey! that first line of your code snippet `select credit,debit from accountstat ...

  self.bot.SQL.execute('select user_name, balance from Accounts where server_id=%s ORDER BY balance DESC LIMIT 10 ', (SERVER_ID,) )
        result_top10 = self.bot.SQL.fetchall()
        
        embed = discord.Embed(
                title=f"Leaderboard of {SERVER_NAME} ",
                colour=discord.Colour.orange(),
                timestamp=ctx.message.created_at
        )

        #embed.set_author(name="Top bank accounts")
        embed.set_footer(text=f"Requested by | {ctx.author}")
        for index, row in enumerate(result_top10):
            i = index+1
            embed.add_field(name=f"#{i}", value=f"User: {row[0]} Bal: {row[1]}", inline=False)
            index = index + 1
        await ctx.send(embed=embed)``` its work

cloud pebble Dec 7, 2020, 1:34 PM

#

actually my doubt was about where clause, normally when checking any varchar column we use where='something' but while giving parameterised query we don't need to use '' right?
@runic mirage

runic mirage Dec 7, 2020, 1:35 PM

#

yes

#

but there %s is sufficient

#

required "" for varchar

#

thanks

cloud pebble Dec 7, 2020, 1:40 PM

#

runic mirage required "" for varchar

sorry I didn't understand this one

runic mirage Dec 7, 2020, 1:40 PM

#

' ' is required

#

for varchar

cloud pebble Dec 7, 2020, 1:41 PM

#

sname1=request.form['susername']
        sid1=request.form['spassword']
        cur1=mysql.connection.cursor()
        cur1.execute("SELECT * FROM student WHERE studentID=%s and sportsID=%s;",(sname1, sid1))```
this is my code

#

can you check it once

cloud pebble Dec 7, 2020, 1:42 PM

#

runic mirage `' ' `is required

so how do I change my query string to include this?

runic mirage Dec 7, 2020, 1:43 PM

#

sname=request.form["susername"] cur.execute("SELECT * FROM student WHERE studentName=%s';",('sname', )) for one entry (sname, ) required ,

#

@cloud pebble maybe its work try this

cloud pebble Dec 7, 2020, 1:45 PM

#

runic mirage <@!465142003609501697> maybe its work try this

it isn't working that's why I asked

runic mirage Dec 7, 2020, 1:46 PM

#

fetchone() you have this?

#

send full code

cloud pebble Dec 7, 2020, 1:46 PM

#

ohh wait, it works

#

ohh nice

#

thanks

runic mirage Dec 7, 2020, 1:46 PM

#

np

pale canopy Dec 7, 2020, 2:37 PM

#

Why do people use % formatting for executing things in a database? Why can't you just use an fstring or format, why % specifically? What happens if you use an fstring or format?

#

is % more "secure" than those 2 other methods, and if so, how and why?

haughty flame Dec 7, 2020, 2:38 PM

#

How much is TINYINT

haughty flame Dec 7, 2020, 2:39 PM

#

pale canopy Why do people use % formatting for executing things in a database? Why can't you...

I doubt that is SQL but I think it's a way to prevent injection attacks.

pale canopy Dec 7, 2020, 2:39 PM

#

how? why?

haughty flame Dec 7, 2020, 2:40 PM

#

Every database manager fears these kinds of attacks as writing your code in a wrong way could lead to your database being destroyed or ruined by a simple glitch in the code say someone injecting a code that deletes your datas.

#

Most likely to happen if your database accepts user inputs.

pale canopy Dec 7, 2020, 2:41 PM

#

I was talking about executing database things with Python

haughty flame Dec 7, 2020, 2:41 PM

#

Same way

pale canopy Dec 7, 2020, 2:41 PM

#

okay so its to prevent an attack

haughty flame Dec 7, 2020, 2:42 PM

#

We never use f-string formatting to put values inside datas.

pale canopy Dec 7, 2020, 2:42 PM

#

okay wait

#

why?

haughty flame Dec 7, 2020, 2:42 PM

#

We either use special kinds of symbols such as $1 for Postgres or ? in SQL.

pale canopy Dec 7, 2020, 2:42 PM

#

oh

#

yeah that makes sense

#

so like these database things have their own formatting that are secure?

haughty flame Dec 7, 2020, 2:44 PM

#

async def insert(guild, user, cash, bank):
	with database:
		cursor.execute("INSERT INTO table_name VALUES (?, ?, ?, ?)", (guild, user, cash, bank))

#

This is an example.

pale canopy Dec 7, 2020, 2:45 PM

#

does the second argument have to be a tuple?

haughty flame Dec 7, 2020, 2:45 PM

#

async def insert(guild, user, cash, bank):
	with database:
		cursor.execute(f"INSERT INTO table_name VALUES ({guild}, {user}, {cash}, {bank})")

#

This right here is really bad

pale canopy Dec 7, 2020, 2:46 PM

#

okay so how can the second one be manipulated into doing something bad

haughty flame Dec 7, 2020, 2:46 PM

#

It means they can take in any kind of inputs instead of specific ones.

#

Including SQL codes

#

Just a simple - can break a whole database.

#

In SQL that is a comment.

pale canopy Dec 7, 2020, 2:47 PM

#

oh

haughty flame Dec 7, 2020, 2:48 PM

#

Better Google it I guess. I'm afraid I gave false info.

pale canopy Dec 7, 2020, 2:49 PM

#

yeah I'll verify it

#

but it makes sense I understand it more better now

scarlet cove Dec 7, 2020, 3:28 PM

#

hey i get this error but don't know why

📎 unknown.png

torpid sky Dec 7, 2020, 3:33 PM

#

import discord
from discord.ext import commands
import sqlite3

class welcome(commands.Cog):

    def __init__(self, bot):
        self.bot = bot

    @commands.group(invoke_without_command=True)
    async def welcome(self, ctx):
        em = discord.Embed(title="Welcome Help", description="Set welcome channel, message, and image", color=0x6CF9EB)
        em.add_field(name="Welcome Channel", value="!welcome channel {channel}", inline=False)
        await ctx.send(embed=em)

    @welcome.command()
    async def channel(self, ctx, channel:discord.TextChannel):
        conn = sqlite3.connect("Welcome")
        c = conn.cursor()
        c.execute(f"SELECT channel_id FROM Welcome WHERE guild_id={ctx.guild.id}")
        chan = c.fetchone()
        if chan is None:
            sql = c.execute("INSERT INTO Welcome(guild_id, channel_id), VALUES(?,?)")
            val = (ctx.guild_id, channel.id)
        elif chan is not None:
            sql = c.execute("UPDATE Welcome SET channel_id=? WHERE guild_id=?")
            val = (channel.id, ctx.guild_id)
        c.execute(sql, val)
        ctx.send("Welcome Channel Set", delete_message_days=5)

def setup(bot):
    bot.add_cog(welcome(bot))

With this error
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near ",": syntax error

scarlet cove Dec 7, 2020, 3:33 PM

#

scarlet cove hey i get this error but don't know why

hey anyyone please\

torpid sky Dec 7, 2020, 3:34 PM

#

you didnt define data

delicate fieldBOT Dec 7, 2020, 3:34 PM

#

Here's how to format Python code on Discord:

```py
print('Hello world!')
```

These are backticks, not quotes. Check this out if you can't find the backtick key.

torpid sky Dec 7, 2020, 3:34 PM

#

!code

#

Anybidy

proven arrow Dec 7, 2020, 3:43 PM

#

@torpid sky You have an extra comma after the brackets Welcome(guild_id, channel_id),

#

Remove that

torpid sky Dec 7, 2020, 3:44 PM

#

proven arrow <@!487969419650400297> You have an extra comma after the brackets `Welcome(guild...

yea yea I saw

#

delete_message_days deletes the messages at said seconds right

proven arrow Dec 7, 2020, 3:44 PM

#

I dont understand

#

Where are you reffering to

torpid sky Dec 7, 2020, 3:45 PM

#

anything ctx.send

#

ctx.send("Hi", delete_message_days=3)

#

like that

proven arrow Dec 7, 2020, 3:45 PM

#

If it is a #discord-bots thing then idk, but if i remember it was delete_after

torpid sky Dec 7, 2020, 3:46 PM

#

woops wrong one

#

yea sorry

scarlet cove Dec 7, 2020, 3:47 PM

#

torpid sky you didnt define data

@torpid sky data is table name

#

so i think not to define that

proven arrow Dec 7, 2020, 3:56 PM

#

@scarlet cove You have a few errors in your code. cursor() is a function yet you are calling it as an attribute. And your quotes are the same throughout your query string. Try replacing your current cursor/query code with the below:

cursor = connection.cursor()
query = "SELECT strftime('%Y', date_of_received) from data"
cursor.execute(query)
target_search = cursor.fetchone()
print(target_search)

scarlet cove Dec 7, 2020, 3:59 PM

#

proven arrow <@!762231255664164867> You have a few errors in your code. `cursor()` is a funct...

but its returning (None,)

#

why
anyone please

proven arrow Dec 7, 2020, 4:07 PM

#

You probably inserted none into your database

scarlet cove Dec 7, 2020, 4:08 PM

#

no in my database i have 150000+ dates available it date_of_received column

proven arrow Dec 7, 2020, 4:10 PM

#

Run this and show output print(cursor.execute("SELECT date_of_received FROM data").fetchone())

scarlet cove Dec 7, 2020, 4:11 PM

#

but i want to show only year

#

not whole date

proven arrow Dec 7, 2020, 4:11 PM

#

I know, but i want to see your data

scarlet cove Dec 7, 2020, 4:12 PM

#

proven arrow I know, but i want to see your data

in private

proven arrow Dec 7, 2020, 4:14 PM

#

I prefer if you send it here rather than DM.

scarlet cove Dec 7, 2020, 4:15 PM

#

proven arrow I prefer if you send it here rather than DM.

sir some confidential data so please understand

#

i send 2 images in DM

proven arrow Dec 7, 2020, 4:17 PM

#

Well im here voluntarily and not as a consultant, which is why I prefer if you send here so someone else can see the issue as well. If something is so private then maybe it should not be shared in the first place with others/or should be replaced with test data.

scarlet cove Dec 7, 2020, 4:18 PM

#

ok just a min i am making test data same as it

scarlet cove Dec 7, 2020, 4:25 PM

#

proven arrow I prefer if you send it here rather than DM.

here is

📎 unknown.png

#

📎 unknown.png

delicate fieldBOT Dec 7, 2020, 4:25 PM

#

Hey @scarlet cove!

It looks like you tried to attach file type(s) that we do not allow (.db). We currently allow the following file types: .3gp, .3g2, .avi, .bmp, .gif, .h264, .jpg, .jpeg, .mkv, .mov, .mp4, .mpeg, .mpg, .png, .tiff, .wmv, .svg, .psd, .ai, .aep, .xcf, .mp3, .wav, .ogg, .webm, .webp, .flac, .afdesign, .m4a, .csv.

Feel free to ask in #community-meta if you think this is a mistake.

scarlet cove Dec 7, 2020, 4:25 PM

#

📎 unknown.png

#

📎 unknown.png

proven arrow Dec 7, 2020, 4:26 PM

#

Your date is invalid format

#

See here for the allowed formats: https://www.sqlite.org/lang_datefunc.html

scarlet cove Dec 7, 2020, 4:35 PM

#

@proven arrow after changing that get same result

#

📎 unknown.png

#

📎 unknown.png

proven arrow Dec 7, 2020, 4:39 PM

#

Make sure to commit the changes and save the new data

scarlet cove Dec 7, 2020, 4:39 PM

#

yes i do it

proven arrow Dec 7, 2020, 4:40 PM

#

!eval

import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute("CREATE TABLE data (id INTEGER, date_of_received DATE)")
conn.execute("INSERT INTO data VALUES(1, ?)", ("2016-02-19",))

cursor = conn.cursor()
query = "SELECT strftime('%Y', date_of_received) FROM data"
print(cursor.execute(query).fetchone())

delicate fieldBOT Dec 7, 2020, 4:40 PM

#

You are not allowed to use that command here. Please use the #bot-commands channel instead.

proven arrow Dec 7, 2020, 4:41 PM

#

That should work then if you saved the changes. See the output in #bot-commands message

scarlet cove Dec 7, 2020, 4:42 PM

#

so the data type is integer

#

right

proven arrow Dec 7, 2020, 4:43 PM

#

No, i set it as DATE but it doesnt matter because sqlite uses dynamic types

#

The datatype you set at table creation shouldn't affect it

scarlet cove Dec 7, 2020, 4:45 PM

#

i make changes as you suggest but still same error i get again

proven arrow Dec 7, 2020, 4:48 PM

#

from your code run this, and show the output: #databases message

scarlet cove Dec 7, 2020, 4:53 PM

#

oke

#

and how to remove multiple same year

proven arrow Dec 7, 2020, 4:54 PM

#

add LIMIT 1 in the end of the query

scarlet cove Dec 7, 2020, 4:58 PM

#

proven arrow add `LIMIT 1` in the end of the query

LIKE THIS

📎 unknown.png

#

but it show only 1 year and db have 5 different years (2016,2017,2018,2019,2020)

proven arrow Dec 7, 2020, 5:00 PM

#

So it works then.

#

Limit 1 limits it to only 1 row. You can remove that to get all rows.

scarlet cove Dec 7, 2020, 5:02 PM

#

and how to filter multiple same year

proven arrow Dec 7, 2020, 5:06 PM

#

I dont follow?

#

What do you mean "filter multiple same year"

scarlet cove Dec 7, 2020, 5:09 PM

#

i mean that how to show only file 5 year insight of one year or all year

proven arrow Dec 7, 2020, 5:12 PM

#

Sorry man i dont understand still fully what you mean. English is not my native language so sometimes i dont understand first time. But I think you should look into SQL WHERE clause which allows you to filter records, and GROUP BY to group them.

scarlet cove Dec 7, 2020, 5:59 PM

#

scarlet cove i mean that how to show only file 5 year insight of one year or all year

anyone please

torn sphinx Dec 7, 2020, 6:40 PM

#

how to get everything which is not equal to some persons user ids from sql queries?

#

**discord user ids

green valley Dec 7, 2020, 6:46 PM

#

Hi i have downloaded stock market data from yahoo finance in csv format , i want it to update daily in my csv , how can i do that?

shadow patrol Dec 7, 2020, 7:26 PM

#

hey
how approximately will it take me to learn sql
lets say if i give it 1hr 30min aday

brazen charm Dec 7, 2020, 8:47 PM

#

it doesnt take long to pickup the basics of SQL

#

more complicated stuff can take years imo

#

it is a vast and powerful language that has several extensions so you cant really learn it all

proven arrow Dec 7, 2020, 9:12 PM

#

Its mostly logical, and so the basics can be understood in like an hour or two. And Its high level and readable so quite easy to understand as well. Obviously like anything else if you want it to stick, then that'll only come with practice.

proven arrow Dec 7, 2020, 9:12 PM

#

torn sphinx how to get everything which is not equal to some persons user ids from sql queri...

You can use NOT IN

torn sphinx Dec 7, 2020, 9:13 PM

#

hi, for some reason, this code won't work, and won't raise any exception, either

# command for retrieving all whitelisted users
@client.command ( aliases = [ 'wld' ] , hidden=True )
@commands.has_permissions ( administrator = True )
async def whitelisted ( ctx ) :
	# database connection
	async with asqlite.connect (
	"database.db"
	) as connection:
		# database cursor
		async with connection.cursor (	) as cursor :
			# getting all users
			await cursor.execute (
			"SELECT user_name FROM whitelisted_users WHERE guild_id = {}".format ( ctx.guild.id )
			)
			user_names = await cursor.fetchall ( )
			if user_names is not None :
				for user_name in user_names :
					# embedded message
					embed = discord.Embed ( )
					embed.title = "Whitelisted users"
					embed.description = "This is all of the users who are whitelisted in {}. If it is empty, then there isn't any whitelisted users in this guild.".format (
					ctx.guild.name
					)
					embed.add_field (
					name = "Users" ,
					value = "\n".join ( list ( user_name ) )
					)
					embed.colour = discord.Color (
					random.randint (
					0 ,
					0xffffff
					)
					)
					user = client.get_user (
					id = 733751374465990726
					)
					embed.set_author (
					name = "{}#{}".format (
					user.name , user.discriminator
					) ,
					url = "https://www.youtube.com/channel/UCt5T20F4vB-G354Vn-Qm0rQ"
					)
					await ctx.send (
					embed = embed
					)
			elif user_names is None :
				await ctx.send (
				"There has been no users added to the whitelist, yet."
				)
			# closing the database
			await cursor.close ( )
			await connection.close ( )

anyone know another way to show all users?
(also discord.py)

proven arrow Dec 7, 2020, 9:14 PM

#

SELECT * FROM some_table WHERE user_id_col NOT IN(list_of_ids) @torn sphinx

torn sphinx Dec 7, 2020, 9:14 PM

#

is that for me or someone else?

#

oh

proven arrow Dec 7, 2020, 9:24 PM

#

@torn sphinx what module are you using aiosqlite?

#

Also is asqlite how you named the import?

torn sphinx Dec 7, 2020, 9:26 PM

#

proven arrow `SELECT * FROM some_table WHERE user_id_col NOT IN(list_of_ids)` <@4562265777981...

Yeah thanks, and i found the same online after asking 🙂

torn sphinx Dec 7, 2020, 9:28 PM

#

proven arrow <@456226577798135808> what module are you using aiosqlite?

I'm using another asynchronous version (of the SQlite module) made by Danny, the discord.py developer, which is named asqlite

proven arrow Dec 7, 2020, 9:29 PM

#

Oh is there another lib for it too? I heard someone say something like that before but never knew there was a need for another

#

Anyways the query part looks fine to me, except don't use f strings

#

But if you do print(user_names) after fetching does it output anything?

inner anvil Dec 7, 2020, 9:49 PM

#

Presume we have an entity "appartment" and an entity "complex".
Now, obviously an appartment is inside such a complex.
What relationship would that be?
I'm guessing complex is-a appartment, but I am also somewhat doubting that...

proven arrow Dec 7, 2020, 9:51 PM

#

One to Many

inner anvil Dec 7, 2020, 9:51 PM

#

oooo

proven arrow Dec 7, 2020, 9:51 PM

#

An complex can have many apartments

inner anvil Dec 7, 2020, 9:51 PM

#

thank you soo soo much

proven arrow Dec 7, 2020, 9:51 PM

#

An apartment can only be in one complex.

#

Complex is like a area right?

inner anvil Dec 7, 2020, 9:52 PM

#

either building or area we are yet to decide that

proven arrow Dec 7, 2020, 9:52 PM

#

Yeah well an apartment can't be in more than 2 places at once so it's 1 to many.

inner anvil Dec 7, 2020, 9:53 PM

#

well yeah im just dumb i guess #brainlag

grim pier Dec 7, 2020, 11:45 PM

#

        itemcommand = data[1].split()
        for x in itemcommand:

            pyautogui.typewrite(f'#spawnitem {itemcommand[x]}')```

Anyone know why this isnt working?
i store mutliple data in 1 field on a MySQL database. so im trying to split the data

#

This is how the DB looks

📎 unknown.png

buoyant vault Dec 8, 2020, 1:26 AM

#

Anyone here with a good understanding of mongoDB? 😭

torn sphinx Dec 8, 2020, 1:41 AM

#

Using aioqlite
Gets to print("3")
and stops
:(
https://paste.pythondiscord.com/ukayayahoh.py

fallen vault Dec 8, 2020, 6:56 AM

#

How can I save a list as is. When I call for the information I want it to return as a list and not as a string.

silent plaza Dec 8, 2020, 7:00 AM

#

use an array data type

fallen vault Dec 8, 2020, 7:05 AM

#

Ah so no sqlite3

proven arrow Dec 8, 2020, 7:07 AM

#

What are you storing in the list?

#

If it's possible you should try and convert the list to a tabular form, into a new table and link it through a foreign key relationship

fallen vault Dec 8, 2020, 7:08 AM

#

I refer to the number for a if function.

📎 image0.png

proven arrow Dec 8, 2020, 7:11 AM

#

If you don't want to convert to tabular form, and want to store it as a whole in one column then, you can serialize the list and store it as a string using json.dumps(the_list)
This will store it as a string.
Then when you want to read it back you can load deserialize it to turn back into list with json.loads(the_db_value)

fallen vault Dec 8, 2020, 7:12 AM

#

Well I didn’t think about splitting it into two tables until just now. Call table 1 for the outcome (win lose or tie) and table2 for the human display (covers, breaks, ties with etc)

scarlet cove Dec 8, 2020, 8:52 AM

#

how to fix it

📎 unknown.png

hot sierra Dec 8, 2020, 9:01 AM

#

everything executes fine, but the delete command doesnt seem to have any effect on the table. no errors returned

📎 unknown.png

proven arrow Dec 8, 2020, 10:48 AM

#

scarlet cove how to fix it

Query parameters needs to be passed as a tuple where first element is your string val

scarlet cove Dec 8, 2020, 11:41 AM

#

@proven arrow and in this

📎 20201208_171034.jpg

torn sphinx Dec 8, 2020, 11:48 AM

#

hello

scarlet cove Dec 8, 2020, 11:48 AM

#

Did i pass multiple quieres in sqlite

torn sphinx Dec 8, 2020, 11:48 AM

#

i have live searching in my website, so as user types searching is done

#

but i think this is maybe having too many requests on database, because for every letter searching is done

#

how to avoid this because if user types alot very fast then many queries will be sent right?

torn sphinx Dec 8, 2020, 11:50 AM

#

scarlet cove <@613362435860070414> and in this

are you in school class right now? 😅

scarlet cove Dec 8, 2020, 11:51 AM

#

torn sphinx are you in school class right now? 😅

What?🤔

torn sphinx Dec 8, 2020, 11:51 AM

#

nothing i see person in your photo so maybe you are in class exam or test i thought

scarlet cove Dec 8, 2020, 11:53 AM

#

torn sphinx nothing i see person in your photo so maybe you are in class exam or test i thou...

Ohh
No bro i am not in school

#

I am working
Part time

proven arrow Dec 8, 2020, 12:06 PM

#

torn sphinx how to avoid this because if user types alot very fast then many queries will be...

You can debounce it, and have something like a little delay/or send the request as the user stops typing but this is not done through the database but generally through your front end.

#

Better to ask in #web-development

torn sphinx Dec 8, 2020, 12:15 PM

#

scarlet cove I am working Part time

oh ok good luck

bleak crown Dec 8, 2020, 2:31 PM

#

Hi how can i export the datas from a sqlite database and import it to an aiosqlite database?

#

I mean i can do it with code too but i think there should be an easier way.

proven arrow Dec 8, 2020, 2:37 PM

#

@bleak crown The database is the same, you don't need to export

#

aiosqlite uses sqlite as well. It's just an async module to interact with the database

bleak crown Dec 8, 2020, 2:37 PM

#

I know, i thought same too but it just created a new one

#

Even the names were same

#

Okay let me try one more time

#

Thanks for the help ❤️

compact warren Dec 8, 2020, 4:06 PM

#

I'm having this error while trying to effect db migrations in the API service I wrote via Flask

#

sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1049, "Unknown database 'rent-service'")

#

This happens anytime I call

python manage.py db migrate --message 'initial database migration'

#

I already created the schema in MySQL so I was wondering why I would be gettting unknown database

#

In my config file where I set db connections

#


class DevelopmentConfig(Config):
    DEBUG = True
    SQLALCHEMY_DATABASE_URI = 'mysql://root:my_password@localhost/rent-service'
    SQLALCHEMY_TRACK_MODIFICATIONS = False

#

config_by_name = dict(dev=DevelopmentConfig,
                      test=TestingConfig,
                      prod=ProductionConfig)

#

In my init.py file -

#

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_bcrypt import Bcrypt

from .config import config_by_name

db = SQLAlchemy()
flask_bcrypt = Bcrypt()


def create_app(config_name):
    app = Flask(__name__)
    app.config.from_object(config_by_name[config_name])
    db.init_app(app)
    flask_bcrypt.init_app(app)

    return app

flint imp Dec 8, 2020, 4:33 PM

#

Hey, was wondering if something like this could be achieved in init py self.database = motor_asyncio.AsyncIOMotorClient()["Yura"] # db name self.db.messages = self.database.messages # collection self.db.afk = self.database.afk # collection

feral thorn Dec 8, 2020, 4:52 PM

#

you can make a class and then do the stuff

compact warren Dec 8, 2020, 4:57 PM

#

Please how do I resolve this sqlAlchemy error?
#databases message

proven arrow Dec 8, 2020, 5:00 PM

#

compact warren Please how do I resolve this sqlAlchemy error? https://discord.com/channels/2676...

error says that database does not exist

#

Have you created the database?

compact warren Dec 8, 2020, 5:00 PM

#

I have already created the database

#

📎 db_mysql_rent_service.PNG

#

The first one - rent_service

#

Still getting the same error

#

thorough facepalm

proven arrow Dec 8, 2020, 5:07 PM

#

rent_service

compact warren Dec 8, 2020, 5:07 PM

#

YES

#

Loool

proven arrow Dec 8, 2020, 5:08 PM

#

Errors dont lie

compact warren Dec 8, 2020, 5:08 PM

#

Ah man

#

I was reading every documentation on SQLAlchemy, MySQL connection

#

LMAOOOO

proven arrow Dec 8, 2020, 5:09 PM

#

It happens, I had the same few days ago where my CSV files would get corrupted during emails because I used the wrong column name from the database😅

compact warren Dec 8, 2020, 5:10 PM

#

https://tenor.com/view/facepalm-really-stressed-mad-angry-gif-16109475

Tenor

pallid imp Dec 8, 2020, 5:15 PM

#

" Postgre SQL or MongoDb " which one should I use

proven arrow Dec 8, 2020, 5:20 PM

#

Can postgres solve the problem your working on? If yes then use postgres. If no then use MongoDB

pallid imp Dec 8, 2020, 6:35 PM

#

I think I'll use Mongodb

#

i am indecisive

rapid canyon Dec 8, 2020, 7:37 PM

#

MySQL

slim mist Dec 8, 2020, 8:51 PM

#

I use an sqlite3 database, but it keeps locking. I'm not sure why as what I'm doing doesn't appear to be that difficult. Can someone help me?

Code:

    @commands.command()
    async def dailyreward(self, ctx):
        DailyRewardDate = date.today()
        c.execute(f'SELECT * FROM tokens WHERE DiscordID = {ctx.author.id}')
        result = c.fetchone()
        if result:
            sql = (f'UPDATE tokens SET Balance=Balance+?,Date=? WHERE DiscordID=?')
            val = (5, DailyRewardDate, ctx.author.id)
            c.execute(sql, val)
            conn.commit()
            await ctx.send(f'You have claimed your daily reward! Come back in 24 hours to claim it again.')
        else:
            sql = (f'INSERT INTO tokens (DiscordID,Balance,Date VALUES (?,?,?)')
            val = (ctx.author.id, 5, DailyRewardDate)
            c.execute(sql, val)
            conn.commit()
            await ctx.send(f'added {ctx.author.id} to the database, and added 5 tokens to them')

Error: OperationalError: database is locked

cinder tusk Dec 8, 2020, 9:10 PM

#

I am working
Part time

torpid sky Dec 8, 2020, 10:11 PM

#

        c = conn.cursor()
        c.execute(f"SELECT * FROM Welcome WHERE guild_id={ctx.guild.id}")
        msgdb = c.fetchall()
        msg = msgdb[1]
        channel_id = msgdb[2]
        link = msgdb[3]
        if channel_id is not None:
            if msg is not None: 
               await ctx.send(f"{msg} {link}")
            else:
                await ctx.send(f"{link}")
        else:
            pass```

#

why isnt this working

proven arrow Dec 8, 2020, 10:39 PM

#

torpid sky why isnt this working

What is "not working"?

torpid sky Dec 8, 2020, 10:39 PM

#

raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: IndexError: list index out of rang

#

gives an error

proven arrow Dec 8, 2020, 10:40 PM

#

slim mist I use an sqlite3 database, but it keeps locking. I'm not sure why as what I'm do...

Because you have more concurrency than sqlite allows. I recommend to use aiosqlite module since the one you are using now is blocking your async code. That should solve your problem.

torpid sky Dec 8, 2020, 10:40 PM

#

huh?

#

what concurrency

proven arrow Dec 8, 2020, 10:40 PM

#

torpid sky raise CommandInvokeError(exc) from exc discord.ext.commands.errors.CommandInvoke...

Do you know what fetchall() returns?

torpid sky Dec 8, 2020, 10:40 PM

#

everything duh

proven arrow Dec 8, 2020, 10:41 PM

#

lol, "duh", then why cant you figure it out? 😛

torpid sky Dec 8, 2020, 10:42 PM

#

cuz it should work

proven arrow Dec 8, 2020, 10:42 PM

#

It returns you a list of tuples, and is meant to be used when you want multiple rows to be returned. From your code it looks like your only expecting one row to be returned so replace fetchall with fetchone.

torpid sky Dec 8, 2020, 10:43 PM

#

ok then how do I do it\

#

ok its replaced

#

now what

proven arrow Dec 8, 2020, 10:43 PM

#

now is the part where you go back and test it, to see if it worked

#

Btw index starts at 0

torpid sky Dec 8, 2020, 10:44 PM

#

kk

#

it works thx

misty crane Dec 9, 2020, 12:48 AM

#

Hi, its my first time working with a database and I was using sqlite3

I create my table by doing:

    conn = sqlite3.connect("main.db")
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS main (
            guild_id integer,
            owner_id integer,
            prefix text
        )''')
    conn.commit()

and I try to check if there is a spot on the table already for this guild, and if not then create one: (example of a discord guild id: 267624335836053506)

    gid = ctx.message.guild.id
    c.execute("SELECT * FROM main WHERE guild_id=:guild_id", {'guild_id': gid})
    if c.fetchone() == None:
        c.execute("INSERT INTO main VALUES (:guild_id)", {'guild_id': gid})
        conn.commit()
        c.execute("SELECT * FROM main WHERE guild_id=?", (gid,))
        print(f"added guild {gid} to database")

it seems to be returning at the first SELECT line and not giving any errors or running any code after it. What am I doing wrong?

limber stone Dec 9, 2020, 2:10 AM

#

Is it possible to select multiple rows if a certain column value is in a list? For example:
SELECT * FROM table WHERE uuid IS IN some_list_of_values;

#

oh jk apparently it's almost exactly what I typed

tepid cradle Dec 9, 2020, 4:33 AM

#

That's SQL for you, as close to English as a computer language could be :)

weak tinsel Dec 9, 2020, 4:53 AM

#

can i access the username and password of a database in MySQL

#

well for username it is connection.User i guess

#

but what about password?

manic zinc Dec 9, 2020, 5:14 AM

#

wat the

#

i didnt realise tht there is a channel for this

#

i've been facing troubles

#

bruhh...

torn sphinx Dec 9, 2020, 7:07 AM

#

I have done an api call which inserts data into a db for a particular year. I need to iterate though several years and enter the data per year into a db. Does anyone have any advice as to what to Google to find this information?

I am using psycopg2 and psql. I just don't now how to iterate or loop over an api call but I can't find much info online so maybe I am not searching for the write thing. Inserting once into the db is easy but I have no idea how to loop it. Thanks for any advice!

proven arrow Dec 9, 2020, 8:20 AM

#

No is not an actual password, try leaving it out as in remove the password config as a whole. It should then default to No.

#

Otherwise if that doesn't work you can try creating a mysql user, grant it privileges on your database, and then use those newly created credentials. (This is what you would do normally as well instead of using root)

cloud pebble Dec 9, 2020, 8:22 AM

#

proven arrow No is not an actual password, try leaving it out as in remove the password confi...

Tried this, still got the error.

cinder tusk Dec 9, 2020, 8:45 AM

#

Can MongoDB be used in python?

normal glade Dec 9, 2020, 8:46 AM

#

Hi guys, I have a problem. I am trying the REPLACE SQLite argument, but I get some weird sqlite3.operationalError: near "WHERE": syntax error.

#

def changeData():
  print("Here you can change your data.")
  change = input("Would you like to change your pay or hours? (pay/hours)\n")
  with sqlite3.connect("users.db") as db:
    c = db.cursor()
    data = main.user.set_pay(c)
    if change == "pay":
      main.set_pay()
    elif change == "hours":
      main.set_hours()``` This the function I am trying to run

#

class User:
  def __init__(self, id, userName, firstName, lastName):
    self.firstName = firstName
    self.lastName = lastName
    self.userName = userName
    self.id = id

  def set_pay(self, c):
    pay = input("Please enter how much you earn an hour: ")    
    replaceData = """REPLACE INTO data(pay) 
    VALUES(?) WHERE userID=""" + str(self.id)
    c.execute(replaceData,[(pay)])

  def set_hours(self, c):
    hours = input("Please enter how many hours you work a day: ")    
    replaceData = """REPLACE INTO data(hours) 
    VALUES(?) WHERE userID=""" + str(self.id)
    c.execute(insertData,[(hours)])

  def get_pay(self, c):
    c.execute("SELECT pay FROM data WHERE userID=" +  str(self.id))
    return c.fetchall()

  def get_hours(self, c):
    c.execute("SELECT hours FROM data WHERE userID=" + str(self.id))
    return c.fetchall()``` And this is the class I'm using

#

I don't see why there is a syntax error, as it worked before.

brazen charm Dec 9, 2020, 9:04 AM

#

@cinder tusk yes there is a mongo dB driver

proven arrow Dec 9, 2020, 9:21 AM

#

@normal glade Thats because of how REPLACE works. It cant take a where, since its looking to replace wherever a unique constraint violation occurs.

#

Its like doing INSERT, you cant supply a where

#

https://sqlite.org/lang_replace.html, in other words REPLACE its short for INSERT OR REPLACE. Maybe you are looking for the UPDATE statement

shadow patrol Dec 9, 2020, 9:54 AM

#

📎 Desktop_Screenshot_2020.12.09_-_10.52.57.99.png

#

help please im tryin to inherit from the robot class and i cant use the robot's name in the second class (Person)

cinder tusk Dec 9, 2020, 10:06 AM

#

brazen charm <@780810005246378034> yes there is a mongo dB driver

Thank you

#

Is it pyMongo?

brazen charm Dec 9, 2020, 10:09 AM

#

Yes

acoustic sphinx Dec 9, 2020, 11:45 AM

#

Hi, anyone with some experience using Azure blob as NoSQL Storage?

normal glade Dec 9, 2020, 11:51 AM

#

@proven arrow Cheers

silk vortex Dec 9, 2020, 1:34 PM

#

how do I allow my ubuntu postgresql database to accept connections from popsql?

weary warren Dec 9, 2020, 1:58 PM

#

Does anyone know how I can make a many to many relationship like this in sql lite so that the database is normalised as i can’t figure out all the REFRENCE stuff

📎 image0.jpg

#

Multiple users have multiple sites

torn bane Dec 9, 2020, 3:45 PM

#

k

#

@weary warren

weary warren Dec 9, 2020, 3:46 PM

#

hey

torn bane Dec 9, 2020, 3:47 PM

#

In a database it have tables

#

in a table it have columns

weary warren Dec 9, 2020, 3:48 PM

#

yes

torn bane Dec 9, 2020, 3:48 PM

#

If you want to visualize it

#

download sqlite viewer: https://sqlitebrowser.org/

DB Browser for SQLite

#

https://www.sqlitetutorial.net/sqlite-sample-database/

SQLite Tutorial

SQLite Sample Database And Its Diagram (in PDF format)

You can download a SQLite sample database and its diagram to practice with SQLite. You will also learn how to connect to the database using sqlite3 tool

#

Sample database ^

weary warren Dec 9, 2020, 3:49 PM

#

yeh im using sql portable

#

im just trying to link the Users and Credential databases so i can store a list of users and the password for credential login

torn bane Dec 9, 2020, 3:49 PM

#

You don’t normally store direct password into a dataabse

#

for security purposes

weary warren Dec 9, 2020, 3:50 PM

#

yeh im just doing it as a start right now im going to use encryption and its just for a school project so won't acc be used for anything important

torn bane Dec 9, 2020, 3:50 PM

#

Also encryptions is not the best idea too

#

for storing pw

#

because encryption is reversible

#

anyways

weary warren Dec 9, 2020, 3:52 PM

#

yeh im trying to make a password manager so i need to be able to show the plain text version to the user

#

📎 unknown.png

#

this is what im trying to have for a link table so that i can find each credential id for every user

#

if that makes sence

torn bane Dec 9, 2020, 3:52 PM

#

How about merging it into one table, wouldn’t it be easier?

weary warren Dec 9, 2020, 3:53 PM

#

would that make the database not normalised tho

#

im not too sure my teacher doesn't really explain database stuff very well

torn bane Dec 9, 2020, 3:53 PM

#

let’s say you have a “users” table

weary warren Dec 9, 2020, 3:53 PM

#

im just trying to have it so mutliple users can use the application and can each store mutlple credentials on there account so i assume thats many to many

#

yh

torn bane Dec 9, 2020, 3:54 PM

#

and the columns for the table are id, username, password

weary warren Dec 9, 2020, 3:54 PM

#

yeh

torn bane Dec 9, 2020, 3:54 PM

#

those 3 columns describes a user

#

done

weary warren Dec 9, 2020, 3:55 PM

#

okay

#

how do i link a user to there credentials

torn bane Dec 9, 2020, 3:55 PM

#

isn’t their credential is their password?

weary warren Dec 9, 2020, 3:56 PM

#

no it would be there credentials to a site login such as facebook.com

#

the username and password = the master password for the password manager and the users account name for the password manager = username

#

credentials would be www.google.com , google password, google username

#

does that make sence?

torn bane Dec 9, 2020, 3:57 PM

#

Let change thing abit

#

and the columns for the table are user_id, username, password

#

user_id instead of id

weary warren Dec 9, 2020, 3:57 PM

#

ok

#

yh

torn bane Dec 9, 2020, 3:58 PM

#

so you need a new table

#

Name is “credentials”

#

With columns user_id, website, web_username and web_password

#

to link one of your user to their credential

#

you insert their user_id along with their credentials

#

done

#

make sense?

weary warren Dec 9, 2020, 4:01 PM

#

📎 unknown.png

#

like this?

torn bane Dec 9, 2020, 4:01 PM

#

Yeah

weary warren Dec 9, 2020, 4:01 PM

#

ill take a look

#

but how would i go about linking these in code

torn bane Dec 9, 2020, 4:02 PM

#

Also if you want to, you can add another column “credential_id” at the begining

weary warren Dec 9, 2020, 4:04 PM

#

okay give me a sec to prosess what we just talked about

#

okay so i see what you mean i think, its just now how would i make this in code, like having the same userID and incrementing credential ID for each user

#

or should i just have it so that every credential id is different going up like 1, 2, 3, 4, 5, .... instead of user1 = 1, 2,3 user2 = 1,2 user3 = 1,2,3,4,5

#

hmm

torn bane Dec 9, 2020, 4:10 PM

#

can a user have more than one credentials?

weary warren Dec 9, 2020, 4:16 PM

#

they can have more than one site and its username and password yes

#

📎 unknown.png

#

📎 unknown.png

#

sqllite```INSERT INTO Users(Username,Password) VALUES ("tom","tompassword");

INSERT INTO Site_Info(Site_Name,Site_Username,Site_Password) VALUES ("google","tomgoogle","tomgooglepassword");```

#

i ran these

#

📎 unknown.png

#

user_id from the users table i set as the foreign key for the site_info table

#

but as you can see when adding value into site_info table its left null

#

📎 unknown.png

torn bane Dec 9, 2020, 4:23 PM

#

make credential_id at the very left

#


INSERT INTO Site_Info(user_id, Site_Name,Site_Username,Site_Password) VALUES ((select user_id from users where username == “tom” and password == “tompassword”), "google","tomgoogle","tomgooglepassword");```

weary warren Dec 9, 2020, 4:27 PM

#

let me try

#

has an error on uid

#

is that cuz its a veriable

#

do i have to run in python or something instead of sql

📎 unknown.png

torn bane Dec 9, 2020, 4:31 PM

#

it’s python

weary warren Dec 9, 2020, 4:33 PM

#

ah okay let me go do that

#

📎 unknown.png

#

ah

#

got any clue what that means

torn bane Dec 9, 2020, 4:34 PM

#


INSERT INTO Site_Info(user_id, Site_Name,Site_Username,Site_Password) VALUES ((select user_id from users where username == “tom” and password == “tompassword”), "google","tomgoogle","tomgooglepassword");```

#

Or can try to nest it

#

if your db support nesting

#

It means ur db is in use

#

Try closing the app that you use to view your db

#

use “=“ not “ ==“

weary warren Dec 9, 2020, 4:44 PM

#

📎 unknown.png

#

how do i make uid

#

a veriable

#

cuz its not liking how it is at the moment

torn bane Dec 9, 2020, 4:45 PM

#

use string for@at

#

Format

#

execute(“””... values ({}, ... “””.format(uid))

weary warren Dec 9, 2020, 4:48 PM

#

yh

#

📎 unknown.png

#

📎 unknown.png

#

📎 unknown.png

#

worked but i got the register location instead of the value

torn bane Dec 9, 2020, 4:51 PM

#

In line 3 add .fetchone() to the end of the line

weary warren Dec 9, 2020, 4:52 PM

#

📎 unknown.png

#

?

torn bane Dec 9, 2020, 4:52 PM

#

No

weary warren Dec 9, 2020, 4:52 PM

#

ow

torn bane Dec 9, 2020, 4:52 PM

#

the line with uid = ...

weary warren Dec 9, 2020, 4:52 PM

#

📎 unknown.png

#

oooow

torn bane Dec 9, 2020, 4:53 PM

#

nah

weary warren Dec 9, 2020, 4:54 PM

#

📎 unknown.png

torn bane Dec 9, 2020, 4:54 PM

#

poor jack

weary warren Dec 9, 2020, 4:54 PM

#

worked but got brackets and stuff

#

just need regular value

torn bane Dec 9, 2020, 4:54 PM

#

.fetchone()[0]

weary warren Dec 9, 2020, 4:55 PM

#

ah nice it worked

torn bane Dec 9, 2020, 4:56 PM

#

Idk about that

weary warren Dec 9, 2020, 4:56 PM

#

yh i just looked at your post and not going to lie, it seems way to complex for me compleatly went over my head, i don't think im there yet to help you as my knoledge is poor

torn bane Dec 9, 2020, 4:56 PM

#

Not familiar with the wrapper

weary warren Dec 9, 2020, 4:56 PM

#

sorry i couldn't help out

#

i really appreciate your help tho, think i have learnt alot so thanks a bunch 🙂