databases

[pale crest]

Or a set.

[torn sphinx]

yep.. I'll go with set

😃

[pale crest]

Set feels more appropriate if it's a number of unique queries.

Batch if it's like a number of queries performed during a particular time interval, or something.

[dawn pulsar]

I'm using MySQL connector on a MariaDB, one of the columns is the datetime on which something happened, how do I make it so that only things that happend x days ago are selected?

[void otter]

You need to add to your query
WHERE datetime>6969 and datetime<4201337

I think so

[dawn pulsar]

But in the query, I need to convert it to a string, and then It won't work?
Because in the database it's a datetime object, but to use it in a query, it'll have to be converted to a string?

[harsh pulsar]

what happens if you do cursor.execute('select * from mytable where date > %s', datetime.now())?

does that work?

if not you might need to either convert to unix epoch time

or use strftime to convert to string

before feeding to query

also please for the love of all that is sacred make sure you are using query params for this

i dont remember if mysql connector uses %s or ?

[dawn pulsar]

I think it's ?

@harsh pulsar

That's what happens when I try this

[harsh pulsar]

can you show your code

[dawn pulsar]

Checkpoint is datetime.now() - x days

Also it says time but it's really the date and time, ehh, not my database

[harsh pulsar]

oh

dont do that though

i literally just said, use query params

[dawn pulsar]

I don't know what that means, halp

I have very little experience with databases

[harsh pulsar]

I think it's ?

i thought you knew what i meant based on that

sorry

uhh one sec

query engines use placeholders in the query

instead of making you struggle w/ escaping and stuff

so instead of .execute('select * from foo where x = {x}'.format(x=1)) which is both cumbersome and dangerous (look up "sql injection") you do .execute('select * from foo where x = %s', (1,)) which lets the database engine correctly escape and process the input data

see example here: https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html

python lets database engines what to use for placeholder, e.g. %s or ?

note that %s is NOT the same as %s used by the % string operator

[dawn pulsar]

aha

Figured it out

Ah

@harsh pulsar How do I do 2 %s?

[harsh pulsar]

show your code not just a screenshot of the error 😐

!ask

[delicate field]

ask

Asking good questions will yield a much higher chance of a quick response:

• Don't ask to ask your question, just go ahead and tell us your problem.
• Try to solve the problem on your own first, we're not going to write code for you.
• Show us the code you've tried and any errors or unexpected results it's giving
• Keep your patience while we're helping you.

You can find a much more detailed explanation on our website.

[harsh pulsar]

the params are always a tuple

so .execute('%s %s %s', (1, 2, 3))

[dawn pulsar]

Oh ok

[harsh pulsar]

obviously thats not correct sql but

[dawn pulsar]

Neat, thanks

[harsh pulsar]

some query engines let you name the params

.execute('%(a)s %(b)s %(c)s', {'a':1,'b':2,'c':3}) but i dont remember which ones allow this

[dawn pulsar]

@harsh pulsar Everythings great, thanks for the help <3
Quick question, I know how to do "WHERE x IN (foo, bar)" but how do I do
"WHERE (foo) in x"

if x is a list in the second one

[harsh pulsar]

x is a column in the db?

[dawn pulsar]

Yes

:D

@harsh pulsar

[harsh pulsar]

cursor.execute('where %s in x', (foo,))?

wait

is this about string containment?

like you want to find a substring in a column?

[dawn pulsar]

One of the columns holds a list

I wanna check if one value is in that list

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '<the column> at line 1

@harsh pulsar

[harsh pulsar]

im not a mariadb user, but afaik mysql doesnt support a native list/array datatype

i could be wrong

[dawn pulsar]

Ok

Yeah, so I gotta figure out <string> in column

Hmm

LIKE

AHA

I got it

[bronze nebula]

hey i just need some SQL help

SELECT * FROM table WHERE condition returns the correct row, but UPDATE table SET key = value WHERE condition just gives me UPDATE 0

i checked that the conditions are the same

[tropic folio]

is this that place to ask for help with sql queries?

[carmine heart]

This is our channel for questions related to databases, yes

[tropic folio]

Just doing an assignemnt and got this question:
"For each academic, give the acnum, givename, famname and the total number of papers s/he has written. Note that if an academic has not written any paper, his/her total should be zero. You can use or not use JOIN operators. "

i tried doing this:

SELECT academic.acnum, academic.givename, academic.famname, COUNT(author.panum)
FROM academic, author
WHERE academic.acnum = author.acnum
GROUP BY academic.acnum, academic.givename, academic.famname, author.acnum;

just confused how to add this:
"if an academic has not written any paper, his/her total should be zero"

[round folio]

I am new to database. Where should I start?

any suggestions??

[void otter]

Watch some videos on yt

[round folio]

@void otter any other specific suggestion?

[void otter]

You really can't learn databases alone

I learned a bit od sqlite while i was learning flask since i needed a db

[round folio]

This was the thing which I was expecting

You started off with sqlite

[ionic pecan]

„Watch some videos“ is bad advice

The PostgreSQL documentation has a great intro to SQL, including setting up Pg itself, see https://www.postgresql.org/docs/11/tutorial.html

[bronze nebula]

hey sorry for a double post but there’s this, also its psql

or pg. whatever you want to call it

[polar patrol]

What do you guys think about using Google Sheets as a database? (Just in general, im curious!)

[torn sphinx]

it's not a database

anything about 3000 rows and you start to experience latency issues

plus it can't handle special symbols and non locale languages when you try to send/receive json payloads

[pure cypress]

It has a lot of provision to be used as one certainly, but in the end it isn't

For lightweight stuff you could probably get away with it

But I really wonder why one would bother if one has the means to host a db themselves

[torn sphinx]

I lead a clan in Warframe, and I have two groups of members: those who are in our discord server and those who are in our in-game clan. These groups partially overlap. I need to store some specific data on each member, like date of joining, clan points etc. How should I organize the database? Right now I see two solutions:

1. One table, primary key is a simple self-incrementing id
2. Two tables: one is for those who are in our discord with user id as primary key; the other is for the remaining in-game members with their aliases as primary key
   Which one would be cleaner, easier to maintain and overall better? Maybe there are other solutions?

[pure cypress]

One table seems easier

You could put a unique index on the discord id/name

[torn sphinx]

Will it let me insert values with NULL discord ids?

[pure cypress]

Oh true

damn

I dont think so?

Worth looking into though

[torn sphinx]

Yeah, gonna search that

It seems that I can exclude NULLs from the UNIQUE duplicate checking

[torn sphinx]

what do you mean null discord IDs

everyone has IDs

and they are unique for each server arent they

I have two groups of members: those who are in our discord server and those who are in our in-game clan. These groups partially overlap

I mean some are in discord only, some are in clan only, but the majority is in both

ok I get it

[golden glacier]

you also arent tied to making the data a primary key, you can always leave that as some generic int and just set whatever fields you want to be unique, unique

and theres absolutely no problem inserting null values into columns

and two tables is definitely the right approach

if theres any mutuality in the data, you can always do a join

[torn sphinx]

I think I'll try to put them all in one table with meaningless PK and unique discord ids / in-game names

[golden glacier]

thats fine as well

but just as a bit of a suggestion.

in the multitable example

you could for example have the warframe table just strictly represent the data available.

and then you could also include non-clan members in the dataset

and join on id = id, clan = myclan etc.

[torn sphinx]

or get everyone to join the discord and avoid two sets of IDs altogether lol

[golden glacier]

its always convenient to keep the data in the same format it comes in as, and then manipulate it later. but thats completely your prerogative.

[torn sphinx]

There are some lazy asses who don't want to be in discord, some just don't like it
Yeah, both categories need the same data stored. I want to move it from google sheets finally

[pure cypress]

It depends how much shared data they have

If there's a lot then I feel 1 table is better

[torn sphinx]

Alright, thanks everyone. Now I just need to get myself a rpi and start using pgsql instead of sqlite

you can upload spreadsheet to bigquery

vrooom

No learning from that though, I want my own database

Another question. Right now clan ranks in the table are automated, thanks to the function that checks the date of joining and sum of points and shows the correct rank:
=IFS(AND(TODAY()-56>=F49, U49>=3),"4th Layer", AND(TODAY()-28>=F49, U49>=2),"3rd Layer", AND(TODAY()-14>=F49, U49>=1),"2nd Layer", ISBLANK(A49)=False,"1st Layer", True, "")
Can it be automated with triggers, or I need to run UPDATE daily?

Doesn't seem like triggers can handle that

[ionic pecan]

https://www.postgresql.org/about/news/1943/

[polar isle]

For PostGreSQL I am trying to make it so I know how many collumns are in a table

length = await self.bot.pg_con.fetchrow("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_catalog = 'Game Hub' AND table_name = 'users'")
print(length)```
This returns
``<Record count=12>``
So it is kinda what I want but is there a way I can have it be just be 12?

[torn sphinx]

Try len()

[torn sphinx]

On the second thought, COUNT should return an interable with one value. Try length[0]

[polar isle]

That works 😃 thanks

but one more thing i cant seem to figure out...

length = await self.bot.pg_con.fetchrow("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_catalog = 'Game Hub' AND table_name = 'users'")```
How can I have this only return how many collumns are in a table thats values are true, so like
```python"length = await self.bot.pg_con.fetchrow("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_catalog = 'Game Hub' AND table_name = 'users' AND ticket = $1", True)```

when i do that it says that ticket is an unidentified column

[copper echo]

How to move column from one table to other?

[torn sphinx]

With one of JOIN queries I suppose

[copper echo]

what is join query?

how to use it

[torn sphinx]

Try searching info about it, I'm on phone and don't remember the details besides there's multiple different joins

