top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration
Print Preview

What is the proper wayto execute dynamic MySQL queries in Python

0 votes
239 views

What is the proper way in which I can execute dynamic MySQL queries in Python? I want to do dynamic queries for both CREATE and INSERT statement.
Here is my attempted code:

sql="create table %s (%%s, %%s, %%s ... )" % (tablename,''.join(fields)+' '.join(types))
cur.execute(sql) 

where 'field' is the fieldnames stored in a list and 'types' are the fieldtypes stored in a list.

posted May 29, 2013 by anonymous

Share this question
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button

1 Answer

0 votes

You need to join the fields and the field types. Use zip(). Then join with commas.

fields_and_types = [%s %s % (field, type) for field, type in zip(fields, types)] 
what_goes_between_the_parens = , .join(fields_and_types) 
sql = create table %s (%s) % (tablename, what_goes_between_the_parens) 

See where that gets you.

answer May 29, 2013 by anonymous
Similar Questions
+3 votes

I am using Python 2.7, MySQl 5.5, mysqldb(driver) and trying to connect to the mysql db using Python.

What I have tried

import MySQLdb
db = MySQLdb.connect(host="localhost",
                     user="root", # username
                      passwd="xxxxx", #  password
                      db="TEST") # name of the data base

Even tried 127.0.0.1 as local host

#ERROR
Traceback (most recent call last):
  File "C:/Python27/connect.py", line 6, in <module>
    db="prakash") # name of the data base
  File "C:\Python27\lib\site-packages\MySQLdb\__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 187, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
  OperationalError: (2003, "Can't connect to MySQL server on 'localhost' (10061)")

I am new to python, please help how to proceed?

+1 vote

Is there a way to make python script that connects to mySQL DB ask for a password on the:

conn = mdb.connect(host, user)
0 votes

I am having trouble matching Python data types with those of MySQL. MySQL has about 7 basic data types including Blobs, Binaries, etc. It also has a rich selection of geometry types. In addition, types like INT have 7 or 8 different options like Primary Key, zero fill, auto inc, etc. I can't seem to find anything in python to match these. I am trying to build a model.py for an existing database that was created with MySQL Workbench.

I do not wish to use anything other than MySQL because of the complexity of my storage needs (searchable text, PDF, Audio, Video and Photos). The text searches will often be word phrase searches through thousands of documents. I need the speed and the data type flexibility.

How do I build or modify a model.py to do this. I really don't want to write a model.py manually. That would really be a major pain. Workbench to the database an import to Django and edit is my choice. Unfortunately, the model.py produced has lost most of the functionality of the original database and I can't seem to figure out how to fix it.

+1 vote

I have kept all the create table SQL querys in a text file. Using readLines i am trying to execute the sql commands as per the code mentioned.

file=open("TABLES.txt","r")
for sql in file.readlines():
self.cursor.execute(sql)

But I am getting Error 1065 ' Query was empty'. More Importantly the tables are being created in the database. The text file is like this.

CREATE TABLE TUserDetails (FirstName VarChar(50) NOT NULL, LastName VarChar(50) NOT NULL, Email_Id VarChar(50) NOT NULL,Type VarChar(50) NOT NULL,Department VarChar(50) NOT NULL,NoOfIncorrectAttempt Integer NOT NULL,Deleted Bit NOT NULL,UserID VarChar(50) NOT NULL,CONSTRAINT TUserDetails_PK PRIMARY KEY CLUSTERED ( UserID ))

CREATE TABLE TRequests(RequestID VarChar(50) NOT NULL,UserID VarChar(50) NOT NULL,Status SmallInt NOT NULL,TimeOfRequest Timestamp NOT NULL,Deleted Bit NOT NULL,Priority Integer NOT NULL,CONSTRAINT TRequests_PK PRIMARY KEY CLUSTERED ( RequestID ))

CREATE TABLE TUserDetailUSERs(UserID VarChar(50) NOT NULL, Type VarChar(50) NOT NULL,Deleted Bit NOT NULL,TimeOfCreation Timestamp NOT NULL,TimeLastUpdated Timestamp NOT NULL,Active Bit NOT NULL,PWDID VarChar(50) NOT NULL,RoleID VarChar(50) NOT NULL,OrganisationID Integer NOT NULL,CONSTRAINT TUserDetailUSERs_PK PRIMARY KEY CLUSTERED ( UserID ))

CREATE TABLE TOrganisations(OrganisationID Integer NOT NULL,OrganisationName VarChar(50) NOT NULL,TimeOfCreation Timestamp NOT NULL,TimeLastUpdated Timestamp NOT NULL, Deleted Bit NOT NULL, CONSTRAINT TOrganisations_PK PRIMARY KEY CLUSTERED ( OrganisationID ))

CREATE TABLE TPWDs(PWDID VarChar(50) NOT NULL,Code VarChar(50) NOT NULL,DateOfCreation Timestamp NOT NULL,DateLastUpdated Timestamp NOT NULL,TimeOfDeletion Timestamp NOT NULL, CONSTRAINT TPWDs_PK PRIMARY KEY CLUSTERED ( PWDID ))

CREATE TABLE TRoles(RoleID VarChar(50) NOT NULL,RoleName VarChar(50) NOT NULL,Description VarChar(50) NOT NULL,TimeOfCreation Timestamp NOT NULL, RoleLastUpdated VarChar(50) NOT NULL, Deleted Bit NOT NULL, CONSTRAINT TRoles_PK PRIMARY KEY CLUSTERED ( RoleID ))

I checked running each sql query individually, and it is qorking file. Now although the tables are being created in the database but i am getting error 1065 as mentioned above

+3 votes

I have a datafeed which is constantly sent to a MySql table. The table grows constantly as the data feeds in. I would like to write a python script which process the data in this table and output the processed data to another table in another MySql database in real-time.

Which are the python libraries which are suitable for this purpose? Are there any useful sample code or project on the web that I can use as reference?


Useful Links with Similar Problem
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#470/147, 3rd Floor, 5th Main,
HSR Layout Sector 7,
Bangalore - 560102,
Karnataka INDIA.
QUERY HOME
...