#databases

1 messages Β· Page 195 of 1

dark mica
#

am I suppose to refactor the code of tortoise orm then?

quick osprey
#

I want to get information from database in both my website using php and python application what is the best way to do that?

keen minnow
#

But that may or may not be the best way for you

quick osprey
#

thanks

#

how should I deal with this error?
Fatal error: Uncaught Error: Class 'SQLite3' not found in C:\xampp\htdocs\salam\index.php

keen minnow
quick osprey
#

I added php to system path but still facing this error

keen minnow
#

but did you add the sqlite3 library?

quick osprey
#

no i didnt but where can i find it? Is it included in php files or i should download it?

keen minnow
#

you may want to go through a php + sqlite tutorial to learn more about it

torn sphinx
#

Command raised an exception: UndefinedTableError: relation "suggestionchannel" does not exist

#

does anyone know how to fix it?

paper flower
#

most likely

torn sphinx
paper flower
#

Check table name, check if you're connected to the right db

torn sphinx
#

I believe that the error was that I had put "suggestionChannel" in the database and the query did not put the capital "C". I changed the name, left only "suggestion" and it worked.

steel bloom
#

Whats best python library to use postgres database? I need to do some basic stuff like create some tables, insert data and filter

paper flower
manic zealot
manic zealot
ivory turtle
#

any suggestions for optimizing a log-only django table in MySQL?

#

I have a table that gets truncated and archived weekly with ~1,000,000 rows each week

#

there are no constraints or indexes on it since its just a logging table

#

but I would like to prevent anything from being memory cached and I would like to optimize for inserts

#

ping me if anyone has ideas for optimization

paper flower
#

If your pk is sequential (e.g. autoincrement) then there's nothing to optimize really πŸ€”

#

Also 1 million rows per week doesn't sound like much and your mysql instance should handle it easily

keen minnow
#

yeah, not sure what there is to optimize there

paper flower
#

Potential optimization might be using bulk insert if there are multiple rows being inserted per request πŸ€”

keen minnow
#

at 1.6 write/sec, I wouldn't even worry about it

#

or write it to GCS/s3

paper flower
#

Well, same, also configuring connection pooling might help if your db seems to be the bottleneck

keen minnow
#

( I mean export it or something)

paper flower
#

I had some problems with django not handling connections well out of the box

keen minnow
#

I am doing more than that on my nuc at home on a postgres docker container using a usb hdd

keen minnow
paper flower
#

It consumed a lot more connections than it needed or something like that

#

We didn't have any problems with sqlalchemy and lifo pool on the other hand

quick osprey
#

what is wrong with this code?

def test():
    cur.execute("INSERT INTO servers(id,serverName,serverLength,subscription,subscriptionExpiry) VALUES(123123123,salam,2,0,Null)")
    conn.commit()

raise errorclass(errno, errval)

pymysql.err.OperationalError: (1054, "Unknown column 'salam' in 'field list'")

storm mauve
#

try using quotes as in VALUES(123123123,'salam',2,0,Null)

quick osprey
#

thanks

quick osprey
#

why am I getting the number of the cell with

sub = cur.execute("SELECT subscription from servers WHERE id = 1231231234124")

Instead of getting the value of cell?

grim vault
#

After executing a SELECT you'll need to fetch the data with row = cur.fetchone() or rows = cur.fetchall()

bright inlet
solar abyss
#

Given a SQLAlchemy model like this:

db = SQLAlchemy(app)

class StashEntry(db.Model):
    __tablename__ = "stashes"

    id       = db.Column(db.Integer, primary_key=True, autoincrement=True)
    count    = db.Column(db.Integer, nullable=False)
    user_id  = db.Column(db.Integer, db.ForeignKey('users.id'))
    color_id = db.Column(db.Integer, db.ForeignKey('colors.id'))

I'm trying to do some bulk updates of data:

from sqlalchemy import update

update(StashEntry).where(StashEntry.color_id==xxx).values(color_id=yyy)

However the data doesn't seem to update, at least I see no changes. Also no errors.

#

I suspect my update() syntax is way off, but the SA docs are pretty useless there. They don't really say what the parameter to update() should be.

#

Or possibly the update gets rolled back at some point and is never committed? I do of course have a db.session.commit() at the end.

#

Maybe this channel is the wrong place too.

solar abyss
#

SQLAlchemy really is a mess.

#

Maybe the update() is just something that has to be executed somewhere? At least I can store it in a variable and log it.

jaunty linden
#

does anybody know the new psycopg lib (psycopg3 not psycopg2)? i'm trying to keep a connection alive but when i test GET request via Postman , FastAPI returns error connection closed

solar abyss
#