[harsh pulsar]

just google "sql join types"

million articles on it

[pure scroll]

you can either go for joins if you need to just query the data

or you can do alter table and add the new column

[stone jacinth]

Hey guys, I'm building a backend for a mobile app and I am trying to decide between GraphQl and a REST API. Any thoughts?

I'm a little concerned with version control. I don't want my app to break if someone uses an outdated version. Does anyone know if that is much of an issue? I.e., can Google Play/App Store push updates through instantly?

[ionic pecan]

Moving columns != join queries, but moving columns sounds like XY. What are you trying to achieve?

[boreal shale]

guys? i created a database with a table and want to insert stuff but it isnt working it isn´t trowing errors.. but i also have nothing in my databse

https://gyazo.com/681e4ce904afb61570a81475b7e1cdad

@grizzled roost can u pls help me?

[grizzled roost]

Well I'll be honest I've never worked with mysql and python in combination but shouldn't there be a way to "submit" a query?
I don't think you're doing that at this point, but I'll look at some docs

[boreal shale]

thx

[grizzled roost]

ah

from looking at some examples from https://www.w3schools.com/python/python_mysql_create_table.asp
I found this line mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

so it does seem like you need to submit/execute a query like that

does this help?

[boreal shale]

maybe i think thx

[grizzled roost]

and lastly, please don't tag helper randomly 🙂

[boreal shale]

dammm yes that explaines everything I needed to refresh my mysql stuff and forgott thast thx AAANNNDDD yes sry I wont do again thx for reminding not to ping u c:

[ionic pecan]

@boreal shale your query in the screenshot wasn't terminated, sql needs to end in ;

[boreal shale]

yeah right also forgott that buuut it´s also working without

[misty nebula]

hello

i am trying to understand something regarding sqlalchemy

is it possible to query to return also the relationship?

[torn sphinx]

