databases

[torn sphinx]

Hey so,

I am making a discord bot,

And I am trying to transfer all Json to a database,

But I never used a database,

So can anyone help me set it up?

[indigo flare]

Right then, I am doing ^ but having a more specific issue, I am trying to connect to my DB with asyncpg and it never gets past my conn = await asyncpg.connect call, if I have host=127.0.0.1 it gives me asyncpg.exceptions.ConnectionDoesNotExistError: connection was closed in the middle of operation, if I remove host= it gives me asyncpg.exceptions.InvalidAuthorizationSpecificationError: Peer authentication failed for user "tts"

[indigo flare]

already fixing on dpy

[lethal yew]

@mortal forum you can connect to the database and load datetime values into array and make something like timers (I don't know which timers are exist in Python)

*timer for each datetime value

[torn sphinx]

can someone help me with connecting python and postgres

[velvet coyote]

what's your driver? @torn sphinx

[torn sphinx]

its latest ig

just downloaded yesterday

[velvet coyote]

no i meant what module are u going to be using in python?

to connect to postgres

[torn sphinx]

postgres sql

oh

wait

import psycopg2
import psycopg2.extras

this

lemme show u code

[velvet coyote]

i think it says i in the docs

[torn sphinx]

    def get_bank_data(self):
        with conn:
            with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
                cur.execute("SELECT id FROM userdata;")  
                users = cur.fetchall()
            return users
        conn.close()

    def open_account(self, user):
        with conn:
            self.user = user
            users = self.get_bank_data()
            id = self.user.id
            if id in users:
                return False
            else:
                with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
                    cur.execute(f"INSERT INTO userdata(id, wallet_chakrapoints, bank_chakrapoints) VALUES({id}, 500, 0)")
                    
                return True
        conn.close()

this is the code

i tried to run this with discord.py

but didnt work

[velvet coyote]

if u want to use discord.py i suggest using asyncpg

[torn sphinx]

what does that do

[velvet coyote]

its another driver

!d asyncpg

[delicate field]

`asyncpg`

[velvet coyote]

its simpler imo

[torn sphinx]

so can i connect python with postgres?

using that

[velvet coyote]

yes

it says how to connect

in the docs

[torn sphinx]

aight thanks

can i search in youtube?

does it shows

[velvet coyote]

the docs has everything

[torn sphinx]

i kinda dont know how to read docs so

i get confuse in docs

[velvet coyote]

import asyncpg

async def connection():
  conn = await asyncpg.connect(postgres://user:password@host:port/database?option=value)

[torn bane]

sql injection

they want to hack me

help

[torn sphinx]

https://tenor.com/view/ricardo-milos-mijolnir-ricardomilos-mijolnir-ricardo-gif-14302852

[velvet coyote]

? don't post random memes in on topic channel

[mystic cairn]

hello, new here and to python - is there a way to sort data in a sqlite database from ascending or descending or insert new data to the top row only?

[shell ocean]

the database isn't inherently ordered; only query results are (though you can create an index)

so like if you wanted results in ascending order you could do SELECT column_1, column_2 FROM table ORDER BY column_2

[torn sphinx]

Is this channel alive

anybody here to help

[sudden flame]

Anyone else like just using simple sqlite instead of postgresql? I find it to be far, far more useful for making packages that others can use and alter easily

what do you need help wit @torn sphinx

[steady lava]

Not for table names. Only values.
Also why do you need to create dynamic table names?
Looks like you can better design your schema
@proven arrow sorry for late reply ..
Actually I had an idea related to different table names like, suppose if multiple users have an access to that CLI music player, then it will first ask to submit the user name, and if the user already exist.. I meant, if there's a table of his playlist then retrieve that playlist and if not, then first do things (playing song, adding more songs to the playlist) and save this playlist by creating a new table under the database.
you get what I am saying ?

so, that's why I needed dynamic table names.

[steady lava]

ohhh I guess, I was doing something wrong.. like I was performing the operation INSERT INTO <database_name> and in actual it should be performed in the table you've created under the DB. like thiis INSERT INTO <table_name> but whatever still the problem is same, i can't use ? as a placeholder for table_name 😦

[mystic cairn]

@shell ocean will that keep the integrity of the order the data set was inputted? or is it better (aka easier) to sort via index instead - not looking to sort the values in the database but the actual rows (if that makes sense) ty

[steady lava]

def run_cursor(obj):
    """ Perform queries on SQL """

    cursor = obj.cursor()
    table_name = input(' Enter table name : ')
    # Create a table, doc string to write multiple SQL query.
    # execute single SQL statement
    cursor.execute(""" CREATE TABLE {0} (
        Id integer,
        Title text,
        Link text
    )""".format(table_name))

    id_ = int(input(' Enter id : '))
    Title = input(' Enter song title : ')
    Link = input(' Enter song link : ')
    cursor.execute("INSERT INTO {0} VALUES (?, ?, ?)".format(table_name), (id_, Title, Link))

    # make changes (commit) to database
    obj.commit()
    obj.close()

this code snippet is only for testing purpose right now, and it works but I've a question related to the database it created.. so, running this code snippet, when I tried checking the same database via mysql then it didn't display its name.. The only database visible there was the one which I created using their CLI interface not with python... why it didn't display the database I created using python's mysqlite3 ?

[proven arrow]

sqlite, and mysql are different databases, so why would you expect it to go to the same one?

[steady lava]

@proven arrow so, the databases created by them are different too ?

[proven arrow]

They are different systems, so again why would they not be different?

[viscid osprey]

With an asyncpg connection pool, should I set up my _pool attribute as a singleton, or does the connection pool already handle multiple instantiations? I have a Database class with a _pool attribute, but I don't want to create a new connection pool every time I instantiate Database

[torn sphinx]

how do i update postgres data using tortoise orm module in python

how do i insert something using WHERE

like i insert two things and the one block value is left empty

i want to fill that 1 value later on

[calm prawn]

So I have a query with sqlite

If My cursor is c of a database named Expcards

c.execute('SELECT Exp FROM Expcards WHERE Level = (?)',(a))

What mistake I am doing here?

I mean I am not sure How to use dynamic data here

[brazen charm]

why are you wrapping it in a ()

sqlite requires the values you give it to be in a tuple firstly, the (a) bit atm just gets simplified to a rn by the interpreter, to make it a tuple do (a,)

you dont need the (?) so change it to ?

[calm prawn]

I am still getting an
AttributeError: 'int' object has no attribute 'execute'

[brazen charm]

thats todo with what c is

[calm prawn]

cursor

[brazen charm]

apparently c is currently a int

no a cursor

[calm prawn]

Thanks you for your help
By mistake I took c as a cursor and also stored a int variable

[topaz glen]

can somone tell me why

cur = connection.cursor()
        
        cur.execute("SELECT UserID FROM employee_personal")

        userID= cur.fetchall()
        print(userID)

prints out this

<sqlite3.Cursor object at 0x000001BCB94791F0>

and not the userID

@bleak pecan mate you have any idea?

[bleak pecan]

Are you printing cur elsewhere?

[topaz glen]

no man, its printing in the correct place

[proven arrow]

Your output does not match the code you sent

[topaz glen]

ive got like 250 lines of code in that file

ive send the code that has that specific infomatin

everytime i make a user it adds to a primary key

[proven arrow]

userID will either be an empty list or will have values if any matches found with that query.
The output you sent shows your printing the cursor object.

[topaz glen]

no the database is filled

userid is a primary key

[proven arrow]

https://discordapp.com/channels/267624335836053506/342318764227821568/779296774451363850

Discord doesnt let you quote anymore?

[topaz glen]

no man im not printing cur]

okay

this is the code that is messing me up

connection.execute('''
        INSERT OR REPLACE INTO employee_personal (firstName,lastName,email,userName,Age,Wage,position)VALUES (?,?,?,?,?,?,?,?)''',
        (register_firstName,register_lastName,register_email,register_userName,register_age,register_Wage,register_position))
        connection.commit()
        connection.execute('''
            INSERT OR REPLACE INTO application_infomation (Username,password,email)
            VALUES (?,?,?)
            ''',
            (username,password, register_email))

        cur = connection.cursor()
        
        cur.execute("SELECT UserID FROM employee_personal")

        rows = cur.fetchall()
        rows = list(rows)
        print(rows[0])

this is the connection funciton

def connect():
    Path = os.getcwd()+"\main_DataBase.db"
    if Path != os.path.islink(Path):
        con = sqlite3.connect(Path)

    return con

connection = connect()

guys im really desperate

@bleak pecan man sorry for the pings but do you see why it wont just print the userid

its also a foregin key idk why tf its nto just replicating in other tables,

the primary key in the empployee personal table is also auto incrementing

[bleak pecan]

Missing a commit there after the second execute & is the switch from connection to cur necessary. Im still not sure on the print

[brazen charm]

UserID is a foreign key?

[topaz glen]

its both

[brazen charm]

🤔

[topaz glen]

its a primary key in the employee_personal table

but its a foreign key in every other table

[desert basin]

how would one loop and return every row of an excel file as a list using pandas?

[topaz glen]

this assignment is due at midnight. i have to finish my powerpoint and this fucking error is the only thing stoping me from submitting, im not saving this bitch to excel

[desert basin]

huh what?

I am asking a question for myself

like, calm down man, did I say anywhere that my question was meant for you?

[topaz glen]

oh...

btw

its a for loop for when you read the csv file

[calm prawn]

If I have a sqlite database where can I upload the database so that my code can access the database pushed on Github

I mean code is pushed on github

[proven arrow]

Its just another file, so you would upload wherever you want to access it

[calm prawn]

Ok

[steady lava]

@proven arrow I know sqlite is a server less database, you can make a use of it either using sqlite3 cli interface or python library, and on the other hand mysql requires a connection to the server running on localhost.. more like a client-server structure..
I just wanted to know, why they can't share the same database.. suppose if I create user.db with python's sqlite3 and accessing the same via mysql or mariadb then It won't display the db.. um, any difference in the structure of both the database ?

[flint wadi]

I want settings row entry to be deleted when current_user is deleted(one-to-one relationship).
Which do you prefer for SQLAlchemy relationship.cascade?
a) cascade = ”all, delete-orphan”
b) Something like cascade = ”save-update, merge, delete, delete-orphan”
(I don’t know what merge, refresh-expire, expunge do)

