logo
down
shadow

How to create SQL query with each column name and its data type


How to create SQL query with each column name and its data type

By : cindynn
Date : November 18 2020, 03:01 PM
will help you I have a CSV file that contains 2 columns: column names of data base and their data types. I wish to write a python code that creates SQL query with each column name and its data type. Earlier I was using a code that generalized every data type to varchar. , if your csv is like below (table_schema.csv):
code :
name, type
Setting, text
Value, integer
import csv

with open('table_schema.csv', 'rt', encoding='utf-8') as f:
    sql_parts = [] 
    headers = ['name', 'type']
    schema = csv.DictReader(f, headers)

    for col_def in list(schema)[1:]:# get rid of header line
        sql_parts.append('{} {}'.format(
            col_def['name'].strip(),
            col_def['type'].strip()))

    sql = 'create table settings(' + ', '.join(sql_parts) + ');'
    print(sql)

# or using reader instead of DictReader

with open('table_schema.csv', 'rt', encoding='utf-8') as f:
    sql_parts = []
    columns = 2
    schema = csv.reader(f)

    for row in list(schema)[1:]: # get rid of header line
        if row != []:
            sql_parts.append('{} {}'.format(row[0].strip(), row[1].strip()))

    sql = 'create table settings(' + ', '.join(sql_parts) + ');'
    print(sql)
create table settings(Setting text, Value integer);


Share : facebook icon twitter icon
How to change the data type of a column without dropping the column with query?

How to change the data type of a column without dropping the column with query?


By : Trevor Cowley
Date : March 29 2020, 07:55 AM
With these it helps If ALTER COLUMN doesn't work.
It is not unusual for alter column to fail because it cannot make the transformation you desire. In this case, the solution is to create a dummy table TableName_tmp, copy the data over with your specialized transformation in the bulk Insert command, drop the original table, and rename the tmp table to the original table's name. You'll have to drop and recreate the Foreign key constraints and, for performance, you'll probably want to create keys after filling the tmp table.
Query to retrieve Text data type column value based on MAX value in other column

Query to retrieve Text data type column value based on MAX value in other column


By : user1640209
Date : March 29 2020, 07:55 AM
Any of those help I have a table with following columns and data
code :
with c as(
  select FldId, Rev, Words
    , row_number() over (partitioned by FldId order by Rev desc) as rn
  from table)
select FldId, Rev, Words 
from c
where rn =1;
SQL query: Trying to create data as a column with the values based on another column. No Pivot or Subquery

SQL query: Trying to create data as a column with the values based on another column. No Pivot or Subquery


By : user6354102
Date : March 29 2020, 07:55 AM
I wish this help you I have been trying to create a new table based on another table. I have an issue with finding the appropriate query to make data from one column be a new field and have that be based on another column. NOTE: I cant use pivot or select sub-queries. , This is a class pivot idiom:
code :
create table table2
select id,
       max(case when type = 'start_date' then type_value end) start_date,
       max(case when type = 'order_amount' then type_value end) order_amount,
       max(case when type = 'account' then type_value end) account
from table1
group by id
Show Columns query showing Column Data type not Column Value

Show Columns query showing Column Data type not Column Value


By : Umar
Date : March 29 2020, 07:55 AM
I wish did fix the issue. Even if the columns of your table change, the columns names of the result come from the query SHOW COLUMNS FROM table. But, you could use mysqli_fetch_array() to get both:
code :
$rs = mysqli_query($con, "SHOW COLUMNS FROM employee");
while ($row = mysqli_fetch_array($rs)) {
    // print_r($row) ;
    // echo $row['Field'] ; // Name of table column.
    echo $row['Type'] ;
}
//1. get columns.

$cols = [] ;
$rs = mysqli_query($con, "SHOW COLUMNS FROM employee");
while ($row = mysqli_fetch_assoc($rs)) {
    $cols[] = $row['Field'];
}

//2. get values :