I've set my .schema as the following:

    c.execute("""CREATE TABLE IF NOT EXISTS matches (champId_1 INTEGER,
                                                     spell1_1 INTEGER,
                                                     spell2_1 INTEGER,
                                                     champId_2 INTEGER,

but when I would look at it in DB Browser, I see that they are BLOB type, not INT

sqlite3

This is how I would INSERT

            for row in blue:
                print(row)
                c.execute("INSERT INTO matches VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", row)
                conn.commit()

where row can be the following:

[412, 4, 14, 421, 11, 4, 145, 4, 7, 39, 4, 12, 24, 4, 12, 1, 'BOTTOM_DUO_SUPPORT']

https://i.gyazo.com/c6c2ada22294c54fcfabfd269701d2fd.png

[tropic grotto]

Hello, I am using Google Firebase- Firestore

I want this:
doc_ref = db.document('GOPBOTdata', 'GOPBOTservers','Name of the server')
doc_ref.set({
u'WelcomeServerChannel': 458611220187054080,
u'ServerPrefix': True
})

It gives:
ValueError: A document must have an even number of path elements

So I tried:
doc_ref = db.collection('GOPBOTdata', 'GOPBOTservers','Name of the server')
doc_ref.set({
u'WelcomeServerChannel': 458611220187054080,
u'ServerPrefix': True
})

Gives:
AttributeError: 'CollectionReference' object has no attribute 'set'

How to fix this?

[fringe tiger]

CollectionReference has add().
DocumentReference has set(), update() and delete().

[tropic grotto]

Awesome thanks

[tropic folio]

hi I have this question

1.5) Return the acnum of academic(s) who wrote the largest number of papers. You must NOT use MAX. An SQL query that lists all academics in decreasing order of their total number of papers is incorrect.

how to di find out the largest number wihtout using max?

this is in oracle sql developer

[ionic pecan]

what have you tried already?

[shut hornet]

Anyone got time to help me ?

Need some help with DML and ODBC

[humble trout]

for couchdb, is there an easy way to ipmort/export db from file?

so i can share the database on github with my collaborators

should i even be sharing the database on github?

[knotty parcel]

Traceback (most recent call last):
  File "main.py", line 16, in <module>
    mydb = client["test"]
TypeError: string indices must be integers

@ionic pecan Can you help us?

[ionic pecan]

the error tells you what you need to know - you're indexing a string with a string, can't do that

[knotty parcel]

So take out the ""

@ionic pecan

[ionic pecan]

if test is a variable pointing to an integer, that would work, yes

[knotty parcel]

@ionic pecan Im trying to create a new database in mongoDB

[ionic pecan]

okay

[knotty parcel]

Why isn't it working

[ionic pecan]

can you show your full code?

[knotty parcel]

client = os.environ.get("client")
print(client)
mydb = client['test']
mycol = mydb["customers"]
mydict = { "name": "John", "address": "Highway 37" }
x = mycol.insert_one(mydict)
myquery = { "address": "Highway 37" }

client = pymongo.MongoClient("mongodb+srv://######:###@ifuture-c5zf8.mongodb.net/test?retryWrites=true")

[ionic pecan]

i'll need your full code, i'm not sure how these two snippets go together

[knotty parcel]

Thats all the code I have that involves pymongo

[ionic pecan]

yes, but it's two snippets

from where is the second

[knotty parcel]

The bottom one is the environment

the .env file

[ionic pecan]

you defined the client in an environment variable?

[knotty parcel]

The environment is a file and I defined client in there

[ionic pecan]

that won't really work that way

you'll need to define the client in your python code

[knotty parcel]

Okay

Traceback (most recent call last):
  File "main.py", line 16, in <module>
    client = pymongo.MongoClient("mongodb+srv://####:##@ifuture-c5zf8.mongodb.net/test?retryWrites=true")
  File "/home/runner/.local/lib/python3.6/site-packages/pymongo/mongo_client.py", line 524, in __init__
    res = uri_parser.parse_uri(entity, port, warn=True)
  File "/home/runner/.local/lib/python3.6/site-packages/pymongo/uri_parser.py", line 318, in parse_uri
    raise ConfigurationError('The "dnspython" module must be '
pymongo.errors.ConfigurationError: The "dnspython" module must be installed to use mongodb+srv:// URIs

[ionic pecan]

i don't quite know what you're trying to tell me with the traceback

[knotty parcel]

So i put the client bit

Into the main code

thats what i get

[harsh pulsar]

any opinions on ORMs? thinking about trying out peewee

[copper echo]

soo i have flask app on pythonanywhere and i cant connect to database which is on my vps

error is connectionrefused

i changed postgresql.conf listen_adresses = '*'

but still

maybe someone knows how to solve it

i read something. and people are saying that i should use sshtunneling

https://www.pythonanywhere.com/forums/topic/1942/

but im still not sure

[ionic pecan]

that post is for hosting postgres on pythonanywhere

do you have a firewall? if so, you need to add a rule that allows connections to port 5432 from the pythonanywhere host

[copper echo]

i dont know if i have

im using ovh

2019-05-26 12:02:39,555: Error running WSGI application
2019-05-26 12:02:39,556: psycopg2.OperationalError: could not connect to server: Connection refused
2019-05-26 12:02:39,556: #011Is the server running on host "ip" and accepting
2019-05-26 12:02:39,556: #011TCP/IP connections on port 22?
2019-05-26 12:02:39,556: 
2019-05-26 12:02:39,557:   File "/var/www/stylus_pythonanywhere_com_wsgi.py", line 16, in <module>
2019-05-26 12:02:39,557:     from flask_app import app as application  # noqa
2019-05-26 12:02:39,557: 
2019-05-26 12:02:39,557:   File "/home/Stylus/mysite/flask_app.py", line 26, in <module>
2019-05-26 12:02:39,557:     sql = """UPDATE cats
2019-05-26 12:02:39,557: 
2019-05-26 12:02:39,558:   File "/home/Stylus/.local/lib/python3.6/site-packages/psycopg2/__init__.py", line 126, in connect
2019-05-26 12:02:39,558:     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)```

[copper echo]

2019-05-26 15:08:57,057: Error running WSGI application
2019-05-26 15:08:57,062: psycopg2.OperationalError: could not connect to server: Connection refused
2019-05-26 15:08:57,062: #011Is the server running on host "x" and accepting
2019-05-26 15:08:57,062: #011TCP/IP connections on port 5432?

still

[true token]

Hey does anyone know how to add music in pygame

[cerulean pendant]

with a #databases ?

[keen cedar]

Hey there :,)

I have doc_name, specialty_type and specialty.

I want to insert those values into a table doctors_specialties on a condition that a table doctors contains the value doc_name and that another table specialties contains the value specialty.

How would I do that in MySQL?

[pure scroll]

you do use foreign key constraints for that

https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

[minor blade]

"Autonomous Databases" - Is this buzzwords like Blockchain or legit?

[keen cedar]

Gotcha, I'll look into that, thanks

[harsh pulsar]

Are there any good articles on safely caching database queries in memory?

Eg I write to table1 a few times a day, but need to query it several hundred times a day, and the table could potentially get big so I don't want to query the database directly every time

I just want to make sure I don't introduce race conditions or end up with a bad cache and start returning them direct results

I'm not terribly experienced in this area of programming so I don't have a great sense of what to even look out for

[pure scroll]

How/where do you want it to cache?

Hundred times a day does not sound like a load to the database

as long as you have proper indexes defined, it should not be an issue

[harsh pulsar]

Thats a good point. Not sure if it being sqlite matters

Was thinking of just caching in memory in python... like it would append to self.active_contracts whenever a new contract is proposed (a few times a day), and it would read off that list instead of querying for active contracts every time

And it would remove a contract from the list whenever it goes inactive

It seems like simple logic but i assume there are considerations im not aware of

[pure scroll]

well, you will need to limit cache size for sure, to not face out of memory error.
In addition to that if you are going to cache things in memory, you will need to make sure that are related to one specific entity are always hitting the same server.

also you might need a mechanism of cache invalidation and so on. If that is just an exercise then for sure you could/should try it.

[harsh pulsar]

No this is for a light duty hobby system but other people will use it and I don't want it to be unreliable

Right now the database is SQLite and the size of objects in cache will be small

But I see

It might be more complicated than it's worth

[pure scroll]

Well, just keep it simple, if you will actually see that your database becomes a bottle neck

[harsh pulsar]

I think I will try to set up indexes first like you suggested

Yeah exactly

[pure scroll]

then I would start thinking on how-to solve the problem

[harsh pulsar]

Good TY

[torn sphinx]

anyone here

well bye

[echo turret]

I'm

[ionic pecan]

maybe wait a bit more than 4 seconds to get a response, lol.

[echo turret]

yh

[vestal mango]

I use PyQt5 for my project. I work on Sqlite3-based databases and I know that it includes QtSql, a database-related module, but I also know about Python's built-in sqlite3 module.
I kind of "accidentally" included methods from both modules in the project and that's because I would find an error or two when using one over the other in certain tasks. Both work fine for my very simple tasks and I don't want to edit them now, I just want some answers if possible

Are there solid reasons why should we prefer one over the other or what does each one miss that the other doesn't?

[pure cypress]

Qt SQL has integration with Qt, obviously

Like you could easily use a table as a provider for a model

I forget the correct term but hopefully you know what I mean with "provider"

The values need to be comma delimited

The values need to be separated by a comma

In your SQL

Yes

np

[wide jolt]

@pliant cedar please do not use the r-word in this server. Self-deprecating or otherwise.

Because it's derogatory, and thus inappropriate language to use here.

[bronze heath]

Any reason why the database is structured like this? https://github.com/ImagicTheCat/vRP/blob/master/vrp/base.lua#L20-L69

I don't see any advantage of using blob this case?

[torn sphinx]

i'm using db browser for sqlite

is there a way to execute sql and export the given table to csv?

[torn sphinx]

what are you querying.. do you just need the csv or do you need to maintain the database

[torn sphinx]

<@&267628507062992896> <@&267629731250176001> <@&295488872404484098>

[plain radish]

!ban 582962206849761299 Ping abuse

[delicate field]

:incoming_envelope: :ok_hand: permanently banned @late olive (Ping abuse).

[acoustic pewter]

thanks :D

[pseudo pollen]

Wtf is happening

[timber stratus]

@plain radish I think these might be selfbots

[plain radish]

probs

doesn't matter much, still gets treated the same

[timber stratus]

It happened to me on another server

[wary merlin]

is safe to use accent for table name ?

[void otter]

what do you mean by accent

something like čaša or?

@wary merlin

[wary merlin]

yeah, that for example

éèñ, ect

[void otter]

don't do that

some systems won't detect that as a letter thingy

use english words

that's my advice

[wary merlin]

ok, also space in name is a bad idea too ?

[void otter]

use underscores

odd_numbers

[wary merlin]

ok

thanks for advises

[void otter]

👍

[pliant oxide]

please help, how to write SELECT and assigment in MySQL

SELECT country_id = countries.id
FROM countries
WHERE codeName LIKE country_code;

this doesn't work

[echo turret]

just do

SELECT country_id as countries.id FROM countries WHERE codeName LIKE country_code;

or isn't this what you mean?

@pliant oxide

[pliant oxide]

error

[echo turret]

use IN

instead of like

[pliant oxide]

            SELECT country_id = countries.id
            FROM countries
            WHERE codeName IN country_code;

this doesn't work

[echo turret]

why the country_id = countries.id ?

[pliant oxide]

I want to find and assign country_id

I don't know how to do it properly

and this simple operation like "Find and store" is too difficult for me ;_;

[echo turret]

SELECT country_id AS countries.id FROM countries WHERE codeName in country_code;

[pliant oxide]

errors

[echo turret]

the . has to be a _

and you have to say something after end IF or change it to END

[pliant oxide]

in what line?

[echo turret]

36

[pliant oxide]

but I mean underscore

[echo turret]

27

the .

[pliant oxide]

countries_id? it doesn't make sense

[echo turret]

so use something else

it cant have a dot

[pliant oxide]

don't get it, but how SELECT know that I need to store id?

[echo turret]

what do you mean

you select something then you can store it later as something or insert it into a different table

[pliant oxide]

I select only id, not entire row

[echo turret]

can you paste the entire code

[pliant oxide]

CREATE DEFINER=`root`@`%` PROCEDURE `add_airport`(airport_code VARCHAR(12), airport_name VARCHAR(128),
    city_code VARCHAR(12), city_name VARCHAR(128),
    country_code VARCHAR(12), country_name VARCHAR(128))
BEGIN
    DECLARE city_id INT;
    DECLARE country_id INT;
    
    SELECT city_id = cities.id
    FROM cities
    WHERE codeName LIKE city_code;
    
    IF city_id > 0 THEN
        SELECT 'First IF';
        INSERT INTO airports VALUES (NULL, city_id, airport_code, airport_name);
    ELSE
        SELECT 'SECONF IF';
        SELECT country_id = countries.id
        FROM countries
        WHERE codeName LIKE country_code;
        
        IF country_id > 0 THEN
            SELECT '1 IF';
            INSERT INTO cities VALUES (NULL, country_id, city_code, city_name);
        ELSE
            SELECT concat('2 IF', country_code);
            INSERT INTO countries VALUES (NULL, country_code, country_name);
            SELECT country_id = countries.id
            FROM countries
            WHERE codeName LIKE country_code;
            INSERT INTO cities VALUES (NULL, country_id, city_code, city_name);
        END IF;
        SELECT city_id = cities.id
        FROM cities
        WHERE codeName LIKE city_code;
        INSERT INTO airports VALUES (NULL, city_id, airport_code, airport_name);
    END IF;
    
END

I'm working in workbench MySQL

[echo turret]

the last END IF; is wrong just do END

[pliant oxide]

I got error if I write "END" instead of "END IF"

There are 2 IF statements and we got two "END IF" what is wrong? :/

[echo turret]

nothing there

fck sry

it's late

[pliant oxide]

SET @something = NULL;
INSERT INTO eskycrawler_dev.countries VALUES (NULL, "PL", "Poland");
SELECT @something = countries.id
FROM countries
WHERE codeName LIKE 'PL';
SELECT @something;

This piece of code even doesn't work

it's simplified version

something is still NULL

this works

SET @something = NULL;
-- INSERT INTO eskycrawler_dev.countries VALUES (NULL, "PL", "Poland");
SELECT @something := countries.id
FROM countries
WHERE codeName LIKE 'PL';

wtf

[echo turret]

SET @something = NULL;
INSERT INTO eskycrawler_dev.countries VALUES (NULL, "PL", "Poland");
SELECT countries.id AS @something
FROM countries
WHERE codeName LIKE 'PL';

try this

[pliant oxide]

this doesn't work

what is the difference between @value and value

uhh

[pliant oxide]

got solution

and know maybe why this works

if you want to know, I can write if you reply ;p

[harsh pulsar]

@something is like storing a variable

[sinful stratus]

https://i.imgur.com/R62DBI5.png

what is wrong here?

ALTER TABLE guilds_config ADD COLUMN prefix NOT NULL DEFAULT '!';

im gonna go crazy

where is the syntax error

help

wait

I forgot to specify the type of the column

im dumb, ignore me. thank you.

[dull scarab]

About how far does a mongodb scale before lacking in performance?

[wary merlin]

there is a way to get a dict or a tuple of (column, value) ?

[dull scarab]

From what?

[wary merlin]

from a sqlite3 database, select + cursor.fetchone

[dull scarab]

sqlite doesn't support storing datamodels afaik

as in it should be seperate columns

[wary merlin]

well, mb I missread the doc https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory

[dull scarab]

Well, it doesnt support it natively at least.

That is monkeypatching in a middleware to format the rows returned

[wary merlin]

better than nothing 🤷

[dull scarab]

Id imagine at least, i havent used it myself 😬

[wary merlin]

it work fine

and handler the case of there is no row

well, better than make a dict by myself

[ember bolt]

would anyone be able to help me set up a postgresql database on my linux server, Ive had a look at tutorials online but I just dk how to do it without the interface (im using ubuntu terminal)

[pure cypress]

If you have a more specific question about it in mind feel free to ask

here or in #unix if more appropriate

[ember bolt]

I just need help setting it up so I can use it as a leveling system for my bot. I just dk how to set up the database on linux because I cant see the interface you have on windows

[pure cypress]

Well the tutorials online should guide you through it

Why are you finding the tutorials to be inadequate?

[ember bolt]

Ive had a look but all of them were for the unbuntu you have a desktop on

I only have a terminal, which is why they dont work for me

[pure cypress]

I am just glancing at the tutorials. I see no reason why they wouldn't apply to your situation

They're all terminal commands

[ember bolt]

because the tutorials go through a desktop and app

[pure cypress]

I went here and clicked on a few tutorials

[ember bolt]

would u be able to link me some tutorials please ?

[pure cypress]

https://wiki.postgresql.org/wiki/Detailed_installation_guides

Yeah check those out

[ember bolt]

thanks :)

https://i.imgur.com/A1vUomK.png Now I cant click off of this , anyhelp ?

[pure cypress]

press enter?

type ok?

[ember bolt]

I did

[pure cypress]

scroll to the bottom and see what's there?

[ember bolt]

I exited full screen and its froze on this https://i.imgur.com/XgZvDR5.png

been froze like it for 5 minutes, tried restarting my server. Same result

[pure cypress]

Was that license coming from that package?

[ember bolt]

yes

[pure cypress]

So did you properly accept the license or what?

[ember bolt]

I had no way to accept the license , it went when I exited fullscreen

[pure cypress]

Did you try scrolling to the bottom?

Like with down arrow

[ember bolt]

theres nothing there

https://i.imgur.com/KP9UmWg.gifv

[pure cypress]

No I meant in the license thing

I honestly don't know cause I've never seen that sort of thing before

[ember bolt]

its not there anymore

it maybe the install im using

lemme restart my server and try again

[pure cypress]

Can you link the guide you're following?

Also are you on debian?

[ember bolt]

https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04

I think ik why, its for ubuntu 16-04

[pure cypress]

ubuntu then?

[ember bolt]

im on 18

no im on ubuntu

[pure cypress]

Right at the top it shows a guide for 18.04

Here's the link then https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04

[ember bolt]

thanks

https://i.imgur.com/5rtDpLj.png seems a bit better, its showing a progress bar

it seems to be freezing at a certain point, this point being 17%

[pure cypress]

Ok I found an answer for you https://askubuntu.com/a/16228/589656

I'm surprised I haven't encountered this myself

[ember bolt]

thank you so much

now its finally installed :D

[ember bolt]

how do I keep my database running ?

[pure cypress]

Using systemd to make it a service is probably the best way

[ember bolt]

does this look all good though ? https://i.imgur.com/VbPhh4u.png and Okay , ill read into it

[pure cypress]

Though I thought postgresql was already a service

[ember bolt]

Oh it may be, Its just on windows u have run it by default

[pure cypress]

https://www.postgresql.org/docs/current/server-start.html

Yeah that describes how to make it a service

[ember bolt]

https://i.imgur.com/vZ2JK9M.png is this what its meant to do ?

[pure cypress]

yes

But that's not making a service

[ember bolt]

thats what it said to do on the website

[pure cypress]

That's a basic way of doing it

[ember bolt]

oh

[pure cypress]

read below and it shows how to make a service

I am pretty sure the command you used wont work out cause it will kill the process once you terminate the ssh connection

[ember bolt]

I dont see any other thing talking about service on that tutorial

[pure cypress]

"When using systemd, you can use the following service unit file (e.g., at /etc/systemd/system/postgresql.service):"

Once you've got that going you can do sudo systemctl --now enable postgresql

[ember bolt]

https://i.imgur.com/ufz5xyN.png any reason I may have permissions denied when I have root access?

[pure cypress]

You're currently not the root user, you're the postegres user

You should be using sudo anyway to run your commands

[ember bolt]

https://i.imgur.com/XxjKl2w.png tried using sudo but this came up

[pure cypress]

Type exit so you leave the postegres user

I have a feeling you didn't even bother to create another user and just have root, which is bad...

[ember bolt]

I got told by my friend to just stay on root

[pure cypress]

No, while it may seem convenient, it's a security issue

[ember bolt]

o , Ill make a new account when I get all of this finished

https://i.imgur.com/qDcw1FY.png but this happened

[pure cypress]

You don't need to even run that command

Just make the service

All you need to do is create the service unit file in the directory they describe and then paste the contents below into the file

[ember bolt]

I used the service unit file but it said there was no directory

[pure cypress]

Do sudo touch /etc/systemd/system/postgresql.service

well I guess you don't need sudo since you're root...

then open the file in an editor and add the contents

[ember bolt]

I dont have an editor on terminal though ?

[pure cypress]

You likely do. nano, vim, vi one of those

But hold on

[ember bolt]

okay

[pure cypress]

Can you check if this file already exists? /usr/lib/systemd/system/postgresql.service

[ember bolt]

okay

[pure cypress]

I noticed that I already had it on my distro (Arch Linux)

[ember bolt]

no it does not

[pure cypress]

Ok so you do need to make it yourself I guess

continue with what I was saying before

[ember bolt]

okay

[pure cypress]

So you could try nano /etc/systemd/system/postgresql.service to open it in an editor

[ember bolt]

okay its opened in the editor

https://i.imgur.com/daf3KUW.png

and ive copied all of the stuff from the docs into it

https://i.imgur.com/gQhezGD.png

[pure cypress]

Now just save and exit

(it's ctrl + o to save and ctrl + x to exit)

[ember bolt]

https://i.imgur.com/kWCFZN9.png ive got this

when trying to save

[pure cypress]

Yeah just press enter to confirm that filename

[ember bolt]

okay done

[pure cypress]

Now do systemctl --now enable postgresql and it should start up

To confirm that it's working, you can check systemctl status postgresql

[ember bolt]

https://i.imgur.com/R8RDWBf.png is this all good?

[pure cypress]

yup 👍

[ember bolt]

thanks so much , im sorry im so slow. Im just not adapted to linux and how that all works yet

Now I gotta see if the database works with my bot x)

[pure cypress]

That's ok, you're getting the hang of it

[ember bolt]

so is it just running in the background now?

[pure cypress]

Yes

And it will automatically start whenever you restart the server

[ember bolt]

okay , lemme try it with my bot

oh damn

thats fancy , thanks for walking me through it<3

[pure cypress]

np

IMO I feel like the guide should have shown you how to do this 🤷

But they didn't bother I guess

[ember bolt]

tbh , sometimes I get lost in all of what its saying and find it hard to pick out whats relevant . Which is why ,in a second attempt, I came here for help to see if someone could guide me through it simper . Which they did

[frigid goblet]

I'm trying to connect to a mysql db with an ssh tunnel in a python flask app, I can connect with my desktop client (mysql workbench) with no issue, I'm however getting "Host 'my ip' is not allowed to connect to this MySQL server"), any clue?

app.config['SQLALCHEMY_BINDS'] = {'two' : 'mysql://{}:{}@{}/{}'.format(db_user, db_pw, host, db)}

tunnel = sshtunnel.SSHTunnelForwarder(
(host, 22), ssh_username=ssh_user, ssh_password=ssh_pw,
remote_bind_address=(localhost, 3306)
)
tunnel.start()

[heady nova]

Can someone recommend a beginner guide for python and MySQL? (English or German)

[torn sphinx]

I just learned python yesterday from this https://docs.python.org/3/tutorial/index.html

[celest plank]

Just seen there is this channel so posting in here too.

I have a question on modelling data (I suppose it's not specific to python, but doing this in django). I always get confused with related models. How do I model a related table that can have only one at a time, but want to keep a historical record. Eg a user can have one address (not supporting multiple properties) but can have previous addresses in the past that we want to look up. What's the best way to model this?

[unreal tartan]

You'll want to have unique keys not just on the unique column, but unique column + column tracking deleted records in a unique way (often the timestamp of deletion).

What I mean with this is

table: Users
columns: id INT

table addresses
columns id INT, user_id INT (FK), deleted_date
Unique key: (user_id + deleted_date)

Now the last thing to realise is that often, depending on your database, a NULL value in 2 different rows won't be seen as equal. so

addresses:
id | user_id | deleted_date
1  |  1    | NULL
2  |  1    | NULL

Would actually be valid for a lot of databases, even with a unique key over user_id + deleted_date.

So - depending on the database - you want to set a default value so it'll become

addresses:
id | user_id | deleted_date
1  |  1    | 0
2  |  1    | 0

Which the database would complain about.

To google for more, these " historical records" records you want, aka, not really deleting thigns is called "soft deletes".
database unique soft deletes should give you more articles with info.

[celest plank]

Thanks, that's a lot to get my head around. I'll google for a bit more detail as you suggested. (I should have mentioned I'm using django and postgres)

[unreal tartan]

Postgres indeed does not see 2 NULL values as equal.

[celest plank]

Hmmm debating if I could just do a foreignkey to address and have the address have something like an occupied_from and left_on date (or a current flag) and that might be an easier implementation as typically users will only have a few prior addresses

[patent glen]

How frequent is deletion vs changes?

if you're mainly doing changes, an "effective date" may be more efficient to query than "deleted date", though you'd need dummy records to represent being really deleted

@celest plank

since you can just find the row corresponding to the max value (or the max value less than a historical date being queried)

oh i see you came up with occupied_from, that's basically the same thing

[torn sphinx]

Is there a type of local json database?

one that doesn't store in the cloud or anything

sorta like mongo

[void otter]

can you run mongo locally?

like in separate container?

[gleaming frost]

Yes

Well. you can have mongo in a docker container

[void otter]

there you go lepto

[gleaming frost]

And just hook all the other containers to it

Mongo is good

[dawn pulsar]

I'm using a MariaDB and I print out the variable, but it formats it weirdly, does anyone know why?

[void otter]

what do you mean weirdly

[dawn pulsar]

print message: ('RedstoneDaedalus', 'Deathrohk', 'NoahLikesPi', 'lukecashwell')


discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''(\'RedstoneDaedalus\', \'Deathrohk\', \'NoahLikesPi\', \'lukecashwell\', \'1All' at line 1

@void otter

Why the double '' and \s

[void otter]

try updating your packages

[dawn pulsar]

what do you mean? I'm using mySql-connector, update that?

[void otter]

yes

are you writing raw sql commands?

[dawn pulsar]

Sorry, just went, I did it differently, I just selected everything and did an "if", probably not the best, but there's only like 50-100 records, so it's not that bad

[void otter]

does it work?

[kindred cargo]

anyone here familiar with mongodb? if so, what's the maximum size for each document?

[torn sphinx]

import mysql.connector


mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="root",
    database="mysql",
    auth_plugin="mysql_native_password"
)

print(mydb)```

mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

[ionic pecan]

is that a question?

[dense basin]

CREATE TABLE restaurant (id int, name text);
CREATE TABLE menu (id int, name text, restaurant_id int, foreign key (restaurant_id) references restaurant(id));
CREATE TABLE menu_item (id int, name text, menu_id int, foreign key (menu_id) references menu(id));

restaurant
-----------------
0|Limoncello

menu
-----------------------
0|limoncello menu|0

menu_item
----------------------
0|steak|0
1|chicken|0

@stone jacinth

seperate tables are more intuitive to a person, yeah. the issue is that if you want to add a new menu, you have to change the entire database, and apply a migration

[stone jacinth]

An interesting

[dense basin]

with a database, you want to group each "type" of item in its own table, whether they're 100% related or not, just so long as they have the same structure

so you have a table for restaurants, a table for menus, with each menu referencing the restaurant it belongs to, and a table for menu_items, with each menu referencing the menu it belongs to.

individual tables might seem intuitive, but then you're doing the db's work manually

[stone jacinth]

could you just have your menu_item table reference the restaurant?

what's the benefit of the menu table?

[dense basin]

most restaurants have multiple menus

[stone jacinth]

To allow multiple menus?

ah got it

[dense basin]

lunch, dinner, specials, wine, etc

but if each only has one menu, then yeah, cut out the menu table :P