[haughty spruce]

Hello

[proven arrow]

@steady lava Again it comes down to it being a different system. Each RDBMS has its own internal way of doing things, even if they might be doing the same thing. They dont always agree on the same things. They dont always support the same syntax/features.

I cant just put some PHP code into my Python file, and expect it to run as if it knows exactly what's going on.

[torn sphinx]

so my code is

            cursor = await db.execute(f"SELECT channel_q FROM settings WHERE guild_id = {ctx.guild.id}")
            resulta = await cursor.fetchone()```

and resulta var returns ('779042764791742465',) i printed it

so how do i just get that string number

i even tried

resulta = await cursor.fetchone()[0]```

but courountine object is subscriptable

nvm i figured it out

[gaunt garden]

Anyone have any learning resources they would recommend for learning the async version of MongoDB (im completely new to Mongo in general). Required to use async Mongo for a project.

[torn sphinx]

can a single sqlite file contain multiple tables?

or no?

[cobalt imp]

I have a docker-compose file that runs a timescaledb(postgres) and a roscontainer which uses pub sub to put data into the database. I'm also having a docker compose file that runs django and pgadmin, later there will also be a angular frontend but for the time being it's just django that serves as a backend. The problem that i'm having is that django is not able to query the data from this timescaledb. I changed my settings.py file to connect to the timescaledb and made sure when running both docker-compose files i used the -f parameters to make them able to talk to eachother (docker-compose -f mycontainer1 -f mycontainer2 up). When I browse to the pgadmin port and login. I can see al of my data inside the table (this confirms that both docker-compose files are able to talk to the containers in those compose files because the timescaledb is ran by a different compose file than the django/pgadmin compose file). For some reason when I try to query data from my django backend I'm getting a whole bunch of errors. See screenshot.

[proven arrow]

Error says your error is to do with your csrf token being missing, not the database. @cobalt imp

[torn sphinx]

hello, i have this line of code:

cursor.execute(f"UPDATE levels SET xp = xp + {randint(3, 7)} WHERE guild = {guild} AND id = {id}")```
and i get this error:
```fix
sqlite3.OperationalError: near ")": syntax error

i cant seem to find the syntax error

could anybody help?

[chilly creek]

Hi, I have a dump of 1 Million JSON objects (equaling 21 GB) and I would like to do big data on it. Which DB do you recommend for this?

[icy fable]

@proven arrow wondering if you know what the proper file/software architecture is using SQLAlchemy? Should I have my table models in one .py and my inserts/query logic in another .py file?

[proven arrow]

@icy fable I dont use sqlalchemy, but the general answer is yes. You should have your models separate, and decoupled from your database queries.

[tepid crag]

Mongo db is good with importing json data

[torn sphinx]

anyone have good resource to get start with mongodb?

something simple to undersdtand for beginner as well 😬

[chilly creek]

I thought about MongoDB, but I thought a "regular" SQL would be more performant than noSQL. Postgres vs MongoDB vs something else? What would be the upsides of Mongo over Postgres for my case? @tepid crag

[brazen charm]

probably nothing

mongo bogs down with any large amount of data pretty quickly

the only thing you'd get is simplicity of adding the data

anything else is probably a drawback

[chilly creek]

So convert it into a regular SQL like MySQL or SQLite entirely?

[torn sphinx]

hmmm i thought mongo was good for that stuff

[brazen charm]

id go with postgres

not really

[torn sphinx]

i was about to use it as well

so i should stick with sql?

[brazen charm]

its probably one of the slowest noSQL dbs around

it depends what your doing

if its only light it doesnt matter

[torn sphinx]

i want to learn how to use it but dont know where to start

like which product to use

atlas, compass? which i need to use?

[brazen charm]

what?

[torn sphinx]

which one do i use to get started?

[chilly creek]

@torn sphinx MongoDB is a very popular noSQL and as such a good option for startups of for example apps - where BASE has advantages over ACID

[torn sphinx]

mongo also support acid i think

[chilly creek]

but I am no expert on that

[brazen charm]

its only popular cuz its dummy easy to setup

wEbScALe

big meme

if you data is tubular i would use either: PostgreSQL or Scylla

[chilly creek]

what does tubular mean?

[brazen charm]

the data can be easily put into columns and rows per item without needing to have a diffrent column each item

imagine it like, "can it fit into a spreadsheet style table"

[chilly creek]

{"id":"261408","created_at":"2008-06-07 04:22:33.99876 UTC","uploader_id":"65792","score":"2","source":"","md5":"854a9406b8d2ec1c8ad08684b14e0944","last_commented_at":"1970-01-01 00:00:00 UTC","rating":"s","image_width":"2403","image_height":"2969","is_note_locked":false,"file_ext":"jpg","last_noted_at":"1970-01-01 00:00:00 UTC","is_rating_locked":false,"parent_id":"0","has_children":false,"approver_id":"0","file_size":"1148874","is_status_locked":false,"up_score":"2","down_score":"0","is_pending":false,"is_flagged":false,"is_deleted":false,"updated_at":"2014-08-29 23:40:43.10679 UTC","is_banned":false,"pixiv_id":"0","tags":[{"id":"470575","name":"1girl","category":"0"},{"id":"469738","name":"90s","category":"0"},{"id":"11716","name":"absurdres","category":"5"},{"id":"16751","name":"bangs","category":"0"},{"id":"537684","name":"blunt_bangs","category":"0"},{"id":"16867","name":"brown_hair","category":"0"},{"id":"13804","name":"earrings","category":"0"},{"id":"4474","name":"fire","category":"0"},{"id":"454933","name":"hair_bun","category":"0"},{"id":"1566","name":"highres","category":"5"},{"id":"446622","name":"hime_cut","category":"0"},{"id":"1707","name":"japanese_clothes","category":"0"},{"id":"16613","name":"jewelry","category":"0"},{"id":"395796","name":"kakinouchi_narumi","category":"1"},{"id":"13197","name":"long_hair","category":"0"},{"id":"1287928","name":"miyu_(vampire_princess_miyu)","category":"4"},{"id":"643898","name":"purple_background","category":"0"},{"id":"464575","name":"ribbon","category":"0"},{"id":"1918","name":"scan","category":"5"},{"id":"212816","name":"solo","category":"0"},{"id":"15189","name":"vampire_princess_miyu","category":"3"},{"id":"89189","name":"yellow_eyes","category":"0"}],"pools":[],"favs":["15115","44474","68238","55507","385172"]}

this would be example data of 1 of the million JSON objects

[brazen charm]

hmmm

looks like it could go into SQL relatively easily

[chilly creek]

thought the same

[brazen charm]

would probably want a decent amount of SQL knowledge though

[chilly creek]

y I think I have decent amount

[brazen charm]

probably a table of tags

table containing the actual thing

then probably just a bit of SQL with some JOINs

[torn sphinx]

should i need to do cache for my bots database queries?

it makes like 500 queries per day?

postgres

[brazen charm]

500 is pretty low

[proven arrow]

Yeah let it do some work. Don't go that easy on it. 😜

[brazen charm]

tbh postgres can easily go into the hundreds of thousands of tuples out a sec without breaking a sweat

[torn sphinx]

oh some people used somethibng called redis so i thought maybe i need this

alright thanks anyways!

[plush kelp]

Hello

[rugged holly]

i want a better way to do what i am doing.

so i have a database which is a list, and each member of the database (i am making a discord bot btw) has it's own dict

and in the dict, there is an inventory of maybe food for example

food is a list

and in the list, there are items like bread

but the bread has an attribute: how much you've eaten

how would i do this

k i'ma open a help channel

[gaunt reef]

anyone know how to search for "Id" using mongodb?

{
  "posts": {
    "row": [
      {
        "Id": "1",
        "PostTypeId": "1",
        "AcceptedAnswerId": "9",
        "CreationDate": "2010-08-17T19:22:37.890",
        "Score": 16,
        "ViewCount": 28440,
        "Body": "<p>What is the hardware and software differences between Intel and PPC Macs?</p>\n",
        "OwnerUserId": "10",
        "LastEditorUserId": "15",
        "LastEditDate": "2010-09-08T15:12:04.097",
        "LastActivityDate": "2017-09-21T12:16:56.790",
        "Title": "What is the difference between Intel and PPC?",
        "Tags": "<hardware><mac><powerpc><macos>",
        "AnswerCount": 9,
        "CommentCount": 0,
        "FavoriteCount": 6,
        "ContentLicense": "CC BY-SA 2.5"
      }
      ]
    }
  }
}

ive already tried something like: db.coll.find({posts:{rows:{"Id":"1"}}}) but it doesnt return anything (no null and no errors, just another blank line in the console)

pls @ me if you have a solution. thanks in advance!

[feral thorn]

Hello, i am new to postgres. From where i can start learning about it? 😀 Please refer me. Thank you

What's your collection name? @gaunt reef

Just do print(collection_name ["Id" ]) and it should work

Or you can also get it in dictionary format

[gaunt reef]

collection name should be Posts

but yea i kinda figured it out now

tyty tho

[feral thorn]

:)

[gaunt reef]

o also @feral thorn not sure if it helps but postgres follows SQL standard, so reading up on any SQL documentation is a good foundation. for learning nuances of postgres i recommend reading their documentation (https://www.postgresql.org/docs/manuals/)

[feral thorn]

Thank you :)

Seems that postgres is more hard than mongodb...lot of fun incoming

[ebon mirage]

hey, does anyone know how to get flask-sqlalchemy intellisense in pycharm?

pls mention

[torn sphinx]

would anyone be able to help me here. I have a cases table for my discord bot and it logs everything but to get the case number I have to count the amount of inserts are in the cases table but I'm coming from a MongoDB background so I am new to PostgreSQL. I have this code but it is returning a 'builtin_function_or_method' object is not iterable error.

Code

db = self.client.db

