CODEWITHSUNDEEP
pythonsqldatabaseswitching
roy
roy

Reputation: 13

Using multiple databases in SQL with Python

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!

Upvotes: 0

Views: 1660

Answers (1)

roy
roy

Reputation: 13

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!

Upvotes: 1

Related Questions