[stone jacinth]

hmm

Okay thanks!

[dense basin]

no problem!

[torn sphinx]

how do you see what columns you have

I tried to do select * but number of columns was too much apparently..

[pure scroll]

depends on the database

usually there is describe table command on SQL databases

[torn sphinx]

lemme see if that works here

[harsh pulsar]

what database engine are you using

[carmine heart]

@lusty sandal In a relational database, you normally only want to store one "atomic" value per attribute/field of your database table

Otherwise, it will be difficult to filter the items by category, it's difficult to ensure the integrity of the data in the list, and so on

A way around that would be to create three tables:

• One with the items in question
• One with the possible categories
• One table that relates items to categories

If you have a fixed number of categories, then you could use something like a Boolean column for each category instead and go with just one (or two) tables.

[pure scroll]

there are arrays in certain SQL databases

[nova hawk]

sqlite doesn't

[pure scroll]

if category is actually just a string, and no other data you can just make use of arrays

[carmine heart]

That is true, so it depends on your specific database engine and your needs

However, they were talking of creating a comma-separated string manually in the help channel, which is probably not a good idea. So, if you have a db engine that supports arrays and those suit your needs, that's another option as well

[cerulean pendant]

@lusty sandal you can use a separate table with a foreign key for the categories

it will make easier to search which objects are in a certain category, really fast

[lusty sandal]

Thanks for that. but actually each record will have multiple categories. how would that work? 😐 I'm not savvy with sql :/

[pure scroll]

It's called many to many relation. Just Google for it ;)

[lusty sandal]

I've been trying to google every possible thing but I just can't seem to get where I want with any way other than having as value "category1,category2,category3"

[subtle galleon]

I'm having a bit of a blank right now. I'm wanting to create a table, that has a one input for each category type, but then has multiple inputs for a final category. I'd like to make another table with these inputs because they have their own attributes as well... but I forget, do I use foreign keys for this? I will attempt to draw a diagram that makes more sense.

Like this, with banks for example.

[pure cypress]

A foreign key is for referencing records that are in another table

If that's what you wanted to do, then use a foreign key

[slender sapphire]

Good evening folks. I'm trying to use pyodbc to connect to Oracle servers at work and it isn't letting me. I have spoken to a coworker and he said it might be because the server's version is too old to work with pyodbc. Does that make sense and what else should I try instead?

[pure cypress]

Why are you doing it through ODBC?

Have you tried cx_Oracle?

[slender sapphire]

I have tried it but it also gives me errors when trying to connect.

And pyodbc works with the netezza servers.

[pure cypress]

Do you know which version of the db you're using?

I don't know about pyodbc, but cx_Oracle works with 11.2+

Actually, may be as far back as 9.2

[slender sapphire]

idk how to check

But MS Access uses them just fine.

[pure cypress]

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/getting-started-with-database-administration.html#GUID-585D0D25-B834-42D9-BF19-74DA98456B3C

[lusty sandal]

using sqlite, can I limit the number of different results and not limit the total results? an example of what I mean while limiting 3:

num15
num15
num15
num13
num13
num20

to show all available num15,num13,num20

[echo turret]

if you're using python you can append the num3 once and the rest as many times as you want and than add the list as a value to a dict with for example key Num. and then use a pandas dataframe to make it into rows and columns

so

if num3 not in List1:
    List1.append(num3)
else:
    pass

that would work I think, I've used it before on my data

@lusty sandal

[sweet nebula]

anyone got any tips for making databases. I've started learning it at school but a lot of the time i cant tell whether tables should have one to many or many to one links etc. Also i struggle with ensuring my databases are in 3rd normal form (Thats as high as we're told to go). Thanks 😃

[craggy coyote]

hey querying optimization question. I have a query that does an outer join with the sole purpose of ordering by the joined table.create_date. Finding the outerjoin to be expensive, any way of avoiding this?

[slender sapphire]

@pure cypress BTW I fixed my problem.

[proper copper]

Anyone have any resources on best practices when it comes to cloud managed database solutions (e.g. AWS RDS, Google Cloud SQL)? Having a hard time figuring how if I should do my own backups outside of their prebuilt ones which only have PITR for 7 days

[gilded viper]

how do i send a sqlite server within a python response

[kindred cargo]

mongodb atlas, how do i update items in an object without replacing the whole object ? ```dataUpdate = {
'Currencies':{
'Wood':woodData
},

                'Profile':{
                    'Experience':expData
                }
            }
            self.records.update_one({'userID':str(ctx.author.id)}, {'$set':dataUpdate})``` this is currently replacing my whole object

[feral falcon]

Hi. I am creating a network of GoT character. I have it all set up, but i am trying to display names instead of nodes. I nailed the names, now i am trying to display names in a way, so that their size depends on the character connection. I hope i was clear what the problem is. Can anyone help?