db.execute("SELECT COUNT(*) FROM cases WHERE guild_id='%s'", (ctx.guild.id,))
result = db.fetchone

for result in result:
  case_number = result['count']
  print(case_number)

Error

'builtin_function_or_method' object is not iterable

[proven arrow]

fetchone() is a function @torn sphinx

[torn sphinx]

what should I do then

[proven arrow]

Which library are you using?

[torn sphinx]

psycopg2

[proven arrow]

Well you should use asyncpg module instead, since that would be async and suit your application. https://magicstack.github.io/asyncpg/current/index.html

psycopg2 will be blocking

[torn sphinx]

ok I will swap to that but once I install it what should I do about my counting Issue?

[proven arrow]

What are you trying to do? The COUNT(*) gives you a number of rows returned by the query.

[torn sphinx]

yes but how do I get that as an integer in a variable in python

[proven arrow]

In asyncpg you can just do fetchval() to get a single value.
So that would be result = await db_connection.fetchval("SELECT COUNT(*) FROM cases WHERE guild_id=$1", ctx.guild.id)

So result would be an integer

[torn sphinx]

awesome thanks so much!

[torn sphinx]

pymongo.errors.ConfigurationError: query() got an unexpected keyword argument 'lifetime'
any fixes?

show code?

I know a bit about pymongo I might be able to help you

[left wagon]

was wondering if anyone had a good tutorial to hashing

[velvet coyote]

await asyncpg.connect('postgresql://-------------@------------------/-----------')
this is how I connect to my db.
If i replace - with the actual information, can anyone connect to the db?

[proven arrow]

@velvet coyote Depends on the setup, and how its been configured.

[torn sphinx]

@velvet coyote you can use psycopg2 for connecting postgresql database

[weak tinsel]

asyncpg is preferred i guess

[steady lava]

How to refresh the database or say remove the duplicate rows from the table right after inserting more into it ?
Suppose, I've some song names and I insert them into a table, now if I insert the same song names into the same table then it should either not create the new rows cause one row with the same values has already exist, or either remove the duplicate one among them.

[torn sphinx]

if you dont want to insert more than once use primary key

[steady lava]

Does it overwrite or what?

[proven arrow]

Use a unique constraint on that column

That way a value can't exist more than once for a given column

[steady lava]

Um, not sure.. can you give me an example please ?

[proven arrow]

https://www.w3schools.com/sql/sql_unique.asp

[steady lava]

@proven arrow along with it, I had to make a use of INSERT OR IGNORE INTO

otherwise it gives an error i.e., unique constraint failed or an exception of IntegrityError

[proven arrow]

Correct

If you wanted to update it instead, you can use INSERT ... ON DUPLICATE KEY UPDATE

https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

[steady lava]

@proven arrow ohhh so it update the value if it already exist in the table ?

Anyways thanks a lot, you helping me a lot :)

[zealous nymph]

anyone who has experience with python sqlite 3 calculated field in a select?

[turbid latch]

test

test

test

[delicate field]

:x: According to my records, this user already has a mute infraction. See infraction #20796.

:x: According to my records, this user already has a mute infraction. See infraction #20796.