$rs = mysqli_query($con, "SELECT * FROM employee");
while($rows = mysqli_fetch_array($rs)){

    //leftside cols
    $pdf->SetFontSize(10);
    $pdf->SetXY(10,50);
    $pdf->Cell(50,10,$cols[0]. ":",0,0,'L');
    $pdf->Cell(30,10,$code,0,1,'C');

    $pdf->Cell(50,10,$cols[1]. ":",0,0,'L');
    $pdf->Cell(30,10,$designation,0,1,'C');

    $pdf->Cell(50,10,$rows[0]. ":",0,0,'L');
    $pdf->Cell(30,10,$department,0,1,'C');
}
Create table with a column of data-type Date creates a column with data-type Timestamp

Create table with a column of data-type Date creates a column with data-type Timestamp


By : user1818354
Date : March 29 2020, 07:55 AM
it should still fix some issue If your Db2 database was created in Oracle Compatibility mode, then DATE columns are implemented as TIMESTAMP(0) columns to match what Oracle does.
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.porting.doc/doc/r0053667.html
Related Posts Related Posts :
  • How to exit/terminate a job earlier and handle the raised exception in apscheduler?
  • python, print intermediate values while loop
  • python to loop over yaml config
  • D3.js is not recognized by PyCharm
  • Access the regularization paths obtained from ElasticNetCV in sklearn
  • Pattern table to Pandas DataFrame
  • Get the earliest date from a column (Python Pandas) after csv.reader
  • Get SystemError: Parent module '' not loaded, cannot perform relative import when trying to import numpy in a Cython Ext
  • Bash or Python : Append and prepend a string recursively in all .tex files
  • Changing a certain index of boolean list of lists change others, too
  • complex dataframe filtering request on the last occurence of a value in Panda/Python [EDIT]
  • How to repeatedly get the contents of a Text widget every loop with tkinter?
  • How to call the tornado.queues message externally
  • How can I use regex in python so that characters not included are disallowed?
  • Discarding randmly scattered empty spaces in pandas data frame
  • Get sums grouped by date by same column filtered by 2 conditions
  • Element disappears when I add an {% include %} tag inside my for loop
  • Django Rest Framework with either a slug or a pk lookup field for the DetailAPIView
  • Flask doesn't stream on Lambda
  • Generate all permutations of fixed length where the elements come from two different sets
  • Making function for calculating distance
  • How to handle multiprocessing based on the limit of CPU's
  • Django - static files is not working
  • Remove x axis and y axis black lines with matplotlib
  • tkinter: assigning multiple functions to one button
  • flask-jwt-extended: Fake Authorization Header during testing (pytest)
  • Setting pandas dataframe value based on row and column conditions
  • swig char ** as a pointer to a char *
  • Confusion over `a` and `b` attributes from scipy.stats.uniform
  • How can I do groupy.apply() without sort my index?
  • Querying Google Cloud datastore with ancestor not returning anything
  • Read value from one thread in Python: queue or global variable?
  • Django - context process query being repeated 102 times
  • Convert a list of images and labels to np array to train tensorflow
  • Lambda not supporting NLTK file size
  • Numpy ndarray image pixel mean for pixel values greater than zero: Normalizing image
  • Understanding output of np.corrcoef for two matrices of different sizes
  • Finding longest perfect match between two strings
  • what is wrong with my cosine similarity? Tensorflow
  • How to manage user content in django?
  • Receiving unsupported operand error while comparing random number and user input.
  • How to wrap the process of creating start_urls in scrapy?
  • How to mark 'duplicated sequence' in pandas?
  • Boolean indexing on multidimensionnal array
  • Unmodified column name index in patsy
  • Cleaner way to unpack nested dictionaries
  • Importing a python module to enable a script to be run from command line
  • Maya Python read and set optionMenu value via variable
  • How can I bind a property to another property in Kivy?
  • Python extracting specific line in text file
  • How to implement n-body simulation with pymunk?
  • Python / matplotlib: print to resolution and without white space / borders / margins
  • Sum up the second value from one dictionary with all values from another dictionary
  • Robot Framework: Open a chrome browser without launching URL
  • Generate inline Bokeh scatterplots in Jupyter using a for loop
  • Group list of dictionaries python
  • Efficient way to apply multiple Boolean mask to set values in a column using pandas
  • Lazy evaluation of a Python dictionary
  • id of xpath is getting changed every time in selenium python 2.7 chrome
  • Matplotlib RuntimeWarning displaying a 3D plot
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org