Python : Sqlite Insert Libraries of varying size into table


My answer accepted in stackoverflow.

Below is the code that I have written to create an insert statement for python which accepts the table name and dictionary. This will allow the varying size dictionaries to directly map into an Insert statement. Similar logical can be used for UPDATE statement as well.


import sqlite3
def create_insert_string(table_name,input_dictionary,conn_db):
#validate connection
c = conn_db.cursor()
#Check if the table is existing in the database if not throw error
table_exist=c.execute('SELECT COUNT(*) FROM sqlite_master WHERE type=\'table\' AND name=\''+table_name+'\'');
if table_exist.fetchone()[0]==0:
return 'Error'
insert_str = 'INSERT INTO '+table_name+' ( '
count=0;
for keys in input_dictionary.keys():
if count == 0:
insert_str=insert_str+ ' '+keys
else:
insert_str=insert_str+ ', '+keys
count=count+1
count=0;
insert_str=insert_str+' ) VALUES ( '
for keys in input_dictionary.keys():
if count == 0:
insert_str=insert_str+ ' :'+keys
else:
insert_str=insert_str+ ', :'+keys
count=count+1
insert_str=insert_str+' )'
return insert_str
# Main Program
conn = sqlite3.connect('C:\\PYTHON_SCRIPTS\\test.db')
dict={'column1':'value1','column2':'value2'}
insert_string=create_insert_string('test',dict,conn)
print(insert_string)
c = conn.cursor()
c.execute(insert_string,dict)
conn.commit()
c.close()
Advertisements

Good Post on Clean Code


Using SQL Statements as a String in Class file while working on Android or Java application can be such a pain.

I was checking in the net to find out whether there are any ways in which we can store the SQL Statments separately so that the code can be more readable.

I found some interesting stuff. Please find below the links for the same.

1. Clean Code

2. Java Practices : Keep SQL out of code.

3. Android : Store SQL Queries in an XML File (Stackoverflow answer)

I am working on a way to store all my SQL Statements in an XML File while working with Android.

Found this post on placing XML File with Data on Android in StackOverflow.

res

  • No subdirectorys are allowed under the specific resource-folders.
  • The R-class indexes all resources and provides simple access.
  • There are some simple methods which help reading files stored in the res-directory

assets

  • Subdirectorys are allowed (as much as you like).
  • No indexing by the R-class
  • Reading resources stored in assets is done using the AssetManager.

Also found a cool post on Working with resources in Android : Link.

 

SQL Statements in the case of Android can be stored in the strings.xml under the resources–> values  folder.

In case you using a Database class which is a pure java class the this link will be able to help you on how to do the same.

In my case, I created a separate DAO Class called the DBClass which deals with the connecting and disconnecting with the database. I set the context of the DAO class so that the strings can be used here.

Cleanest way to build an SQL string in Java


Properties file can be used:

update_query=UPDATE user_table SET name=? WHERE id=?

Then with the help of a simple utility class:

public class Queries {

    private static final String propFileName = "queries.properties";
    private static Properties props;

    public static Properties getQueries() throws SQLException {
    	InputStream is = 
    		Queries.class.getResourceAsStream("/" + propFileName);
    	if (is == null){
    		throw new SQLException("Unable to load property file: " + propFileName);
    	}
    	//singleton
    	if(props == null){
    		props = new Properties();
    		try {
    			props.load(is);
    		} catch (IOException e) {
    			throw new SQLException("Unable to load property file: " + propFileName + "\n" + e.getMessage());
    		}			
    	}
    	return props;
    }

    public static String getQuery(String query) throws SQLException{
    	return getQueries().getProperty(query);
    }

}

you might use your queries as follows:

PreparedStatement stm = c.prepareStatement(Queries.getQuery("update_query"));

All credits to the guy who gave the answer.

Source : StackOverflow

Android Programming – setContentView()


setContentView() is a very important function when it comes to programming with Android.

One has to understand its use completely to work with Android UserInterface.

Basically what this function does is display the Layout created thorugh XML or the Dynamically created layout view in the Screen.

 

Syntax –

For an XML Layout –

setContentView(R.layout.showexcer);

where showexcer.xml is your xml file.

For Dynamically created Java View/Layout

View v1=new View(this);
setContentView(v1);

Similarly a dynamically created layout can be dispalyed using setContentView();

  • setContentView is amethod of Activity class. So to call you the class should be a subclass of Activity class.

Android Database Table export to CSV – Part 1


The best way to export data out of Android Sqlite database is create a copy the DB file into the SDCard.

Copy the Android database into DB File –

Advantages –

  • The import and export of the database would be a lot easy.
  • All tables could be backed up using a single file. ( This can have the backup of the user defined configuration of the program also ).

Disadvantages –

  • We need special tools to read this DB file which makes it difficult to be portable.

Copy a table into CSV file –

Advantages –

  • Only data required by the user can be given.
  • It is the more readable and portable format.

In my application I have decided to have both this methods. Will be updating soon.

Create a Alert Dialog in Android


Dialogs are generally used to display the the intermediate data like Status Messages, Warning Messages or Error Messages.

In this post we will go through how to create a Dialog Box in Android App development –

  • Create a function will call the Alert Dialog (AddEx is the activity. You can use your activity name over here ).
  1: private void displayDialog(String str1)
  2: {
  3:   AlertDialog.Builder builder = new AlertDialog.Builder(AddEx.this);
  4:   builder.setMessage(str1);
  5:   builder.setCancelable(false);
  6:   builder.setTitle("Status");
  7:   builder.setPositiveButton("OK", new DialogInterface.OnClickListener() {
  8:          @Override
  9:          public void onClick(DialogInterface dialog, int id) {
 10:           AddEx.this.finish();
 11:          }
 12:        });
 13:   AlertDialog alert = builder.create();
 14:   alert.show();
 15: }

  • Call this function anywhere in your activity class.
  1:  if(status == true)
  2:  {
  3:   displayDialog("Completed Successfully");
  4:  }
  5:  else
  6:  {
  7:    displayDialog("Error inserting into Table");
  8:  }
  • In the Alert Dialog you can also implement builder.setNegativebutton() method to have “CANCEL” part included in the dialog. Implementation is similar to setPositiveButton.