Using multiple databases in SQL with Python

Question

I'm working on a registration site and right now I am trying to make multiple bases at the same time.

I have 2 databases, one called Tables, and the other called Digit.

I want to use the SAME function for both of them, only to manage 2 databases at the same time, differ by the front end choice.

I tried to use %s as a place holder for the table name, while i have a dict at the beginning for the different databases (1: Tables, 2 : Digit)

cursor.execute("SELECT * FROM %s WHERE active = %s AND isFull = %s ORDER BY minLevel" , [bases[DB], 1,0])

This is the code I wrote, I was hoping to switch the databases based on the DB given from the front end of the site.

And.. it didn't work. I'm really stuck here, and I am not sure if this way is even legal...

Thanks a head for you help!

Are these different databases or different Tables in a database? please add data regarding what database, and what module are you using to connect to it — Alonme
– Alonme, Commented Mar 20, 2020 at 22:11
Hey, i figured it out. i hope my answer below will answer your questions too! if not i'll be glad to explain for future need — roy
– roy, Commented Mar 21, 2020 at 21:42

roy · Accepted Answer · 2020-03-20 22:55:55Z

I figured it out!

thanks to another post by the way - thank to cursor.query( 'select * from %s;', ('thistable',) ) throws syntax error 1064: ...near ' 'thistable' ' at

the problem is you cant use %s on "database variables", like column, databases, etc.

the way to work around it is to build the query as a string beforehand and then to use execute in this format : cursor.execute (q , [variable]) while q is the pre-built query. and while building the query to add the database wanted

so the code above should look like (i have a pre built dictionary)

q= "SELECT * FROM " + dict[Number] + " WHERE active = %s AND isFull = %s ORDER BY minLevel"
cursor.execute(q , [1,0])

while dict is the name of the dictionary, number is the variable i got from the front end. and active , is Full and minLevel are columns of mine i use.

hope it will help somebody!

Collectives™ on Stack Overflow

Using multiple databases in SQL with Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related