def network():
    
    G=nx.Graph()
    for i in range(len(intermatrix)):  # ustvari vozle
        G.add_node(i)
    
    for i in range(len(intermatrix)): # doda povezave
        for j in range(i+1,len(intermatrix),1):
            if e[i,j]>0.0:
                G.add_edge(i,j)
    
    
    labels0 = [line.rstrip('\n') for line in open('characters.txt')] # uvozi imena
    labels={}
    for lll in range(len(labels0)):
        labels[lll]=labels0[lll]
    
    
    #lege=nx.spring_layout(G,scale=100,k=0.2)
    lege=nx.fruchterman_reingold_layout(G,k=2.0/np.sqrt(len(intermatrix)))
    
    nx.draw(G, lege, node_size=0, node_color='blue', alpha=0.9, linewidths=0.3, edge_color='grey', width = 0.4) 
    nx.draw_networkx_labels(G,lege,labels,font_size=4,font_color='k')  
    plt.gcf().text(0.06, 0.99, 'Character network', fontsize=9)
    plt.axis('off')
    '''plt.xlim(-0.15,1.15)
    plt.ylim(-0.15,1.15)'''
    filename='character_network.png'
    plt.savefig(filename,dpi=200,bbox_inches="tight")
    plt.show()
    plt.close()```

[broken cloud]

Hey

can any1 here help me related to sqlite3 or aiosqlite?

#help-coconut

[indigo mason]

Maybe that's a question for #databases?

And if someone's going to help you they will

Just be patient

I'm sure you'll get the help you need

[graceful nimbus]

So I'm kind of getting mindfucked. I'm using sqlite3, and I'm trying to search for a var (which has assigned "discord" for example) in a column called PLATFORM in a table called DATA. And I can't seem the figure out the correct syntax to .execute it

[graceful nimbus]

I was thinking something like this:
transfer.execute("SELECT * FROM Data where PLATFORM = "+var+"")

But it gives this error: sqlite3.OperationalError: no such column: discord

var = "discord"

[coral panther]

Try:
transfer.execute("SELECT * FROM Data WHERE PLATFORM = '{}';".format(var))

Strings typically need single quotes around them to execute properly.

I havent used sqlite3 though, that's how I would do it in MySQL

[slate spire]

you shouldn't be using .format or + for sql queries, as that might open you up to injection vulns

[coral panther]

That's true, if the variable is defined by a user.

[cerulean pendant]

@graceful nimbus transfer.execute("SELECT * FROM Data where PLATFORM = ?", (var,))

using parameterization has other advantages, not just sql injection prevention - it frees you from escape/quote hell, and can be optmized at query compliation level by the database @coral panther

[subtle galleon]

Is there a way to alter a column name in sqlite3?

[cerulean pendant]

ALTER TABLE I think

[subtle galleon]

Do you happen to know what specifically goes after the table name?

[cerulean pendant]

from the top of my head, no

[subtle galleon]

I accidentally created an ambigious column name and now it won't accept it in my joined tables.

[cerulean pendant]

but it should be a google query away

you can prefix the column name with the source table name to resolve ambiguities in joins

ALTER TABLE mytable RENAME COLUMN badname TO goodname

https://www.sqlite.org/lang_altertable.html

SELECT table1.foo, table2.foo FROM table1 JOIN table2 ON table1.bar = table2.baz

[subtle galleon]

Alright, thanks. I had found stuff for other SQL types but not sqlite.

Yikes. Still throwing an error.

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: near "COLUMN": syntax error

I would sincerely hate to have to drop and remake this; it has 40000 some entries in it.

[cerulean pendant]

show your join @subtle galleon

[subtle galleon]

I ended up dropping it. But I still have the join saved.

c.execute("SELECT sum(endos), regions.region_name FROM nations INNER JOIN regions ON regions.regionID = regionID WHERE regionID=? AND (status=? OR status=?)", (id,"WA Member", "WA Delegate"))

[cerulean pendant]

FROM nations INNER JOIN regions ON regions.regionID = nations.regionID

that is all you need

just prefix the ambiguous column with the name of the table

[subtle galleon]

I did do that as well, but it was giving me operation errors still.

I can show a screenshot... if I can find it.

It's above 40000 some cursor objects.

[cerulean pendant]

by the way, you need sqlite version 3.25, and the version included with python is older than that; you'd need to run that query in the standalone sqlite3 client (outside python)

(the rename column one)

[subtle galleon]

...oh. That's why.

Yeah, it's fairly impossible to find in the sea of cursor objects.

[cerulean pendant]

well, you don't have to rename columns to make joins, that would be hell

I have lots of tables with the same column and those are just the columns that I need to join

[subtle galleon]

Not sure why it kept giving me syntax errors then.

Yesterday it worked fine when I had two different names.

[cerulean pendant]

otherwise I would run out of nice names fast

[subtle galleon]

On the topic of sqlite3, how do I limit my query to the top 100 results, for future reference?

I tried SELECT TOP 100 (columnnames) FROM table but it didn't work.

[serene thicket]

Hey all, I am having an issue with a certain query i am running within my python script. For some brief context, my script pulls a bunch of data via an api, does some manipulation and then inserts it into a MSSQL Server database.

The issue i am having is with the following query when ever there is a name (first or last) that contains a single '
Query:sql MERGE looker.looker_scratch.namsortemp AS target USING (SELECT '{firstName}' AS firstName ,'{lastName}' AS lastName ,'{likelygender}' AS likelygender ,'{countryoforigin}' AS countryoforigin ,'{regionoforigin}' AS regionoforigin) AS source (firstName, lastName, likelygender, countryoforigin, regionoforigin) ON target.firstname = '{firstName}' and target.lastname = '{lastName}' WHEN NOT MATCHED BY TARGET THEN INSERT (firstName, lastName, likelygender, countryoforigin, regionoforigin) VALUES (?, ?, ?, ?, ?) WHEN MATCHED THEN UPDATE SET target.firstname = '{firstName}' ,target.lastname = '{lastName}' ,target.likelygender = '{likelygender}' ,target.countryoforigin = '{countryoforigin}' ,target.regionoforigin = '{regionoforigin}';

i understand the issue, given that when a name comes in with a ' it obviously confuses python

how can i alter the query above, or potentially a different spot in my code, to cater for this?

these are the variables that are being piped into the above query py firstName = genderdata['firstName'] lastName = genderdata['lastName'] likelygender = genderdata['likelyGender'] countryoforigin = origindata['countryOrigin'] regionoforigin = origindata['regionOrigin']

[pure scroll]

how do you execute this query

and how do you insert values to it?

[serene thicket]

here is my code @pure scroll

https://mystb.in/lipecawuya.py

it is very messy still as i just started putting it together, and i am by no means a python expert, so im sure there are plenty of other things that could be better

[pure scroll]

your problem is that you are mixing passing arguments and injecting them directly

most of the times you don';t want to inject parameters into the query string directly since that may open the doors to sql injection

I'm not really sure what your mysql python driver supports

but by the standart there should be a way to insert parameter by names
https://www.python.org/dev/peps/pep-0249/#paramstyle

that should do the job for you if you will replace all your f-string formatting with it

[serene thicket]

im not using mysql, im using MSSQL Server, not sure if that makes a difference

[pure scroll]

yeah it does not 😃

[serene thicket]

coolies

[pure scroll]

it's all about the library that you use

[serene thicket]

ill take a look at the article you showed me

[pure scroll]

it might that that it does not follow this standart so it has it's own way of inserting values into the template

[serene thicket]

ahh ok, well as far as im aware, the library im using for my database connection is the stock standard / most common one to use in my case, so hopefully it does

ill take a look and give it a go, appreciate it!

@pure scroll I assume this is me lacking some understanding here, but I would assume based off what i am already doing, and what it says in the document, that i would want to change those variables to ? same as i have for values, but then i obviously end up getting an error where i am now passing 17 values instead of 5

what am i missing here? I feel like its something painfully obvious

[pure scroll]

well it might accept named params, so you are able to pass them not by position by using ? but by name using :

if not then yeah, you would have to pass it more than just 5 times

[serene thicket]

makes sense, will give it a go thanks

[hazy crystal]

Hello guys

can someone help me with sqlalchemy 😄

I'm trying to do something with association_proxy

This is the code in question

    friends = association_proxy('a_friend', 'friend_b',
                                  creator=lambda u: Friendship(friend_b=u))

Right now I can get friends back if its on the first col (friend_b). The problem rises that if it is on the second fol (friend_a) I don't get any result back

How do I go about doing a double check there without setting a different association_proxy

[unreal tartan]

What's your model like?
Just this?
Friend 1..* ----- 0..* Friendship 0..* ----- 1..* Friend

[hazy crystal]

class Friendship(db.Model):
    __tablename__ = "friendship"

    id = db.Column('id', db.Integer(), primary_key=True, nullable=False)
    chatKey = db.Column('key', db.String(128), nullable=False)
    friend_a_id = db.Column(db.ForeignKey('user.id'))
    friend_a = db.relationship('User',
                            primaryjoin=friend_a_id == User.id,
                            back_populates='a_friend')

    friend_b_id = db.Column(db.ForeignKey('user.id'))
    friend_b = db.relationship('User',
                            primaryjoin=friend_b_id == User.id,
                            back_populates='b_friend')

    messages = db.relationship('Message', backref='friendship_chat')

The table in question

I mean friendship table

this is user table

class User(db.Model, UserMixin):
    __tablename__ = "user"

    id = db.Column('id', db.Integer(), primary_key=True, nullable=False)
    username = db.Column('username', db.String(128), nullable=False)
    password = db.Column('password', db.String(128), nullable=False)
    messages = db.relationship('Message', backref='user')

    a_friend = db.relationship('Friendship',
                            primaryjoin=lambda: User.id == Friendship.friend_a_id,
                            back_populates='friend_a')

    friends = association_proxy('a_friend', 'friend_b',
                                  creator=lambda u: Friendship(friend_b=u))


    rooms = association_proxy('a_friend', 'chatKey',
                                  creator=lambda u: Friendship(friend_b=u))

    b_friend = db.relationship('Friendship',
                            primaryjoin=lambda: User.id == Friendship.friend_b_id,
                            back_populates='friend_b')

I'm doing a chat app and its using self many to many relationship

a user will have many users as friends

and it should refer its class to get the users

one way works but if the user is found on the second col it doesn't get the friends.

I can fix it with another association_proxy col but I would like it to be on the same col as friends

[unreal tartan]

ah yea I understand the issue now.

[hazy crystal]

There are many way to do this but sqlalchemy docs is so confusing

I have yet to understand what association_proxy is doing

[unreal tartan]

I don't think it applies in this case per se.

[hazy crystal]

This code was implemented from a reddit post. I just changed a few variables to fix it (since it dind't work)

I have knowledge about sqlalchemy though

[unreal tartan]

What you want is.. I guess mostly called a "self referencing many-to-many"

[hazy crystal]

yeah thats how its called

I have googled a bit about that but no luck so far in implementing it properly. Most solutions didn't work for me and this one causes this issue

[hazy crystal]

Found another way to make the ralationship 😄

[unreal tartan]

How did you do it?
Cause I actually dont see a way to do this without having 2 friendship relations if both friends want to see each other as friend.

[hazy crystal]

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String(255))

    friends = relationship('User',
                           secondary=friendship,
                           primaryjoin=id==friendship.c.user_id,
                           secondaryjoin=id==friendship.c.friend_id)

    def befriend(self, friend):
        if friend not in self.friends:
            self.friends.append(friend)
            friend.friends.append(self)

    def unfriend(self, friend):
        if friend in self.friends:
            self.friends.remove(friend)
            friend.friends.remove(self)

    def __repr__(self):
        return '<User(name=|%s|)>' % self.name

friendship = Table(
    'friendships', Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id'), index=True),
    Column('friend_id', Integer, ForeignKey('users.id')),
    UniqueConstraint('user_id', 'friend_id', name='unique_friendships'))

much simplier

[unreal tartan]

okay, so that essentially makes 2 friendships per relation between 2 users as I thought.

[hazy crystal]

Now i just need a way to convert that Table into a class xD

[graceful nimbus]

@cerulean pendant That worked, thank you.

[unreal tartan]

The association proxy could still be useful if you wish to get your friends often without caring about the friendship properties.

class User(Base):
    __tablename__ = "users"

    id = Column('id', Integer(), primary_key=True, nullable=False)
    username = Column('username', String(128), nullable=False)
    password = Column('password', String(128), nullable=False)
    # messages = relationship('Message', backref='user')

    friendships = relationship('Friendship',
                               primaryjoin=lambda: User.id == Friendship.friend_a_id,
                               back_populates='friend_a')
    friends = association_proxy('friendships', 'friend_b',
                                creator=lambda friend: Friendship(friend_b=friend, chatKey=''))

    def befriend(self, friend):
        if friend not in self.friends:
            self.friends.append(friend)
            friend.friends.append(self)

    # Without association proxy
    def befriend2(self, friend):
        if friend not in [friendship.friend_b
                          for friendship in self.friendships]:
            self.friendships.append(Friendship(friend_a=self, friend_b=friend))
            friend.friendships.append(Friendship(friend_a=friend,
                                                 friend_b=self))

    def unfriend(self, friend):
        if friend in self.friends:
            self.friends.remove(friend)
            friend.friends.remove(self)

class Friendship(Base):
    __tablename__ = "friendships"

    id = Column('id', Integer(), primary_key=True, nullable=False)
    chatKey = Column('key', String(128), nullable=False)
    friend_a_id = Column(ForeignKey('users.id'))
    friend_a = relationship('User',
                            primaryjoin=friend_a_id == User.id,
                            back_populates='friendships')

    friend_b_id = Column(ForeignKey('users.id'))
    friend_b = relationship('User',
                            primaryjoin=friend_b_id == User.id)

    # messages = relationship('Message', backref='friendship_chat')

befriend2 is what you'd need, kinda, without association proxy.

and then (with association proxy) user.friends will be a list of User objects. user.friendships will be a list of the friendship objects

@hazy crystal

[hazy crystal]

Wouldn't that run into the issue of Userb not getting friends if it was initiated from UserA. Bcz you are doing one join in this case

[unreal tartan]

the befriend methods will set the friendship in the other direction.

So if userA befriends userB, userB will also get A added to the friends

[hazy crystal]

I will check this.

Thanks

[ivory knot]

how do i convert a column of epoch time to timestamps in a pd datadrame

dataframe*

[mystic hedge]

HI All! I'm working with pymysql to update a record in a MySQL database, and the variables are correct, but this runs without an error, but it doesn't update the row

cursor.execute("UPDATE places SET `longitude`=%s AND `latitude`=%s WHERE `id`=%s" , (long, lat, theID))

Any ideas?

Thanks, I'm a bit of a noob at Python, I confess

[ionic pecan]

@mystic hedge do you commit the transaction?

[mystic hedge]

You mean cursor.commit() ?

I saw that somewhere and I thought about it.. but no to answer your question, no

1 sec

[ionic pecan]

I think .commit() is a method on the connection, not cursor

[mystic hedge]

OH.. cause I just got an error: AttributeError: 'DictCursor' object has no attribute 'commit'

So, I just used the connection which is "db".. db.commit() .. and no dice..

I'm using pymysql.. could that be the issue?

Maybe you're referring to another package?

[ionic pecan]

What do you mean with "no dice"?

did it still not update the row?

[mystic hedge]

Nothing happened

no

[ionic pecan]

Then my guess is you don't have any entry with id equal to theID in the database - can you check that manually?

[mystic hedge]

yeah, I do.. It's actually within a loop, and it's the first entry.. here's the output

-86.570008
34.783156500000004
1

Long, lat, and ID

[ionic pecan]

do you do the insert and update in the same transaction?

[mystic hedge]

I'm not inserting anything, I'm just updating

[ionic pecan]

ah, I misunderstood the "within a loop" then

[mystic hedge]

I'm sry, I'm a noob from PHP

😃

[ionic pecan]

okay - if you do a ```py
cursor.execute("SELECT * FROM places WHERE id=%s", (theID,))
print(cursor.fetchall())