Well, adding the stuff inside a db.execute(update(....)) had no effect. I'm starting to actively regret using SQLAlchemy.

#

I guess the only way is to do a StashEntry.query(...).all() and then loop the results and manually set the properties, db.session.add() and finally db.session.commit() . But that will be abysmally slow.

#

Ah, it must be db.session.execute(update(...)), so that it's in the session being committed.

jaunty linden
#

smh psycopg connection

shy apex
# 
import pymysql

def mysqlconnect():
	
    # To connect MySQL database

    conn = pymysql.connect(

        host='localhost',

        user='root',

        password = "removed",

        db="mydata"

        ) 
        
    cur = conn.cursor()

    cur.execute("show databases")
    
    
    output = cur.fetchall()

    print(output)
    l = enumerate(["Ayush", "Rahul","Sahil","Bipul"], start=2)
    
    for i,n in l:
        cur.execute("insert into Marks(Serial_no, Name) values(%s, %s)", (i, n))
    cur.execute("select * from Marks")
    print(cur.fetchall())
    conn.close()
    
# Driver Code
if __name__ == "__main__" :

    mysqlconnect()β€Š

Even after successful execution the MySQL db is not updated when i check it in a linux terminal

grim vault
#

Do a conn.commit() after the insert loop.

shy apex
#

Its worked thanks

jaunty linden
#

does anybody know the new psycopg lib (psycopg3 not psycopg2)? i'm trying to keep a connection alive but when i test GET request via Postman , FastAPI returns error connection closed ......
bump

jaunty linden
#

looks like everybody just living off ORMs

rocky arch
#

Hey guys,
I have a Discord Bot with a SQL Database.
At the beginning the Bot will connect with the Database and then only do "SELECT", "UPDATE", or "INSERT" SQL statements.

After a while I will get the Error message
"Command raised an exception: OperationalError: 2055: Lost connection to MySQL server at 'Database name', system error: 104 Connection reset by peer"

This will happen once you try to access the Database.

Everything will work fine after a restart, so why is this happening? I never close the connection or reconnect. Is this the problem? Do I have to reconnect to SQL once in a while?

#

I connect to the database with

db= mysql.connector.connect(
host = "hostname",
user="username",
password="pw",
database ="name"
)
cursor = db.cursor()

And then do stuff like

cursor.execute("SELECT ... FROM ... WHERE ...")
result = cursor.fetchone() # fetchone only if its one result
cursor.reset()

And how can I check if I am still connected and reconnect?

(I am using phpMyAdmin)

Please ping me if you respond

delicate fieldBOT
#