:incoming_envelope: :ok_hand: applied mute to @turbid latch until 2020-11-22 05:21 (9 minutes and 59 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

[twin crag]

Hi guys

How r u

Who can help.with script?

[tiny needle]

send code

[viscid osprey]

I'm talking to a Postgres db, and when I try execute the following query

        bdayRecs = await self.bot.db.records("SELECT member_id, date_of_birth FROM member \
            WHERE birthday_greeting_time = null AND date(date_of_birth) = $1", datetime.date.today())

I get the error AttributeError: 'method_descriptor' object has no attribute 'today'. What am I doing wrong, please?

[tiny needle]

I am trying to create a table when a discord bot joins a guild:

@bot.event
async def on_guild_join(guild):
    command = """CREATE TABLE IF NOT EXISTS ?
    channel INTEGER,
    primary key(channel)"""

    conn.execute(command, [guild])```
and i get 
`sqlite3.OperationalError: near "?": syntax error`
what am i doing wrong?

[weak tinsel]

anyone online

ahh nvm

[proven arrow]

@viscid osprey The error is the datetime your passing the query. date is supposed to be a function.

Also null does not equal null

Instead you should compare like this, birthday_greeting_time IS NULL

@tiny needle You can't use query parameters for table name.

[viscid osprey]

I found I should import datetime instead of from datetime import datetime and I can use datetime.date.today(), but thanks for the null tip, it completely slipped my mind, and my next question was why am I getting no results 🙂

[proven arrow]

Probably because of the = null you did

[viscid osprey]

Yep

[tiny needle]

so i just concatenate the channel with the command?

[proven arrow]

Well first why do you need to create tables dynamically like that?

Generally that's a No, unless you have a good specific reason.

[tiny needle]

well i want to store a variable specific to a guild, and i thought that was the best way

[proven arrow]

Not necessarily. Instead you should have a single table where you store all the guilds. And then use this tables Primary Key to link it to other tables. Or if you want it easier then store the variable as a column in the guilds table.

[steady lava]

@proven arrow Hey me again 😄
I just wanna know is there any other method or constraint by which I can ensure if the particular Database contains a table or not, if it contains then do nothing and control goes to next part of the code, and if it isn't then first create the table and then do other task.
right now, I've written this function just to make sure that if the file is a database file or not, and if it contains any table or not..

def isTableExist(cursor):
  """ check if table exist inside a DB or not"""

  # if file exists
  nonlocal path
  if os.path.isfile(path):
    if sys.getsizeof(path) > 100:
      # data is stored in bytes
      with open(path, 'rb') as file:
        dbheader = file.read(100)
        # defines an sql db file.
        if dbheader[0:16] == b'SQLite format 3\x00':
          # Check if table exists or not
          cursor.execute("""
                             SELECT count(*)
                             FROM sqlite_master
                             WHERE type='table'
                             AND name='song_info'
          """)

          return cursor.fetchone()[0]
  return False

[proven arrow]

@steady lava To check if a table exists you normally check the schema which you seem to be doing currently. When creating tables, you can do CREATE TABLE IF NOT EXISTS which will only create the table if it doesnt exist.

[steady lava]

hmm, I checked that syntax before
so, this part of code is kind of like extra

 exist = self.isTableExist(cursor)
tablename = "song_info"
if not exist:
 # Create a table (if it doesn't exists)
cursor.execute(""" 
      CREATE TABLE {0}
     ( 
      songs text UNIQUE
     ) 
""".format(tablename))

and not necessary when I can simply do CREATE TABLE IF NOT EXISTS ??

[proven arrow]

Yeah, and try to avoid using format like that.

[steady lava]

hahaha you meant statement written inside triple quotes?

[proven arrow]

I meant with the .format(tablename)

[steady lava]

oh because of crafted input ?? (forgot the word "crafted" 😄 )

I meant, it's less secure way right? mentioned in docs too

[weak tinsel]

SQL injection 👀

[zealous nymph]

hello guys

anyone who could help me with a calculated field in sqlite3 using python?

[proven arrow]

Can you be more specific as to what you are trying to do? Also do you mean generated/computed columns?

[zealous nymph]

i'm trying to do something like this

select type, start_date, end_date, series_source, series_name, ((julianday(end_date) - julianday(start_date)) * 86400.0) as duration```

start and end date contain this

datetime.datetime(2020, 10, 1, 0, 0, tzinfo=tzutc()), datetime.datetime(2020, 10, 1, 0, 0, tzinfo=tzutc()),```

@proven arrow

[steady lava]

@proven arrow do you think if I encrypt my sqlite database (that only contains songs path or links) then it would add more security so that others can't have access to this database and do anything to it? I meant I don't want any second person to check someone's playlist db, only the methods that are used to interact with sqlite can do perform that action.

I checked some implementation i.e.., SEE and sqlitecypher but you have to purchase license for both of them

What if I save encrypted song path/links and whenever I wanna retrieve my saved playlist then at the time of playing a song, player would decrypt each song before playing it

[proven arrow]

@zealous nymph So what issue are you having then? You want to know how to make that as a computed column?

@steady lava Then just make sure you don't write queries in a way that allows such a thing.

I don't use sqlite so I'm not too sure on what people use to secure it.

@torn sphinx You can push/pull it. The data will stay. So you just would want to make sure that the file is there. But generally the file on your host/and in development should not be in version control, and should just sit on your host/dev machine in its directory.

[elder socket]

sd = dt.date.today() - dt.timedelta(days=1)
strs = 'select * from email where created_at >=' + sd'

hello can someone help me with this query?
the query is for mysql

[icy fable]

Within SQLAlchemy, when defining relationships (one-to-one, one-to-many, many-to-one, & many-to-many), it mentions making these relationships 'bidirectional' by adding backref() or back_populates(). My main question is, what is a good example of when not to have a bidirectional relationship?

For example, I have a Curriculum table and a Quiz table. Every curriculum can have one or more quizzes while every quiz belongs narrowly to just one curriculum. Would this necessitate a bidirectional relationship? I fear I'm not understanding the implications of this.

[proven arrow]

@elder socket which library are you using to connect to the database?

[zealous nymph]

@proven arrow well when i execute my query the result doesn't contain the duration column

[proven arrow]

Remove type from the statement, I guess that's causing an error

[zealous nymph]

but type is an column in de table

[proven arrow]

Oh ok I see I thought you were trying to get type of column

Well is there any error?

[zealous nymph]

Yeah i loop over each row and extract the values

ValueError: not enough values to unpack (expected 6, got 5)```

    frame_type, start_date, end_date, source, name, duration = row```

when i check what is in row

[proven arrow]

Ok so your row is returning 5 and your expecting 6

[zealous nymph]

duration isn't

[proven arrow]

Can you show full query/code

[elder socket]

working now , forgot to convert datetime to string

[proven arrow]

Correct, that was your fix, although you should not be making a query through concatenation, which is why I asked what library you use.

[elder socket]

im using sqlalchemy

whats the better way to do it?

[zealous nymph]

'''
            select type, start_date, end_date, series_source, series_name, ((julianday(end_date) - julianday(start_date)) * 86400.0) as duration
            from EventFrames
            order by {} {}
            limit :page_size offset :offset
        '''.format(sorting_fields[query.sort_field], sorting_directions[query.direction])```

[proven arrow]

Use parameter binding or parameterised queries. Sqlalchemy should have docs on how to do this.

[elder socket]

noted . gonna research on this

[proven arrow]

@zealous nymph The query looks fine. Have you tried fetching just a single row and printing it's values before you do any processing?

[zealous nymph]

@proven arrow this is what i do

cursor.execute(interval_query, params)
            return [_row_to_event_frame(row) for row in cursor.fetchall()]

_row_to_event_frame

does this

frame_type, start_date, end_date, source, name, duration = row```

it puts is in an object

[proven arrow]

I meant to test just do:

cursor.excute(....)
print(cursor.fetchone())

And check the output

[proven arrow]

It's up to you where to put these relationships. Different applications have different needs. If you have a case where it's required then use it. Bi directional would allow you to access information from both sides.

You may want to access what curriculum a quiz belongs to as well as get all the quizzes of a curriculum.

For example, given you have a model from one side of the relationship you may want to get some data from it.

Quiz() -> getCurriculum()
Curriculum() -> getAllQuiz()

[icy fable]

I really appreciate your input, my friend. Can you give me a hypothetical example where one-way directional relationship would make sense? I'm having a hard time imagining a reason not to make all my relationships in my database bidirectional.

[feral thorn]

So just wanted to ask that can i dump my Mongodb database into Postgresql?

[torn sphinx]

@granite valve

[granite valve]

How do i set up a SQL database

[proven arrow]

Well it depends on your application. You can implement bidirectional or the opposite which is unidirectional. The one you decide is up to you. It's something you have to decide.
But for example, you could say the following:
Subject -> getModules()
But in your application you don't care about what subject a module belongs too, so you may not to bidirectional.

[torn sphinx]

there are multiple SQL databases for python

[icy fable]

@proven arrow Thank you as always. You are a huge reason I come here, and I appreciate your help

[torn sphinx]

do you know what SQL is? @granite valve

[granite valve]

No

[wintry stream]

it's like another programming language, but for databases

Structured Query Language

[proven arrow]

You can't just dump it like that since the format would be different. You would have to parse the content.

[wintry stream]

pretty much every database uses SQL to do stuff

[torn sphinx]

@granite valve then you'll need to learn SQL

to store a ton of data in a database

[granite valve]

Ok

[wintry stream]

if you want to work with databases you need to know SQL

[torn sphinx]

i'd also recommend sqlite3

for python sql

[wintry stream]

and SQL is very easy

[mint umbra]

yess

it is easy indeed

[naive sandal]

aiosqlite in this case

[mint umbra]

im a learner myself

[wintry stream]

didn't this come from a discord bot? then don't use sqlite3 as this isn't async

[tiny needle]

how would i insert a value into a table if there isn't already an entry with the same primary key?

[torn sphinx]

oh, ok

ive been using sqlite3 for my discord bot

[wintry stream]

does depend a bit on the database, but a lot have IF NOT EXISTS or something along those lines

[tiny needle]

sqlite3

[wintry stream]

just google (database) if not exists

[tiny needle]

ok

[feral thorn]

Ahh..so it will be like get it in cv first and do the needful...hmm

[mint umbra]

hey guys so i basically have a project where ive to create databases in python and then connect them to sql. There are few more specifications and ill probably be here alot asking for help 😓 because im not great at sql and python connectivity

[delicate field]

Hey @mint umbra!

Uh-oh! It looks like your message got zapped by our spam filter. We currently don't allow .txt attachments, so here are some tips to help you travel safely:

• If you attempted to send a message longer than 2000 characters, try shortening your message to fit within the character limit or use a pasting service (see below)

• If you tried to show someone your code, you can use codeblocks
(run !code-blocks in #bot-commands for more information) or use a pasting service like:

https://paste.pythondiscord.com

[mint umbra]

oh

my bad

i was sending a code in that was giving errors

[wintry stream]

you're apologising to a bot

[mint umbra]

lmao

yes

[tiny needle]

noob question: what does INSERT OR REPLACE INTO do?

[mint umbra]

insert does the job of inserting values into the databse where as replace into replaces a pre existing value from the database with something else

i hope im not wrong

[wintry stream]

i can guess it tries to insert, and if it already exists it replaces it (overwrites it)

[tiny needle]

ok

thanks

thought so

[elder socket]

Same here. I always encounter error in uploading data to sql.

[mint umbra]

yeah man

i keep getting this error

anybody know what to do about this?

line 1, in <module>
import mysql.connector as ms
ModuleNotFoundError: No module named 'mysql.connector'; 'mysql' is not a package```

ive installed mysql too

[proven arrow]

What's your file called?

[wintry stream]

@mint umbra mySQL is a database

not a driver

[proven arrow]

That's also the name of their driver too xD

[wintry stream]

unless the driver is also called mySQL which i would find very bad naming

that's like the worst naming ever

[elder socket]

What package are u using?

[wintry stream]

read 3 messages up

[elder socket]

Nvm i only knew sqlalchemy

[mint umbra]

oh

idts thats the name of my driver

how do i check driver name/change it?

[proven arrow]

That is the correct name, as for your error, I asked a question. #databases message

[mint umbra]

oh my bad

its called "ipproject.py"

[proven arrow]

Did you install the correct one?
https://pypi.org/project/mysql-connector-python/

There is some packages on pypi which are deprecated

[mint umbra]

yes i did

[elder socket]

Are u working inside environment?

[mint umbra]

uhhh not sure what that means

[proven arrow]

It's a virtual development environment, where your python version and modules etc are isolated from others environments including your system.

You'll have a folder called venv or env inside your project

[elder socket]

How did you install ur package?
I tried to install ur package and run same code. It works on me.

[silk vortex]

I'm using asyncpg to connect to a database. I pg_dumped my database, uploaded the sql file to my VPS, created a database, and then restored it. But when I run my bot, it says that the database does not exist. When I try to create the database again, it says database already exists. Does anyone know how to fix this?

[mint umbra]

hmm

idk bruh its weird

this is how im doing it

anytyhing possibly wrong in this?

[elder socket]

do you still have same error ?

[mint umbra]

yes

[elder socket]

did you use
pip install mysql.connector in your terminal?

[mint umbra]

NO WAIT

something happened

its a different error now

    print('availablity:',row[1])s
                                ^
SyntaxError: invalid syntax```

i can fix this error

but im just letting you know

that the mysql connector error is gone

okay nvm

the above error was just overriding the main error

its back

    import mysql.connector as ms
ModuleNotFoundError: No module named 'mysql.connector'; 'mysql' is not a package```

the same thing

[elder socket]

how did you install mysql.connector?

pip install?

[mint umbra]

yes

and i retried installing it just now

it said

already fulfilled

and installed

[elder socket]

have u encounter same error with other modules?

[silk vortex]

#databases message can someone help me?

[elder socket]

try pip3 install.
or check ur python version

[wintry stream]

can you look at the database using pgadmin4?

http://ip.of.vps/pgadmin4

[finite lily]

Hey all. Using asyncpg for my next project. I'm kind of used to using Django and got spoiled. With Django, it'll commit "migrations" as you change your models. What are the best practices if not using? Just create sql scripts to execute as you evolve your schema for that commit?

[jaunty yew]

Maybe try pymysql?

[mint umbra]

whats that

[chilly creek]

[pgSQL13]
Hi, I have a dump of 1Million JSON objects that I want to integrate into a DB.
Every object contains a key 'object_id'
some objects have a key 'parent_id', referring to another object - I set this as an FK. Relevant part is:

CREATE TABLE public.image
(
    image_id integer NOT NULL,
    parent_id integer,
    CONSTRAINT image_image FOREIGN KEY (parent_id)
        REFERENCES public.image (image_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
)

Problem is: There is a chance the parent_id is either: 1.Not INSERTed yet or 2.No part of the dump at all
Trying to insert a dataset with the parent not in the DB already errors, as it violates the constraint.
What is the recommended way to do now?
Should I disable the FK-constraint? Or do something else?

for some reason ALTER TABLE image DISABLE CONSTRAINT image_image does not work, throwing a Syntax error on CONSTRAINT.
https://www.postgresql.org/message-id/346433adf2d0bd1056c201cb5f372e49%40highgo.com.cn
What am I doing wrong?

[jolly acorn]

@torn sphinx Not to dissuade you ... but not alot of folks spent a whole lot of time in tk. You might find a web-app with flask to be considerably easier.

[spark ravine]

Hey I'm using Postgres, and I want to create a unique constraint between two int columns. I would like to have data like:

(1,2)
(2,1)

But another record have (1,2) or (2,1) should not work. I'm just double checking that creating this unique constraint would follow the data above.

[proven arrow]

@spark ravine Yes you can add the unique constraint to both those columns combined

[spark ravine]

awesome thanks!

[proven arrow]

@chilly creek you have the wrong syntax. It's DROP, and not DISABLE. https://www.postgresql.org/docs/13/ddl-alter.html#DDL-ALTER-REMOVING-A-CONSTRAINT

[modest grove]

some1 know why I have error? IntegrityError: UNIQUE constraint failed: autoRole.autoRoleId

[proven arrow]

@modest grove You have some column which only allows unique values. You are probably trying to enter another same value again, which fails the unique constraint

[modest grove]

so what I need to do if this is AUTO_INCREMENT? @proven arrow

like "INSERT INTO servers (id, serverId, commandPrefix) VALUES ('/', '{str(message.guild.id)}', '!');" the id is not null and auto increment

[proven arrow]

@modest grove If the ID is not null and auto increment then why are you trying to insert the value '/' for it? It's not even an integer?

[toxic mist]

i have a code in sqlite
is there a way to do selection like this

first where user_id(a column name) = 288292299229929
second count the total rows and store it in a variable
third we display a list of rows by using LIMIT OFFSET feature

cur.execute('''SELECT * FROM info WHERE user_id=? LIMIT 1 OFFSET ?;''',(user,arg,))

I am using this line but it doesn't counts how many rows are there

@topaz vine

[proven arrow]

@toxic mist Use COUNT(*) to get number of rows

SELECT COUNT(*) FROM info WHERE .....

[toxic mist]

umm is it possible to count only the where user_id = 82828191919
part and apply the limit offset part@proven arrow

umm like count all the cards a particular user has
then Limit them

[steady lava]

Then just make sure you don't write queries in a way that allows such a thing.
@proven arrow sorry i don't get it, can you please explain to me one more time?
I asked, if it's possible to encrypt the sqlite database table or database itself, so other users can't have access to it and do any manipulation with that table or if it's possible to encrypt the values that I am saving into the each user db's table? and at the time of retrieving the values then before giving it to music player, decrypt all of them.

[proven arrow]

It will do that. The count(*) will give count of the filtered rows from the where.

[proven arrow]

Just dont trust the user. Never directly pass user input into the database without first checking it and verifying it. Use parameterized queries to help you, since they would avoid SQL injection attacks.

[steady lava]

@proven arrow Actually I don't have any such thing that can connect the user input with the database. I am storing songs path or links (if path wasn't found in local directory).. so I've created another script that searches a song in following manner :

1. Check in local directories

   • IF FOUND : return the path and song list to the music player and add them to both the playlist (temporary - use later to store song in database, and mpv provided - helps in to remove from the queue, play Nth position of a queue, or similar operations.
   • IF NOT FOUND : - go to point 2

2. Check in cache file (this file contains youtube links alongwith the song names. like this -> { crawlinglinkinpark : <youtube_link> }
   If the song isn't here too, then do a ---

3. YOUTUBE SEARCH and get the link of a song.. once the link if fetched then save this link into that cache link (mentioned above, point 2). So that it doesn't have to go to the youtube to fetch the song link... if the link is present in cache file, then it will get it from there..

Once the link is found, then give it to music player.

[proven arrow]

User can also imply an external system, so if you get song name from youtube, and was to store this in the database then the same applies. So still use parameterised queries, for INSERT/UPDATE/DELETE statements.

You should first be worried about securing your server, and then if you want encrypt the database using the software you mentioned last time.

[flint field]

yeah

[mint umbra]

guys i did pip uninstall mysql pip uninstall mysql.connector and then re installed them. yet when i import mysql.connector this error comes saying import mysql.connector as ms ModuleNotFoundError: No module named 'mysql.connector'; 'mysql' is not a package

[proven arrow]

@mint umbra You have the wrong package. Try pip install mysql-connector-python

[mint umbra]

okay trying

already satisfied

what do i import in my code tho

[proven arrow]

import mysql.connector

[mint umbra]

import mysql-connector-python as ms?

i did that

same error comes

[proven arrow]

do pip show mysql-connector-python and show the output

[mint umbra]

@proven arrow

[proven arrow]

Ok looks fine, and if you open a python shell outside of your project directory and do import mysql.connector do you get any errors

[mint umbra]

yes

[proven arrow]

Can you show screenshot of the shell?

[mint umbra]

there you go

@proven arrow

how do i check the name of my python script file

stackoverflow has answers

about this error

my scripts name isnt mysql.py

idk what it is

someone please help

ping me if youre helping me

im editing a file in vsc where the import error is occurring everytime i run the programme, the files name is not mysql.py

[proven arrow]

I dont know too much about python packages, but your package is installed. Since pip show earlier confirmed it. Try with pip3 (should be located in your python install scripts folder) not sure if that makes any difference. Or if none of that works then try in a virtual environment.

[mint umbra]

what is a virtual environment

[proven arrow]

Its a way of isolating and keeping you depenedencies/packages seperate. Currently your kind of in the Global environment, where all packages can be accessed by all files. What you do with a package in one environment doesnt affect others.

https://packaging.python.org/guides/installing-using-pip-and-virtual-environments/

[mint umbra]

thanks man

ask up

[mint umbra]

man its so much work i cant seem to do anything and even installing venv is feeling so hechtic

the ones youve ticked

wait what

my bad

which ones did you tick?

yeah

first off

they have asked us the routes

so never pick options which do not specify route in them

yes

after you do that

i think it is

[glass gorge]

can someone point me in the right direction to this error

[mint umbra]

but you didnt get any marks for either did you?

[mint umbra]

a and d both can give the correct answer

[dusky plaza]

Discord added replies to mesages? Noice

[glass gorge]

i believe i bound the dbs correctly but im not sure

[mint umbra]

the having function will be inapropriate here

[glass gorge]

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from flask_bcrypt import Bcrypt
import os
from dotenv import find_dotenv, load_dotenv
from flask_login import LoginManager
from flask_mail import Mail

load_dotenv(find_dotenv())

app = Flask(__name__, instance_path=os.environ.get("INSTANCE_FOLDER"))
app.config.from_object(__name__)
app.config["SECRET_KEY"] = os.environ.get("SECRET_KEY")
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["SQLALCHEMY_DATABASE_URI"] = os.environ.get("SQLALCHEMY_DATABASE_URI")
app.config["SQALCHEMY_BINDS"] = {
    'user_data': os.environ.get('SQLALCHEMY_DATABASE_URI'),
    'content': os.environ.get('SQLALCHEMY_DATABASE_URI2')
}



#app bindings
db = SQLAlchemy(app)
migrate = Migrate(app, db)
bcrypt = Bcrypt(app)
mail = Mail(app)

#login manager
login_manager = LoginManager(app)
login_manager.login_view = "login"
login_manager.login_message_category = "info"

#csrf.CSRFProtect(app)
from staffDashboard.routes import routes
routes(app)```

[mint umbra]

a,d ig

im not sure rn

limit and from dont do the job

yeah i see

'from' selects the table

and 'limit' provides a limit for what to show

the rest give us groups of rows in the output

select is correct

[proven arrow]

Select is not correct

[mint umbra]

hmm

ignore my answer

[proven arrow]

And looks like homework/assignment, probably not in line with the rules 😬

[mint umbra]

oops

[glass gorge]

halp plis

db binds

select still counts though? cuz you need to select, and then sort. select comes a level above; isn't it implied that you can select multiples

select X from Y where value > 3

[proven arrow]

Select doesnt filter

[glass gorge]

ermmm selecting is filtering

otherwise it would return the whole data set

i could be wrong

[proven arrow]

No as the name implies its selecting

[glass gorge]

right

and select means

picking values

select * from dataset

[proven arrow]

Yes just gets the data from the other parts of the query which filter it

[glass gorge]

gotcha

yeah its a bit confusing

[mint umbra]

hmm

[proven arrow]

You can do just SELECT 1; as well where there is no filter

[glass gorge]

id argue select technically is filtering, but it doesn't specify the variables to filter by

i mean you can filter lol if you do *

[proven arrow]

Look into WHERE and HAVING for your question. Read up and you will see what the correct answer is.

[glass gorge]

he's right

im just nit picking

i still think im right in theory

because selecting is just one step above filtering, i still think it's included in filtering

[proven arrow]

Regarding the steps, take a look at this which illustrates it better, #databases message

Oh actually that message thread even explains the WHERE and HAVING xD

[glass gorge]

i mean you can't deny

selecting

is the act of choosing

anyway idc

you're right

binds anyone O.o

😦

[elder socket]

@mint umbra have u fix ur problem?

[mint umbra]

no

someone told me

to import something else

[torn sphinx]

Anyone know of any good libraries for flat file data storage

[elder socket]

Ayt. Are u able to use sql now?

[mint umbra]

yes

it works

import MySQLdb is the new fix of import mysql.connector

working now

no error

[elder socket]

okies

[mint umbra]

ty @elder socket

ty @proven arrow

for all the help

ill come back with more errors soon ahhaha

[cosmic smelt]

if I open a help channel, would one of you be able to quickly help me understand what I'm doing wrong?

working with mysql.connector, some really basic stuff

[proven arrow]

@cosmic smelt Or ask your question here

[cosmic smelt]

alright. working with mysql.connector.
I've got 3 files:

import mysql.connector

config = {
    'user': 'root',
    'password': 'password',
    'host': 'localhost'
}

db = mysql.connector.connect(**config)
cursor = db.cursor()

import mysql.connector
from mysql.connector import errorcode
from database import cursor

database_name = 'acme'
tables = {}

tables['logs'] = (
    "CREATE TABLE `logs` ("
    " `id` int(11) NOT NULL AUTO_INCREMENT,"
    " `text` varchar(250) NOT NULL,"
    " `user` varchar(250) NOT NULL,"
    " `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,"
    " PRIMARY KEY (`id`)"
    ") ENGINE=InnoDB"
)


def create_database():
    cursor.execute("CREATE DATABASE IF NOT EXISTS {} DEFAULT CHARACTER SET 'utf8'".format(database_name))
    print("database {} created".format(database_name))


def create_tables():
    cursor.execute("USE {}".format(database_name))

    for table_name in tables:
        table_description = tables[table_name]
        try:
            print("creating table ({}) ".format(table_name), end="")
            cursor.execute(table_description)
        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
                print("table already exists")

            else:
                print(err.msg)


create_database()
create_tables()

and

from database import db, cursor


def add_log(text, user):
    sql = "INSERT INTO logs(text, user) VALUES (%s, %s)"
    cursor.execute(sql, (text, user,))
    db.commit()
    log_id = cursor.lastrowid
    print("added log {}".format(log_id))


add_log('this is log one', 'name')
add_log('this is log two', 'name')
add_log('this is log three', 'name')

the last file returns the error mysql.connector.errors.ProgrammingError: 1046 (3D000): No database selected but only when run the code in a file for itself, what am I missing?
if I run the code in the snippet above, it works fine.

I understand what the error means, I just can't see what I'm doing wrong, as I'm calling the database (db) from the other file

[torn sphinx]

You need to specify a database to connect to inside config

i think but i am not sure

[proven arrow]

Correct, do that

[cosmic smelt]

the connection string works, it's only the last bit of code that doesn't work when run in a separate file

I'm calling config = { 'user': 'root', 'password': 'password', 'host': 'localhost' }
in db

so from database import db
and I use db.commit() to commit

if I put ```python
from database import db, cursor

def add_log(text, user):
sql = "INSERT INTO logs(text, user) VALUES (%s, %s)"
cursor.execute(sql, (text, user,))
db.commit()
log_id = cursor.lastrowid
print("added log {}".format(log_id))

add_log('this is log one', 'name')
add_log('this is log two', 'name')
add_log('this is log three', 'name')

into the 2nd file at the bottom, it works fine

[torn sphinx]

can someone help me with window functions? (or maybe its not this i need but idk what i need)

[proven arrow]

Its hard to see which file is which, also you should not be importing cursor like that.

[cosmic smelt]

@proven arrow how come?

[proven arrow]

Get a cursor object when you need it, and then dispose when done.

Since cursor would be global

[cosmic smelt]

does it not dispose of itself when used?

[proven arrow]

No, https://www.python.org/dev/peps/pep-0249/#cursor-objects

[cosmic smelt]

oh, good to know

[torn sphinx]

if anyone know pls ping

[chilly creek]

yeah, I dropped it now, I hoped for an option to disable the constraint without fully deleting it. Now I keep it as a comment in case I want to enable it again later

[proven arrow]

Yeah that is the way to disable it (to just drop it).

@torn sphinx what are you trying to solve?

[chilly creek]

just a bit confused: I use psycopg2 to connect to my Postgre DB. In postgre, you can use the with context manager to connect to the DB or use a cursor. For example:
with conn.cursor() as cursor:. This automatically closes the connection/cursor when leaving the indent block.
In some examples of the database module I saw no 1 using the context manager. The first entry of the google search does it like this for example:

import MySQLdb
db = MySQLdb.connect("host machine", "dbuser", "password", "dbname")
cursor = db.cursor()
query = """SELECT * FROM sampletable"""
lines = cursor.execute(query)
data = cursor.fetchall()
db.close()

same goes for sqlite, firebirdsql

Are the examples just bad or do these modules not support the with operator?

[proven arrow]

Some modules support it some dont. @chilly creek

[toxic mist]

actually i want to use where statement count rows
then after ther are counted use LIMIT/OFFSET how can i do it

[proven arrow]

It does that

[brazen charm]

you've completed half the query by yourself

[proven arrow]

What I sent you earlier is what you need

COUNT(*) gives you count of results matching the Where

[toxic mist]

i mean

then how do i do

the limit/offset part

in one line i use count

cur.execute('''SELECT COUNT(*) FROM info WHERE user_id = 2882911991919;''')
then do i use the limit/offset in seperate line?

[proven arrow]

Well what result are you wanting?

Do you want just the count value? Or other columns with it?

[toxic mist]

count value for all data for a user

and then

some limited rows

[proven arrow]

Which database are you using?

[toxic mist]

sqlite

3

ee i just thought i can make 2 cursor

but that would take extra time

[proven arrow]

SELECT *, COUNT() OVER FROM info WHERE user_id = xxx

Try that, if I understand your question correctly that's what you need.

It uses a window function and each row will have an extra column at the end that gives you total count of how many rows match the WHERE.

You can still use the limit and offset as well

[toxic mist]

umm

ok

btw which is faster joining 2 tables
and using them or
using them seperately and then using variables to link them @proven arrow

eee idk how to state problems lol

[proven arrow]

Just join and let your database do the work, so you get a single result set back. And Performance like that you will never notice probably for your use case.

[toxic mist]

umm ok

one of my table would have 1.8k+ rows(250 atm) and other one would keep increasing assuming people use it :p

[proven arrow]

Still should be fine

[toxic mist]

umm ok

need to read my notes coz i forgot joining

[proven arrow]

Also the example to your question you can see here

!eval

import sqlite3

with sqlite3.connect(":memory:") as db:
    db.execute("CREATE TABLE info (id INTEGER PRIMARY KEY, user_id int)")
    db.execute("insert into info values (1, 123)")
    db.execute("insert into info values (2, 545)")
    db.execute("insert into info values (3, 999)")
    db.execute("insert into info values (4, 877)")

curs = db.execute("SELECT *, COUNT() OVER() FROM info LIMIT 2")
rows = curs.fetchall()

print(rows)

[delicate field]

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

[proven arrow]

Oh well

#bot-commands message
If you check the result you can see that the last column returns 4 because that is the total count matching the where clause. But the rows returned are 2 due to the limit. If that makes sense?

[toxic mist]

umm ok

what does over() do?

and how do i print the count?

[brazen charm]

please god dont

[toxic mist]

why not

[proven arrow]

OVER() is part of the window function so we apply the count function to those groups

[toxic mist]

umm ok

[proven arrow]

Or you'll end up like the Health service from the UK 😅

[toxic mist]

so i put my where statement inside over?

[proven arrow]

I think they used some sort of excel spreadsheet for their data storage

[toxic mist]

and this one

[brazen charm]

Lets go over it:

• Slow
• Not made to be a database
• Requires dealing with the google oauth system
• limited amount of uses per month as set by google's api
• Litterally easier to just use a normal db like sqlite or postgres

[proven arrow]

See the example I showed and the output. The count is added as the last column.

Although it depends what kind of data you want to store in sheets. And how it's to be used.

[toxic mist]

u didn't print the no. of rows

[proven arrow]

It did, the value was 4. Look again.

[toxic mist]

oh ok

thx

[tiny needle]

using SQLite, is the correct syntax for adding in a value where a condition is met: INSERT INTO tblname(column) VALUES (values) WHERE <condition>?

[tiny needle]

yeah, then it ran out of space xD

[proven arrow]

Insert statement doesn't have where clause

[tiny needle]

oh

how would i insert a value at the row where another row meets a condition?

[proven arrow]

What is the the condition? Why do you need this requirement?

[tiny needle]

its for a discord bot, i want to insert a channel id into a column where the guild id matches the id from the guild the command was invoked in

[proven arrow]

I think you are looking for the UPDATE statement

[tiny needle]

ah

maybe

[proven arrow]

So that guild row already exists right?

[tiny needle]

yes

[proven arrow]

Yeah so update is what you need

[tiny needle]

ok

so UPDATE tblname(channel) VALUES (<channel id>) WHERE guild = <guild id>?

[proven arrow]

UPDATE tablename SET channel=new_value WHERE ...

[tiny needle]

ah ok, thanks :)

is it just the single = or == like python uses?

[proven arrow]

Single

[tiny needle]

thanks so much :)

[umbral light]

does anyone have had build a crawler and processing into crawler_data, crawler_dissemination , and merge the dissemination data into the customer one? I want to properly build a schema for this

[torn sphinx]

what's the difference between sql and nosql databases? Any tradeoffs I should know about?

I'm already learning Postgres and my friend told me to look into MongoDB

[brazen charm]

Mostly how they handle the data

Sql is relation e.g. Columns and rows which makes it very good for consistent bit of data

Nosql is generally better for bits of data that might not be as constant or other reasons

As for mongoDB

ItS WeBScALe

Which is just a massive meme at this point

Postgres is faster than mongo in I think every single possible situation regardless of what data it is

The python drivers for Sql dbs are also alot better than Nosql ones and alot more to choose from just cuz it's been around longer

If you're gonna go with sql - > postgres is top dog

If you wanna go for nosql - > scylla is a monster

Mongo is easy for people to get started with but very lacking in other areas like speed and consistency

[vital edge]

Hello I would've like some help if possible
When I do
https://cdn.discordapp.com/attachments/696432394974265374/780579561461121054/unknown.png
Everything works just fine but when I uncomment it errors and I have no clue why
https://cdn.discordapp.com/attachments/696432394974265374/780579666523848724/unknown.png

[marsh prawn]

Can anyone suggest which database to learn with django and python????

[shell ocean]

any SQL dialect will be fine

Django abstracts most of that away from you anyway

[marsh prawn]

What about firebase?

[shell ocean]

...why?

[proven arrow]

Because ORDER BY should be at the end of your query.

[velvet coyote]

Hey why am I getting this error?

    current_channel = await self.connection.fetchrow('''
AttributeError: 'coroutine' object has no attribute 'fetchrow'

this is the code

    @property
    async def connection(self):
        """A function which connects to the db"""
        conn = await asyncpg.connect(Bot.db)
        return conn

async def someotherfuntion():
      current_channel = await self.connection.fetchrow(''' SELECT * FROM dms WHERE user_id = $1''', message.author.id)

[silk ember]

you need to use .fetchrow on your cursor

[velvet coyote]

huh

    conn = await asyncpg.connect('postgresql://postgres@localhost/test')
    # Execute a statement to create a new table.
    await conn.execute('''
        CREATE TABLE users(
            id serial PRIMARY KEY,
            name text,
            dob date
        )
    ''')

    # Insert a record into the created table.
    await conn.execute('''
        INSERT INTO users(name, dob) VALUES($1, $2)
    ''', 'Bob', datetime.date(1984, 3, 1))

    # Select a row from the table.
    row = await conn.fetchrow(
        'SELECT * FROM users WHERE name = $1', 'Bob')
    # *row* now contains
    # asyncpg.Record(id=1, name='Bob', dob=datetime.date(1984, 3, 1))

    # Close the connection.
    await conn.close()

the docs shows i can use on connection

[knotty gyro]

how to check for value in a cell in sqlite3?

[elder socket]

hi

how to alter sql views?

py
ALTER TABLE financial2_main ADD COLUMN LOB INT DEFAULT 1;

i need the same output of command when altering table in views

[elder socket]

*SOLVED
had to recreate the view

[knotty gyro]

how to store a list in sqlite3 column?

[steady totem]

Any sql pro here? Need help with a query

[chilly creek]

@steady totem https://dontasktoask.com/

@knotty gyro not neccesarily recommended as it breaks ACID (atomicity), but if you want an OOP approach - it is possible to create nested tables

[steady totem]

@chilly creek You're right 🙂

So here it goes

I have this query which returns the below table

I limited return to 3 of each city, and also some return one because condition doesn't satisfy.

However I want to return 3 of new york one from san francisco and one from chicago

The query right now is this:

SELECT
city,
group_id,
group_name,
members
FROM
(
SELECT
c.city,
g.group_id,
g.group_name,
g.members,
RANK() OVER ( PARTITION BY city ORDER BY members DESC ) AS rankname
FROM
grp g
INNER JOIN city c ON c.city_id = g.city_id
WHERE
g.rating = 5
) AS b
WHERE
rankname <= 3

Any idea how to limit results to 3 new york 1 sf and 1 chicago?

[chilly creek]

join 3 selects together, 1 with WHERE city='new york' LIMIT 3 and about the same for sf and chicago

[steady totem]

aha that was very simple

thanks @chilly creek

@chilly creek You mean join with union and 3 different select queries right?

[chilly creek]

yep

could imagine there is a better solution, but union should work

[steady totem]

Not very elegant though and have to write the conditions for each query

But works yeah

[vital edge]

        Q_Remove = """
                    DELETE        
                    FROM Horaire
                    WHERE 
                        Horaire.ID = Info.idHoraire
                        AND Info.idMessage = (%s)
                   """```
I want to do this but it doesn't work obviously
What would be the right way ?

I also tried with an inner join but didn't work either

Q_Remove = """
                    DELETE        
                    FROM Horaire
                    INNER JOIN Info 
                        ON Horaire.ID = Info.idHoraire
                    WHERE 
                        Info.idMessage = (%s)
                   """```
doesn't work either

[clear elbow]

Anyone know why this errors?

mydb = mysql.connector.connect(
  host="<host>",
  user="<username>",
  password="<my_pass>"
)

print(mydb)```
Everything matches up, yet I don't know why it does not connect...

[cosmic smelt]

well you've only declared the string, you're not using it

the syntax is also wrong, I think

[clear elbow]

I am following https://www.w3schools.com/python/python_mysql_getstarted.asp

so I don't know lol

[cosmic smelt]

I would write

mydb = {
  'user': 'root',
  'password': 'password',
  'host': 'localhost'
}

db = mysql.connector.connect(**mydb)
cursor = db.cursor()

replace root, password, localhost with whatever you're using

[clear elbow]

k, lemme try that

[cosmic smelt]

also printing the connection string does nothing

:}

[clear elbow]

lol

[cosmic smelt]

does it work, or

[clear elbow]

I think it is an error with the database, as I got this error
mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '<server>' (11001 getaddrinfo failed)

[cosmic smelt]

can I see the full code

[clear elbow]

mydb = {
  'user': '<user>',
  'password': '<pass>',
  'host': '<host>"'
}

db = mysql.connector.connect(**mydb)
cursor = db.cursor()```

I have another method I can use, but it will take me a bit to set it up

[cosmic smelt]

you've assigned a database name right?

[clear elbow]

yes

I was trying to use an online databse hosting, but it looks like I will have to host myself

[cosmic smelt]

the above works on a mysql express/community server

unsure what it's called

[clear elbow]

I am setting up one myself, I really just need to add a user, and set up the actual database haha

[cosmic smelt]

:}

[icy fable]

I just had to tell someone, I built my first database query logic and it WORKS finally 🙂 Thanks to the help of some folks in this discord channel for sure

[bold hamlet]

CREATE TABLE usuario (nombre_xd INT AUTO_INCREMENT PRIMARY KEY, nombre VARCHAR(255), puesto VARCHAR(100))

Whats

error

??

[torn sphinx]

i wrongly added postimg column in users table

how can I get it removed?

I've been using alter table to add rows

is there any way to delete row?

[mortal scarab]

DELETE FROM tablename WHERE clause

[torn sphinx]

ugm

i mean

i want to completely remove that postimg thing @mortal scarab

[mortal scarab]

DROP COLUMN name

[sharp nest]

@torn sphinx ALTER TABLE table_name DROP COLUMN column_name;

[restive parrot]

I am making a game but i need to store some data like game Progress and user Choices like themes and Stuff and we want to store all this Locally*
My question is which Database should I use for it as i want to deploy the Game on platform like Steam
I have started doing Sqlite but i dont like it that much as the data is exposed (anyone who knows programming can modify it)

can i use db like mongo db for such things and if yes then How?
The main thing is that the users dont have an easy access to the database

[torn sphinx]

Why people don't use f strings

in sql

[shell ocean]

because that allows SQL injection

[torn sphinx]

what's injection?

SQL injection

[tiny needle]

where, if the code is not written properly, you can use true statements to gain access to data in a database

[shell ocean]

basically, the user can run arbitrary SQL code

and do stuff to your database

[lusty quarry]

Hi, I was trying to install firebase through pycharms but getting some errors

can someone help?

[proven arrow]

For example, this is why.

user_input = "1234"  # input you expect
user_input = "1234; DROP TABLE students;"  # input user actually gives. Note the extra drop command.
cursor.execute(f"SELECT * FROM students WHERE value={user_input}")  #  = SELECT * FROM students WHERE value=1234; DROP TABLE students;

Can you see what would happen? This example uses DROP but you can do the same to run (inject) other queries.

@lusty quarry What errors?

[torn sphinx]

Hi, I need some help to insert some data into a postgres table using psycopg2. I did an API call which returns a dictionary
with multiple key:value pairs of 'group' and 'count '. So I created a qsql table and I want to insert all the
value of group in the group column and count in the count column. Here is a snippet of the data (which is stored
in the variable parsed) I want to insert:

```{'result': {'count': 1, 'data': {'reportData': {'data': [{'group': 'Physical Abilities', 'count': 566864}, 
{'group': 'Communication Skills', 'count': 516756}, {'group': 'Cleaning', 'count': 473754},```

So you can see the multiple group and count keys which are nested. Here is the part of my code which I am struggling
with:

cur = conn.cursor()
cur.execute("INSERT INTO ingest.hospo_occupations_US_2010 (group, count) VALUES(%(group)s, %(count)s)", (json.dumps(parsed),))
conn.commit()
cur.close()
conn.close()```

I get this error: TypeError: tuple indices must be integers or slices, not str

[proven arrow]

json.dumps will give you a string

Your argument mapping should be like this: {'group': json.dumps(...), 'count': json.dumps(...)} Where you replace the ... with the corresponding data from your dict.

[torn sphinx]

ah ok, thank you @proven arrow I will try it that way. Should that then add all the values for group and count? (and not just once)

[proven arrow]

What do you mean, add all values and not just once?

[torn sphinx]

i mean that there are multiple keys of group and count so I want to put all the values in the table columns.

'group': 'Physical Abilities', 'count': 566864,
'group': 'Communication Skills', 'count': 516756,
'group': 'Cleaning', 'count': 473754,

so it just doesnt grab the top row for example

[lusty quarry]

ERROR: Command errored out with exit status 1: 'c:\users\asus\desktop\thedate\venv\scripts
\python.exe' -u -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'C:\Users\ASUS
AppData\Local\Temp\pip-install-36fiaa1u\pycryptodome\setup.py'"'"'; file='"'"'C:
\Users\ASUS\AppData\Local\Temp\pip-install-36fiaa1u\pycryptodome\setup.py'"'"';f=ge
tattr(tokenize, '"'"'open'"'"', open)(file);code=f.read().replace('"'"'\r\n'"'"', '"'"
'\n'"'"');f.close();exec(compile(code, file, '"'"'exec'"'"'))' install --record 'C:\Us
ers\ASUS\AppData\Local\Temp\pip-record-2f7z5un5\install-record.txt' --single-version-exter
nally-managed --compile --install-headers 'c:\users\asus\desktop\thedate\venv\include\site
\python3.8\pycryptodome' Check the logs for full command output.

[proven arrow]

Well it depends how you get the items from the list. Those are all elements of a single list. If you want to store all those 3 items in a single column, then you would have to serialize that entire list. @torn sphinx

[torn sphinx]

Yeah I want to store all the items, I have a lot to store. Thanks for the tip, I will look into serialising it.

[proven arrow]

serialising is just json.dumps()

[torn sphinx]

ok cool, newbie here haha

[proven arrow]

But i meant dont index that list. Looking at your dictionary it would be something like json.dumps(my_dictionary['result']['data']['reportData']['data'])

[lusty quarry]

@proven arrow what can I do for this?

[proven arrow]

Are you sure the error is firebase related? Does not look like it

What is it you are trying to do

[torn sphinx]

@proven arrow thanks

[lusty quarry]

Yes I was using pip install pyrebase command

[proven arrow]

Oh, maybe its an issue with that library since its not official. Try and use the official library for python

[lusty quarry]

and how to do that?

sorry I'm new to this

[proven arrow]

Let me find the link, its called firebase-admin or something like that.

@lusty quarry https://pypi.org/project/firebase-admin/

[lusty quarry]

That's successfully installed but is it goanna work like pyrebase command?

[proven arrow]

Probably not, but you will have to check since they are different. You can check the docs here: https://firebase.google.com/docs/reference/admin#python

[lusty quarry]

Ok thanks!

[proven arrow]

You will want firebase_admin.db for the realtime database or firebase_admin.firestore for firestore

[lusty quarry]

It's done, I manually installed it though the project Interpreter

Thanks @proven arrow

[torn sphinx]

My code doesn't use drop and stuff

It's just inserting and updating

[long cipher]

#help-pancakes

[proven arrow]

@torn sphinx That still doesnt matter. It was just an example, the same can happen with update.
Run this code and you will see.

import sqlite3

with sqlite3.connect(":memory:") as db:
    db.execute("CREATE TABLE users (id INTEGER, email TEXT)")
    db.execute("INSERT INTO users values (1, '[e-mail]'), (2, '[e-mail]')")

user_id = "id IS NOT NULL"
new_email = "xx"

db.execute(f"UPDATE users SET email = '{new_email}' WHERE id = {user_id}")
cursor = db.execute("SELECT * FROM users")

rows = cursor.fetchall()
print(rows)

[torn sphinx]

So what should I do ;-;

@lufth

@proven arrow

[proven arrow]

Pass values as parameters instead. See examples here, https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.execute

In the above example, you can replace the same update query with: ```
db.execute("UPDATE users SET email = ? WHERE id = ?", (new_email, user_id))

[torn sphinx]

aight

i have a qustion about psycopg2
does cursor.execute()
execute the commands in it or i need to use another function
because deleting a row is not working :<

yes

the connection is good

ok

import psycopg2 as ppg

#connecting
connection = ppg.connect(
    host="localhost",
    user="******",
    password="*****",
    database="*****"
)

with connection.cursor() as cursor:
    #doing stuff

    id = input("Enter the user's ID: ")
    cursor.execute(f"DELETE FROM users WHERE id={id}")


#closing conection
connection.close()```

wym

oh

what does that do

hmm

i didnt know that

lemme test it

if i delete the same user 2 times it should give me an error right?

because it is not giving me any

i checked teh user doesnt exist

it worked

thank you

@proven arrow

    cursor = await db.execute('SELECT prefix FROM settings WHERE guild_id = ?', (guild.id))```
as you said.

but looks like its not working'

ValueError: parameters are of unsupported type

[quartz moon]

Hello I want to add a new empty dictionary to every existing document inside a mongodb collection, I wonder if the following code would do that: db.books.update( {}, { $set: { users: {} } }, {multi: true} }) Ty in advance, any help is very welcome

[brazen charm]

mongo will ignore empty docs

[quartz moon]

The document is not empty it has several fields, but I want to add a new one to all the existing documents

[azure oyster]

Hello anyone knows how can i write a user data to a json file

[cold quail]

db = await mysql.connect(**SECRETS.MYSQL_SECRET)
cursor = await db.cursor()
await cursor.execute("SELECT Emoji, Role, message_id, channel_id FROM reactionroles WHERE guild_id = 681821783908810752 and message_id = 781177874077843516 and Emoji = '😏'")
result = await cursor.fetchall()
await db.commit()
await cursor.close()
db.close()

(('😂', 714901263799681096, 781177874077843516, 681821783908810758), ('😏', 714902014814847047, 781177874077843516, 681821783908810758))

Why does it print Those two even though I search for an entry where the Emoji is 😏 ?

[torn sphinx]

Does anyone know any good databases for beginners

[torn sphinx]

It's highly unrecommended

Json files tend to break and make you lose your info

[azure oyster]

what can i use then

a database would be better but im starting with json

[dusky plaza]

Let's say I have first_name and last_name columns in my sql db. I want to perform a search combining them but I don't know wether the first word user entered is first name or last name. What would be the best solution to this? Beside making a full_name column

[azure oyster]

client_data = {
"ID: ": user_id,
"Client Info": ["Client name": user_name,
"Client Age": user_age,
"Client Email": user_email,
"Client Gender": user_gender
]
}

i have an error

invalid sytax

at this line

"Client Info": ["Client name": user_name,

[dusky plaza]

You have multiple errors in that

[azure oyster]

o fak

[dusky plaza]

Starting from "ID:"

[azure oyster]

hmm

what abuot now

client_data = {
"ID": user_id,
"Client Info": ["Client name": user_name,
"Client Age": user_age,
"Client Email": user_email,
"Client Gender": user_gender
]
}

[dusky plaza]

Use { instead of [

[azure oyster]

ig it works ow

[dusky plaza]

Also for JSON standard is to use camelCase

[normal glade]

Hiya, I need some help, anyone available?

[azure oyster]

i have 1 more question

i made like a bank system for registering users now and im taking the input and storing it and now i want to put everything in the json file

but its not making multiple users

its overwriting

[dusky plaza]

You want to store each user object in its own array and append to the json file

[azure oyster]

How would i do that

.append?

[dusky plaza]

You need to know json.load and dump() also basic file manipulation

I am on phone so can't type much

[azure oyster]

alright

i use dump

for storing

[normal glade]

He means that you need to know what it does if you want to manipulate files

[dusky plaza]

First load the file, append to it and then dump to same file

[normal glade]

If you simply use json.dump() it will dump whatever the input is, and removing the rest

Instead you want it to add something

[azure oyster]

oh makes sense

[normal glade]

What he said

@dusky plaza How can I check if a row has value using sqlite3

  hours REAL)```

[dusky plaza]

Can you give s bit more detail?

[normal glade]

So I want to check if pay has a value set

I am making an expense checker and I just finished the login/register/menu

Using sqlite databases

[dusky plaza]

For reverse use is null

Thats the sql

[normal glade]

But now when people log in for the first time, I want them to enter their data

If this isn't their first time logging in it'll just skip the function

Aight, I'ma try that

[dusky plaza]

Fuck typing on phone lol

[normal glade]

True

I get ya

I don't think this is exactly what I'm looking for

Are you on a pc anytime this evening?

[gentle finch]

hello

grammar

[normal glade]

?

[gentle finch]

bcz you are grammarly correct person

u r nerd

[normal glade]

xD, cheers

I guess

[dusky plaza]

Does the query not work or it gives wrong data? I have not used SQLite3 much so maybe the syntax is wrong there

[normal glade]

Uhm

I think it works, but it's not quite what I am looking for

Here the idea

login > (check if data has been filled in. IF NOT: fill in data) > menu of things you can do on the app when logged in```

Right now I am stuck at (check if data has been filled in)

[proven arrow]

Just check the count, of how many rows returned where the column you require not filled is empty/null

If the count is 0 then its filled, otherwise its not

[normal glade]

Aight

And then for some reason it says that the row is not defined

c.execute("""
CREATE TABLE IF NOT EXISTS data(
  pay REAL,
  hours REAL
)""")```

def information():
  if main.is_logged_in:
    pay = input("Please tell me how much you earn an hour: ")
    hours = input("Please tell me how many hours you work a day: ")
    with sqlite3.connect("users.db") as db:
        c = db.cursor()
    insertData = """INSERT INTO users(pay,hours)
    VALUES(?,?)"""
    c.execute(insertData,[(pay),(hours)])
    while True:
      if pay == None or hours == None:
        print(data)
        if isinstance(data, float) and isinstance(data2, float):
          insertData = """INSERT INTO userData(Pay,Hours)
          VALUES(?,?)"""
          c.execute(insertData,[(data),(data2)])
          db.commit()
          break
  else:
    pass```

When I run this, it says pay not defined

In the example given it isn't structures as it's supposed to be yet, but it's just for testing

[proven arrow]

Show the full error

[dusky plaza]

Are you sure this syntax is correct? (insertData,[(data),(data2)])

I don't remember much but I don't think you pass in a list of tuples

for sqlite

[normal glade]

Ah, no that isn't, but that shouldn't be the problem I thinjk

[dusky plaza]

also those tuples are invalid, tuples must have 2 values

so [(data,),(data2,)] would be correct

[proven arrow]

What they have is fine

@normal glade your table is called data but you insert into table called users ?

[normal glade]

Hmm

It seems to be doing something now

[proven arrow]

You also named the table inside while loop something different as well

[normal glade]

Yeah, I changed that now

So now I need to do the following: is logged in? > check if user has data > continue/no data? > enter data

But now I have two different tables

users and data

But how would I link the data in data to an user in users

[dusky plaza]

you join them on the id

make an id column in users and autoincrement it

and in data make user_id column

thats how you make relationships

[normal glade]

Aight, let me try that

You mean use join()?

[dusky plaza]

nah, you use sql JOIN keyword

[proven arrow]

@normal glade This explains it well. https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

[normal glade]

Cheers

[orchid oyster]

is there a way for aiosqlite to access a :memory: database created by sqlite3?

[dusky plaza]

What is even the use case for that?

[normal glade]

      c.execute("""SELECT * FROM users
        FULL OUTER JOIN data
        ON users. = data.pay
        """)```

If I were to use this, I would have to create a row in side my users table for pay aswell, or do I see that wrong?

[orchid oyster]

I'm using a :memory: database as a placeholder for a real database, so I don't have to constantly delete it for testing purposes; I plan on having the script check for a database's existence, and create it if not, before accessing it from an event loop

hence, I need to use both sqlite3 and aiosqlite

[dusky plaza]

interesting, however I never used that so I am not sure

[orchid oyster]

I guess I could just have the check take place all in the event loop, but I don't want to risk something trying to access it before it is ready

[dusky plaza]

@normal glade you probably wanna join on users.id = data.user_id

[orchid oyster]

that would be bad

actually, yeah

it would be very bad

[normal glade]

def information(pay, hours):
  if main.is_logged_in:
    with sqlite3.connect("users.db") as db:
      c = db.cursor()
    while True:
      c.execute("SELECT rowid FROM data WHERE pay = ?", (pay,))
      data = cursor.fetchall()
      if len(pay)==0:
        print('There is no data stored')
        pay = input("Please enter how much you earn an hour: ")
        hours = input("Please enter how many hours you work a day")
        insertData = """INSERT INTO data(pay,hours)
        VALUES(?,?)"""
        c.execute(insertData,[(pay),(hours)])```

Would this not be an option?

[orchid oyster]

the database would need to be cached in its entirety before the data is usable, and the time that would take grows as it gets larger

[normal glade]

@dusky plaza / @proven arrow

[proven arrow]

@normal glade Why are you checking len(pay)?

pay is what you input through the function

[normal glade]

Yeah, idk what I'm doing

Do you have time to enter a call at any chance @proven arrow ?

[proven arrow]

No sorry, but why not just ask here?

Your check should be if not data:, and you need to be breaking out the loop otherwise its inifinte

[normal glade]

    while True:
      data = cursor.fetchall()
      if not data:```

from login import *
import __main__ as main

def information():
  if main.is_logged_in:
    with sqlite3.connect("users.db") as db:
      c = db.cursor()
    while True:
      data = c.fetchall()
      if not data:
        print('There is no data stored')
        pay = input("Please enter how much you earn an hour: ")
        hours = input("Please enter how many hours you work a day: ")
        insertData = """INSERT INTO data(pay,hours)
        VALUES(?,?)"""
        c.execute(insertData,[(pay),(hours)])
        db.commit()
        break
      else:
        print("hiya")
  else:
    pass

This is what I have now, but for some reason the data doesn't get inserted to the database