in the same program executing the update, does it print anything?

[mystic hedge]

Actually, that's the exact code that I have to create the for each loop (i'm sry, I don't know how to say it any other way)

I mean, iterate

Yea, it does do something, I do get the right data from that.. It's just when I get within that foreach iteration, I can't seem to commit the changes to the DB

[ionic pecan]

hmmm

can you post your full code here? or on https://paste.pydis.com if it's too long

make sure to remove connection strings for the database

[mystic hedge]

it's a local db

https://paste.pydis.com/izomukuqow.py

there's a little bit of commenting junk in there

my apologies

[ionic pecan]

to check whether the values updated, do you check the output of this program via the print statements or some other way?

[mystic hedge]

I'm looking at the localhost database via Querious (a sequel pro like app for mac)

[ionic pecan]

Hm. I have a feeling that Querious might see some old version of the data, is there some way to refresh the query result?

[mystic hedge]

Yeah, I've refreshed it several times, and it's not changed at all

Again, the 'pymysql' package works, right?

[ornate isle]

it does if youre using python >=3.5 or python 2.7

[mystic hedge]

I'm using python3

3.7.3

[ornate isle]

i never use pymysql on its own, so i'm really not an expert, but i do notice that the pymysql quickstart and examples use context managers to commit in a transaction

[ionic pecan]

Yeah, for the cursors

in their example they close the cursor before committing

[mystic hedge]

So close first?

[ionic pecan]

You could try moving the db.commit() below the loop and closing the cursor before running that - aside from that I'm out of ideas

you could always just use autocommit=True, but ehhhh

[ornate isle]

well i mean they also open it in a context manager using with

this i a good practice in general and since i dont know much about the library and since their own examples show it using this method, i would start with that

create the cursor inside a ctx manager with with db.cursor() as cursor:, execute whatver you need inside that block, then db.commit() once you're outside of that black. the cursor will have been closed propertly automatically and then it seems that's when to commit

all their docs and examples demonstrate this pattern which is not uncommon

there is almost certainly a way to do it without the context manager but you should not really bother trying to look for it

[mystic hedge]

Ok, thx guys! i really appreciate it

Is there another library you

recommend?

Even with their example copied, with some modifications I'm still getting the same

[mystic hedge]

I FIGURED IT OUT!

Before, the code was like this:

UPDATE places SET `longitude`=%s AND `latitude`=%s

It SHOULD have had a comma where I had AND

like this:

UPDATE places SET `longitude`=%s, `latitude`=%s

I'm sorry about that guys, I messed it

up

[ornate isle]

hahaha

love it

@mystic hedge it's okay one of my engineers spent 4 hours debugging what turned out to be a rogue .

still a good practice to use context managers and such

[mystic hedge]

lol that sucks

ill look into that

[ornate isle]

not a better mysql lib most likely but im a postgres guy in general. i do prefer to use a layer or two above raw sql in general though. either a full ORM or just a query builder but that's my preference

[mystic hedge]

nice

[ornate isle]

i prefer to write things like: python db.table('videos').where('views', '>=', 500000).order_by('views', 'desc').first() or ```python
Video.update(is_public=False).where(Video.published_at.is_null())

[short holly]

Does anyone know which application in redhat I can use to open and edit sqlite3 databases?

Kexi didn't work

[mystic hedge]

@ornate isle I totally agree. I come from the PHP world and we have lots of abstraction layers (and that snippet you gave looks almost like Laravel PHP) .. I appreciate your help!

[ornate isle]

@short holly install sqlitebrowser if you want a gui. sqlite3 on the command line obviously for doing it that way. never heard of kexi. 'dbeaver' is another free multi-db gui that would work. also almost anything commercial like datagrip will work with it

[stone jacinth]

Hello everyone

Do you guys think 30 is too late to career change?

tech is such a cool industry

[hazy mango]

@stone jacinth that’s more of a question for #career-advice

[polar isle]

length = await self.bot.pg_con.fetchrow("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_catalog = 'Game Hub' AND table_name = 'users')```