:incoming_envelope: :ok_hand: applied mute to @deep lance until <t:1658092115:f> (9 minutes and 59 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

static wing
rocky arch
#

Sometimes multiple times per minute, sometimes only once in a week or longer

#

And I have the feeling, it happens only after a longer time without any interaction (1-2 days)

static wing
#

Or worst case, you could set up some sort of background task to poll the mysql server to keep the connection alive.

rocky arch
#

I am using a Server from Pebble Host.
I think the best way would be a check if the Bot is still connected. So the Bot will work, no matter the amount of time it has no interactions

#

mhhh

rocky arch
rocky arch
trim lintel
rocky arch
#

Currently disconnect and then reconnect (instead of just connect).

But what you are saying is advanced stuff. Do you have a Link to a good Documentation?

trim lintel
#

Pooling is not really advanced as it sounds, especially since the library handles everything for you, and you don’t really write any more code (in fact you will probably write less code). I think aiomysql is the official library for asynchronous MySQL driver. Here’s a link to pooling in the documentation https://aiomysql.readthedocs.io/en/latest/pool.html

#

The idea is the pool maintains a collection of connections. Each time you want to talk with the database you grab one from the pool. This way you avoid the connect/disconnect complexity and resource consuming tasks.

keen minnow
#

(also look at the recycling/validation of connections in the pool)

rocky arch
#

Okay a lot to learn. I will have a look at it. Thank you

lime hearth
#

hi

stone ocean
#

in mongo db can i sort my characters array by the character's rarity?

unknown.png
spring furnace
#

People that have worked with PopSQL, is it useful?
Worth actually trying out with this GitHub Students program

torn sphinx
#

can someone explain this please?

unknown.png
manic willow
#

Youre building an SQL query and then preparing the data to pass along to the cursor.execute as a parameter list

#

the %s in the query string are placeholders that will be replaced by the parameters below

#

feel free to ping me when you're back, need to head out πŸ™‚

torn sphinx
#

i dont understand data_tuple = part

manic willow
torn sphinx
#

this one

manic willow
#

Ah πŸ™‚ Okay so I think below that line theres probably a line with "cursor.execute. The values in the "data_tuple" variable you can pass as the second argument to "cursor.execute" to safely replace the placeholders (the %s in the query) with the values in the tuple

#

Is there a cursor.execute line below?

torn sphinx
#

yes

manic willow
#

Yup πŸ™‚πŸ‘

icy oyster
#

Can I pass a None-type object as text in sqlite

#

or I have to convert None into "None"

#

and then pass it

paper flower
#

By default every column is nullable if you don't specify not null constraint

icy oyster
#

so text, int, float is nullable?

paper flower
icy oyster
#

kk

#

tysm

paper flower
#

Except primary key, but that might depend on the dialect πŸ€”

#

Not sure

manic willow
#

Primary key disallows NOT NULL in sql standard, does it not?

undone leaf
#

Hey I’m looking to get pointed in the right direction. I have two databases that I need to print out the difference in the lists.

Picture for example:

IMG_6906.jpg
fading patrol
tough kettle
#

should i be adding more stuff to a model class?

class User(db.Model):
    name = db.String()
    email = db.String()
    password = db.String()


class TodoList(db.Model):
    name = db.String()
    user = db.relationship("User")

    def get_by_user(self, user):
        return stuff

    def get_by_list_name(self, list_name: str):
        return self.query.filter_by(name=name)

Like the get methods?

#

because i don't see them being used in docs

#

or anywhere else

unkempt prism
manic willow
#

Yes thorough this looks like sql alchemy

buoyant hemlock
#

so i was wonder, I have four tables: Customers, Orders, OrderDetails and products, i wondering that would be best for the OrderDetails table:
1.
OrderID
CustomerID
ProductIDs ( where each product is seperated by a comma. So i will get this data like 58823,23818,23299,239293,123123 etc.
Amount
Sum
2. I have an entry for each productID, that has the same OrderID. but this seems a bit redundant? Im more wondering if method 1 has any issuses?

#

however, if many products are bought, the cell data becomes very big?

#

or maybe i can categorized it somehow

#

well i could print a message, if nrOfOrderedItems>100 "Call our business phone to make this order2

lone oracle
dapper yacht
#

postgres question: can i put a value into a variable and retrieve it in a nicer way than select 'foo' into varname; insert into tbl(col) values ((select * from varname));?

tight junco
#

does mysql not have CREATE TABLE IF NOT EXISTS?
because i keep getting this

C:\Users\Scratcha\AppData\Local\Programs\Python\Python39\lib\site-packages\aiomysql\cursors.py:239: Warning: Table 'users' already exists
  await self._query(query)
C:\Users\Scratcha\AppData\Local\Programs\Python\Python39\lib\site-packages\aiomysql\cursors.py:239: Warning: Table 'userss' already exists
  await self._query(query)```
dapper yacht
#

it does, but that package seems to be giving a warning

solid girder
#

im not sure if this counts as a database, so please let me know if this is the wrong place.

im trying to create a script which through user input writes to a spreadsheet, however i need it to read if the cell has a number inside and if it does, move down 1 cell. Is this possible to do?

#

the spreadsheet will be a database which is why i thought it best to post this question in here

#

i just need pointing in the right direction of what to google or which package to use

fading patrol
# solid girder im not sure if this counts as a database, so please let me know if this is the w...

Yeah, this is super basic stuff. Maybe start here: https://realpython.com/openpyxl-excel-spreadsheets-python/ and then if you get stuck, #β“ο½œhow-to-get-help

A Guide to Excel Spreadsheets in Python With openpyxl – Real Python

In this step-by-step tutorial, you'll learn how to handle spreadsheets in Python using the openpyxl package. You'll learn how to manipulate Excel spreadsheets, extract information from spreadsheets, create simple or more complex spreadsheets, including adding styles, charts, and so on.

stone ocean
#

in mongodb how would i sort my characters array by their rarity?

unknown.png
fading patrol
stone ocean
#

collection.find({'_id':userid}, {'characters':1}).sort('rarity', -1)
i assume i can't do something like this

solid girder
dapper yacht
#

in a trigger function, what's the difference between these two, when the first one works, but second one doesn't:

begin
    if new.is_seller then
        insert into party_seller_details(id) values (new.id);
    end if;
    return new;
end;

begin
    if new.is_seller = true then
        insert into party_seller_details(id) values (new.id);
    end if;
    return new;
end;
#

logically, they're both the same, surely?

#

this is for a postgresdb

grim vault
#

!e

for test_var in (0, 1, 2, "", "a", [], [0]):
    print(f"{test_var = !r:>3};\tbool({test_var!r:>3}) = {bool(test_var)};\ttest_var == True -> {test_var == True}")
delicate fieldBOT
#

@grim vault :white_check_mark: Your eval job has completed with return code 0.

001 | test_var =   0;	bool(  0) = False;	test_var == True -> False
002 | test_var =   1;	bool(  1) = True;	test_var == True -> True
003 | test_var =   2;	bool(  2) = True;	test_var == True -> False
004 | test_var =  '';	bool( '') = False;	test_var == True -> False
005 | test_var = 'a';	bool('a') = True;	test_var == True -> False
006 | test_var =  [];	bool( []) = False;	test_var == True -> False
007 | test_var = [0];	bool([0]) = True;	test_var == True -> False
dapper yacht
grim vault
#

True is a specific value (in python and most languages it's integer 1). So if you use the equal sign, the variable must be that value. Wherever any integer not equal to 0 can be seen a true-ish value if not explicitly compared to true.
if new.is_seller then can be logical true for a range of values, like 10, -3, 17 and so on.
but if new.is_seller = true then is only logical true if the variable has the value true.

late canyon
#

i need help in py mysql databases

grim vault
#

Because you only do the ins/val/execute AFTER the for loop, which means only once with the last values.

#

Indent line 12-14 to be inside the for loop. You can put line 12 before the for loop because the statement is the same for all values.

late canyon
#

is it okay now?

unknown.png
grim vault
#

The execute must also be inside the loop.

late canyon
#

okay

#

thanks mate

winged mirage
#

working on some SQL and thought of something I'm curious about - I was taught to write commands in all caps, but more recently, I tend to use lowercase. Anyone know if there's a reason one is favored over another or if it's just a matter of preference?

fading patrol
hollow egret
#

It also makes it easier to distinguish column names from actual SQL logic

#

also indenting the contents of your SQL statement nicely is a clear way to make it more readable

winged mirage
#

Noted, thank you both πŸ™

opaque wave
#

hey guys, can someone help me conceptually with the following query? I have already made a script that works but I don't think it's done in the proper sql way and was told to rewrite it since it's hard to read and maintain as well.

I have the following tables:
Table 1, which has a single entry per username and includes information such as min_deposit(date) and marketing data associated with each username
Table 2, again a single entry per username -- of which only the country of the users is of interest
Product 1, many entries per username and includes information about how much they spend on this product
Product 2, same ^
... goes on to table Product 6

Approximately the query should return, each customer's sums of how much they spent for each product along with the marketing data, their country filtered by the min_deposit date.

My current query does something like this:
get filtered Table 1 data left join with Table 2 left join with Product 1 group by,
use all of the above left join with Product 2 group by,
use all of the above left join with Product 3 group by

Of course I have omitted most of the details, but the logic of the above query is to stack left joins and each time use more attributes in the group by

what is another way of doing it?

paper flower
opaque wave
#

yes, but the main issues to take into account is that Product tables have to filtered by Table 1's min_deposit otherwise these are huge tables and i need to ensure that the joins do not lead to crazy sums

#

since Table 1 has 1 to many mapping to all product tables

#

though as I said the above capture the essence of the problem, the real problem is a bit more complex

opaque gale
#

conundrum:

  • Within my venv: run command to migrate database against PostgreSQL (success and tables exist)
  • Same as above, but run command line to create an admin user (success and record exists)
  • Log into flask shell and run User.query.all() and it returns my user from the PostgreSQL table
  • use curl to hit the api, and it says table not found (shows that it is trying to hit sqlite3)
    [8:54 AM]
    that last part I know because i'm tailing the gunicorn logs in supervisor. It even shows that gunicorn is connected through the virtual environment....so i know it is using the same environmental configs
#

This is using flask and sqlalchemy

sterile pelican
#

Is there a way to determine at what position a specific record is when sorted with ORDER BY without using python (postgresql)

fading patrol
# sterile pelican Is there a way to determine at what position a specific record is when sorted wi...

Third answer here looks promising: https://stackoverflow.com/questions/907438/can-i-get-the-position-of-a-record-in-a-sql-result-table

Stack Overflow
Can I get the position of a record in a SQL result table?

If I do something like

SELECT * FROM mytable ORDER BY mycolumn ASC;
I get a result table in a specific order.

Is there a way in SQL to efficiently find out, given a PK, what position in that res...

sterile pelican
hollow egret
hidden wedge
#

anyone have experience with the merge statement in postgres?

#

nvm just foiund out its depracated

opaque gale
unkempt prism
worn cliff
cinder thunder
#

Hello
SQL Alchemy
if i have "chain" of tables linked by foreign key (#1 to #2, #2 to #3 and its), does SQL Alchemy can do "magic" about deleting rows in "child" tables if it delete rows in one of "parent" tables ?

#

so it will remove rows what will loose "parents" by key