So, right now this will grab how many columns are in my table but can I filter this? Like if I want it to only count values that fit a param (like if a value is true, then it will count it but if it is false it won't)

[pure scroll]

just add it to where part of sql statement?

[heady nova]

        (f"INSERT INTO [table](type, executor, victim, reason, endtime) VALUES ('{what}', '{whom}', '{who}', '{why}', '{new_time}');")``` why does this give me an `ProgrammingError`?

[void flower]

Hello there.

I've read through the documents, and I believe I have a question I would like to ask with respect to docker, sql lite, and migrations for testing my django app. However, I'm not sure the best way to do this

I have posted the question on stack overflow here

https://stackoverflow.com/questions/56488801/django-system-checks-migrations-and-tests-pass-locally-but-fail-in-docker-ci

Is there someone who might be able to weigh in on this and correct any misunderstanding I have on how to achieve my goal?

[torn sphinx]

2019-06-08T09:27:03.180820+00:00 app[worker.1]: Ignoring exception in on_message_edit 2019-06-08T09:27:03.182090+00:00 app[worker.1]: Traceback (most recent call last): 2019-06-08T09:27:03.182146+00:00 app[worker.1]: File "/app/.heroku/python/lib/python3.6/site-packages/discord/client.py", line 307, in _run_event 2019-06-08T09:27:03.182149+00:00 app[worker.1]: yield from getattr(self, event)(*args, **kwargs) 2019-06-08T09:27:03.182150+00:00 app[worker.1]: File "spark.py", line 461, in on_message_edit 2019-06-08T09:27:03.182152+00:00 app[worker.1]: channid = str(get_log_channel(before.server.id)) 2019-06-08T09:27:03.182156+00:00 app[worker.1]: File "spark.py", line 170, in get_log_channel 2019-06-08T09:27:03.182157+00:00 app[worker.1]: create_server_if_not_exists(server_id) 2019-06-08T09:27:03.182180+00:00 app[worker.1]: File "spark.py", line 134, in create_server_if_not_exists 2019-06-08T09:27:03.182182+00:00 app[worker.1]: c.execute("INSERT INTO Servers VALUES (%s, %s, %s, %s, %s)", (str(server_id), str(""), str(""), False, False, "")) 2019-06-08T09:27:03.182242+00:00 app[worker.1]: TypeError: not all arguments converted during string formatting```
The function that appears to be creating thses errors is:
```py
def create_server_if_not_exists(server_id: str):
    c.execute("SELECT COUNT(*) FROM Servers WHERE ServerID=%s", (str(server_id),))
    server_count = c.fetchone()[0]
    if server_count < 1:
        print("[Servers Table] Creating server with id " + str(server_id))
        c.execute("INSERT INTO Servers VALUES (%s, %s, %s, %s, %s)", (str(server_id), str(""), str(""), False, False, ""))
        db.commit()```

The table is created like this:
```c.execute("""CREATE TABLE IF NOT EXISTS Servers( ServerID BIGSERIAL, Logchannel TEXT, Welcomechannel TEXT, Welcomertf BOOLEAN, Logtf BOOLEAN, Joinrole TEXT)""")```

Please tag me when you awnser, thanks in advance

@terse stump Could you help me please m8?

[river barn]

See how you have %s, %s, %s, %s, %s 5 places for strings but you pass (str(server_id), str(""), str(""), False, False, "" 6

and btw % formatting (which is terrible) already converts all args to strings

[indigo mason]

@torn sphinx Using Python format to condition quires provides anyone that can put something into your query full access to your database to do whatever they want with it

And anyway

You shouldn't be using %s for formatting

!tag f-strings

[delicate field]

f-strings

In Python, there are several ways to do string interpolation, including using %s's and by using the + operator to concatenate strings together. However, because some of these methods offer poor readability and require typecasting to prevent errors, you should for the most part be using a feature called format strings.

In Python 3.6 or later, we can use f-strings like this:

snake = "Pythons"
print(f"{snake} are some of the largest snakes in the world")

In earlier versions of Python or in projects where backwards compatibility is very important, use str.format() like this:

snake = "Pythons"

# With str.format() you can either use indexes
print("{0} are some of the largest snakes in the world".format(snake))

# Or keyword arguments
print("{family} are some of the largest snakes in the world".format(family=snake))

[river barn]

that's even worse

http://initd.org/psycopg/docs/sql.html

[indigo mason]

That is not what I said

[river barn]

wait what driver are you using

[torn sphinx]

I was going to use fstrings, I love em the dB system is old and I don't have the patience to rewrok it

[river barn]

sqlite? psycopg?

[indigo mason]

Whether or not you have the patience

Someone could wipe your entire db with the way you have it set up

[river barn]

If he uses pysqlite ? formatting is the best way

[terse stump]

Be careful do you do not open your code to SQL injection

Your driver will have documentation on this

Using ? Is what I do

[river barn]

psycopg doesn't support ? iirc

[terse stump]

I have never used it, should be some documentation on it

[river barn]

yes > http://initd.org/psycopg/docs/sql.html

[terse stump]

There you go @torn sphinx

[scarlet cedar]

Guys, I'm not sure if this question fits here as it's not a specific "database", but here's my question.

I made a really simple script to scrap some forum threads from time to time, and when the script finds a thread it fits the requirements, it interacts with it and also add the thread id to a .json to avoid duplicities.

I have the script gitted, in github, so I have it backed up, everything except the .json, how could I have it "updated" so if I lose the files I can recover the json?

I guess doing it in github would be a bad idea? as I would have to commit a lot of times per day

[indigo mason]

You might wanna check rule 5 first

(It may not be a violation)

[hazy mango]

@scarlet cedar what site are you scraping?

[scarlet cedar]

Thanks for the input @indigo mason , but it's not anything illegal, just recollecting some data

@hazy mango It's a forum

[indigo mason]

It may not be illegal but it might break their ToS

[hazy mango]

Which forum @scarlet cedar that's what I meant

[scarlet cedar]

I don't wanna spam the channel, let's not diverge

[terse oracle]

Any PONY ORM users here? I am struggling with a TypeError.
"TypeError: Invalid value for attribute"
The attribute is set as Required(str) and the input to it is "Temperature" which should not give any issues from what I can tell. I have made multiple classes with relations and double checked them using https://editor.ponyorm.com without any luck in figuring out where the root of my problem comes from.
It's a script that should be used to load a CSV file into a db.

[tacit elk]

Im trying to dive into SQL (sqlite specifically) and im just browsing other code to try and understand foreign keys. my google searching has been exhausted!

more specifically trying to reverse engineer some code from "InstaPy"

SQL_CREATE_ACCOUNTS_PROGRESS_TABLE = """
    CREATE TABLE IF NOT EXISTS `accountsProgress` (
        `profile_id` INTEGER NOT NULL,
        `followers` INTEGER NOT NULL,
        `following` INTEGER NOT NULL,
        `total_posts` INTEGER NOT NULL,
        `created` DATETIME NOT NULL,
        `modified` DATETIME NOT NULL,
        CONSTRAINT `fk_accountsProgress_profiles1`
        FOREIGN KEY(`profile_id`) REFERENCES `profiles`(`id`));"""

what does the fk_accountsProgress_profiles1 mean?

I know that profiles is another table but the 1 is confusing me

[void flower]

Hi all! Is there someone who might have the chance to help me with an issue using docker, django, and some failed migrations that only happen in the container

[pure cypress]

@tacit elk It's just the name of the constraint. Sometimes people don't bother naming their constraints and let the database automatically assign a name. In this case, they explicitly give it a name. I don't think the "1" at the end means anything

@void flower Just go ahead and ask your question

[void flower]

Ok

I have a stack overflow post here

https://stackoverflow.com/questions/56488801/django-system-checks-migrations-and-tests-pass-locally-but-fail-in-docker-ci

But the gist of it, is that I can run migrations locally and run my tests locally perfectly fine

I'm using django and docker right now

When I run the code in docker thought my django system checks fail

[void otter]

so hugobot i see 1 comment on your question on stackoveflow

did you try that

[pure cypress]

That's their own comment

[void flower]

Yeah

[tacit elk]

@pure cypress ok so if I didn't add a constraint it would effectively do the same thing linking the foreign key?

that would fall in line with the documentation i've read

[pure cypress]

@tacit elk Yeah cause I think naming constraints is optional, at least for the databases I've worked with. By the way, if you didn't know, a "foreign key" is a type of "constraint" so that's how the two terms relate

[tacit elk]

yep 😉 hence why I was confused.. it looked like double dipping into the relationship

[void flower]

I'm pretty positive my issue has something to do with docker execution

I know that there isn't environment stored between layers

and that each docker layer is executed in its own shell

But that still doesn't make sense why it fails

There is an sqlite3 instance available in the container

[pure cypress]

So it fails when making migrations, not when applying them, right?

[void flower]

yes when, I make migrations

Specifically if I do the system checks that is what's failing

[pure cypress]

Do all your versions match up between local and the container?

[void flower]

Yes

100%

[pure cypress]

Have you tried creating a docker image locally and testing with that?

Oh I see the comment says you've tried that

[void flower]

Yeah that's how I'm testing right now

I've completely removed the ci/cd from the equation and am just doing a local docker configuration

[pure cypress]

Can you show the dockerfile

[void flower]

It is very very simple

FROM python:3.7

COPY ./back_end /opt/back_end
WORKDIR /opt/back_end

RUN apt-get update -qy
RUN apt-get install -y python-dev python-pip
RUN pip3 install -r requirements.txt

RUN python3 manage.py makemigrations piano_gym_api
RUN python3 manage.py migrate
RUN python3 manage.py test

This moves the repository into the docker image

and then it installs the python tools set

and then it runs the migrations

@pure cypress The craziest thing about this

If I leave out the django migrations, and test commands

Then I run a docker comand to enter the container

The python django commands will work

However if those commands are run in the Dockerfile they fail

[pure cypress]

wtf

[void flower]

I agree

It makes no sense

That's why I have a theory this has to do with docker's layer's but I have no idea honestly

[pure cypress]

Try using the other commands instead of manage.py?

I forget what they're called

[void flower]

I'm not sure what commands you're referencing.

[pure cypress]

I'll look it up

$ django-admin <command> [options]
$ manage.py <command> [options]
$ python -m django <command> [options]```

Those are the same

Another thing to try, especially if you think it's a layer issue

Just chain all the commands together with &&

so you get no intermediate layers that way

[void flower]

Ah yes. @pure cypress I did try that

and it didn't work either

🤔

[pure cypress]

Is this open source or a private repo

Maybe I could run a test myself

[void flower]

It's a private gitlab repo

But I'm more than happy to provide you access

If I concatenate all the commands into a single layer this is the output

[pure cypress]

Yeah ok this is me https://gitlab.com/Markkk

[void flower]

Ok one moment

@pure cypress I've given you access to https://gitlab.com/Hugbot/piano_gym/

I've made a branch for you on testing/branch_for_markk

From the root directory all you have to do for the image to build is run

docker build -t piano-gym-api -f Dockerfile_Piano_Gym_API .

[pure cypress]

Hmm I don't have access yet

Do I need to go somewhere to accept?

[void flower]

Is this the correct Markk?

[pure cypress]

That's not me

[void flower]

oh noes

[pure cypress]

3 k's

[void flower]

oh i see

an extra k

[pure cypress]

yes people take my name how dare they

[void flower]

lol, monsters

I believe you should have access now

[pure cypress]

I do, but not to the code 🤔

[void flower]

one moment

[pure cypress]

The repository tab on the left does not exist

[void flower]

you're a developer now and should have access

[pure cypress]

👍

[void flower]

Please don't steal my product 😂

(joke)

[pure cypress]

does this just use sqlite you said?

[void flower]

Yes it should just be sqlite

The first step is to get ci/cd supported

then I'll be migrating to postgres

[pure cypress]

oh dear

[void flower]

and eventually supporting persistence through docker compose

surprised pikachu face

[pure cypress]

your image is already 1.13gb jeez

[void flower]

gb?

[pure cypress]

yeah'

[void flower]

oh lord

I was going to optimize later

It all started as "I'll just get some tests running"

[pure cypress]

I think you just ave a lot of python deps that's all

[void flower]

I think so as well

The directory itself for the backend is not big at all

[pure cypress]

well ok I get the same error

so time to debug

[void flower]

I'm glad you're able to reproduce it

[pure cypress]

No hold on

It's the same error I believe

but it fails on migrate rather than makemigrations

[void flower]

You can actually do this without either of them

if you do python manage.py check

It will also fail

Because it's the system checks that are actually failing